CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle DBMS_ZHELP
Version 11.1
 
General
Source $ORACLE_HOME/rdbms/admin/prvtzhlp.plb
Dependencies
DBMSZEXP_SYSPKGGRNT DBMS_RULE_EXP_RS_INTERNAL
DBMS_AQ_SYS_EXP_INTERNAL DBMS_SCHED_MAIN_EXPORT
DBMS_FILE_GROUP_EXP OBJ$
DBMS_RMGR_GROUP_EXPORT OBJAUTH$
DBMS_RMGR_PLAN_EXPORT SYSAUTH$
DBMS_RULE_EXP_EC_INTERNAL USER$
DBMS_RULE_EXP_RL_INTERNAL  
Security Model Execute is granted to PUBLIC on the related DBMS_ZHELP_IR
 
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

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
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(
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

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
System Privileges
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [235 users online]    © 2010 psoug.org