| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Fine Grained Access Control (FGCA)
If by "fine-grained access control" you mean what I call "row-level
security", then here is a proof-of-concept that I adapted to show 1 function
for two different policies on two different tables.
SQL> create table mug
2 (mug_id number,
3 mug_shape varchar2 (10), 4 mug_capacity number
SQL> create table china
2 (plate_id number,
3 pattern varchar2 (16), 4 plate_diameter number
SQL> column schema_name new_value test_tbl_owner SQL> select user as schema_name from dual ; SCHEMA_NAME
6 if owner = '&&test_tbl_owner' and object_name = 'MUG'
7 then
8 if sys_context ('userenv', 'session_user') != 'SYSTEM'
9 then
10 v_policy := 'mug_id < 100' ;
11 end if ;
12 end if ;
13 if owner = '&&test_tbl_owner' and object_name = 'CHINA'
14 then
15 if sys_context ('userenv', 'session_user') != 'SYSTEM'
16 then
17 v_policy := 'pattern = ''Kent Gardens''' ;
18 end if ;
19 end if ;
20 return v_policy ;
SQL> exec dbms_rls.add_policy (object_schema => '"&&test_tbl_owner"', -
> object_name => 'MUG', -
> policy_name => 'MUG_POLICY', -
> function_schema => '"&&test_tbl_owner"', -
> policy_function => 'COLLECTIBLE_POLICY', -
> statement_types => 'SELECT')
Procedure PL/SQL terminee avec succes.
SQL> exec dbms_rls.add_policy (object_schema => '"&&test_tbl_owner"', -
> object_name => 'CHINA', -
> policy_name => 'CHINA_POLICY', -
> function_schema => '"&&test_tbl_owner"', -
> policy_function => 'COLLECTIBLE_POLICY', -
> statement_types => 'SELECT, INSERT, UPDATE,
DELETE', -
> update_check => true, -
> enable => true)
Procedure PL/SQL terminee avec succes.
SQL> insert into mug (mug_id, mug_shape, mug_capacity)
2 values (1, 'ROUND', 2) ;
1 ligne creee.
SQL> commit ;
Validation effectuee.
SQL> insert into mug (mug_id, mug_shape, mug_capacity)
2 values (101, 'SQUARE', 4) ;
1 ligne creee.
SQL> commit ;
Validation effectuee.
SQL> -- SQL> -- this insert will fail due to security policy SQL> -- SQL> insert into china (plate_id, pattern, plate_diameter)2 values (1, 'Azalea', 25) ;
*
ERREUR a la ligne 1 :
ORA-28115: regle comportant une violation d'option de controle
SQL> commit ;
Validation effectuee.
SQL> insert into china (plate_id, pattern, plate_diameter)
2 values (2, 'Kent Gardens', 27) ;
1 ligne creee.
SQL> commit ;
Validation effectuee.
SQL> -- SQL> -- two rows in table, but only one returned because of SQL> -- security policy SQL> -- SQL> select * from mug ;
MUG_ID MUG_SHAPE MUG_CAPACITY
--------- ---------- ------------
1 ROUND 2
SQL> --
SQL> -- system can insert and view rows that other users can't
SQL> --
SQL> insert into "&&test_tbl_owner". china (plate_id, pattern,
plate_diameter)
2 values (1, 'Azalea', 25) ;
ancien 1 : insert into "&&test_tbl_owner". china (plate_id, pattern,
plate_diameter)
nouveau 1 : insert into "JRK". china (plate_id, pattern, plate_diameter)
1 ligne creee.
SQL> commit ;
Validation effectuee.
SQL> select * from "&&test_tbl_owner". china ; ancien 1 : select * from "&&test_tbl_owner". china nouveau 1 : select * from "JRK". china PLATE_ID PATTERN PLATE_DIAMETER
--------- ---------------- --------------
2 Kent Gardens 27
1 Azalea 25
SQL> select * from "&&test_tbl_owner". mug ; ancien 1 : select * from "&&test_tbl_owner". mug nouveau 1 : select * from "JRK". mug
MUG_ID MUG_SHAPE MUG_CAPACITY
--------- ---------- ------------
1 ROUND 2
101 SQUARE 4
SQL> --
SQL> -- the original user won't see the row inserted by system
SQL> --
SQL> --
SQL> -- two rows in table, but only one returned because of
SQL> -- security policy
SQL> --
SQL> select * from china ;
PLATE_ID PATTERN PLATE_DIAMETER
--------- ---------------- --------------
2 Kent Gardens 27
-----Original Message-----
I too am working with FGAC and agree that documentation is not very helpful! I did download an Oracle whitepaper, it is more high-level but may help - labelsecurity how to.pdf.
I would try to offer some advice, however, my understanding is that each FGAC policy applies to a single object and a single function is associated with each policy. Now you can use this same function for another policy, however, this sounds different than what you are describing below. In my situation, each function will be different because the underlying table structure is different.
-----Original Message-----
I am trying to setup FGCA for our customers on their reporting system. I have got this working for a single table, however, when a policy needs to be added for multiple tables. It fails returning no rows from the second table. Oracle documentation is not very helpful this arena. Can anyone provide any pointers to white papers or documents on this subject.
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: Jacques.Kilchoer_at_quest.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Wed Mar 05 2003 - 19:08:57 CST
 |
 |