1

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

  • Why was it locked? – Michael Kutz Feb 8 at 10:50
  • :) That really doesn't matter. We need to develop a shell script for this. – Aravindhan K Feb 8 at 13:02
  • Use PASSWORD_LOCK_TIME=1/48 to automatically unlock them after 30 minutes instead. – eckes Feb 8 at 13:04
  • If a user gets the ability to run other commands as sys they can cause a lot of damage from destroying the database to changing passwords or being able to act as other users when they should not be able to. – Joe W Feb 8 at 13:56
  • You can’t do that with a script the user has access to. You. Ishtar be able to do it with a sudo wrapped command however it is easier to use a server based solution (little web app), however as written before automatic unlock is much less complicated. – eckes Feb 8 at 21:05
4

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.

  • You might want to include some reasons as to why this should never happens such as what a user could do if they are able to either modify the script that is being run or directly login as sys as a result of this. With a quick example of them deleting everything in the database. – Joe W Feb 8 at 13:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.