** Oracle Security: Locking User Accounts ** 1

[rasprer]

Hi Guys,

Is there function or setting that will 'Lock' as user account "after so many days of inactivity"?. Say that a user account was created on March 1st and used for a few days. It is now April 1st...and we would like to lock this account.

Is this possible through Oracle? If not, is there an alternative to doing this?

Your advice in this matter is appreciated!!

 

[rasprer]

Can someone please provide info? I'm stuck

 

[carp]

I'm not aware of any out-of-the-box method, but it seems to me that you could create a table (xx_user_logons) that consists of two columns (user_name, last_logon). Then create a login trigger that updates the user's record with the current date (If the user has no record in the table, then insert one.).

Then a second program could be set up to run each night. It would examine the table and lock any accounts where the time between the current sysdate and the last_logon surpasses your designated threshold (e.g., 30 days).

Elbert, CO
1126 MDT

 

[rasprer]

Thanks CARP...

What table or tables other than the new table would I trigger the event on...Which system table would be used?

 

[carp]

Logon triggers fire when the user logs on. So you don't have to worry about hanging the trigger on a table; Oracle takes care of it for you.

Check under the Triggers section of the PL/SQL Users Guide. This will give you an example of how to create a logon trigger.

Elbert, CO
1137 MDT

 

[rasprer]

Thanks Carp...I'll check for 'Logon Trigger' samples. Would you happen to have a link available or any viable resources?

 

[SantaMufasa]

Following are the components of my "Login Trigger" that documents the most recent connection of each user to the database:

Section 1 -- SYS's "Logon" table:

CREATE TABLE SYS.LOGONS
       (MIN_LOGIN_TIME DATE,
        MAX_LOGIN_TIME DATE,
        LOGIN_CNT NUMBER,
        OS_USER_NAME VARCHAR2(100),
        MACHINE_TERMINAL VARCHAR2(100),
        ORACLE_USER_NAME VARCHAR2(30),
        LATEST_SESSION_ID NUMBER,
         CONSTRAINT LOGONS_OSUSER_MACHINE_OCUSER UNIQUE (OS_USER_NAME,
	MACHINE_TERMINAL, ORACLE_USER_NAME));

Section 2 -- My function that gathers specific login information by category:

create or replace function sys.get_session_info
		(sess_id    in number
		, info_type in varchar2) return varchar2
	is
		w_osuser	varchar2(100);
		w_machine	varchar2(100);
		w_user		varchar2(100);
	begin
		for i in (Select	osuser, machine, user into
					w_osuser, w_machine, w_user
					from v$session
					where audsid = sess_id
					order by logon_time desc) loop
			if	upper(info_type) = 'OSUSER' then
				return w_osuser;
			elsif	upper(info_type) = 'MACHINE' then
				return w_machine;
			elsif	upper(info_type) = 'USER' then
				return w_user;
			end if;
		end loop;
	end;
/*	Above function accesses session information */
/

Section 3 -- Actual Login-audit trigger:

CREATE OR REPLACE TRIGGER sys.logontrigger
AFTER LOGON ON DATABASE
DECLARE
	cnt_hold	number;
BEGIN
	select count(*) into cnt_hold
		from sys.logons
		where OS_USER_NAME = get_session_info(userenv('sessionid'),'OSUSER')
		  and MACHINE_TERMINAL = get_session_info(userenv('sessionid'),'MACHINE')
		  and ORACLE_USER_NAME = get_session_info(userenv('sessionid'),'USER');
	if cnt_hold = 0 and get_session_info(userenv('sessionid'),'USER') is not null then
		INSERT INTO sys.logons
			(MIN_LOGIN_TIME         
			,MAX_LOGIN_TIME  
			,Login_cnt       
			,OS_USER_NAME           
			,MACHINE_TERMINAL       
			,ORACLE_USER_NAME       
			,LATEST_SESSION_ID
			)
		VALUES
		(sysdate
		,sysdate
		,1
		,get_session_info(userenv('sessionid'),'OSUSER')
		,get_session_info(userenv('sessionid'),'MACHINE')
		,get_session_info(userenv('sessionid'),'USER')
		,userenv('sessionid'));
	else
		update sys.logons set
			 	MAX_LOGIN_TIME = sysdate
				,login_cnt = login_cnt+1
				,LATEST_SESSION_ID = userenv('sessionid')
			where OS_USER_NAME = get_session_info(userenv('sessionid'),'OSUSER')
			  and MACHINE_TERMINAL = get_session_info(userenv('sessionid'),'MACHINE')
			  and ORACLE_USER_NAME = get_session_info(userenv('sessionid'),'USER');
	end if;
END;
/*	Above trigger audits all database logins */
/

Section 4 -- Script that displays formatted report of sys.logons table (whose output too wide to display here):

REM ***************************************************************
REM Script produces formatted report of logins, sorted by date/time.
REM ***************************************************************
col a heading "Earliest Login Date/Time" format a26
col a2 heading "Latest Login Date/Time" format a26
col b heading "Latest|Oracle|Session|ID" format 99999999
col c heading "OS User Login Name" format a20
col d heading "Login Machine/|Terminal Name" format a25
col e heading "Oracle User Name" format a20
col f heading "Number|of|Logins" format 9,999,999
set linesize 200
select     to_char(min_login_time,'YYYY-Mon-DD (Dy) hh24:mi:ss') a
   ,to_char(max_login_time,'YYYY-Mon-DD (Dy) hh24:mi:ss') a2
   ,login_cnt f
   ,Latest_Session_ID b
   ,os_user_name c
   ,machine_terminal d
   ,Oracle_user_name e
from sys.logons
order by max_login_time

Section 5 -- Then whenever I want to restrict dormant users, I run the following code against my logon-trigger's sys.logon table:

set feedback off
set pagesize 0
set trimspool on
spool temp.sql
select 'alter user '||name||' account lock;'
from
(select oracle_user_name name
from sys.logons
where (oracle_user_name,max_login_time) in
(select oracle_user_name, max(max_login_time)
from sys.logons
group by oracle_user_name
having sysdate-max(max_login_time) > 30) -- 30 days is max dormant period
union
(select username from dba_users where account_status = 'OPEN'
minus select distinct oracle_user_name from sys.logons)
)
where name not in ('SYS','SYSTEM')
/
spool off
prompt
prompt To lock accounts, '@temp'
prompt


alter user DBSNMP account lock;
alter user OUTLN account lock;
alter user SUMMIT account lock;
alter user TEST2 account lock;
alter user TFQY_DICTPROD account lock;
alter user USER1 account lock;
alter user USER2 account lock;
alter user USER_ID account lock;
alter user YADA2 account lock;
alter user YADA3 account lock;

To lock accounts, '@temp'

Let us know if this resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA @ 19:06 (26May04) UTC (aka "GMT" and "Zulu"), 12:06 (26May04) Mountain Time)

 

[rasprer]

Mufasa...Thanks so much...I'll try it.

 

[rasprer]

Mufasa...

Where did you get this code...Is this what you are using currently.

 

[SantaMufasa]

Rasprer,

Yes, I wrote and use the code. Is there an issue?

Mufasa
(aka Dave of Sandy, Utah, USA @ 19:39 (26May04) UTC (aka "GMT" and "Zulu"), 12:39 (26May04) Mountain Time)

 

[rasprer]

No Mufasa...Not at all. I was just wondering if an Oracle schedule can be created to process the 'Account lock' command for those users who have not logged in after 30days?

Thank you so much.

 

[SantaMufasa]

Rasprer,

Yes, you certainly could schedule this activity within "DBMS_JOBS" for example, to run on a regular basis.

Mufasa
(aka Dave of Sandy, Utah, USA @ 21:21 (26May04) UTC (aka "GMT" and "Zulu"), 14:21 (26May04) Mountain Time)