DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

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

  1. #1
    Join Date
    Dec 2001
    Location
    Tel-Aviv,Israel
    Posts
    233

    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

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    in your fine grain access control procedures you must give the owner, creater and invoker of the proc doing a refresh a null policy

  3. #3
    Join Date
    Dec 2001
    Location
    Tel-Aviv,Israel
    Posts
    233
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width