Home » SQL & PL/SQL » SQL & PL/SQL » Get the MetaData for FGA Policy (Oracle Database 19c Enterprise Edition Release 19.0.0.0.0)
Get the MetaData for FGA Policy [message #689837] Tue, 14 May 2024 10:52 Go to next message
wtolentino
Messages: 404
Registered: March 2005
Senior Member
How do you get the meta data of an FGA. I tried this approach, and I am getting an error. I know it might not work but I tried it somehow.

select DBMS_METADATA.GET_DDL('FGA_POLICY','CODE_SED_TRANS_TEMP_POL_00') ddl_str from dual;


ORA-31600: invalid input value CODE_SED_TRANS_TEMP_POL_00 for parameter NAME in function GET_DDL
ORA-06512: at "SYS.DBMS_METADATA", line 6751
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 6628
ORA-06512: at "SYS.DBMS_METADATA", line 9774
ORA-06512: at line 1
31600. 00000 -  "invalid input value %s for parameter %s in function %s"
*Cause:    A NULL or invalid value was supplied for the parameter.
*Action:   Correct the input value and try the call again.

FGA policy exist "CODE_SED_TRANS_TEMP_POL_00".
/forum/fa/14792/0/

Thanks.

[Updated on: Tue, 14 May 2024 11:19]

Report message to a moderator

Re: Get the MetaData for FGA Policy [message #689838 is a reply to message #689837] Tue, 14 May 2024 11:24 Go to previous messageGo to next message
wtolentino
Messages: 404
Registered: March 2005
Senior Member
I figure it out using this

select dbms_metadata.get_dependent_ddl('RLS_POLICY','<Table Name>,'<Table Owner>') from dual;

[Updated on: Tue, 14 May 2024 11:30]

Report message to a moderator

Re: Get the MetaData for FGA Policy [message #689839 is a reply to message #689838] Tue, 14 May 2024 11:52 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9097
Registered: November 2002
Location: California, USA
Senior Member
Here is an example that gives a little more complete information.  

If creating this policy, first you would create the package specification and package body that you get from dba_source.
Then you would create the policy that you get from dbms_metadata.

SCOTT@orcl_12.1.0.2.0> select text
  2  from   dba_source
  3  where  (owner, name) in
  4  	    (select pfschma, ppname
  5  	     from   sys.fga$
  6  	     where  pname = 'DEPT_ACCESS_HANDLED')
  7  order  by type, line
  8  /

TEXT
--------------------------------------------------------------------------------
PACKAGE audit_handler
AS
  PROCEDURE handle_tab_access
    ( object_schema  IN VARCHAR2
    , object_name    IN VARCHAR2
    , policy_name    IN VARCHAR2
    );
END audit_handler;
PACKAGE BODY audit_handler
AS
  PROCEDURE handle_tab_access
    ( object_schema  IN VARCHAR2
    , object_name    IN VARCHAR2
    , policy_name    IN VARCHAR2
    )
  AS
    PRAGMA AUTONOMOUS_TRANSACTION;
  BEGIN
      INSERT INTO user_query_audit
        ( username, when, query)
      VALUES
        ( USER, SYSTIMESTAMP, SYS_CONTEXT ('USERENV', 'CURRENT_SQL'));
      COMMIT;
  END handle_tab_access;
END audit_handler;

25 rows selected.

SCOTT@orcl_12.1.0.2.0> select dbms_metadata.get_dependent_ddl
  2  	      ('FGA_POLICY', 'DEPT', 'SCOTT')
  3  from   dual
  4  /

DBMS_METADATA.GET_DEPENDENT_DDL('FGA_POLICY','DEPT','SCOTT')
--------------------------------------------------------------------------------

  BEGIN DBMS_FGA.ADD_POLICY('SCOTT','DEPT','DEPT_ACCESS_HANDLED','','','SCOTT','
AUDIT_HANDLER.HANDLE_TAB_ACCESS',TRUE,'SELECT',DBMS_FGA.DB_EXTENDED,DBMS_FGA.ANY
_COLUMNS,NULL); END;


1 row selected.
Re: Get the MetaData for FGA Policy [message #689840 is a reply to message #689839] Thu, 16 May 2024 14:18 Go to previous message
wtolentino
Messages: 404
Registered: March 2005
Senior Member
Thanks Barbara.
Previous Topic: ORA-30372: fine grain access policy conflicts with materialized view
Next Topic: previous month
Goto Forum:
  


Current Time: Fri Jun 21 00:36:55 CDT 2024