Re: dbms_rls and 815E on NT

A copy of this was sent to zaybx_at_hotmail.com (gigadud) (if that email address didn't require changing) On 19 Jan 2000 15:35:37 -0600, you wrote:

>Does anyone know if there is a way using the dbms_rls ( Row level
>Security ) on a insert how can i set the value of a column. This
>column is the one i am using for ny predicate( where statement )
>
 

you use dbms_rls to restrict access to data.

You can use a trigger to supply non-defaultable values.

You would code:

create trigger my_trigger
before insert on T for each row
begin
  :new.dept_id := Your_Function_That_Returns_The_Correct_id_For_This_User; end;
/

it happens on the back end, will not affect the client.  

>example:
>
>Function DEMO.MY_FUNCTION( p_schema IN Varchar2, p_object IN Varchar2
>)
> RETURN Varchar2 IS
>
>BEGIN
>
> if ( user = 'SYS' ) then
> Return '';
> else
> Return ( 'DEPT_ID = 12' );
> end if ;
>
>
>END;
>
>
>When running a insert against a policy that uses this function i would
>like to set the DEPT_ID = 12. The dept isn't being set in the client
>and we are trying to use this feature so we don't have to modify the
>client code. I would like to do this on the backend.
>
>Any thoughts would be appreciatted.
>
>-Don-

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Jan 20 2000 - 10:42:45 CST