Clone Oracle User with user_profile.sql Script

ADVERTISEMENT

📌 What This Script Does:

This SQL script generates a full export of an Oracle database user — including their creation statement, profile, password hash, granted roles, system & object privileges, and tablespace quotas — and outputs the commands with a new target username.

It’s ideal for:

  • Migrating a user to another Oracle environment
  • Cloning users across dev, test, and prod
  • Backing up user privilege setups for audit or recovery

It outputs a ready-to-run .sql file that recreates the target user with all permissions intact, cloned from the original source user.

What’s Included in the Output:

✅ CREATE USER with profile and password hash
✅ System privileges (GRANT SELECT ANY TABLE, etc.)
✅ Role grants (GRANT DBA, GRANT CONNECT, etc.)
✅ Object-level grants (tables, views, procedures, etc.)
✅ Tablespace quotas (if applicable)

Final Script: user_profile.sql

-- Clone Oracle User Creation & Grants
SET HEADING OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET WRAP ON
SET PAGESIZE 133
SET LINESIZE 150

-- Prompt for source and target usernames
ACCEPT source_user CHAR PROMPT 'Enter source username: '
ACCEPT target_user CHAR PROMPT 'Enter target (new) username: '

-- Start spooling to file in current directory
SPOOL &&target_user..sql

-- 1. Create User with Profile and Password Hash
SELECT 
  'CREATE USER ' || '&&target_user' || 
  ' PROFILE ' || profile || 
  ' IDENTIFIED BY VALUES ''' || password || 
  ''' DEFAULT TABLESPACE ' || default_tablespace || 
  ' TEMPORARY TABLESPACE TEMP;'
FROM sys.dba_users
WHERE username = UPPER('&&source_user');

-- 2. Grant System Privileges
SELECT 
  'GRANT ' || privilege || ' TO ' || '&&target_user' || ';'
FROM sys.dba_sys_privs
WHERE grantee = UPPER('&&source_user');

-- 3. Grant Roles
SELECT 
  'GRANT ' || granted_role || ' TO ' || '&&target_user' || 
  DECODE(admin_option, 'YES', ' WITH ADMIN OPTION;', ';')
FROM sys.dba_role_privs
WHERE grantee = UPPER('&&source_user');

-- 4. Grant Object Privileges
SELECT 
  'GRANT ' || privilege || ' ON ' || owner || '.' || table_name || 
  ' TO ' || '&&target_user' || 
  DECODE(grantable, 'YES', ' WITH GRANT OPTION;', ';')
FROM sys.dba_tab_privs
WHERE grantee = UPPER('&&source_user');

-- 5. Tablespace Quotas
SELECT 
  'ALTER USER ' || '&&target_user' || 
  ' QUOTA ' || bytes || ' ON ' || tablespace_name || ';'
FROM sys.dba_ts_quotas
WHERE username = UPPER('&&source_user') AND bytes > 0;

-- Stop spooling
SPOOL OFF

How to Use It:

  1. Save the script as user_profile.sql.
  2. Run it from SQL*Plus or SQLcl as SYS or DBA: bashCopyEditsqlplus / as sysdba @user_profile.sql
  3. Enter the source username (the user to clone from).
  4. Enter the target username (the new user to create).
  5. The script will generate TARGETUSER.sql in the current directory with all required commands.

You can now run that file to fully recreate the user with privileges cloned from the original.

✅ Result:

You’ll get a complete, clean TARGETUSER.sql file in your current folder. Run this file on another Oracle instance to recreate the user with the same profile, roles, system/object grants, and quotas — all ready to go.

ADVERTISEMENT