Oracle DBMS_ZHELP
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Interested in Oracle GoldenGate? Check out the IGGOUG,
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
DBMS_ZHELP_IR
Object Privileges
Packages
System Privileges

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-2014 Daniel A. Morgan All Rights Reserved