Prev: 10gAS Apache HTTP SSL connection on port 443
Next: ora-01461 when importing a 10.2 dump
From: trpost on 9 Mar 2007 11:37
I am looking for the SQL syntax to create an additional database user
that is a copy of an existing user. So my new user will have a
different username and password, but same permissions and access as
another existing user. I am looking for the SQL syntax, not how to do
it through a GUI tool.

Thanks!

From: Michel Cadot on 9 Mar 2007 11:45

<trpost(a)gmail.com> a �crit dans le message de news: 1173458266.313271.77960(a)8g2000cwh.googlegroups.com...
|I am looking for the SQL syntax to create an additional database user
| that is a copy of an existing user. So my new user will have a
| different username and password, but same permissions and access as
| another existing user. I am looking for the SQL syntax, not how to do
| it through a GUI tool.
|
| Thanks!
|

There is no SQL statement for that.
Have a look at:

http://www.dba-village.com/dba/village/dvp_tips.TipDetails?TipIdA=1877

Regards
Michel Cadot


From: trpost on 9 Mar 2007 16:29
Thanks for your response, I visited the URL provided and found the
article on "How to copy all the privileges from one user to another".
What would be the steps outside of a script for creating a user from
scratch to be just like another user? I was able to pick out from the
script that there were 3 table containing the privledge information
(dba_sys_privs, dba_tab_privs, dba_col_privs). However for the sake of
understanding I would like to see how this is done systematically from
scratch at the beginning from creating the user through assigning
permissions. So how would I step by step create a new user and then
find out all that is necessary to make that user look just like
another user. Thanks!


From: Michel Cadot on 9 Mar 2007 17:01

<trpost(a)gmail.com> a �crit dans le message de news: 1173475765.500196.87910(a)64g2000cwx.googlegroups.com...
| Thanks for your response, I visited the URL provided and found the
| article on "How to copy all the privileges from one user to another".
| What would be the steps outside of a script for creating a user from
| scratch to be just like another user? I was able to pick out from the
| script that there were 3 table containing the privledge information
| (dba_sys_privs, dba_tab_privs, dba_col_privs). However for the sake of
| understanding I would like to see how this is done systematically from
| scratch at the beginning from creating the user through assigning
| permissions. So how would I step by step create a new user and then
| find out all that is necessary to make that user look just like
| another user. Thanks!
|
|

For the prerequisite "create user" statement query dba_users
and dba_ts_quotas for the quotas on tablespaces..

Regards
Michel Cadot


From: trpost on 9 Mar 2007 17:24
I found this on DBA-Village which is exactly what I was after, with
instructions on how to generate a create user script:

this is the basic idea.
spool the output and run.
it is not complete. It has some missing grantee objects.
Change it to your fit.

----------
scott(a)9i > @cr_user_like
Enter user to model new user to: SCOTT
Enter new user name: ANOTHERSCOTT
Enter new user's password: ANOTHERTIGER
create user ANOTHERSCOTT identified by ANOTHERTIGER default tablespace
USERS temporary tablespace TEMP profile DEFAULT;
grant DBA to ANOTHERSCOTT;
grant CONNECT to ANOTHERSCOTT;
grant RESOURCE to ANOTHERSCOTT;
grant UNLIMITED TABLESPACE to ANOTHERSCOTT;
grant SELECT ANY DICTIONARY to ANOTHERSCOTT;
alter user ANOTHERSCOTT default role DBA;
alter user ANOTHERSCOTT default role CONNECT;
alter user ANOTHERSCOTT default role RESOURCE;
scott(a)9i > get cr_user_like
1 set pages 0 feed off veri off lines 500
2 accept oldname prompt "Enter user to model new user to: "
3 accept newname prompt "Enter new user name: "
4 accept psw prompt "Enter new user's password: "
5 -- Create user...
6 select 'create user &&newname identified by &&psw'||
7 ' default tablespace '||default_tablespace||
8 ' temporary tablespace '||temporary_tablespace||' profile '||
9 profile||';'
10 from sys.dba_users
11 where username = upper('&&oldname');
12 -- Grant Roles...
13 select 'grant '||granted_role||' to &&newname'||
14 decode(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION')||';'
15 from sys.dba_role_privs
16 where grantee = upper('&&oldname');
17 -- Grant System Privs...
18 select 'grant '||privilege||' to &&newname'||
19 decode(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION')||';'
20 from sys.dba_sys_privs
21 where grantee = upper('&&oldname');
22 -- Grant Table Privs...
23 select 'grant '||privilege||' on '||owner||'.'||table_name||' to
&&newname;'
24 from sys.dba_tab_privs
25 where grantee = upper('&&oldname');
26 -- Grant Column Privs...
27 select 'grant '||privilege||' on '||owner||'.'||table_name||
28 '('||column_name||') to &&newname;'
29 from sys.dba_col_privs
30 where grantee = upper('&&oldname');
31 -- Set Default Role...
32 select 'alter user &&newname default role '|| granted_role ||';'
33 from sys.dba_role_privs
34 where grantee = upper('&&oldname')
35* and default_role = 'YES';

----------
using export and import
----------
You need to take an export.
and duing import
option 1: use show=y and logfile=somelog.log
now somelog.log has all the information you want.
The actuall import IS NOT done.
option 2: just do a plain import to the new instance with rows=n .
Import will be done,without any rows.
Just precretae the user and tablespace.



 |  Next  |  Last
Pages: 1 2
Prev: 10gAS Apache HTTP SSL connection on port 443
Next: ora-01461 when importing a 10.2 dump