Andrew Fraser DBA

Unlock oracle accounts without alter user system privilege

I wanted to give non-DBA users (Servicedesk, Operations) the ability to unlock database user accounts. The ‘alter user’ system privilege allows that, but it also gives out a lot more privileges (change password, kill session, alter DBA accounts) than I thought safe.

So instead I created a pl/sql procedure to handle this securely. Usage for it is just:

SQL> set serverout on
SQL> exec sys.unlock_user('username')

CREATE OR REPLACE PROCEDURE sys.unlock_user
/***************************
|| Name : sys.unlock_user
|| Author : Andrew Fraser
|| Date : 02-Jul-2013
|| Purpose : Allow normal users to unlock database accounts
|| Parameters :
||     p_username - the username of the user - case INsensitive
|| Installation notes:
||     This procedure should be owned by sys and the user account then needs execute privilege on this procedure only.
||     Users other than sys could own this procedure if they are given explicit select on dba_role_privs and dba_users.
|| Change History :
***************************/
   ( p_username IN VARCHAR2 )
AS
   v_num_superusers NUMBER := 0 ;
   v_num_users NUMBER := 0 ;
   v_num_locked NUMBER := 0 ;
BEGIN
   /* Check account exists */
   SELECT COUNT(*) INTO v_num_users FROM dba_users WHERE username = UPPER ( p_username ) ;
   IF v_num_users = 1
   THEN
      /* Check account is currently locked */
      SELECT COUNT(*) INTO v_num_locked FROM dba_users WHERE username = UPPER ( p_username ) and account_status LIKE '%LOCKED%' ;
      IF v_num_locked = 1
      THEN
         /* Check is not a superuser */
         SELECT COUNT(*) INTO v_num_superusers FROM sys.dba_role_privs WHERE grantee = UPPER(p_username) AND granted_role IN ('DBA','IMP_FULL_DATABASE') ;
         IF v_num_superusers = 0
         THEN
            /* Unlock account */
            EXECUTE IMMEDIATE 'alter user ' || p_username || ' account unlock' ;
            dbms_output.put_line ( 'Success : ' || p_username || ' unlocked.' ) ;
         ELSE
            dbms_output.put_line ( 'Warning : ' || p_username || ' is a superuser account, which cannot be unlocked by this utility, so no action taken.' ) ;
         END IF ;
      ELSE
         dbms_output.put_line ( 'Warning : ' || p_username || ' is not currently locked, so no action taken.' ) ;
      END IF ;
   ELSE
     dbms_output.put_line ( 'Error : ' || p_username || ' does not exist, please check input and try again.' ) ;
   END IF ;
END ;
/
sho err
grant execute on sys.unlock_user to my_normal_user ;