| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Virtual Private Database
A copy of this was sent to "Tim Romano" <tim_at_ot.com>
(if that email address didn't require changing)
On Wed, 22 Dec 1999 08:22:25 -0500, you wrote:
>Thank you once again. Another question comes to mind regarding column-level
>security applied to a table that has been set up for row-level security
>using DBMS_RLS.
>
>Assume a PARTS table has been set up for row-level security, by vendor name.
>ACME CAPACITORS sees only their capacitors, ZENITH SEMICONDUCTOR sees only
>their chips. User ACME wants to give user ZENITH access to a subset of the
>columns relating to its capacitors, e.g. everything but the ACTUAL_MFG_COST
>column; and so user ACME creates a VIEW against the PARTS table:
>
>CREATE VIEW RETAIL_CAPACITORS as
>Select PART_NO, DESCRIPTION, SUGGESTED_RETAIL_COST
>from PARTS
>
>GRANT SELECT ON RETAIL_CAPACITORS TO ZENITH
>
>
>Now what happens when user ZENITH selects against the RETAIL_CAPACITORS
>view?
>
> Select * from RETAIL_CAPACITORS
>
>Does user ZENITH get a list of ACME capacitors, or nothing at all?
>
>
>Tim Romano
>
>
if the RLS is attached to a table -- any VIEW built on that table will 'inherit' the RLS as well.
In this case, if you have RLS policy that uses the current USERNAME to figure out the rows they can see, then the user ZENITH would see *their* data (their semiconductors) not ACME's capacitors.
You cannot subvert or get around RLS. It is similar to mandatory access controls -- MAX (a B level of security term) which always override discrentionary access controls -- DAC (a C2 security term).
User ACME cannot get around the system policy that ZENITH may see only their data. The system policy would have to support this.
Here is an example:
tkyte_at_ORA8IDEV.WORLD> connect tkyte/tkyte
Connected.
tkyte_at_ORA8IDEV.WORLD>
tkyte_at_ORA8IDEV.WORLD> drop user acme cascade;
User dropped.
tkyte_at_ORA8IDEV.WORLD> drop user zenith cascade;
User dropped.
tkyte_at_ORA8IDEV.WORLD>
tkyte_at_ORA8IDEV.WORLD> grant connect, resource to acme identified by acme;
Grant succeeded.
tkyte_at_ORA8IDEV.WORLD> grant connect, resource to zenith identified by zenith;
Grant succeeded.
tkyte_at_ORA8IDEV.WORLD> tkyte_at_ORA8IDEV.WORLD> tkyte_at_ORA8IDEV.WORLD> REM Create the demo data for this example... tkyte_at_ORA8IDEV.WORLD> tkyte_at_ORA8IDEV.WORLD> drop table parts;
Table dropped.
tkyte_at_ORA8IDEV.WORLD> create table parts
2 ( part_no int,
3 company varchar2(20),
4 description varchar2(20),
5 suggested_retail_cost int,
6 actual_mfg_cost int
7 );
Table created.
tkyte_at_ORA8IDEV.WORLD>
tkyte_at_ORA8IDEV.WORLD> grant all on parts to acme with grant option;
Grant succeeded.
tkyte_at_ORA8IDEV.WORLD> grant all on parts to zenith with grant option;
Grant succeeded.
tkyte_at_ORA8IDEV.WORLD> tkyte_at_ORA8IDEV.WORLD> tkyte_at_ORA8IDEV.WORLD> insert into parts values ( 1, 'ACME CAPACITORS', 'xxxx',100, 50 );
1 row created.
tkyte_at_ORA8IDEV.WORLD> insert into parts values ( 2, 'ACME CAPACITORS', 'yyyy', 200, 75 );
1 row created.
tkyte_at_ORA8IDEV.WORLD>
tkyte_at_ORA8IDEV.WORLD> insert into parts values ( 3, 'ZENITH SEMICONDUCTOR',
'aaaa', 10, 5 );
1 row created.
tkyte_at_ORA8IDEV.WORLD> insert into parts values ( 4, 'ZENITH SEMICONDUCTOR', 'bbbb', 20, 7 );
1 row created.
tkyte_at_ORA8IDEV.WORLD> tkyte_at_ORA8IDEV.WORLD> tkyte_at_ORA8IDEV.WORLD> tkyte_at_ORA8IDEV.WORLD> tkyte_at_ORA8IDEV.WORLD> REM start with a policy that filters based on the current tkyte_at_ORA8IDEV.WORLD> REM connected user tkyte_at_ORA8IDEV.WORLD> tkyte_at_ORA8IDEV.WORLD> create or replace function my_security_policy( obj_schema in varchar2, 2 obj_name in varchar2 )3 return varchar2
6 if ( user = 'ACME' ) 7 then 8 return ' company = ''ACME CAPACITORS'' '; 9 elsif ( user = 'ZENITH' ) 10 then 11 return ' company = ''ZENITH SEMICONDUCTOR'' '; 12 else 13 return ' 1=0 '; 14 end if;
Function created.
tkyte_at_ORA8IDEV.WORLD> begin
2 dbms_rls.add_policy 3 ( object_schema => user, 4 object_name => 'parts', 5 policy_name => 'my_policy', 6 function_schema => user, 7 policy_function => 'my_security_policy', 8 statement_types => 'select, insert, update, delete' );9 end;
PL/SQL procedure successfully completed.
tkyte_at_ORA8IDEV.WORLD> tkyte_at_ORA8IDEV.WORLD> tkyte_at_ORA8IDEV.WORLD> tkyte_at_ORA8IDEV.WORLD> REM As acme, show what data we see, create a view andgrant
tkyte_at_ORA8IDEV.WORLD> REM on that to zenith... tkyte_at_ORA8IDEV.WORLD> tkyte_at_ORA8IDEV.WORLD> connect acme/acmeConnected.
PART_NO COMPANY DESCRIPTION SUGGESTED_RETAIL_COST ACTUAL_MFG_COST
---------- -------------------- -------------------- ---------------------
---------------
1 ACME CAPACITORS xxxx 100
50
2 ACME CAPACITORS yyyy 200
75
tkyte_at_ORA8IDEV.WORLD> create or replace view parts_view as select part_no,
company, description,
2 suggested_retail_cost from tkyte.parts;
View created.
tkyte_at_ORA8IDEV.WORLD> grant select on parts_view to zenith;
Grant succeeded.
tkyte_at_ORA8IDEV.WORLD> tkyte_at_ORA8IDEV.WORLD> tkyte_at_ORA8IDEV.WORLD> tkyte_at_ORA8IDEV.WORLD> REM Zenith still sees only zenith data -- that is thepolicy
PART_NO COMPANY DESCRIPTION SUGGESTED_RETAIL_COST ACTUAL_MFG_COST
---------- -------------------- -------------------- ---------------------
---------------
3 ZENITH SEMICONDUCTOR aaaa 10
5
4 ZENITH SEMICONDUCTOR bbbb 20
7
tkyte_at_ORA8IDEV.WORLD> select * from acme.parts_view;
PART_NO COMPANY DESCRIPTION SUGGESTED_RETAIL_COST
---------- -------------------- -------------------- ---------------------
3 ZENITH SEMICONDUCTOR aaaa 10
4 ZENITH SEMICONDUCTOR bbbb 20
--
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 Wed Dec 22 1999 - 10:02:00 CST
 |
 |