cursors, views, and fga.

From: DaLoverhino <DaLoveRhino_at_hotmail.com>
Date: 6 Jun 2006 14:49:43 -0700
Message-ID: <1149630583.320576.190350@j55g2000cwa.googlegroups.com>

I have a bunch of audited policies I want to turn off. I don't want to type 1000 times:

execute dbms_fga.drop_policy( blah, blah, blah);

So I figure I can use a cursor and the view of dba_audit_policies:

declare
  cursor policy_cursor

  is     select object_schema, object_name, policy_name
         where policy_name like 'PATTERN...'

  prec policy_cursor%ROWTYPE;
begin
  open policy_cursor
  loop
    fetch policy_cursor into prec;
    exit when brec%NOTFOUND;

    dbms_fga.drop_policy( object_schema=>prec.object_schema,
                          object_name=>prec.object_name,
                          policy_name=>prec.policy_name);

  end loop;
  close policy_cursor;
end;
/

Well, the question is, drop_policy() also changes the view which the cursor is working on. So I'm wondering if this will mess things up? I believe I tried something like this a long time ago, and I did get some strange behavior something like:

dba_audit_policies shows policies still, but sys.fga_log$ shows no sql statements.
Or dbms_fga.add_policy() complains about assigning two policies with the same name to one object, yet dba_audit_policies shows no audit policies.

Anyways, if this can't be done, is the only feasible work aroun would be to create a temporary table with schema, object, and policy and use that temporary table for the cursor?

thanks. Received on Tue Jun 06 2006 - 16:49:43 CDT