Note: Technical Talk being moved to:
www.nazimcricket.com/wiki


Tuesday, 5 May 2009

Create User Scripts

There are many ways of cloning a user account or two from one database to another. Here are a few simple SQLs to dynamically generate scripts from the source database, which can then be executed on the target database.

I have in this example assumed that the tablespaces etc in the source and target database are identical in name and size. I have also chosen to set all the user roles (by default) as Default.

select 'CREATE USER ' || username || ' IDENTIFIED BY VALUES ''' || password || ''' DEFAULT TABLESPACE ' || default_tablespace || ' TEMPORARY TABLESPACE ' || temporary_tablespace || ' PROFILE ' || profile || ' ACCOUNT UNLOCK;' from dba_users where username in (&USERS);

select 'GRANT ' || granted_role || ' TO ' || grantee || decode(admin_option,'YES',' WITH ADMIN OPTION;',';') from dba_role_privs where grantee IN (&USERS);

select 'GRANT ' || privilege || ' ON ' || owner || '.' || table_name || ' TO ' || grantee || decode(grantable,'YES',' WITH GRANT OPTION;',';') from dba_tab_privs where grantee IN (&USERS);

select 'ALTER USER ' || username || ' QUOTA ' || decode(max_bytes,-1,'UNLIMITED ON ' || TABLESPACE_NAME || ';', max_bytes/1024 || 'K ON ' || TABLESPACE_NAME || ';') from dba_ts_quotas where username in (&USERS);

No comments: