Re: Cloning of a user

Andreas Boehm wrote:
>
> Hi,
>
> > > does anyone know a script that allows to clone a user in oracle via
> > > SQL similar to the oem? I need to configure a "copy" of a user on
> > > another instance without having the scripts that created the user.
> > > Grants, roles, privileges and profiles should be "copied".
> > You can do that using export and import
> > exp ... owner=<olduser>
> > imp ... fromuser=<olduser> touser=<newuser>
> > The create user <newuser> statement has to be issued before imp.
>
> does this mean, the import will import all grants, privileges, roles
> and profiles of the user, if this user will be created with no grants,
> no roles and without any privilges?
>
> regards,
> Andreas Boehm

Hello Andreas,
assuming you are using Orcle 8.1.7, take a look at the utilities manual, it's worth your time and energy because both are mighty tools. Create yourself a free account at OTN if you don't already have one. http://otn.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a76955/toc.htm

Important info concerning export are availabe at: http://otn.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a76955/ch01.htm#17481

> > The create user <newuser> statement has to be issued before imp.
>
> does this mean, the import will import all grants, privileges, roles
> and profiles of the user, if this user will be created with no grants,
> no roles and without any privilges?

vital info about import are in the same document http://otn.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a76955/ch02.htm#33570 take a close look at section 'Import Modes'

sceanrio, eg user SYS creates a user (very minimal): SQL> create user bla identified by bla default tablespace mm_local temporary tablespace temp;  

User created.  

SQL> alter user bla quota 0 on system;  

User altered.  

SQL> alter user bla quota 20M on mm_local;  

User altered.  

SQL> grant create session to bla;  

Grant succeeded.

eg: user manu exports her objects (tables, sequences...):  exp manu/<passwd> file=mm.dmp grants=y owner=manu rows=y compress=n ....
Export terminated successfully without warnings.

user system (has DBA role) imports the data from user manu to user bla: imp system/<passwd> file=mm.dmp fromuser=manu touser=bla ...

Export file created by EXPORT:V08.01.07 via conventional path  

Warning: the objects were exported by MANU, not by you  

import done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set ...
...Import terminated successfully with warnings. Warning: You find yourself in a big mess, if exp/imp include referential integrity constraints. Always import the parent table first, after that the child table (and that's not the only pitfall you might encounter... hence my advice, both are mighty tools).

Note: You have both for export and import pretty self-explanatory case studies.
Hope this get you started
Manuela Mueller Received on Tue Nov 06 2001 - 17:51:38 CST