Home » SQL & PL/SQL » SQL & PL/SQL » DBMS_RLS
DBMS_RLS [message #187864] Wed, 16 August 2006 01:32 Go to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
hi!
im testing on dbms_rls, i just dont why it wont work on INSERT statements =)



SQL> desc locktab
 Name                   Null?    Type
 ---------------------- -------- ---------------
 OBJECT_NAME            NOT NULL VARCHAR2(30)
 OBJECT_TYPE                     VARCHAR2(18)



SQL> select * from locktab;

OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------
one                            onet
two                            twot
three                          threet

SQL> create function lockme (object_schema varchar2, object_name varchar2)
  2  return varchar2
  3  as
  4  begin
  5     return '1 = 2';
  6  end;
  7  /

Function created.

SQL> exec dbms_rls.add_policy('RHANI', 'LOCKTAB', 'LOCKIT', 'RHANI', 'LOCKME','INSERT, UPDATE');

PL/SQL procedure successfully completed.


SQL> select policy_name
  2  ,sel,ins,upd,del,enable
  3  from dba_policies
  4  /

POLICY_NAME                    SEL INS UPD DEL ENA
------------------------------ --- --- --- --- ---
LOCKIT                         NO  YES YES NO  YES

SQL> update locktab
  2  set object_type = 'towt'
  3  where object_name = 'two'
  4  /

0 rows updated.

SQL> ed
Wrote file afiedt.buf

  1  update locktab
  2  set object_type = 'towt'
  3* where object_name = 'three'
SQL> /

0 rows updated.

SQL> insert into locktab values('four', 'fourt');

1 row created.

SQL> insert into locktab
  2  select 'four', 'fourt'
  3  from dual
  4  /

1 row created.

SQL> select * from locktab
  2  /

OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------
two                            twot
three                          threet
one                            onet
four                           fourt
four                           fourt

SQL>




why does the insert isnt applied the policy? thanks sir/mam =)



got it working! the check update option have to be TRUE.

[Updated on: Wed, 16 August 2006 02:48]

Report message to a moderator

Re: DBMS_RLS [message #188705 is a reply to message #187864] Mon, 21 August 2006 04:48 Go to previous message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
You need to set update_check=>true. Example:

SQL> CREATE TABLE locktab (
  2    object_name VARCHAR2(30) NOT NULL,
  3    object_type VARCHAR2(18)
  4  );

Table created.

SQL> INSERT INTO locktab VALUES ('one', 'onet');

1 row created.

SQL>
SQL> CREATE OR REPLACE FUNCTION lockme(object_schema VARCHAR2, object_name VARCHAR2)
  2  RETURN VARCHAR2
  3  AS
  4  BEGIN
  5     RETURN '1 = 2';
  6  END;
  7  /

Function created.

SQL>
SQL> EXEC dbms_rls.add_policy('SCOTT', 'LOCKTAB', 'LOCKIT', 'SCOTT', 'LOCKME','INSERT,UPDATE', TRUE);

PL/SQL procedure successfully completed.

SQL>
SQL> SELECT policy_name, sel, ins, upd, del, enable
  2    FROM user_policies
  3  /

POLICY_NAME                    SEL INS UPD DEL ENA
------------------------------ --- --- --- --- ---
LOCKIT                         NO  YES YES NO  YES

SQL>
SQL> UPDATE locktab SET object_type = 'XXX' WHERE object_name = 'one';

0 rows updated.

SQL>
SQL> INSERT INTO locktab SELECT 'two', 'twot' from dual;
INSERT INTO locktab SELECT 'two', 'twot' from dual
            *
ERROR at line 1:
ORA-28115: policy with check option violation


SQL> SELECT * FROM locktab;

OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------
one                            onet
Previous Topic: calling a function from a proc
Next Topic: Image Pblm
Goto Forum:
  


Current Time: Fri Dec 09 07:45:58 CST 2016

Total time taken to generate the page: 0.23335 seconds