unlease power of DBMS_METADATA.GET_***

Want to check up which granted roles and system privileges on schema? DBMS_METADATA.GET_*** can work for this besides query directly on ***_SYS_PRIVS or ***_ROLE_RPIVS.

The DBMS_METADATA package provides a way for you to retrieve metadata from the database dictionary as XML or creation DDL and to submit the XML to re-create the object. Keep in mind:  run this package with dedicated server connection.

Example 1: schema system privileges and granted roles
SQL> set long 4000
SQL> set pagesize 4000
SQL> select dbms_metadata.get_granted_ddl(‘SYSTEM_GRANT’,’MYUSER’) from dual;

DBMS_METADATA.GET_GRANTED_DDL(‘SYSTEM_GRANT’,’MYUSER’)
——————————————————————————–

  GRANT SELECT ANY DICTIONARY TO "MYUSER"
  GRANT ANALYZE ANY TO "MYUSER"
  GRANT CREATE PROCEDURE TO "MYUSER"

 
SQL> select dbms_metadata.get_granted_ddl(‘ROLE_GRANT’,’MYUSER’) from dual;

DBMS_METADATA.GET_GRANTED_DDL(‘ROLE_GRANT’,’MYUSER’)
——————————————————————————–

   GRANT "CONNECT" TO "MYUSER"
   GRANT "RESOURCE" TO "MYUSER"


Example 2: Tables definition

SQL> EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,’SEGMENT_ATTRIBUTES’,FALSE);

PL/SQL procedure successfully completed.

SQL> SELECT DBMS_METADATA.GET_DDL(‘TABLE’,table_name,’&1′) from dba_tables where owner=’&1′ and rownum<2;
Enter value for 1: SCOTT
Enter value for 1: SCOTT
old   1: SELECT DBMS_METADATA.GET_DDL(‘TABLE’,table_name,’&1′) from dba_tables where owner=’&1′ and rownum<2
new   1: SELECT DBMS_METADATA.GET_DDL(‘TABLE’,table_name,’SCOTT’) from dba_tables where owner=’SCOTT’ and rownum<2

DBMS_METADATA.GET_DDL(‘TABLE’,TABLE_NAME,’SCOTT’)
——————————————————————————–

  CREATE TABLE "SCOTT"."BONUS"
   (    "ENAME" VARCHAR2(10),
        "JOB" VARCHAR2(9),
        "SAL" NUMBER,
        "COMM" NUMBER
   )


SQL>  EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,’DEFAULT’,TRUE);

PL/SQL procedure successfully completed.

SQL> SELECT DBMS_METADATA.GET_DDL(‘TABLE’,table_name,’&1′) from dba_tables where owner=’&1′ and rownum<2;
Enter value for 1: SCOTT
Enter value for 1: SCOTT
old   1: SELECT DBMS_METADATA.GET_DDL(‘TABLE’,table_name,’&1′) from dba_tables where owner=’&1′ and rownum<2
new   1: SELECT DBMS_METADATA.GET_DDL(‘TABLE’,table_name,’SCOTT’) from dba_tables where owner=’SCOTT’ and rownum<2

DBMS_METADATA.GET_DDL(‘TABLE’,TABLE_NAME,’SCOTT’)
——————————————————————————–

  CREATE TABLE "SCOTT"."BONUS"
   (    "ENAME" VARCHAR2(10),
        "JOB" VARCHAR2(9),
        "SAL" NUMBER,
        "COMM" NUMBER
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SYSTEM"
  MONITORING

Advertisements

About Pang

Oracle DBA working in database, fusion middleware and identity management and cloud.
This entry was posted in Security. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s