I suggest adding:

select dbms_metadata.GET_GRANTED_DDL ('DEFAULT_ROLE', 'SCOTT') ddl_text
from dual;

Been waiting for years to catch you :)

Why is there so much space in between grants?

Thanks,
But if the user has a grant (for example select) on an object of another schema, how is it possible to get it with dbms_metadata ?

Hi Tom,
If have been using a script of my own to create a user. I know that this can be used instead but it does have one missing details.
"Alter user username quota xxx/unlimited on tbs;"

How do I get the quota information?

Thanks as always..

ops$tkyte%ORA10GR2> select dbms_metadata.get_ddl( 'USER', 'SCOTT' ) from dual
  2  UNION ALL
  3  select dbms_metadata.get_granted_ddl( 'SYSTEM_GRANT', 'SCOTT' ) from dual
  4  UNION ALL
  5  select dbms_metadata.get_granted_ddl( 'OBJECT_GRANT', 'SCOTT' ) from dual
  6  UNION ALL
  7  select dbms_metadata.get_granted_ddl( 'ROLE_GRANT', 'SCOTT' ) from dual
  8  union all
  9  select dbms_metadata.get_granted_ddl( 'TABLESPACE_QUOTA', 'SCOTT' ) from dual
 10  /

DBMS_METADATA.GET_DDL('USER','SCOTT')
-------------------------------------------------------------------------------

   CREATE USER "SCOTT" IDENTIFIED BY VALUES 'F894844C34402B67'
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP"



  GRANT SELECT ANY TRANSACTION TO "SCOTT"

  GRANT CREATE VIEW TO "SCOTT"

  GRANT UNLIMITED TABLESPACE TO "SCOTT"



  GRANT SELECT ON "SYS"."V_$PROCESS" TO "SCOTT"

  GRANT SELECT ON "SYS"."V_$SESSION" TO "SCOTT"

  GRANT SELECT ON "SYS"."DBA_DATAPUMP_JOBS" TO "SCOTT"



   GRANT "CONNECT" TO "SCOTT"

   GRANT "RESOURCE" TO "SCOTT"



  DECLARE
  TEMP_COUNT NUMBER;
  SQLSTR VARCHAR2(200);
BEGIN
  SQLSTR := 'ALTER USER "SCOTT" QUOTA UNLIMITED ON "USERS"';
  EXECUTE IMMEDIATE SQLSTR;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE = -30041 THEN
      SQLSTR := 'SELECT COUNT(*) FROM USER_TABLESPACES
              WHERE TABLESPACE_NAME = ''USERS'' AND CONTENTS = ''TEMPORARY''';
      EXECUTE IMMEDIATE SQLSTR INTO TEMP_COUNT;
      IF TEMP_COUNT = 1 THEN RETURN;
      ELSE RAISE;
      END IF;
    ELSE
      RAISE;
    END IF;
END;

Hi Tom,

I slightly modified your script to avoid the "not found" errors:

select (case
when ((select count(*)
from dba_users
where username = '&&Username') > 0)
then dbms_metadata.get_ddl ('USER', '&&Username')
else to_clob (' -- Note: User not found!')
end ) Extracted_DDL from dual
UNION ALL
select (case
when ((select count(*)
from dba_ts_quotas
where username = '&&Username') > 0)
then dbms_metadata.get_granted_ddl( 'TABLESPACE_QUOTA', '&&Username')
else to_clob (' -- Note: No TS Quotas found!')
end ) from dual
UNION ALL
select (case
when ((select count(*)
from dba_role_privs
where grantee = '&&Username') > 0)
then dbms_metadata.get_granted_ddl ('ROLE_GRANT', '&&Username')
else to_clob (' -- Note: No granted Roles found!')
end ) from dual
UNION ALL
select (case
when ((select count(*)
from dba_sys_privs
where grantee = '&&Username') > 0)
then dbms_metadata.get_granted_ddl ('SYSTEM_GRANT', '&&Username')
else to_clob (' -- Note: No System Privileges found!')
end ) from dual
UNION ALL
select (case
when ((select count(*)
from dba_tab_privs
where grantee = '&&Username') > 0)
then dbms_metadata.get_granted_ddl ('OBJECT_GRANT', '&&Username')
else to_clob (' -- Note: No Object Privileges found!')
end ) from dual
/

Tom:

IS there any script or database view to get "all grants & privileges on the particular database user objects granted to other users/roles".

Can we get the same using dbms_metadata package.

Thank you

ops$tkyte%ORA10GR2> select dbms_metadata.get_dependent_ddl( 'OBJECT_GRANT', 'EMP', 'SCOTT' ) from dual;

DBMS_METADATA.GET_DEPENDENT_DDL('OBJECT_GRANT','EMP','SCOTT')
-------------------------------------------------------------------------------

  GRANT SELECT ON "SCOTT"."EMP" TO "CONNECT"

  GRANT SELECT ON "SCOTT"."EMP" TO "A"

Thanks Tom!

select dbms_metadata.GET_GRANTED_DDL('OBJECT_GRANT', '<GRANTEE>') from dual ;

is giving me all the grants on the current schema objects to grantee.

Tom,
I tried this script and found that if we have only connect and system resource privilege for a user. the below script fails.

select bms_metadata.get_granted_ddl( 'SYSTEM_GRANT', USERNAME )
from dba_users

Trying to think of a query that will get me each of these values, without going to a small procedure or a anonymous block.

Any thoughts

Tom,
I am just a developer but curious:

1) Are all tablespaces avaialble to all users by default and access only controlled by quota a user has?

2) Is there a Query I can do to get what are the distinct tablespaces and quotas for a user (if not extracting the DDL)

I have slightly modified the query from "MZ from Switzerland" to get the select statements to extract the grants for all the users in the database:

---------------------------------------------
-- ###########################################
---------------------------------------------
create or replace procedure get_case_sqls_for_ddls_ver1 as

cursor get_username is
select username
from dba_users
;

begin


for l_user in get_username loop

DBMS_OUTPUT.PUT_LINE('-----------------------');
DBMS_OUTPUT.PUT_LINE('select (case');
DBMS_OUTPUT.PUT_LINE(' when ((select count(*)');
DBMS_OUTPUT.PUT_LINE(' from dba_users');
DBMS_OUTPUT.PUT_LINE(' where username = '''||l_user.username||''') > 0)');
DBMS_OUTPUT.PUT_LINE(' then dbms_metadata.get_ddl (''USER'', '''||l_user.username||''')');
DBMS_OUTPUT.PUT_LINE(' else to_clob ('' -- Note: User not found!'')');
DBMS_OUTPUT.PUT_LINE(' end ) "--Extracted_DDL" from dual');
DBMS_OUTPUT.PUT_LINE('UNION ALL');

DBMS_OUTPUT.PUT_LINE('-----------------------');
DBMS_OUTPUT.PUT_LINE('select (case');
DBMS_OUTPUT.PUT_LINE(' when ((select count(*)');
DBMS_OUTPUT.PUT_LINE(' from dba_ts_quotas');
DBMS_OUTPUT.PUT_LINE(' where username = '''||l_user.username||''') > 0)');
DBMS_OUTPUT.PUT_LINE(' then dbms_metadata.get_granted_ddl (''TABLESPACE_QUOTA'', '''||l_user.username||''')');
DBMS_OUTPUT.PUT_LINE(' else to_clob ('' -- Note: No TS Quotas found!'')');
DBMS_OUTPUT.PUT_LINE(' end ) from dual');
DBMS_OUTPUT.PUT_LINE('UNION ALL');

DBMS_OUTPUT.PUT_LINE('-----------------------');
DBMS_OUTPUT.PUT_LINE('select (case');
DBMS_OUTPUT.PUT_LINE(' when ((select count(*)');
DBMS_OUTPUT.PUT_LINE(' from dba_role_privs');
DBMS_OUTPUT.PUT_LINE(' where grantee = '''||l_user.username||''') > 0)');
DBMS_OUTPUT.PUT_LINE(' then dbms_metadata.get_granted_ddl (''ROLE_GRANT'', '''||l_user.username||''')');
DBMS_OUTPUT.PUT_LINE(' else to_clob ('' -- Note: No granted roles found!'')');
DBMS_OUTPUT.PUT_LINE(' end ) from dual');
DBMS_OUTPUT.PUT_LINE('UNION ALL');


DBMS_OUTPUT.PUT_LINE('-----------------------');
DBMS_OUTPUT.PUT_LINE('select (case');
DBMS_OUTPUT.PUT_LINE(' when ((select count(*)');
DBMS_OUTPUT.PUT_LINE(' from dba_sys_privs');
DBMS_OUTPUT.PUT_LINE(' where grantee = '''||l_user.username||''') > 0)');
DBMS_OUTPUT.PUT_LINE(' then dbms_metadata.get_granted_ddl (''SYSTEM_GRANT'', '''||l_user.username||''')');
DBMS_OUTPUT.PUT_LINE(' else to_clob ('' -- Note: No System Privileges found!'')');
DBMS_OUTPUT.PUT_LINE(' end ) from dual');
DBMS_OUTPUT.PUT_LINE('UNION ALL');

DBMS_OUTPUT.PUT_LINE('-----------------------');
DBMS_OUTPUT.PUT_LINE('select (case');
DBMS_OUTPUT.PUT_LINE(' when ((select count(*)');
DBMS_OUTPUT.PUT_LINE(' from dba_tab_privs');
DBMS_OUTPUT.PUT_LINE(' where grantee = '''||l_user.username||''') > 0)');
DBMS_OUTPUT.PUT_LINE(' then dbms_metadata.get_granted_ddl (''OBJECT_GRANT'', '''||l_user.username||''')');
DBMS_OUTPUT.PUT_LINE(' else to_clob ('' -- Note: No Object Privileges found!'')');
DBMS_OUTPUT.PUT_LINE(' end ) from dual');
DBMS_OUTPUT.PUT_LINE('/');
DBMS_OUTPUT.PUT_LINE('----------------------------------------------------------------------');

end loop;

end;
/


---------------------------------------------
--###########################################
---------------------------------------------
The above proc when called with the foll. will give the SQLs for all users:

---------------------------------------------
-- ###########################################
---------------------------------------------
set pages 50000
set serveroutput on size unlimited
spool C:\jc\workarea_av\Siebel_QA\DBMS_METADATA\sqls_gathered_frm_trial_run_1.sql
exec get_case_sqls_for_ddls_ver1
spool off
---------------------------------------------
-- ###########################################
---------------------------------------------

These SQLs generated can in turn be run as follows to get the master-list of all the grants in the database:

---------------------------------------------
-- ###########################################
---------------------------------------------
spool C:\jc\workarea_av\Siebel_QA\DBMS_METADATA\grants_by_running_trial3_ver0.0.sql
conn system/correctPW@psblprd2_taf
set long 1000000000
set pages 50000
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE);
@C:\jc\workarea_av\Siebel_QA\DBMS_METADATA\sqls_gathered_frm_trial_run_1.sql
spool off
---------------------------------------------
-- ###########################################
---------------------------------------------

The above spool file grants_by_running_trial3_ver0.0.sql will be the master file of all grants for all users in the DB.

Thanks to everyone for the excellent thread/postings.

Not sure if somebody already posted it, but I updated the original script based on a couple of ideas you guys provided for getting the info from all users. In this way will not fail and is not necessary to created a stored procedure.


set head off
set pages 0
set long 9999999
spool user_script.sql
SELECT DBMS_METADATA.GET_DDL('USER', USERNAME) || '/' DDL
FROM DBA_USERS
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('TABLESPACE_QUOTA', USERNAME) || '/' DDL
FROM DBA_USERS where username in (select username from dba_ts_quotas)
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', USERNAME) || '/' DDL
FROM DBA_USERS where username in (select grantee from dba_role_privs)
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', USERNAME) || '/' DDL
FROM DBA_USERS where username in (select grantee from dba_sys_privs)
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', USERNAME) || '/' DDL
FROM DBA_USERS where username in (select grantee from dba_tab_privs);
spool off;

{CODE}
SQL> show user;
USER is "SCOTT"
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> select dbms_metadata.get_dependent_ddl( 'OBJECT_GRANT', 'EMP', 'SCOTT' ) from dual;
ERROR:
ORA-31608: specified object of type OBJECT_GRANT not found
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_METADATA", line 3915
ORA-06512: at "SYS.DBMS_METADATA", line 5770
ORA-06512: at line 1



no rows selected

SQL>
{/CODE}

How to correct above error. Please guide me.
When I Preview Review, code tag not seems working.
Thanks and Regards
Girish Sharma

scott%ORA11GR2> select dbms_metadata.get_dependent_ddl( 'OBJECT_GRANT', 'EMP', 'SCOTT' ) from dual; 
ERROR:
ORA-31608: specified object of type OBJECT_GRANT not found
ORA-06512: at "SYS.DBMS_METADATA", line 5088
ORA-06512: at "SYS.DBMS_METADATA", line 7681
ORA-06512: at line 1



no rows selected

scott%ORA11GR2> grant select on emp to ops$tkyte;

Grant succeeded.

scott%ORA11GR2> select dbms_metadata.get_dependent_ddl( 'OBJECT_GRANT', 'EMP', 'SCOTT' ) from dual; 

DBMS_METADATA.GET_DEPENDENT_DDL('OBJECT_GRANT','EMP','SCOTT')
-------------------------------------------------------------------------------

  GRANT SELECT ON "SCOTT"."EMP" TO "OPS$TKYTE"

Thank you sir. This line "if you have no grants, it throws the exception *by design*." and your demo removed my doubt.

Best Regards
Girish Sharma