| How to automatically lock account after 10 minutes [message #404147] |
Wed, 20 May 2009 10:37  |
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 #404149 is a reply to message #404148] |
Wed, 20 May 2009 10:52  |
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 #404160 is a reply to message #404157] |
Wed, 20 May 2009 11:24 |
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 #404339 is a reply to message #404176] |
Thu, 21 May 2009 07:27 |
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 |
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 |
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 ;
/
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
