Home » RDBMS Server » Security » Copying a user
Copying a user [message #191285] Tue, 05 September 2006 08:21 Go to next message
RivetJoint
Messages: 7
Registered: September 2006
Location: UK
Junior Member
I wish to create a user in a database which is identical, except for name, to a user which already exists.

The user I want to make a copy of has so many rights and roles that Enterprise Manager crashes when I try and use the create like function. The user in question is the "apps" user in an E-Business Suite system.

Can I use exp to generate some DCL for the existing user and just change the username?
Re: Copying a user [message #191287 is a reply to message #191285] Tue, 05 September 2006 08:28 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
http://www.orafaq.com/forum/m/120560/42800/?srch=cr_user_like#msg_120560
Re: Copying a user [message #191408 is a reply to message #191285] Wed, 06 September 2006 03:44 Go to previous message
RivetJoint
Messages: 7
Registered: September 2006
Location: UK
Junior Member
Thank you, I took out the two "connect" lines and it worked.

For reference the code I used was:

set pages 0 feed off veri off lines 500
accept oldname prompt "Enter user to model new user "
accept newname prompt "Enter new user Name "
accept pw prompt "Enter Users Password "
spool users.sql
select 'create user &&newname identified by &&pw'||
' default tablespace '||default_tablespace||
' temporary tablespace '||temporary_tablespace||' profile ' ||
profile||';'
from sys.dba_users
where username = upper('&&oldname');
select 'grant '||granted_role|| ' to &&newname'||
decode(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION')||';'
from sys.dba_role_privs
where grantee = upper('&&oldname');
select 'alter user &&newname default role '|| granted_role ||';'
from sys.dba_role_privs
where grantee = upper('&&oldname')
and default_role = 'YES';
select 'grant '||privilege||' to &&newname'||
decode(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION')||';'
from sys.dba_sys_privs
where grantee = upper('&&oldname');
select 'grant '||privilege|| ' on '||owner||'.'||table_name||' to &&newname;'
from sys.dba_tab_privs
where grantee = upper('&&oldname')
and privilege in ('SELECT', 'REFERENCES');
select 'grant '||privilege||'on '||owner||'.'||table_name||
'('||column_name||') to &&newname;'
from sys.dba_col_privs
where grantee = upper('&&oldname');
select 'grant select on '||owner||'.'||table_name||' to &&newname;'
from sys.dba_tables
where owner = upper('&&oldname');
spool off

Previous Topic: Encryption and Decryption
Next Topic: user permission
Goto Forum:
  


Current Time: Thu Mar 28 03:47:19 CDT 2024