ORA-30372: fine grain access policy conflicts with materialized view

Hi all,

I'm trying to create materialized view with a query on two tables which are protected by RLS.
In the Metalink note 158187.1 there is an explaination : "to solve this problem you must define the policy function to return a NULL
policy during create and refresh operations on the materialized view".

I've created two functions ,as they explained on both of the secured tables:
SQL> create or replace FUNCTION FILTERS_TREE_sec (D1 VARCHAR2, D2 VARCHAR2) RETURN VARCHAR2
2 IS
3 d_predicate VARCHAR2 (2000);
4 BEGIN
5 IF USER = 'TRP'
6 THEN
7 RETURN '';
8 END IF;
9 d_predicate := 'uname = SYS_CONTEXT(''FILTERS_TREE'', ''uname'')';
10 RETURN D_predicate;
11 END FILTERS_TREE_sec;
12 /

Function created.

SQL> create or replace FUNCTION TBL_SUSPECTED_NUMBERS_sec (D1 VARCHAR2, D2 VARCHAR2) RETURN VARCHAR2

2 IS
3 d_predicate VARCHAR2 (2000);
4 BEGIN
5 IF USER = 'TRP'
6 THEN
7 RETURN '';
8 END IF;
9 d_predicate := 'uname = SYS_CONTEXT(''TBL_SUSPECTED_NUMBERS'', ''uname'')';
10 RETURN D_predicate;
11 END TBL_SUSPECTED_NUMBERS_sec;
12 /

Function created.

User TRP is the schema which own all the tables and suppose to create and refresh the snapshot.


After createing the functions,i've been trying to create the snapshot and i've failed again:

SQL> CREATE SNAPSHOT mw_general_alerts
2 STORAGE (INITIAL 1m NEXT 1m MINEXTENTS 1 MAXEXTENTS 999 PCTINCREASE 0) LOGGING
3 TABLESPACE general_alerts
4 BUILD IMMEDIATE
5 REFRESH FORCE
6 START WITH sysdate
7 NEXT sysdate + 6/24
8 as
9 select tsn.alert_id,
10 tsn.filter_id,
11 ft.TITLE as filter_name,
12 tsn.expire_time,
13 tsn.alert_time,
14 tsn.alert_virtual_time,
15 tsn.alert_open,
16 tsn.severity,
17 tsn.on_call,
18 tsn.number_of_cdrs,
19 tsn.call_id,
20 tsn.cdr_source,
21 tsn.lccore_id,
22 tsn.ring_id,
23 tsn.unique_num,
24 tsn.completed,
25 tsn.log_flag,
26 tsn.cdr_id_1,
27 tsn.cdr_source_1,
28 tsn.cdr_id_2,
29 tsn.cdr_source_2,
30 tsn.channel_key,
31 get_entity_name(tsn.filter_id,1) as entity_name_1,
32 get_entity_value(tsn.filter_id,1,alert_id) as entity_value_1,
33 get_entity_name(tsn.filter_id,2) as entity_name_2,
34 get_entity_value(tsn.filter_id,2,alert_id) as entity_value_2,
35 tsn.update_time
36 from tbl_suspected_numbers tsn,filters_tree ft
37 where tsn.filter_id=ft.filter_id;
where tsn.filter_id=ft.filter_id
*
ERROR at line 37:
ORA-30372: fine grain access policy conflicts with materialized view

Any ideas?

Thanks in advance,
Nir

in your fine grain access control procedures you must give the owner, creater and invoker of the proc doing a refresh a null policy

Thanks!

I have a security packege ,which contains all the fine grain access control for the tables.
Do you mean that i should add a null policy function to the security package with the name of the MATERIALIZED VIEW and its owner,invoker,refresher?

Nir