Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Aravindhan.

Asked: February 08, 2019 - 8:30 am UTC

Last updated: February 08, 2019 - 11:03 am UTC

Version: 11.2

Viewed 1000+ times

You Asked

Hello,

We have so many user ID locks from the front-end users. We need to provide a shell script to the user which they can run on OS level and unlock the ID themselves when the DBA is not present.

The sys password needs to be decrypted during runtime for script run & encrypted after script executes successfully. (Ideally they should not see the password)

We have multiple databases so the parameter has to be passed in the below manner only.

Example syntax : ./tmp/unlock_user.sh user instance_name

This is too much to ask. I know. If anyone can help, It will be great.

OS : Generic Linux
Database : 11gR2

and Chris said...

The sys password needs to be decrypted during runtime for script run & encrypted after script executes successfully.

No it doesn't!

There's no need to do this as SYS at all. Just create a procedure that unlocks a given username. With suitable protection against SQL injection:

grant create session, create procedure, alter user 
  to user_admin identified by user_admin;
  
conn user_admin/user_admin

create or replace procedure unlock_user (
  username varchar2
) as
begin

  execute immediate 'alter user ' || 
    sys.dbms_assert.schema_name ( username ) || 
    ' account unlock';
    
end unlock_user;
/


Having this as a shell script on the database server is also a bad idea. This means staff need accounts on this machine, which increases your security risks.

Create a simple web app instead. This connects as a user that has execute privs on the procedure above:

conn chris/chris

grant create session 
  to unlock_app_user identified by unlock_app_user;

grant execute on user_admin.unlock_user to unlock_app_user;

alter user hr account lock;

conn hr/hr

Connection Failed

conn unlock_app_user/unlock_app_user

exec user_admin.unlock_user ( 'MADE_UP_USER' );

ORA-44001: invalid schema

exec user_admin.unlock_user ( 'HR' );

conn hr/hr

select user from dual;

USER   
HR     


If you need to run this on many databases, create the procedure on each one. And add an option in the app that chooses the appropriate database to unlock the account on.

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Security

All of the vital components for a secure database are covered in the Security guide.