Reverse Engineering with DBMS_METADATA
Posted by Dirk Nachbar on Monday, July 13, 2009 with 8 comments
Today I'm coming with PL/SQL stuff, nothing with Oracle Application Server :-)
At the moment I'm developing for a customer a small reverse engineering routine to capture an Oracle Database (CreateDB.sql, Tablespaces, Users, Grants aso). For this I'm using the package dbms_metadata. If you are using this package to capture e.g. all objects grants to all users, you may receive errors like this "SQL Error: ORA-31608: specified object of type OBJECT_GRANT not found". This comes up, if the user don't hold any object grants.
To avoid this simply run a query like this, which will generate you a list of DBMS_METADATA.GET_GRANTED_DDL statements for every user which holds objects grants:
You can adapt this construct easily on SYSTEM_GRANT and ROLE_GRANT.
At the moment I'm developing for a customer a small reverse engineering routine to capture an Oracle Database (CreateDB.sql, Tablespaces, Users, Grants aso). For this I'm using the package dbms_metadata. If you are using this package to capture e.g. all objects grants to all users, you may receive errors like this "SQL Error: ORA-31608: specified object of type OBJECT_GRANT not found". This comes up, if the user don't hold any object grants.
To avoid this simply run a query like this, which will generate you a list of DBMS_METADATA.GET_GRANTED_DDL statements for every user which holds objects grants:
select 'select DBMS_METADATA.GET_GRANTED_DDL(''OBJECT_GRANT'', '||a.username||') from dual;'from dba_users a, (select distinct(grantee) from dba_tab_privs) bwhere a.username = b.grantee;You can adapt this construct easily on SYSTEM_GRANT and ROLE_GRANT.
Categories: DBMS_METADATA, Oracle
I tried to use the script and got an error. I had to modify it to look like this
ReplyDeleteselect 'select DBMS_METADATA.GET_GRANTED_DDL(''OBJECT_GRANT'', '||a.username||') from dual;'
from dba_users a, (select distinct(grantee) from dba_tab_privs) b
where a.username = b.grantee;
to work. notice the replacement of the '' with || to concatenate in a.username.
Hi Anonymous :-)
ReplyDeletethanks for the hint, you are right, just a typo by my side. It's now corrected.
Cheers
Dirk
Thanks for the helpful information. Hope to hear more from you.
ReplyDeleteHi, great query, but you need some extra quotes to make the SQL output valid:
ReplyDeleteselect 'select DBMS_METADATA.GET_GRANTED_DDL(''OBJECT_GRANT'', '||''''||a.username||''''||') from dual;'
from dba_users a, (select distinct(grantee) from dba_tab_privs) b
where a.username = b.grantee;
muchas gracias sir Dirk ,
ReplyDeleteBesides very useful and to the rescue, educational.
... and thanks for the added comments by Anonymous
(which is not me, many people go by that name:)
Hola Anonymous,
ReplyDeleteno hay problema. Usted es bienvenido.
Saludos Dirk
Hi Dirk,
ReplyDeletecan you upload your scripts to your website?
Thanks
Hi Anonymous,
ReplyDeleteI will try to find somewhere a space for uploading my scripts & utilities, but unfortunally in Blogspot its not possible to upload them directly. So you may check in a few days again, I will do a new blogpost where I publish my scripts & utilities.
Or if you read this here, you may drop me another comment with you email (I will not publish your email) so that I can inform you directly.
Cheers
Dirk