Home » RDBMS Server » Server Administration » How to automatically lock account after 10 minutes (10.2.0.4 Solaris)
How to automatically lock account after 10 minutes [message #404147] Wed, 20 May 2009 10:37 Go to next message
Orna
Messages: 62
Registered: November 2008
Member
Hi

I am setting up a special audited schema in my production DB that will be unlocked per request to enable users to perform certain operations.
I want the account to automatically lock after 10 minutes.

What is the best way to accomplish that ?
remember - a dbms_job or a cronjob that will run every 10 minutes and lock the account is not good - because that could be 2 minutes after it was unlocked.

it needs to be locked 10 minutes AFTER it was locked.

One option I thought about is to set a dbms_job that will look at the aud$ table every minute ( since this schema is audited ) and will check the last LOGON operation to this account
and if it passed 10 minutes - to lock it

Any other suggestions ? maybe something that is already implemented as an option and does not require coding ?
Orna
Re: How to automatically lock account after 10 minutes [message #404148 is a reply to message #404147] Wed, 20 May 2009 10:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you mean kill the session after 10 minutes, use profile to set max connection time.
If you mean lock account your can create a procedure to unlock the account and then submit a job to lock it within 10 minutes.

Regards
Michel
Re: How to automatically lock account after 10 minutes [message #404149 is a reply to message #404148] Wed, 20 May 2009 10:52 Go to previous messageGo to next message
Orna
Messages: 62
Registered: November 2008
Member
thank you

Yes, I will be limiting connect time and idle time via profile regardless.

I was talking about locking it for subsequent connections.
the problem with your approach is that you assume the account will always be unlocked using this procedur e- which might not be true.
I want the lock part to be independant of the way the account was unlocked.

I have another idea - I will set an on logon trigger that will submit a dbms job to lock it after 10 minutes !

Orna
Re: How to automatically lock account after 10 minutes [message #404157 is a reply to message #404149] Wed, 20 May 2009 11:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
the problem with your approach is that you assume the account will always be unlocked using this procedur e- which might not be true.

Why? Locking/unlocking are DBA jobs, if there are specific requirements they should be enclose inside procedure. In addition, you can grant the management of this account to some non-DBA users granting them the privilege to execute the procedure.
Nobody should unlock a locked account without a good reason and when the reason is known then the process is determined (I hope you have written processes for administrative tasks) and so the procedure should be used.
If not, you have an organizational problem, you can solve it in a technical way but it is a wrong one.

Quote:
I was talking about locking it for subsequent connections.

This is not the same requirement. Locking within 10 minutes allows subsequent connections... if they are in the 10 minutes.

Quote:
I have another idea - I will set an on logon trigger that will submit a dbms job to lock it after 10 minutes !

This is also not the same requirement. Imagine you unlock the account and user does not connect during 10 days, then the account is open during this time.

You must have a clear requirement.

Regards
Michel
Re: How to automatically lock account after 10 minutes [message #404160 is a reply to message #404157] Wed, 20 May 2009 11:24 Go to previous messageGo to next message
Orna
Messages: 62
Registered: November 2008
Member
1. Yep, I do have an organizational problem, you are correct - but I don't think I will be able to solve it anytime soon.

2. Yep - on logon trigger will not be a good solution because it means that the login should follow immediately after the unlock and that might not be the case always

I do have DDL trigger that tracks and logs all DDLs done on our production databases - I can modify this trigger to capture an unlock operation to this account ( pretty simple ) and then submit a job to lock it again after 10 minutes.
kind of dangerous messing with a system trigger - but worth a thought

orna
Re: How to automatically lock account after 10 minutes [message #404163 is a reply to message #404157] Wed, 20 May 2009 11:28 Go to previous messageGo to next message
Orna
Messages: 62
Registered: November 2008
Member
I think I might go back to the original suggestion you made and write a prodecure that will umlock and then lock again after 10 minutes.
and I will just have to see how that will work in our chaos

Orna
Re: How to automatically lock account after 10 minutes [message #404176 is a reply to message #404160] Wed, 20 May 2009 11:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I do have DDL trigger that tracks and logs all DDLs done on our production databases - I can modify this trigger to capture an unlock operation to this account ( pretty simple ) and then submit a job to lock it again after 10 minutes.
kind of dangerous messing with a system trigger - but worth a thought

You can also create a new one just for this task...

Quote:
I think I might go back to the original suggestion you made and write a prodecure that will umlock and then lock again after 10 minutes.

I think this is the best solution and if you really can't go to a new DDL trigger, don't mess up your current one.

Regards
Michel
Re: How to automatically lock account after 10 minutes [message #404339 is a reply to message #404176] Thu, 21 May 2009 07:27 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
How about a slightly different solution -

1) Are your users going to need to connect to the account multiple times? If not, let the trigger lock it as soon as they've connected - it won't disconnect them

2)create an on-logon trigger that resets the users password. That way the account can only be logged onto once.

3) The argument from the post above that the account could be unlocked for days if you use an on-login trigger is slightly spurious - yes, the account can be unlocked for an indefinite period, but only so long as no-one logs onto it - as soon as anyone connects via that account, then it'll be locked in 10 minutes. If you combine this with an overnight job to re-lock the account, then I don't see a security weakness here.
Re: How to automatically lock account after 10 minutes [message #404343 is a reply to message #404339] Thu, 21 May 2009 07:41 Go to previous messageGo to next message
Orna
Messages: 62
Registered: November 2008
Member
I cannot lock it immediately, or reset the password upon connect since people would want sometimes multiple sessions opened to perform whatever work they need. I don't want to restrict them to one session.

I think that an onlogon trigger that will fire upon login and submit a job to lock the account after 10 minutes is a good solution .
However - a nightly job to just lock the account again is a bit tricky since work on this acocunt can be 24*7 - and I don't want to lock the account if it was just opened .
Of course - that can be worked around by checking when was the last login to this account and lock it if it was over, say an hour ago.

thanks for you comments
Re: How to automatically lock account after 10 minutes [message #404354 is a reply to message #404339] Thu, 21 May 2009 08:29 Go to previous messageGo to next message
Orna
Messages: 62
Registered: November 2008
Member
this is what I ended up creating :

CREATE OR REPLACE TRIGGER firecall.lock_account_trigger
after logon on schema
declare

job_exists varchar2(1) := 0;

begin

select count(*)
into job_exists
from dba_jobs where job = 12347;

IF job_exists = 0
THEN

DBMS_JOB.isubmit(job => 12347, what => 'dba_scripts.lock_audited_account;', next_date => sysdate + 1/48);
commit;
END IF;


end ;
/
Re: How to automatically lock account after 10 minutes [message #404359 is a reply to message #404354] Thu, 21 May 2009 08:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"job" parameter in an OUTPUT one, you didn't choose the job number, Oracle chooses and returns it.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Re: How to automatically lock account after 10 minutes [message #404360 is a reply to message #404359] Thu, 21 May 2009 08:45 Go to previous messageGo to next message
Orna
Messages: 62
Registered: November 2008
Member
yeh, but it works that way too just fine
Re: How to automatically lock account after 10 minutes [message #404366 is a reply to message #404360] Thu, 21 May 2009 08:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Aaargh, I didn't see it, you use Isubmit, NEVER do that, it is an internal, not documented function, use SUBMIT.

Regards
Michel
Re: How to automatically lock account after 10 minutes [message #404367 is a reply to message #404366] Thu, 21 May 2009 08:52 Go to previous messageGo to next message
Orna
Messages: 62
Registered: November 2008
Member
Smile
will do
Re: How to automatically lock account after 10 minutes [message #404758 is a reply to message #404367] Sat, 23 May 2009 06:58 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

How people will able to find "Undocumented" function Smile
Re: How to automatically lock account after 10 minutes [message #404769 is a reply to message #404758] Sat, 23 May 2009 09:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
They copy old code, read bad advices on web or use "desc".

Regards
Michel
Re: How to automatically lock account after 10 minutes [message #405143 is a reply to message #404769] Tue, 26 May 2009 08:58 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It is somewhat documented - If you look on metalink (doc id 67687.1) then you get a published description of it, explaining what it does.
Re: How to automatically lock account after 10 minutes [message #405148 is a reply to message #405143] Tue, 26 May 2009 10:03 Go to previous message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I wonder what could be the purpose/benefit for a user (we) to give the job number? We have to first check it does not exist yet.

Regards
Michel
Previous Topic: Get ALL user definitions
Next Topic: SQLPLUS not logging in.
Goto Forum:
  


Current Time: Fri Dec 02 16:50:59 CST 2016

Total time taken to generate the page: 0.31700 seconds