{"id":4357,"date":"2025-06-08T02:11:00","date_gmt":"2025-06-08T02:11:00","guid":{"rendered":"https:\/\/w3buddy.com\/?post_type=cposts&#038;p=4357"},"modified":"2025-06-22T13:51:03","modified_gmt":"2025-06-22T13:51:03","slug":"clone-oracle-user-with-user_profile-sql-script","status":"publish","type":"cposts","link":"https:\/\/w3buddy.com\/blog\/notes\/oracle-dba-d2d-tasks\/clone-oracle-user-with-user_profile-sql-script\/","title":{"rendered":"Clone Oracle User with user_profile.sql Script"},"content":{"rendered":"\n<h3 class=\"wp-block-heading\">\ud83d\udccc What This Script Does:<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">This SQL script generates a full export of an Oracle database user \u2014 including their creation statement, profile, password hash, granted roles, system &amp; object privileges, and tablespace quotas \u2014 and outputs the commands <strong>with a new target username<\/strong>.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">It&#8217;s ideal for:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Migrating a user to another Oracle environment<\/li>\n\n\n\n<li>Cloning users across dev, test, and prod<\/li>\n\n\n\n<li>Backing up user privilege setups for audit or recovery<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">It outputs a ready-to-run <code>.sql<\/code> file that recreates the target user with all permissions intact, cloned from the original source user.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">What\u2019s Included in the Output:<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">\u2705 <code>CREATE USER<\/code> with profile and password hash<br>\u2705 System privileges (<code>GRANT SELECT ANY TABLE<\/code>, etc.)<br>\u2705 Role grants (<code>GRANT DBA<\/code>, <code>GRANT CONNECT<\/code>, etc.)<br>\u2705 Object-level grants (tables, views, procedures, etc.)<br>\u2705 Tablespace quotas (if applicable)<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Final Script: <code>user_profile.sql<\/code><\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>-- Clone Oracle User Creation &amp; Grants\nSET HEADING OFF\nSET FEEDBACK OFF\nSET VERIFY OFF\nSET WRAP ON\nSET PAGESIZE 133\nSET LINESIZE 150\n\n-- Prompt for source and target usernames\nACCEPT source_user CHAR PROMPT 'Enter source username: '\nACCEPT target_user CHAR PROMPT 'Enter target (new) username: '\n\n-- Start spooling to file in current directory\nSPOOL &amp;&amp;target_user..sql\n\n-- 1. Create User with Profile and Password Hash\nSELECT \n  'CREATE USER ' || '&amp;&amp;target_user' || \n  ' PROFILE ' || profile || \n  ' IDENTIFIED BY VALUES ''' || password || \n  ''' DEFAULT TABLESPACE ' || default_tablespace || \n  ' TEMPORARY TABLESPACE TEMP;'\nFROM sys.dba_users\nWHERE username = UPPER('&amp;&amp;source_user');\n\n-- 2. Grant System Privileges\nSELECT \n  'GRANT ' || privilege || ' TO ' || '&amp;&amp;target_user' || ';'\nFROM sys.dba_sys_privs\nWHERE grantee = UPPER('&amp;&amp;source_user');\n\n-- 3. Grant Roles\nSELECT \n  'GRANT ' || granted_role || ' TO ' || '&amp;&amp;target_user' || \n  DECODE(admin_option, 'YES', ' WITH ADMIN OPTION;', ';')\nFROM sys.dba_role_privs\nWHERE grantee = UPPER('&amp;&amp;source_user');\n\n-- 4. Grant Object Privileges\nSELECT \n  'GRANT ' || privilege || ' ON ' || owner || '.' || table_name || \n  ' TO ' || '&amp;&amp;target_user' || \n  DECODE(grantable, 'YES', ' WITH GRANT OPTION;', ';')\nFROM sys.dba_tab_privs\nWHERE grantee = UPPER('&amp;&amp;source_user');\n\n-- 5. Tablespace Quotas\nSELECT \n  'ALTER USER ' || '&amp;&amp;target_user' || \n  ' QUOTA ' || bytes || ' ON ' || tablespace_name || ';'\nFROM sys.dba_ts_quotas\nWHERE username = UPPER('&amp;&amp;source_user') AND bytes > 0;\n\n-- Stop spooling\nSPOOL OFF<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">How to Use It:<\/h3>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>Save the script<\/strong> as <code>user_profile.sql<\/code>.<\/li>\n\n\n\n<li>Run it from SQL*Plus or SQLcl as SYS or DBA: bashCopyEdit<code>sqlplus \/ as sysdba @user_profile.sql<\/code><\/li>\n\n\n\n<li>Enter the <strong>source username<\/strong> (the user to clone from).<\/li>\n\n\n\n<li>Enter the <strong>target username<\/strong> (the new user to create).<\/li>\n\n\n\n<li>The script will generate <code>TARGETUSER.sql<\/code> in the current directory with all required commands.<\/li>\n<\/ol>\n\n\n\n<p class=\"wp-block-paragraph\">You can now run that file to <strong>fully recreate the user<\/strong> with privileges cloned from the original.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">\u2705 Result:<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">You\u2019ll get a complete, clean <code>TARGETUSER.sql<\/code> 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 \u2014 all ready to go.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>\ud83d\udccc What This Script Does: This SQL script generates a full export of an Oracle database user \u2014 including their creation statement, profile, password hash, granted roles, system &amp; object privileges, and tablespace quotas \u2014 and outputs the commands with a new target username. It&#8217;s ideal for: It outputs a ready-to-run .sql file that recreates [&hellip;]<\/p>\n","protected":false},"template":"","meta":{"googlesitekit_rrm_CAowu461DA:productID":""},"categories":[952,984],"class_list":["post-4357","cposts","type-cposts","status-publish","hentry","category-notes","category-oracle-dba-d2d-tasks"],"_links":{"self":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/cposts\/4357","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/cposts"}],"about":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/types\/cposts"}],"wp:attachment":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media?parent=4357"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/categories?post=4357"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}