SQL Script to Mirror a user in Oracle Database

Save the following as mirror_user.sql.

SET SERVEROUTPUT ON;
SET VERIFY OFF;

-- Prompt for source user
PROMPT Enter the source user name (to mirror):
DEFINE SOURCE_USER = &1

-- Prompt for target user
PROMPT Enter the target user name (new user):
DEFINE TARGET_USER = &2

-- Prompt for target user password
PROMPT Enter the password for the new user:
DEFINE NEW_PASSWORD = &3

DECLARE
-- Variables for SQL commands and user details
v_sql VARCHAR2(4000);
v_default_ts VARCHAR2(100);
v_temp_ts VARCHAR2(100);
BEGIN
-- Fetch the default and temporary tablespaces for the source user
SELECT default_tablespace, temporary_tablespace
INTO v_default_ts, v_temp_ts
FROM dba_users
WHERE username = UPPER('&SOURCE_USER');

-- Create the target user
v_sql := 'CREATE USER ' || UPPER('&TARGET_USER') ||
' IDENTIFIED BY "' || '&NEW_PASSWORD' || '"' ||
' DEFAULT TABLESPACE ' || v_default_ts ||
' TEMPORARY TABLESPACE ' || v_temp_ts;
EXECUTE IMMEDIATE v_sql;
DBMS_OUTPUT.PUT_LINE('User ' || UPPER('&TARGET_USER') || ' created.');

-- Grant roles
FOR r IN (SELECT 'GRANT ' || granted_role || ' TO ' || UPPER('&TARGET_USER') ||
CASE WHEN admin_option = 'YES' THEN ' WITH ADMIN OPTION;' ELSE ';' END AS sql_command
FROM dba_role_privs WHERE grantee = UPPER('&SOURCE_USER')) LOOP
EXECUTE IMMEDIATE r.sql_command;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Roles granted to ' || UPPER('&TARGET_USER') || '.');

-- Grant system privileges
FOR s IN (SELECT 'GRANT ' || privilege || ' TO ' || UPPER('&TARGET_USER') ||
CASE WHEN admin_option = 'YES' THEN ' WITH ADMIN OPTION;' ELSE ';' END AS sql_command
FROM dba_sys_privs WHERE grantee = UPPER('&SOURCE_USER')) LOOP
EXECUTE IMMEDIATE s.sql_command;
END LOOP;
DBMS_OUTPUT.PUT_LINE('System privileges granted to ' || UPPER('&TARGET_USER') || '.');

-- Grant object privileges
FOR o IN (SELECT 'GRANT ' || privilege || ' ON ' || owner || '.' || table_name ||
' TO ' || UPPER('&TARGET_USER') ||
CASE WHEN grantable = 'YES' THEN ' WITH GRANT OPTION;' ELSE ';' END AS sql_command
FROM dba_tab_privs WHERE grantee = UPPER('&SOURCE_USER')) LOOP
EXECUTE IMMEDIATE o.sql_command;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Object privileges granted to ' || UPPER('&TARGET_USER') || '.');

DBMS_OUTPUT.PUT_LINE('Mirroring of ' || UPPER('&SOURCE_USER') || ' to ' || UPPER('&TARGET_USER') || ' completed successfully.');
END;
/
EXIT;

Execution Steps

Execute it in SQL*Plus and provide the required inputs:

bash

sqlplus / as sysdba @mirror_user.sql

SQL*Plus will prompt for:

Source user name: The user you want to mirror.

Target user name: The new user name.

New password: The password for the new user.

Example

Enter the source user name (to mirror): SOURCE_USER

Enter the target user name (new user): TARGET_USER

Enter the password for the new user: NewPassword123

Please do like and subscribe to my youtube channel: https://www.youtube.com/@foalabs If you like this post please follow,share and comment

SQL Script to Mirror a user in Oracle Database