Thursday 16 October 2014

Copying an Oracle User

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;