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.

I tried to use the script and got an error. I had to modify it to look like this
select '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.
Anonymous
July 27, 2009 6:27 PMHi Anonymous :-)
thanks for the hint, you are right, just a typo by my side. It's now corrected.
Cheers
Dirk
Dirk Nachbar
July 29, 2009 5:42 PMThanks for the helpful information. Hope to hear more from you.
control valves
August 10, 2009 7:31 AMHi, great query, but you need some extra quotes to make the SQL output valid:
select '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;
Anonymous
November 8, 2010 1:38 PMmuchas gracias sir Dirk ,
Besides 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:)
Anonymous
March 24, 2011 1:38 PMHola Anonymous,
no hay problema. Usted es bienvenido.
Saludos Dirk
Dirk Nachbar
March 24, 2011 1:45 PMHi Dirk,
can you upload your scripts to your website?
Thanks
Anonymous
March 31, 2011 9:31 AMHi Anonymous,
I 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
Dirk Nachbar
March 31, 2011 3:36 PM