Oracle – How to Unlock User Shell Script for Oracle

linuxoracleoracle-11g-r2unix

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

Best Answer

We have so many user ID locks from the front-end users.

Good Problem Statement.
More common than you might think.

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.

No. You don't.

You need to find out why the users are locking their accounts so often and resolve the Root Cause of that problem, not fiddle about trying to fix the Symptoms.

The sys password needs to be decrypted during runtime for script run

No.
Just ... No.

... & encrypted after script executes successfully. (Ideally they should not see the password)

There should never be any possibility of Users seeing the SYS password.
EVER.

Remember: your job as DBA is to clean up the mess that other people make. Always keep the biggest and best Tools for yourself.

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 may be part of your Problem.

If Users have different passwords in different databases, then they're going to get themselves muddled up and lock themselves out.

Do your users really have shell access to your database hosts?

Next you'll be telling me that they connect directly to the Database and run SQL ...

Moving forward ...

If there's an Application layer between the Users and your Database, then you should consider introducing an "Application" account, whose credentials are used exclusively by the Application and that users are never aware of. All database connections will be made using these credentials and Users will not have their own accounts in your databases. OK, this means that you'll have to re-implement account authentication, because the database won't be doing it for you, but that shouldn't be a huge job.

Using an Application account has other benefits.

If a User "locks themselves out" in this "shared-connection" model, the Application can still support them in resetting their password (or whatever), because the Application has its own credentials and connection to the Database. If the User has their own credentials and connection, then they cannot get into the database to reset their password in the database, hence your current predicament.

If there is no Application layer between the Users and the Database (and I would seriously ask "why not"?), then you may have to look at something [a lot] more intrusive, like hooking Oracle into Kerberos or [indirectly] Active Directory, to provide you Users with a true, single signon experience.

Related Question