Oracle | DBMS_FINE_GRAIN

Oracle DBMS_FINE_GRAIN_DEP Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

Purpose This package provides a table function FG_ELEMENTS that translates the fine-grain dependency information stored in dependency$ into a user readable format.

AUTHID CURRENT_USER

Data Types TYPE fg_rec IS RECORD(element_num IN NUMBER, element_desc IN VARCHAR2(30)); TYPE fg_tab IS TABLE OF fg_rec;

Dependencies

DBA_FINE_GRAIN_DEPENDENCIES	PUBLIC_FINE_GRAIN_DEPENDENCY	UTL_RAW
DEPENDENCY$

Documented No

First Available 11.2

Security Model Owned by SYS with no privileges granted

Source $ORACLE_HOME/rdbms/admin/dbmsfgd.sql This package is not part of the default installation. To create the object package and associated views the script must be run manually.

SQL> @?/rdbms/admin/dbmsfgd.sql

      

      Package created.

      

      No errors.

      

      View created.

      

      View created.

      

      View created.

      

      View created.

      

      Synonym created.

      

      Grant succeeded.

      

      Session altered.

      

      PL/SQL procedure successfully completed.

      

      Grant succeeded.

      

      Synonym created.

      

      Package body created.

      

      No errors.

FG_ELEMENTS

Translates, into human readable form, fine grain dependency information fg_elements(bitvec IN RAW) RETURN fg_tab PIPELINED;

conn sys@pdbdev as sysdba

      

      desc dependency$

      

      desc dba_fine_grain_dependencies

      

      SELECT COUNT(*)

      FROM dba_fine_grain_dependencies;

      

      SELECT element_desc, COUNT(*)

      FROM dba_fine_grain_dependencies

      GROUP BY element_desc;

      

      col owner format a20

      col type format a15

      col referenced_name format a20

      

      SELECT owner, name, type, referenced_name, element_desc

      FROM dba_fine_grain_dependencies;

DDL for DBA_FINE_GRAIN_DEPENDENCIES (edited for brevity and clarity) CREATE OR REPLACE FORCE VIEW sys.fine_grain_dependencies AS SELECT u.name, o.name, DECODE(o.type#, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER', 4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE', 7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE', 10, 'NON-EXISTENT', 11, 'PACKAGE BODY', 12, 'TRIGGER', 13, 'TYPE', 14, 'TYPE BODY', 22, 'LIBRARY', 28, 'JAVA SOURCE', 29, 'JAVA CLASS', 32, 'INDEXTYPE', 33, 'OPERATOR', 42, 'MATERIALIZED VIEW', 43, 'DIMENSION', 46, 'RULE SET', 55, 'XML SCHEMA', 56, 'JAVA DATA', 59, 'RULE', 62, 'EVALUATION CONTXT', 87, 'ASSEMBLY', 92, 'CUBE DIMENSION', 93, 'CUBE', 94, 'MEASURE FOLDER', 95, 'CUBE BUILD PROCESS', 'UNDEFINED'), DECODE(po.linkname, null, pu.name, po.remoteowner), po.name, DECODE(po.type#, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER', 4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE', 7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE', 10, 'NON-EXISTENT', 11, 'PACKAGE BODY', 12, 'TRIGGER', 13, 'TYPE', 14, 'TYPE BODY', 22, 'LIBRARY', 28, 'JAVA SOURCE', 29, 'JAVA CLASS', 32, 'INDEXTYPE', 33, 'OPERATOR', 42, 'MATERIALIZED VIEW', 43, 'DIMENSION', 46, 'RULE SET', 55, 'XML SCHEMA', 56, 'JAVA DATA', 59, 'RULE', 62, 'EVALUATION CONTXT', 87, 'ASSEMBLY', 92, 'CUBE DIMENSION', 93, 'CUBE', 94, 'MEASURE FOLDER', 95, 'CUBE BUILD PROCESS', 'UNDEFINED'), po.linkname, DECODE(BITAND(d.property, 3), 2, 'REF', 'HARD'), e.element_num, e.element_desc FROM sys."_CURRENT_EDITION_OBJ" o, sys.disk_and_fixed_objects po, sys.dependency$ d, sys.user$ u, sys.user$ pu, TABLE(dbms_fine_grain_dep.fg_elements(d.d_attrs)) e WHERE o.obj# = d.d_obj# AND o.owner# = u.user# AND po.obj# = d.p_obj# AND po.owner# = pu.user#;