How To Create A Duplicate User In Oracle?

To create a duplicate Oracle user, you must export the Data Definition Language (DDL) for the source user's grants and definitions and then execute the modified DDL for the new user.

The process involves several steps and requires the SYS or a user with the EXP_FULL_DATABASE role and sufficient privileges.

Method 1: Duplicate user with the DBMS_METADATA package

The DBMS_METADATA package is the most robust and recommended method for cloning users and their permissions. It can extract the full DDL for a user's creation, system privileges, roles, and tablespace quotas.

Prerequisites

• You must have SELECT_CATALOG_ROLE, EXP_FULL_DATABASE, or SYSDBA privileges to extract the necessary metadata.
• You will be working in a command-line tool like SQL*Plus or a graphical tool like SQL Developer.

Step 1: Generate the user creation script

Connect as a user with administrative privileges (e.g., SYS AS SYSDBA) and use DBMS_METADATA.GET_DDL to extract the CREATE USER statement for the source user. This will include the tablespace assignments and profile.

SET LONG 2000000
SET PAGESIZE 0
SET LINESIZE 132
SET FEEDBACK OFF
SET ECHO OFF
SPOOL create_duplicate_user.sql
SELECT DBMS_METADATA.GET_DDL('USER', 'SOURCE_USER') FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL('TABLESPACE_QUOTA', 'SOURCE_USER') FROM DUAL;
SPOOL OFF

Use code with caution.

Replace SOURCE_USER with the name of the user you want to copy.

Step 2: Generate scripts for grants

Next, extract the system and role grants for the source user.

SPOOL create_duplicate_user_grants.sql
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', 'SOURCE_USER') FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', 'SOURCE_USER') FROM DUAL;
SPOOL OFF

Use code with caution.

Step 3: Edit the DDL scripts

Open the generated .sql files in a text editor and perform a search-and-replace operation.

• For create_duplicate_user.sql:
  • Find and replace SOURCE_USER with NEW_USER.
  • If you need a new password, change IDENTIFIED BY VALUES '...' to a new password statement like IDENTIFIED BY "NewPassword".
• For create_duplicate_user_grants.sql:
  • Find and replace SOURCE_USER with NEW_USER.
  • Find and replace ROLE_GRANT with ROLE_GRANT (not strictly necessary but good practice). Ensure ROLE_GRANT statements come after the CREATE USER and SYSTEM_GRANT statements to avoid errors.

Step 4: Execute the modified scripts

Execute the modified scripts in the following order:

1. Create the user and quotas:@create_duplicate_user.sql
2. Grant privileges:@create_duplicate_user_grants.sql

This method will create a new user with all the same privileges and tablespace quotas as the source user, but without any of the data or schema objects.

Method 2: Duplicate a user's schema using Oracle Data Pump

For duplicating a user's schema objects and data, Oracle Data Pump is the ideal tool. This method is used for migrating a user and their data to another schema.

Step 1: Export the source user's schema

Using the expdp utility, create a dump file containing the source user's schema.

expdp 'SYS/password AS SYSDBA' SCHEMAS=SOURCE_USER DUMPFILE=source_user.dmp LOGFILE=source_user.log

Use code with caution.

Step 2: Create the new user

Create the new user manually in the database.

CREATE USER new_user IDENTIFIED BY password DEFAULT TABLESPACE users QUOTA UNLIMITED ON users;
GRANT CONNECT, RESOURCE TO new_user;

Use code with caution.

Step 3: Import the schema into the new user

Using the impdp utility, import the dump file, mapping the source schema to the new schema.

impdp 'SYS/password AS SYSDBA' DUMPFILE=source_user.dmp REMAP_SCHEMA=SOURCE_USER:NEW_USER LOGFILE=new_user.log

Use code with caution.

Step 4: Recreate grants and roles

The Data Pump import does not move system or role grants. For these, use the DBMS_METADATA method described above to generate the grant scripts, edit them, and execute them for the NEW_USER.

Method 3: Duplicating a user with Oracle SQL Developer

Oracle SQL Developer offers a graphical interface to clone users, simplifying the process.

Step 1: Access the DBA panel

• In SQL Developer, go to View > DBA.
• Connect to your database as a SYS or DBA user.

Step 2: Select the source user

• Expand the Security node in the DBA panel.
• Expand the Users node.
• Right-click on the SOURCE_USER and select Create Like.

Step 3: Configure the new user

• A "Create User" dialog will open with most fields pre-populated based on the SOURCE_USER.
• Enter the new username, NEW_USER, and set a password.
• Verify and adjust the roles, system privileges, and tablespace quotas as needed.

Step 4: Finalize the creation

• Click Apply to create the new user.
• SQL Developer will generate and execute the necessary DDL statements.

Method 4: Duplicate a user's schema and data manually (for small schemas)

This method is viable for copying a user's schema objects and data without using Data Pump, especially for smaller schemas.

Step 1: Generate schema object DDL

Use DBMS_METADATA.GET_DDL to extract DDL for tables, indexes, and other objects.

-- Example for a table
SELECT DBMS_METADATA.GET_DDL('TABLE', 'MY_TABLE', 'SOURCE_USER') FROM DUAL;

Use code with caution.

Step 2: Create the new user

Create the new user manually as described in Method 2.

Step 3: Execute the DDL for the new user

Execute the DDL scripts for all schema objects, making sure to change the schema and other relevant parameters (TABLESPACE, etc.).

Step 4: Copy the data

Copy the data from the source schema to the new schema using INSERT INTO ... SELECT ....

INSERT INTO new_user.my_table SELECT * FROM source_user.my_table;

Use code with caution.

Step 5: Copy grants

Extract and apply grants as described in Method 1.

Summary of methods

Feature	DBMS_METADATA (Method 1)	Data Pump (Method 2)	SQL Developer (Method 3)	Manual Copy (Method 4)
Copies User	Yes	No (requires manual creation)	Yes	Yes (requires manual creation)
Copies Privileges	Yes	No (requires DBMS_METADATA)	Yes	Yes (requires DBMS_METADATA)
Copies Roles	Yes	No (requires DBMS_METADATA)	Yes	Yes (requires DBMS_METADATA)
Copies Tablespace Quotas	Yes	No (requires DBMS_METADATA)	Yes	Yes (requires manual creation)
Copies Schema Objects	No	Yes	No	Yes (requires manual DDL)
Copies Data	No	Yes	No	Yes (requires INSERT statements)
Best For	Cloning users/roles/privileges only	Cloning users with schema objects and data	Quick, interactive cloning of users/privs	Small, custom schema/data copies