Create User Script

From NazimWIKI
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);