OLS Cannot Hide Columns on Materialized Views (Doc ID 1235138.1)

Last updated on OCTOBER 13, 2010

Applies to:

Oracle Server - Enterprise Edition - Version: 9.2.0.1 to 11.2.0.2 - Release: 9.2 to 11.2
Information in this document applies to any platform.

Symptoms

Using OLS to hide the materialized views columns does not work.
Testcase:

conn / as sysdba

create user apps identified by apps;
grant connect,resource,create materialized view to apps;

grant exempt access policy to apps;

BEGIN
sa_sysdba.drop_policy('XX',TRUE);
sa_sysdba.create_policy (
policy_name => 'XX'
,column_name => 'XX_XX'
,default_options => 'ALL_CONTROL,HIDE'
);
END;
/

grant execute on sa_components to XX_dba;
grant execute on sa_user_admin to XX_dba;
grant execute on sa_label_admin to XX_dba;
grant execute on sa_policy_admin to XX_dba;
grant execute on sa_audit_admin to XX_dba;
grant XX_dba to apps;

BEGIN
SA_USER_ADMIN.SET_USER_PRIVS(
policy_name => 'XX',
user_name => 'APPS',
privileges => 'FULL,PROFILE_ACCESS'
);
END;
/

conn apps/apps

BEGIN
SA_COMPONENTS.CREATE_LEVEL(
policy_name => 'XX',
level_num => 10,
short_name => 'CO',
long_name => 'CO'
);
SA_COMPONENTS.CREATE_LEVEL(
policy_name => 'XX',
level_num => 20,
short_name => 'RE',
long_name => 'RE'
);
END;
/
BEGIN
SA_LABEL_ADMIN.CREATE_LABEL(
policy_name => 'XX',
label_tag => 1300,
label_value => 'CO',
data_label => TRUE
);
SA_LABEL_ADMIN.CREATE_LABEL(
policy_name => 'XX',
label_tag => 1100,
label_value => 'RE',
data_label => TRUE
);
END;
/

BEGIN
SA_USER_ADMIN.SET_USER_PRIVS(
policy_name => 'XX',
user_name => 'APPS',
privileges => 'FULL,PROFILE_ACCESS'
);
END;
/

create table test (col1 number);
alter table test add primary key(col1);

BEGIN
SA_POLICY_ADMIN.APPLY_TABLE_POLICY(
policy_name => 'XX'
, schema_name => 'APPS'
, table_name => 'TEST'
, table_options => null
, LABEL_FUNCTION => null
, predicate => null
);
END;
/

insert into test (col1,xx_xx) values(1, char_TO_LABEL('XX','CO'));
insert into test (col1,xx_xx) values(2, char_TO_LABEL('XX','CO'));
insert into test (col1,xx_xx) values(3, char_TO_LABEL('XX','RE'));

create materialized view log on test;

drop materialized view test1;

/* a.* on its own doesnt pick up xx_xx column */

create materialized view test1 build immediate refresh fast as
select a.*,xx_xx from test a;

/* unhide column otherwise apply table policy fails */
conn lbacsys/lbacsys

BEGIN
SA_SYSDBA.ALTER_POLICY('XX','ALL_CONTROL');
END;
/

BEGIN
SA_POLICY_ADMIN.APPLY_TABLE_POLICY(
policy_name => 'XX'
, schema_name => 'APPS'
, table_name => 'TEST1'
, table_options => null
, label_function => null
, predicate => null
);
END;
/


/* hide dolumn again */
BEGIN
SA_SYSDBA.ALTER_POLICY('XX','ALL_CONTROL,HIDE');
END;
/

/* column xx_xx is visible on MV */

SQL> select * from test1;

COL1 XX_XX
---------- ----------
1 1300
2 1300
3 1100

/* not on the base table */

SQL> select * from test;

COL1
----------
1
2
3


Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms