Create Users Like Another User In Oracle Database

Updated: Jul 17, 2021

It is always a challenge for a DBA to get a quick pick on methodology to clone/mirror an User ID with same privileges as other user. It is obvious that people move around the teams and often DBA team has to perform cleanup of IDs and create new IDs for new members joining the team.

Most of the organisations do not use directory services like OID, ODEE, etc.. mostly due to license implications or lack of knowledge to handle the infrastructure. So keeping things simple, there are application schemas and then application users configured locally in the Databases. In a highly secured and compliant env, generally application users are given only Read Only privileges and if yours is a micro-service based application, more likely you may have multiple schemas (eventually multiple readonly roles).

So lets see how to quickly clone an existing user and create a new user with exact application access, system grants, table privileges, etc.. Below script is referred from an Oracle document while people may not get easily while they need it.

Reference: How To Create Users Like Another User In Oracle Database At SQL Command Line, Not From OEM (Doc ID 1352212.1)

Simply run the below query and follow the prompts, and you are done!!

set pages 0 feed off veri off lines 500
accept oldname prompt "Enter user to model new user to: "
accept newname prompt "Enter new user name: "
accept psw prompt "Enter new user's password: "


-- Create user...

select 'create user &&newname identified by &psw'||
' default tablespace '||default_tablespace||
' temporary tablespace '||temporary_tablespace||' profile '||
profile||';'
from sys.dba_users
where username = upper('&&oldname');


-- Grant Roles...

select 'grant '||granted_role||' to &&newname'||
decode(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION')||';'
from sys.dba_role_privs
where grantee = upper('&&oldname');


-- Grant System Privs...

select 'grant '||privilege||' to &&newname'||
decode(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION')||';'
from sys.dba_sys_privs
where grantee = upper('&&oldname');


-- Grant Table Privs...

select 'grant '||privilege||' on '||owner||'.'||table_name||' to &&newname;'
from sys.dba_tab_privs
where grantee = upper('&&oldname');


-- Grant Column Privs...

select 'grant '||privilege||' on '||owner||'.'||table_name||
'('||column_name||') to &&newname;'
from sys.dba_col_privs
where grantee = upper('&&oldname');


-- Set tablespace Quotas...

select 'alter user '||username||' quota '||
decode(max_bytes, -1, 'UNLIMITED', max_bytes)||
' on '||tablespace_name||';'
from sys.dba_ts_quotas
where username = upper('&&oldname');

-- Set Default Role...

set serveroutput on
declare
defroles varchar2(4000);
begin
  for c1 in (select * from sys.dba_role_privs
             where grantee = upper('&&oldname')
             and default_role = 'YES'
            )
  loop
    if length(defroles) > 0 then
      defroles := defroles||','||c1.granted_role;
    else
      defroles := defroles||c1.granted_role;
    end if;
  end loop;
dbms_output.put_line('alter user &&newname default role '||defroles||';');
end;
/

Consider spooling this to a sql file and run it at one shot.

More to this, the whole process can be easily automated by running an ansible/yaml script in the background by passing the &&oldname, &&newname and the password as a variable from a user access request form (if its readily available in your Org) and integrate with your scripts to pass them as variables.

Well, another simple way of doing this is from SQL Developer (I'm sure many of you might already know this) as shown below,

Click on View > DBA