Oracle | DBMS

Oracle DBMS_ZHELP 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 Undocumented but related to system and object privileges

AUTHID DEFINER

Dependencies

DBMSZEXP_SYSPKGGRNT	DBMS_RMGR_PLAN_EXPORT	LBAC_SERVICES
DBMS_AQ_SYS_EXP_INTERNAL	DBMS_RULE_EXP_EC_INTERNAL	OBJ$
DBMS_AW_EXP	DBMS_RULE_EXP_RL_INTERNAL	OBJAUTH$
DBMS_CUBE_EXP	DBMS_RULE_EXP_RS_INTERNAL	SYSAUTH$
DBMS_FILE_GROUP_EXP	DBMS_SCHED_MAIN_EXPORT	USER$
DBMS_RMGR_GROUP_EXPORT	KU_NOEXP_VIEW

Documented No

First Available Not Known

Security Model Owned by SYS with EXECUTE granted to LBACSYS

Source $ORACLE_HOME/rdbms/admin/prvtzhlp.plb

Subprograms

GET_OBJECT_GRANTS

GET_SYSPRIV_GRANTS

GET_OBJECT_GRANTS

Returns information on one user granted privileges on the designated object

When STAT = TRUE repeated calls return the same user. When STAT = FALSE cycles through the users who have privileges on the designated object.

Overload 1 dbms_zhelp.get_object_grants( OBJ_ID IN NUMBER, CASCADE IN BOOLEAN, GRANTOR OUT VARCHAR2, GRANTEE OUT VARCHAR2, GRANT_OPTION OUT BINARY_INTEGER, PRIV OUT NUMBER, OBJ_SCHEMA OUT VARCHAR2, OBJ_NAME OUT VARCHAR2, STATE IN OUT BOOLEAN);

conn uwclass/uwclass@pdbdev

      

      GRANT select ON servers TO hr;

      GRANT select ON servers TO scott;

      GRANT select ON servers TO sh;

      

      conn / as sysdba

      

      SELECT object_id

      FROM dba_objects_ae

      WHERE object_name = 'SERVERS'

      AND owner = 'UWCLASS'

      AND object_type = 'TABLE';

      

      set serveroutput on

      

      DECLARE

       gtor VARCHAR2(30);

       gtee VARCHAR2(30);

       gopt PLS_INTEGER;

       priv VARCHAR2(40);

       osch VARCHAR2(30);

       onam VARCHAR2(30);

       stat BOOLEAN := FALSE;

      BEGIN

        dbms_zhelp.get_object_grants(53099, TRUE, gtor, gtee, gopt, priv, osch, onam, stat);

      

        dbms_output.put_line(gtor);

        dbms_output.put_line(gtee);

        dbms_output.put_line(gopt);

        dbms_output.put_line(priv);

        dbms_output.put_line(osch);

        dbms_output.put_line(onam);

      END;

      /

      

      -- repeat seven more times to watch cycling

      /

      

      /

      

      /

      

      /

      

      /

      

      /

      

      /

Overload 2

dbms_zhelp.get_object_grants(

      OBJ_SCHEMA   IN     VARCHAR2

      OBJ_NAME     IN     VARCHAR2,

      OBJ_CLASS    IN     NUMBER,

      CASCADE      IN     BOOLEAN,

      GRANTOR         OUT VARCHAR2,

      GRANTEE         OUT VARCHAR2,

      GRANT_OPTION    OUT BINARY_INTEGER,

      PRIV            OUT NUMBER,

      STATE        IN OUT BOOLEAN);

Object Class	Name
1	index
2	table
3	cluster
4	view
6	sequence
7	procedure
8	function
9	package
13	type

conn uwclass/uwclass@pdbdev

      

      GRANT select ON servers TO hr;

      GRANT select ON servers TO scott;

      GRANT select ON servers TO sh;

      

      conn / as sysdba

      

      set serveroutput on

      

      DECLARE

       osch VARCHAR2(30) := 'UWCLASS';

       onam VARCHAR2(30) := 'SERVERS';

       ocls PLS_INTEGER := 2;

       casc BOOLEAN := TRUE;

      

       gtor VARCHAR2(30);

       gtee VARCHAR2(30);

       gopt PLS_INTEGER;

       priv VARCHAR2(40);

       stat BOOLEAN := FALSE;

      BEGIN

        dbms_zhelp.get_object_grants(osch, onam, ocls, casc, gtor, gtee, gopt, priv, stat);

      

        dbms_output.put_line(gtor);

        dbms_output.put_line(gtee);

        dbms_output.put_line(gopt);

        dbms_output.put_line(priv);

      END;

      /

      

      -- repeat seven more times to watch cycling

      /

      

      /

      

      /

      

      /

      

      /

      

      /

      

      /

GET_SYSPRIV_GRANTS

Returns information on one user granted a specified system privilege

When STAT = TRUE repeated calls return the same user. When STAT = FALSE cycles through the users who have designated privilege. dbms_zhelp.get_syspriv_grants( priv IN NUMBER, grantee OUT VARCHAR2, admin_option OUT BINARY_INTEGER, state IN OUT BOOLEAN);

SELECT privilege, name

      FROM system_privilege_map;

      

      set serveroutput on

      

      DECLARE

       priv PLS_INTEGER := -5;

       gtee VARCHAR2(30);

       aopt PLS_INTEGER;

       stat BOOLEAN := FALSE;

      BEGIN

        dbms_zhelp.get_syspriv_grants(priv, gtee, aopt, stat);

      

        dbms_output.put_line(gtee);

        dbms_output.put_line(aopt);

      END;

      /