Oracle DBMS_ZHELP
Version 12.2.0.1

General Information
Library Note Morgan's Library Page Header
"I spent my entire adult life looking out for the well-being, the training, the equipping of the troops for whom I was responsible. I will not be lectured about what our military needs by a five-deferment draft dodger,"
~ Sen. Tammy Duckworth
Purpose Undocumented but related to system and object privileges
AUTHID DEFINER
Dependencies
DBMSZEXP_SYSPKGGRNT DBMS_RMGR_PLAN_EXPORT LBAC_UTL
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 documented
Security Model Owned by SYS with no privileges granted
Source $ORACLE_HOME/rdbms/admin/prvtzhlp.plb
Subprograms
 
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;
/

Related Topics
Built-in Functions
Built-in Packages
DBMS_ZHELP_IR
Object Privileges
System Privileges
What's New In 12cR1
What's New In 12cR2

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2017 Daniel A. Morgan All Rights Reserved