Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Bill.

Asked: January 07, 2004 - 9:20 am UTC

Answered by: Tom Kyte - Last updated: April 27, 2006 - 3:27 pm UTC

Category: Database - Version: 9.2.0

Viewed 1000+ times

You Asked

Tom,
Happy New Year. I have been working on using Portal with OLS to secure data. One of the articles I have read indicates that one should be careful to insure that a given session returns a constant predicate. This is to avoid potential problems with cached cursors and 'old' predicates. My question is, if the developer has a session terminate coded in prior to a "role" switch in the application context (which would initiate a new session) would this bypass the stated concerns regarding 'old' predicates? Role in this case refers to an application context setting used by OLS to restrict access. I am a little puzzled as I thought the predicate attachment was done dynamically at each query execution, so maybe you could elaborate on how predicate attachment fits into the normal process of execution and whether this has changed since 8i. We are currently running Oracle 9.2.0.3.0 with OLS.

Thanks very much!

and we said...

this is no longer an issue as of 817 of the database.

If you change a context, it invalidates the session cursor cache properly so the affected queries WILL be reparsed.




and you rated our response

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

Many Thanks for a reply on what looked like a very busy day for you!

January 09, 2004 - 7:58 am UTC

Reviewer: Bill Schwartz from Montpelier, VT

Tom,

Thanks much! I thought that it was probably no longer an issue as I could not find any relative notes later than the one mentioned, but I hate to assume anything.

Tom - Need to ask one question regarding OLS...

September 24, 2004 - 1:11 pm UTC

Reviewer: Bill from Vermont USA

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
PL/SQL Release 9.2.0.3.0 - Production
CORE    9.2.0.3.0       Production
TNS for Linux: Version 9.2.0.3.0 - Production
NLSRTL Version 9.2.0.3.0 - Production

Using OLS

I have the following situation:

Application users connect via proxy schema (which has privileges on other schemas containing application data).

Label Security policy in effect on main application tables in other schemas.

Application contains one materialized view, refresh fast on commit, enable query rewrite.

From the docs (Oracle 9i Label Security Administrator's Guide Release 2 (9.2)) it appears that MVs are 
treated as tables, not views, so even if the underlying tables are protected via an OLS policy, the MV 
is not.  Do you see any potential issues with applying a policy to the MV?  This is NOT a remote 
replication MV, it exists in the same schema as the underlying tables.  Schema owner has full privilege on 
OLS policy.

My concern was with the fast refresh on commit with query rewrite enabled.  My goal is to have the 
MV fully created (and fully refreshed) but filtered on select.

I realize that you will be incommunicado for a while, but I was hoping to get this in via Ask A Question and this seems like my last chance for almost 2 weeks to ask you.  Enjoy your time away!

Thanks for your input! 

Tom Kyte

Followup  

September 26, 2004 - 9:24 am UTC

(David Knox, Chief Security Engineer for Oracle's Information Assurance Center, provided this response)

The good news is that you can get the benefits of OLS and the MV at the same time. However, you do not have to apply the OLS policies to the MV; you only need to ensure that the MV definition includes the OLS label column when it is created.

For readers following along, here is how it works (more details can be found in the book "Effective Oracle Database 10g Security By Design" Osborne, 2004)

First I will create a base table to which i will apply my OLS policy. This is done in a schema I created called SEC_MGR:

sec_mgr@KNOX10G> -- Create a big table
sec_mgr@KNOX10G> CREATE TABLE big_tab AS
2 SELECT * FROM all_objects;

Table created.

sec_mgr@KNOX10G> -- Grant privileges to LBACSYS
sec_mgr@KNOX10G> GRANT UPDATE ON big_tab TO lbacsys;

Grant succeeded.

sec_mgr@KNOX10G> -- Grant privileges to SCOTT
sec_mgr@KNOX10G> GRANT SELECT ON big_tab TO scott;

Grant succeeded.

Next, I create an OLS policy and apply it to the table. For those unfamiliar with this, the code is presented here. The label policy simply enforces row access to that of the object owner (i.e., user's only see their own objects)

sec_mgr@KNOX10G> CONN lbacsys/lbacsys
Connected.

lbacsys@KNOX10G> /*** ====================================================================
lbacsys@KNOX10G> ** Step 1: Create the Label Security Policy.
lbacsys@KNOX10G> ** ====================================================================
lbacsys@KNOX10G> ***/
lbacsys@KNOX10G> -- drop it if it already exists
lbacsys@KNOX10G> EXEC sa_sysdba.drop_policy(policy_name => 'OLS_MV');

PL/SQL procedure successfully completed.

lbacsys@KNOX10G> BEGIN
2 sa_sysdba.create_policy (policy_name => 'OLS_MV',
3 column_name => 'olslabel'
4 );
5 END;
6 /

PL/SQL procedure successfully completed.

lbacsys@KNOX10G> /*** ====================================================================
lbacsys@KNOX10G> ** Step 2: Define the Components of the Labels.
lbacsys@KNOX10G> ** Define the levels, compartments, and groups which form the components
lbacsys@KNOX10G> ** of the 'OLS_MV' policy's labels.
lbacsys@KNOX10G> ** ====================================================================
lbacsys@KNOX10G> ***/
lbacsys@KNOX10G>
lbacsys@KNOX10G> REM ====================================================================
lbacsys@KNOX10G> REM Creating a single level called 'FLAT'
lbacsys@KNOX10G> REM ====================================================================
lbacsys@KNOX10G>
lbacsys@KNOX10G> BEGIN
2 sa_components.create_level (policy_name => 'OLS_MV',
3 long_name => 'Single level',
4 short_name => 'FLAT',
5 level_num => 1000
6 );
7 END;
8 /

PL/SQL procedure successfully completed.

lbacsys@KNOX10G> REM ====================================================================
lbacsys@KNOX10G> REM Creating OLS Groups; one group for each username in database.
lbacsys@KNOX10G> REM ====================================================================
lbacsys@KNOX10G>
lbacsys@KNOX10G> DECLARE
2 i NUMBER := 10;
3 BEGIN
4 FOR rec IN (SELECT username
5 FROM all_users
6 WHERE username NOT IN ('SYS', 'SYSTEM', 'LBACSYS'))
7 LOOP
8 sa_components.CREATE_GROUP (policy_name => 'OLS_MV',
9 long_name => rec.username,
10 short_name => rec.username,
11 group_num => i,
12 parent_name => NULL
13 );
14 sa_label_admin.create_label (policy_name => 'OLS_MV',
15 label_tag => i,
16 label_value => 'FLAT::'
17 || rec.username
18 );
19 sa_user_admin.set_user_labels (policy_name => 'OLS_MV',
20 user_name => rec.username,
21 max_read_label => 'FLAT::'
22 || rec.username
23 );
24 i := i + 10;
25 END LOOP;
26 END;
27 /

PL/SQL procedure successfully completed.

lbacsys@KNOX10G> COMMIT ;

Commit complete.

lbacsys@KNOX10G> /*** ====================================================================
lbacsys@KNOX10G> ** Apply OLS to base table. This adds the label column,
lbacsys@KNOX10G> ** but will not enforce access
lbacsys@KNOX10G> ** ====================================================================
lbacsys@KNOX10G> **/
lbacsys@KNOX10G>
lbacsys@KNOX10G> BEGIN
2 sa_policy_admin.apply_table_policy (policy_name => 'OLS_MV',
3 schema_name => 'SEC_MGR',
4 table_name => 'big_tab',
5 table_options => 'NO_CONTROL'
6 );
7 END;
8 /

PL/SQL procedure successfully completed.

lbacsys@KNOX10G> -- Label the data by setting the label equal to the owner's.
lbacsys@KNOX10G> -- This makes it easy to test the enforcement
lbacsys@KNOX10G> UPDATE sec_mgr.big_tab
2 SET olslabel = char_to_label ('OLS_MV', 'FLAT::' || owner)
3 WHERE owner NOT IN ('SYS', 'SYSTEM', 'LBACSYS', 'PUBLIC');

7455 rows updated.

lbacsys@KNOX10G> COMMIT ;

Here is a critical point - creating the MV. The important thing, as with all MV's, is that we ensure we capture all the columns we need. In this case, the OLS label column, called OLSLABEL in this example, will be needed.
lbacsys@KNOX10G> CONN sec_mgr/oracle10g
Connected.

GLOBAL_NAME
-----------------------------------------------------------------
sec_mgr@KNOX10G

sec_mgr@KNOX10G> DROP MATERIALIZED VIEW big_tab_aggs;

Materialized view dropped.

sec_mgr@KNOX10G> -- Create a materialized view on table
sec_mgr@KNOX10G> CREATE MATERIALIZED VIEW big_tab_aggs
2 BUILD IMMEDIATE
3 REFRESH ON COMMIT
4 ENABLE QUERY REWRITE AS
5 SELECT owner, COUNT(*), olslabel FROM big_tab GROUP BY owner, olslabel
6 /

Materialized view created.

sec_mgr@KNOX10G> ANALYZE TABLE big_tab_aggs COMPUTE STATISTICS;

Table analyzed.

sec_mgr@KNOX10G> SET timing on
sec_mgr@KNOX10G> -- note that NULL OLS labels mean data is restricted by default
sec_mgr@KNOX10G> SELECT owner, COUNT (*), olslabel
2 FROM big_tab
3 GROUP BY owner, olslabel
4 /

OWNER COUNT(*) OLSLABEL
------------------------------ ---------- ----------
DHS 3 40
SYS 21513
XDB 302 360
DEMO 20 260
UECF 1 70
DMSYS 867 470
JFCOM 5 180
MDSYS 572 400
OUTLN 7 490
SCOTT 18 210
TKYTE 15 30
WKSYS 351 320
WMSYS 226 450
CTXSYS 337 390
DBSNMP 13 460
EXFSYS 166 440
ORDSYS 1482 430
PUBLIC 19144
SYSMAN 1233 340
SYSTEM 419
LBACSYS 223
OLAPSYS 703 380
SEC_MGR 52 270
WK_TEST 45 370
DATA_OWNER 2 90
ORDPLUGINS 38 420
FLOWS_FILES 11 250
FLOWS_010500 978 230
SI_INFORMTN_SCHEMA 8 410

29 rows selected.

Elapsed: 00:00:00.19
sec_mgr@KNOX10G> -- show the MV is rewriting the SQL
sec_mgr@KNOX10G> SET autotrace traceonly
sec_mgr@KNOX10G> SELECT owner, COUNT (*), olslabel
2 FROM big_tab
3 GROUP BY owner, olslabel
4 /

29 rows selected.

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
0
SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=29 Bytes=377)


1 0
MAT_VIEW REWRITE ACCESS (FULL) OF 'BIG_TAB_AGGS' (MAT_VIEW REWR
ITE) (Cost=3 Card=29 Bytes=377)


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
1191 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
29 rows processed

sec_mgr@KNOX10G> SET autotrace off
sec_mgr@KNOX10G> SET timing off

Next, we apply the OLS policy to the base table (not to the MV). Then we'll test by issuing queries. What you will notice is that the queries which are transparently re-written by OLS can still take advantage of the MV. OLS is enforced on READ_CONTROL to match your requirement of "filtering on select"

sec_mgr@KNOX10G> CONN lbacsys/lbacsys
Connected.

lbacsys@KNOX10G> REM ====================================================================
lbacsys@KNOX10G> REM Enforce read access on table
lbacsys@KNOX10G> REM ====================================================================
lbacsys@KNOX10G>
lbacsys@KNOX10G> BEGIN
2 sa_policy_admin.remove_table_policy (policy_name => 'OLS_MV',
3 schema_name => 'SEC_MGR',
4 table_name => 'big_tab'
5 );
6 -- Apply policy to base table
7 sa_policy_admin.apply_table_policy (policy_name => 'OLS_MV',
8 schema_name => 'SEC_MGR',
9 table_name => 'big_tab',
10 table_options => 'READ_CONTROL'
11 );
12 END;
13 /

PL/SQL procedure successfully completed.

lbacsys@KNOX10G> --Show OLS is working
lbacsys@KNOX10G> CONN sec_mgr/oracle10g
Connected.

sec_mgr@KNOX10G> SELECT owner, COUNT (*)
2 FROM sec_mgr.big_tab
3 GROUP BY owner
4 /

OWNER COUNT(*)
------------------------------ ----------
SEC_MGR 52

sec_mgr@KNOX10G> CONN scott/tiger
Connected.

scott@KNOX10G> SELECT owner, COUNT (*)
2 FROM sec_mgr.big_tab
3 GROUP BY owner
4 /

OWNER COUNT(*)
------------------------------ ----------
SCOTT 18

scott@KNOX10G> -- Show MV is being referenced
scott@KNOX10G> SET autotrace traceonly
scott@KNOX10G> SELECT owner, COUNT (*)
2 FROM sec_mgr.big_tab
3 GROUP BY owner
4 /

Execution Plan
----------------------------------------------------------
0
SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=1 Bytes=13)


1 0
SORT (GROUP BY) (Cost=4 Card=1 Bytes=13)


2 1
MAT_VIEW REWRITE ACCESS (FULL) OF 'BIG_TAB_AGGS' (MAT_VIEW RE
WRITE) (Cost=3 Card=1 Bytes=13)

Hope this helps.


Tom and David - excellent as usual!

October 13, 2004 - 10:16 am UTC

Reviewer: Bill from Montpelier, VT

Thanks to both you (Tom) and David, not only for a well-presented and applicable reply but also for replying just before you left on a lengthy trip! I greatly appreciate that you took that time to reply, and am in your debt!

Tom Kyte

Followup  

October 13, 2004 - 10:27 am UTC

no worries, i'll pass the thanks onto David, he did the work :)

dsdsd

April 27, 2006 - 11:54 am UTC

Reviewer: A reader


Tom Kyte

Followup  

April 27, 2006 - 3:27 pm UTC

fgfgfg