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!
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.