+ Reply to Thread
Results 1 to 8 of 8

Oracle create duplicate user account

  1. Oracle create duplicate user account

    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!


  2. Re: Oracle create duplicate user account


    a écrit dans le message de news: 1173458266.313271.77960@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/villa...ls?TipIdA=1877

    Regards
    Michel Cadot



  3. Re: Oracle create duplicate user account

    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!



  4. Re: Oracle create duplicate user account


    a écrit dans le message de news: 1173475765.500196.87910@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



  5. Re: Oracle create duplicate user account

    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@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@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.




  6. Re: Oracle create duplicate user account

    On Mar 9, 1:29 pm, trp...@gmail.com wrote:
    > 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!


    If you have access to metalink, there are example scripts that do what
    you want. For example, see Note:90449.1 for the create user statement
    (you can grab the one it generates and change the username and use a
    proper password rather than identified by values). Search metalink
    for "Database Scripts Library Index" but keep in mind, some of them
    are old and may contain old wives tails.

    Note there are also docs available at tahiti.oracle.com for the
    complete syntax. There are probably scripts floating about on the
    web, of various quality.

    If you have a system with the strings and grep commands a gander at a
    full export file (with rows=n, even) can be quite enlightening. Just
    grep for a known username and imagine there are semicolons at the end
    of the commands.

    We can't just give you a standard set of commands, since grants,
    synonyms and privileges are site-specific, hopefully controlled by a
    decent DBA. In many cases, packaged applications require things to be
    just plain weird.

    jg
    --
    @home.com is bogus.
    http://www.folloder.com/pix/clips/smokingmonkey.swf


  7. Re: Oracle create duplicate user account

    On 9 Mar 2007 08:37:46 -0800, trpost@gmail.com wrote:

    >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!
    >

    The Enterprise Manager has the Create like ... function (reached by
    right-clicking on a User)

    Jaap.

  8. Re: Oracle create duplicate user account

    On Mar 9, 12:37 pm, trp...@gmail.com wrote:
    > 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!


    You might try something like this

    1) export with no data
    2) import with the list option so that you put a copy of ALL the SQL
    into a text file, but it is not actually executed
    3) Edit the text file to extract and change the specifics features you
    need for a new user, making it identical to the existing user
    4) Run the script you just created

    This way you "know" you have all privs,roles,etc taken care of.


+ Reply to Thread