个人总结

通过sql command对一个用户进行like create.总体思路是对源用户的权限,默认表空间等等信息进行收集,然后再拼出创建语句进行用户的创建.

How To Create Users Like Another User In Oracle Database At Sql Command Line Not From OEM [ID 1352212.1]

		Modified 26-SEP-2011 Type HOWTO Status REVIEWED

In this Document
Goal
Solution

Applies to:

Oracle Server - Enterprise Edition - Version: 11.1.0.7 and later [Release: 11.1 and later ]
Information in this document applies to any platform.

Goal

To create DB users with the same system privilege, roles, object privileges and other privileges using SQLPLUS command line.

Solution

To get the user's privileges you can query the following tables:
-- Roles...
dba_role_privs

-- System Privs...
dba_sys_privs

-- Table Privs...
dba_tab_privs

-- Column Privs...
dba_col_privs

Also, you can use the DBMS_METADATA package:
--- FOR OBJECTS PRIVILEGES GRANTED TO A USER

SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT',upper('&GRANTED_USER'))
FROM DUAL;

--- FOR SYSTEM PRIVILEGES GRANTED TO A USER

SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT',upper('&GRANTED_USER'))
FROM DUAL;

--- FOR ROLES GRANTED TO A USER

SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT',upper('&GRANTED_USER'))
FROM DUAL;

The following script. can be used to create a user identical to another user :

(please note that the following script. is not supported by Oracle)

set pages 0 feed off veri off lines 500

accept oldname prompt "Enter user to model new user to: "
accept newname prompt "Enter new user name: "
accept psw prompt "Enter new user's password: "

-- Create user...

select 'create user &&newname identified by &psw'||
' default tablespace '||default_tablespace||
' temporary tablespace '||temporary_tablespace||' profile '||
profile||';'
from sys.dba_users
where username = upper('&&oldname');

-- Grant Roles...

select 'grant '||granted_role||' to &&newname'||
decode(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION')||';'
from sys.dba_role_privs
where grantee = upper('&&oldname');

-- Grant System Privs...

select 'grant '||privilege||' to &&newname'||
decode(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION')||';'
from sys.dba_sys_privs
where grantee = upper('&&oldname');

-- Grant Table Privs...

select 'grant '||privilege||' on '||owner||'.'||table_name||' to &&newname;'
from sys.dba_tab_privs
where grantee = upper('&&oldname');

-- Grant Column Privs...

select 'grant '||privilege||' on '||owner||'.'||table_name||
'('||column_name||') to &&newname;'
from sys.dba_col_privs
where grantee = upper('&&oldname');

-- Tablespace Quotas...

select 'alter user '||username||' quota '||
decode(max_bytes, -1, 'UNLIMITED', max_bytes)||
' on '||tablespace_name||';'
from sys.dba_ts_quotas
where username = upper('&&oldname');

-- Set Default Role...

set serveroutput on
declare
defroles varchar2(4000);
begin
for c1 in (select * from sys.dba_role_privs
where grantee = upper('&&oldname')
and default_role = 'YES'
) loop
if length(defroles) > 0 then
defroles := defroles||','||c1.granted_role;
else
defroles := defroles||c1.granted_role;
end if;
end loop;
dbms_output.put_line('alter user &&newname default role '||defroles||';');
end;
/

Show Related Information Related

Products

Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Enterprise Edition

ITPub博客

How To Create Users Like Another User In Oracle Database At Sql Command Line

个人总结

Applies to:

Goal

Solution

最新文章