Create User Script
Jump to navigation
Jump to search
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.
Note: Some word-wrapping done here
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);