Haven't posted in a while,
Here's a quick script to copy an oracle user
Note: it does not schema objects, only grants and privs.
v_like_usr := 'USER_TO_COPY';
v_new_usr := 'NEW_NEW';
Edit the above lines and run
set feedback off;
set serverout on;
DECLARE
V_temp_tbs varchar2(20);
v_dflt_tbs varchar2(20);
v_profile varchar2(20);
v_new_usr varchar2(20);
v_cons_grp varchar2(50);
v_like_usr varchar2(20);
cursor c1 is select granted_role, ADMIN_OPTION from dba_role_privs where grantee = v_like_usr;
cursor c2 is select * from dba_tab_privs where grantee = v_like_usr and table_name not in (select GROUP_OR_SUBPLAN from DBA_RSRC_PLAN_DIRECTIVES);
BEGIN
v_like_usr := 'USER_TO_COPY';
v_new_usr := 'NEW_NEW';
select temporary_tablespace into V_temp_tbs from dba_users where username = v_like_usr;
select default_tablespace into v_dflt_tbs from dba_users where username = v_like_usr;
select profile into v_profile from dba_users where username = v_like_usr;
select INITIAL_RSRC_CONSUMER_GROUP into v_cons_grp from dba_users where username = v_like_usr;
dbms_output.put_line(' ');
dbms_output.put_line('Creating User: ' || v_new_usr || ' to be like User: '|| v_like_usr);
execute immediate 'create user '|| v_new_usr ||' identified by n3w_p4ss_4_t0d4y password expire default tablespace '|| v_dflt_tbs ||' temporary tablespace '|| V_temp_tbs ||' profile '|| v_profile;
dbms_output.put_line('Assigning roles to User: ' || v_new_usr);
FOR i IN c1
LOOP
IF i.ADMIN_OPTION = 'YES' THEN
execute immediate 'grant ' || i.granted_role || ' to '|| v_new_usr || ' with admin option';
DBMS_OUTPUT.PUT_LINE('Granting ' || i.granted_role || ' to '|| v_new_usr || ' with admin option');
ELSE
execute immediate 'grant ' || i.granted_role || ' to '|| v_new_usr;
DBMS_OUTPUT.PUT_LINE('Granting ' || i.granted_role || ' to '|| v_new_usr);
END IF;
END LOOP;
dbms_output.put_line('Assigning table grants to User: ' || v_new_usr);
FOR j IN c2
LOOP
IF j.GRANTABLE = 'YES' THEN
execute immediate 'grant '|| j.PRIVILEGE ||' on '|| j.owner ||'.'|| j.table_name ||' to '|| v_new_usr || ' with grant option';
DBMS_OUTPUT.PUT_LINE('Granting '|| j.PRIVILEGE ||' on '|| j.owner ||'.'|| j.table_name ||' to '|| v_new_usr || ' with grant option');
ELSE
execute immediate 'grant '|| j.PRIVILEGE ||' on '|| j.owner ||'.'|| j.table_name ||' to '|| v_new_usr;
DBMS_OUTPUT.PUT_LINE('Granting '|| j.PRIVILEGE ||' on '|| j.owner ||'.'|| j.table_name ||' to '|| v_new_usr);
END IF;
END LOOP;
IF v_cons_grp != 'DEFAULT_CONSUMER_GROUP' THEN
dbms_output.put_line('Assigning Consumer group '|| v_cons_grp ||' to User: ' || v_new_usr);
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(DBMS_RESOURCE_MANAGER.ORACLE_USER,v_new_usr,v_cons_grp);
DBMS_RESOURCE_MANAGER_privs.grant_switch_consumer_group(v_new_usr,v_cons_grp, FALSE);
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA;
ELSE
dbms_output.put_line('Assigning Consumer DEFAULT_CONSUMER_GROUP to User: ' || v_new_usr);
END IF;
COMMIT;
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('No Data Found');
END;
/
set feedback on;
