| Copying a user [message #191285] |
Tue, 05 September 2006 08:21  |
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 #191408 is a reply to message #191285] |
Wed, 06 September 2006 03:44  |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
