October 17, 2007 - 12pm Central time zone
Reviewer: FAN
I suggest adding:
select dbms_metadata.GET_GRANTED_DDL ('DEFAULT_ROLE', 'SCOTT') ddl_text
from dual;
Been waiting for years to catch you :)
October 18, 2007 - 2pm Central time zone
Reviewer: Alexander
Why is there so much space in between grants?
Migrating a User
October 19, 2007 - 8am Central time zone
Reviewer: jean-marie faye from france
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 ?
How to get the tablespace quota?!
November 1, 2007 - 12am Central time zone
Reviewer: A reader
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..
Followup November 2, 2007 - 11am Central time zone:
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;
Avoid Errors in Script
November 8, 2007 - 10am Central time zone
Reviewer: MZ from Switzerland
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
/
Get all grants/privileges on particular user objects to other users/roles
January 6, 2008 - 12am Central time zone
Reviewer: Megala
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
Followup January 7, 2008 - 8am Central time zone:
example taken virtually verbaitim from the documentation for dbms_metadata...
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_metada.htm#sthref3547
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"
January 7, 2008 - 1pm Central time zone
Reviewer: Megala
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.
April 11, 2008 - 1pm Central time zone
Reviewer: MB from Michigan,USA
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
Followup April 11, 2008 - 2pm Central time zone:
how about getting granted the privileges to see that information?
June 20, 2008 - 4am Central time zone
Reviewer: A reader
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)
Followup June 20, 2008 - 10am Central time zone:
1) data is released to users by "grants", if you have been granted SELECT on a table, it matters not what tablespace it is in, you can select from it.
The ability to CREATE segments in a tablespace is granted either via a grant (unlimited tablespace is a privilege, gives you the right to use anything) or by using a quota on a tablespace by tablespace basis.
So, in order to select, insert, update, delete, merge data - you need a grant on the segment (table) - the tablespace is not relevant.
In order to create new segments, you either need "unlimited tables" or a positive quota on the tablespace(s) you would like to place these segments in.
2) dba_tablespaces shows tablespaces, user_ts_quotas shows quotas
|