May 2, 2005: On creating a similar user

Unfortunately, there is no SQL statement available in Oracle to create a new user similar to an already existing (or created) user. Under a similar user, I understand another user that has the same quotas on tablespaces, the same privileges and the same synonyms.

Therefore, a procedure is needed to create a similar user:

create or replace procedure create_user_like (
  orig_user varchar2,
  clone     varchar2,
  pw        varchar2
)
  authid current_user
as
  def_ts    varchar2(30);
  tmp_ts    varchar2(30);
  profile   varchar2(30);
  priv      varchar2(40);
  admin     varchar2( 3);
  with_adm  varchar2(18);
  quota     varchar2(20);
begin

  select default_tablespace, temporary_tablespace, profile
  into   def_ts            , tmp_ts              , profile
  from   sys.dba_users
  where   username = orig_user;

  execute immediate
    'create user ' || clone  || ' identified by ' || pw ||
    ' default tablespace '   || def_ts            ||
    ' temporary tablespace ' || tmp_ts            ||
    ' profile '              || profile;

  for r in (
    select
      privilege, admin_option
    from
      sys.dba_sys_privs
    where
      grantee = orig_user) loop

    if r.admin_option = 'YES' then
      with_adm := ' with admin option';
    else
      with_adm := '';
    end if;

    execute immediate
      'grant ' || r.privilege || ' to ' || clone ||
      with_adm;
  end loop;

  for r in (
    select granted_role, admin_option
    from   sys.dba_role_privs
    where  grantee = orig_user) 
    loop

    if r.admin_option = 'YES' then
      with_adm := ' with admin option';
    else
      with_adm := '';
    end if;

    execute immediate 'grant ' || r.granted_role || ' to ' || clone || with_adm;
  end loop;

  for r in (
    select owner, table_name, privilege, grantable
    from sys.dba_tab_privs
    where grantee = orig_user) loop

    if r.grantable = 'YES' then
      with_adm := ' with admin option';
    else
      with_adm := '';
    end if;

    execute immediate
      'grant ' || r.privilege ||
      ' on '   || r.owner     || '.' || r.table_name ||
      ' to '   || clone       || with_adm;
  end loop;

  for r in (
    select owner, table_name, column_name, privilege, grantable
    from sys.dba_col_privs
    where grantee = orig_user) loop

    if r.grantable = 'YES' then
      with_adm := ' with admin option';
    else
      with_adm := '';
    end if;

    execute immediate
      'grant ' || r.privilege   ||
      '('      || r.column_name || ')' ||
      ' on '   || r.owner       || '.' || r.table_name ||
      ' to ' || clone ||
      with_adm;
  end loop;

  for r in (
    select tablespace_name, max_bytes
    from sys.dba_ts_quotas
    where username = orig_user) loop

    if r.max_bytes = -1 then
      quota := 'unlimited';
    else
      quota := to_char(r.max_bytes);
    end if;

    execute immediate
      'alter user ' || clone    ||
      ' quota '     || quota    ||
      ' on '        || r.tablespace_name;
  end loop;

  for r in (
    select synonym_name, table_owner, table_name 
    from dba_synonyms
    where owner = orig_user) loop

    execute immediate
      'create synonym ' || clone || '.' || r.synonym_name ||
      ' for ' || r.table_owner   || '.' || r.table_name;
  end loop;

end;
/

Testing the procedure

In order to test the procedure, some users are created: orig (the user to be duplicated), owner_1, owner_2 (owner_1 and owner_2 own some tables and will have granted some privileges), proc_executor (who will execute the procedure) and proc_owner (who will create the procedure)

connect rene/rene

create user orig
  identified by orig
  default   tablespace data
  temporary tablespace temp
  quota     10M on data
  quota     20M on temp
  quota     unlimited on ts_01;

create user owner_1
  identified by owner_1
  default   tablespace data
  temporary tablespace temp
  quota unlimited on data
  quota unlimited on temp;

create user owner_2
  identified by owner_2
  default   tablespace data
  temporary tablespace temp
  quota unlimited on data
  quota unlimited on temp;

create user proc_executor
  identified by proc_executor
  default   tablespace data
  temporary tablespace temp;

create user proc_owner
  identified by proc_owner
  default   tablespace data
  temporary tablespace temp;

The users receive their necessary privileges to do their work:

grant create session, create synonym                to orig;
grant create session, create table                  to owner_1;
grant create session, create table, create role     to owner_2; 
grant create session, create procedure, create user to proc_owner;

The procedure owner needs privileges grantable by a sysdba.

connect / as sysdba

grant select on dba_users      to proc_owner;
grant select on dba_sys_privs  to proc_owner;
grant select on dba_role_privs to proc_owner;
grant select on dba_tab_privs  to proc_owner;
grant select on dba_col_privs  to proc_owner;
grant select on dba_ts_quotas  to proc_owner;
grant select on dba_synonyms   to proc_owner;

owner_1 creates his tables:

connect owner_1/owner_1

create table selectable(txt varchar2(30));
create table insertable(txt varchar2(30));

insert into selectable values ('selectable owned by owner_1');

commit;

Then he grants some privileges on these tables so that orig can do a select or an insert on the table's data:

grant select        on selectable to orig;
grant select,insert on insertable to orig;

Owner_2 also creates a table:

connect owner_2/owner_2

create table column_grants(txt varchar2(30), col number);
create table role_grant(txt varchar2(30));

grant update (col) on column_grants to orig;
grant select on column_grants to orig;
insert into column_grants values ('Moo', 1);

create role owner_2_role;

grant select, insert on role_grant to owner_2_role;
grant owner_2_role to orig;

orig creates a synonym for owner_2.column_grants. This synonym should be copied with the create_user_like procedure.

connect orig/orig

create synonym syn_col_gr for owner_2.column_grants;

proc_owner creates the procedure:

connect proc_owner/proc_owner

create procedure .... [see above]....

grant execute on create_user_like to proc_executor;

proc_executer will execute many privileges along with the procedure, so, the predefined role dba is granted to him.

connect / as sysdba

grant dba to proc_executor;

proc_executor connects to the database and executes create_user_like. A new user is thus created, named CLONE, having the password CLONE_PW.

connect proc_executor/proc_executor

exec proc_owner.create_user_like('ORIG', 'CLONE', 'CLONE_PW');

Now, let the clone connect to the database (and see if he was truly created and has the indicated password).

connect clone/clone_pw
Connected.

Yep, that seems to work.

Can he select from owner_1.selectable?

SQL> select * from owner_1.selectable;

TXT
------------------------------
selectable owned by owner_1
SQL> insert into owner_1.insertable values ('inserted by clone');
SQL> select * from owner_1.insertable;

TXT
------------------------------
inserted by clone
SQL> update syn_col_gr set col=3 where txt = 'Moo';
SQL> select * from syn_col_gr;

TXT                                   COL
------------------------------ ----------
Moo                                     3
SQL> insert into owner_2.role_grant values ('inserted through role [clone]');
SQL> select * from owner_2.role_grant;

TXT
------------------------------
inserted through role [clone]

Yes, the privileges and synonyms seem to be copied, as well.

As a last check, the quotas are verified:

connect rene/rene

select tablespace_name, 
  case when max_bytes = -1 then 'Unlimited' else
    lpad(to_char(max_bytes/1024/1024),length('Unlimited')) end quota_mb
from 
  dba_ts_quotas where username = 'CLONE';

The following output shows that the quotas were copied as well.

TABLESPACE_NAME                QUOTA_MB
------------------------------ ---------
TS_01                          Unlimited
TEMP                                  20
DATA                                  10

More on Oracle

This is an on Oracle article. The most current articles of this series can be found here.


	René Nyffenegger's collection of things on the web
	René Nyffenegger on Oracle - Most wanted - Feedback - Follow @renenyffenegger