20 | 10 | 2017
Latest Articles
Popular Articles

Scripts

Grant a "standard" DB user the possibility to unlock a user

User Rating:  / 0
PoorBest 

GRANT A "STANDARD" DB USER THE POSSIBILITY TO UNLOCK A USER

 

How to grant a standard database user the possibility to unlock a user

1. Without the need to grant him the alter user privilege

2. Without the risk internal accounts are unlocked

 

create or replace procedure I_AM_DBA.I_UNLOCK_USER ( which_user varchar2 )
authid definer
/* Guy Lambregts 2016-09-21 */
/* This procedure can be used to grant a database user the possibility to unlock a user */
/* without the need to grant the alter user privilege */
/* STEP 1 as sysdba "create user i_am_dba identified by secret account lock" */
/* STEP 2 as sysdba "grant alter user to i_am_dba" + "grant select any dictionary to i_am_dba" */
/* STEP 3 as sysdba compile this procedure under schema i_am_dba */
/* STEP 4 as sysdba "grant execute on i_am_dba.i_unlock_user to a_user" */
/* STEP 5 as sysdba "grant select any dictionary to a_user" */
/* usage : connected as a_user : SQL > I_AM_DBA.I_UNLOCK_USER ( which_user => 'COCONUT' ); */
as
statement varchar2(100);
begin
select 'alter user '||username||' account unlock' into statement from dba_users
where username = which_user
and username not in ('SYS','SYSTEM','DIP','SYSDG','SYSBACKUP','XDB','ANONYMOUS','GSMADMIN_INTERNAL',
'APPQOSSYS','WMSYS','GSMCATUSER','XS$NULL','SYSKM','OUTLN','GSMUSER','OJVMSYS','ORACLE_OCM','AUDSYS','I_AM_DBA');
execute immediate(statement);
exception
when no_data_found then
raise_application_error(-20001, 'user does not exists or you are not allowed to unlock this user');
when others then
raise_application_error(-20002,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
end;
/

##########################################################################################################################

 

MYDB 21/09/2016 14:53 +02:00 > exec I_AM_DBA.I_UNLOCK_USER ( 'SCOTT' );

PL/SQL procedure successfully completed.

MYDB 21/09/2016 14:53 +02:00 > exec I_AM_DBA.I_UNLOCK_USER ( 'COCONUT' );
BEGIN I_AM_DBA.I_UNLOCK_USER ( 'COCONUT' ); END;
*
ERROR at line 1:
ORA-20001: user does not exists or you are not allowed to unlock this user
ORA-06512: at "I_AM_DBA.I_UNLOCK_USER", line 22
ORA-06512: at line 1