Steps to copy or clone the user permission from one user to another user in Oracle

Run as SYS or DBA User

-- ===============================
-- Clone User Privileges Script
-- ===============================

SET SERVEROUTPUT ON
DECLARE
  v_source   VARCHAR2(30) := UPPER('&SOURCE_USER'); 
  v_target   VARCHAR2(30) := UPPER('&TARGET_USER'); 
BEGIN
  -- ===============================
  -- Clone SYSTEM Privileges
  -- ===============================
  FOR p IN (
    SELECT privilege
    FROM dba_sys_privs
    WHERE grantee = v_source
  )
  LOOP
    BEGIN
      EXECUTE IMMEDIATE 'GRANT ' || p.privilege || ' TO ' || v_target;
    EXCEPTION WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Skipping Privilege: ' || p.privilege);
    END;
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('✔ System Privileges Copied.');

  -- ===============================
  -- Clone ROLE Privileges
  -- ===============================
  FOR r IN (
    SELECT granted_role
    FROM dba_role_privs
    WHERE grantee = v_source
  )
  LOOP
    BEGIN
      EXECUTE IMMEDIATE 'GRANT ' || r.granted_role || ' TO ' || v_target;
    EXCEPTION WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Skipping Role: ' || r.granted_role);
    END;
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('✔ Roles Copied.');

  -- ===============================
  -- Clone OBJECT Privileges
  -- ===============================
  FOR o IN (
    SELECT owner, table_name, privilege
    FROM dba_tab_privs
    WHERE grantee = v_source
  )
  LOOP
    BEGIN
      EXECUTE IMMEDIATE
        'GRANT ' || o.privilege || ' ON ' || o.owner || '.' || o.table_name || 
        ' TO ' || v_target;
    EXCEPTION WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Skipping Object Priv: ' || o.owner || '.' || o.table_name);
    END;
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('✔ Object Privileges Copied.');

END;
/

⚙️ Usage

Run the script in SQL*Plus:

sqlplus sys as sysdba

SQL> @clone_user_privs.sql
Enter value for SOURCE_USER: USER1
Enter value for TARGET_USER: USER2

📝 Notes

⭐ What it clones?

Privilege Type	Cloned
System Privileges	✔
Roles	✔
Object Privileges	✔
Tablespace Quota	✖
Profiles	✖
Password	✖
Data Pump Privileges	✔ via roles

➕ Optional: Clone Tablespace Quota

Add this block if you want quota to be copied:

FOR q IN (
  SELECT tablespace_name, bytes
  FROM dba_ts_quotas
  WHERE username = v_source
)
LOOP
  EXECUTE IMMEDIATE
    'ALTER USER ' || v_target ||
    ' QUOTA UNLIMITED ON ' || q.tablespace_name;
END LOOP;

🚀 Clone Profile & Password (Optional)

DECLARE
  v_profile VARCHAR2(30);
BEGIN
  SELECT profile INTO v_profile
  FROM dba_users WHERE username = v_source;

  EXECUTE IMMEDIATE
    'ALTER USER ' || v_target || ' PROFILE ' || v_profile;
END;
/

🔒 Important Recommendations

• Run as SYS or user with SELECT_CATALOG_ROLE
• Test on non-production first
• Some privileges might fail (logged by DBMS_OUTPUT)
• Adjust script if both users in different schemas