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
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 21c
What's New In 23c

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