How To Create Users Like Another User In Oracle Database At SQL Command Line, Not From OEM (Doc ID 1352212.1)
Solution 1:
exec dbms_metadata.set_transform_param(dbms_metadata.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE);
select dbms_metadata.get_ddl( 'USER', '' ) from dual
UNION ALL
select dbms_metadata.get_granted_ddl( 'SYSTEM_GRANT', '' ) from dual
UNION ALL
select dbms_metadata.get_granted_ddl( 'OBJECT_GRANT', '' ) from dual
UNION ALL
select dbms_metadata.get_granted_ddl( 'ROLE_GRANT', '' ) from dual
exec dbms_metadata.set_transform_param(dbms_metadata.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE);
select dbms_metadata.get_ddl( 'USER', '
UNION ALL
select dbms_metadata.get_granted_ddl( 'SYSTEM_GRANT', '
UNION ALL
select dbms_metadata.get_granted_ddl( 'OBJECT_GRANT', '
UNION ALL
select dbms_metadata.get_granted_ddl( 'ROLE_GRANT', '
UNION ALL
select dbms_metadata.get_ddl('TABLESPACE','USER_DATA') from dual;
Solution 2:
To get the user's privileges you can query the following tables:
-- For Table privileges: DBA_TAB_PRIVS
-- For Column privileges: DBA_COL_PRIVS
-- For System privileges: DBA_SYS_PRIVS
-- For Roles: DBA_ROLE_PRIVS
You can also use the DBMS_METADATA package:
-- For object 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 scripts can be used to create a user identical to another user :
NOTE: The commands are provided as is and are not supported by Oracle.
You will have to verify yourself that the commands are applicable to your environment.
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');
-- Set 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');
NOTE:
The above generated commands must be executed to actually create the new user with its privileges
-- 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;
/
.........To find the user details with out any error ......
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
/
