Auditing an Oracle database for security issues is very important. PeteFinnigan.com provides all of the information and tools that you will need Click here for details of PeteFinnigan.com Limited's detailed Oracle database security audit service Click here for details of PeteFinnigan.com Limited's Oracle Security Training Courses
There are 60 visitors online    
Cookie Policy:We only use essential cookies on small sections of this website. For details see here.


Back

is it possible to lock out SYS using FAILED_LOGIN_ATTEMPTS in a profile

This very short article comes from a question i was asked on email by a visitor to my web site about whether it is OK to associate a profile with a failed_login_attempts setting for every user in the database including SYS. The person who asked me the question was afraid that if every account could be locked out including SYS then if someone wanted to brute force every account a Denial of Service could be affected.

Here is my answer to him with a short test showing that the SYS account cannot be locked out by setting the failed_login_attempts limit in the profile. On the other hand setting the failed_login_attempts for all users could potentially mean that those accounts are locked out. Here is the mail:

Thanks for your email, the answer to your question is simple - it is not possible to lock the SYS account by failed_login_attempts so a denial of service cannot be done on SYS but this also means it is possible to brute force the SYS account. It is possible to lock the SYS account explicitly by issuing "alter user sys account lock" - but this does not stop you logging in as SYS with "as sysdba" - so it is not possible to lock out SYS. Here is an example to show you:

	Connected to:
	Personal Oracle9i Release 9.2.0.1.0 - Production
	With the Partitioning, OLAP and Oracle Data Mining options
	JServer Release 9.2.0.1.0 - Production
	
	SQL> col profile for a10
	SQL> col resource_name for a25
	SQL> col limit for a15
	SQL> select profile,resource_name,limit
	  2  from dba_profiles
	  3  where resource_type='PASSWORD'
	  4  order by profile;
	
	PROFILE    RESOURCE_NAME             LIMIT
	---------- ------------------------- ---------------
	DEFAULT    FAILED_LOGIN_ATTEMPTS     UNLIMITED
	DEFAULT    PASSWORD_LIFE_TIME        UNLIMITED
	DEFAULT    PASSWORD_REUSE_TIME       UNLIMITED
	DEFAULT    PASSWORD_REUSE_MAX        UNLIMITED
	DEFAULT    PASSWORD_VERIFY_FUNCTION  NULL
	DEFAULT    PASSWORD_LOCK_TIME        UNLIMITED
	DEFAULT    PASSWORD_GRACE_TIME       UNLIMITED
	
	7 rows selected.
	
	SQL> alter profile default limit failed_login_attempts 1;
	
	Profile altered.
	
	SQL> select username,account_status
	  2  from dba_users
	  3  where username in ('SYS','SYSTEM','SCOTT');
	
	USERNAME                       ACCOUNT_STATUS
	------------------------------ --------------------------------
	SCOTT                          OPEN
	SYSTEM                         OPEN
	SYS                            OPEN
	
	SQL> -- lock scott first
	SQL> connect scott/junk@sans
	ERROR:
	ORA-01017: invalid username/password; logon denied
	
	
	Warning: You are no longer connected to ORACLE.
	SQL> -- first failure, next should lock it
	SQL> connect scott/junk@sans
	ERROR:
	ORA-28000: the account is locked
	
	
	SQL> -- OK scott is locked, now try system, first attempt should be 1017
	SQL> connect system,junk@sans
	Enter password: 
	
	SQL> connect system/junk@sans
	ERROR:
	ORA-01017: invalid username/password; logon denied
	
	
	SQL> -- next should lock it
	SQL> connect system/junk@sans
	ERROR:
	ORA-28000: the account is locked
	
	
	SQL> -- that works as expected, now try to lock SYS, again first try
	shou
	SQL> connect sys/junk@sans
	ERROR:
	ORA-01017: invalid username/password; logon denied
	
	SQL> -- next attempt should lock it??
	SQL> connect sys/junk@sans
	ERROR:
	ORA-01017: invalid username/password; logon denied
	
	
	Warning: You are no longer connected to ORACLE.
	SQL> -- didn't work, try again for luck!
	SQL> connect sys/junk@sans
	ERROR:
	ORA-01017: invalid username/password; logon denied
	
	
	SQL> connect sys/junk@sans
	ERROR:
	ORA-01017: invalid username/password; logon denied
	
	
	SQL> -- OK, so it is not possible to lock out SYS!
	SQL> connect sys/change_on_install@sans as sysdba
	Connected.
	SQL> select username,account_status
	  2  from dba_users
	  3  where username in ('SYS','SYSTEM','SCOTT');
	
	USERNAME                       ACCOUNT_STATUS
	------------------------------ --------------------------------
	SCOTT                          LOCKED(TIMED)
	SYSTEM                         LOCKED(TIMED)
	SYS                            OPEN
	
	SQL> -- put everything back as was
	SQL> alter user scott account unlock;
	
	User altered.
	
	SQL> alter user system account unlock;
	
	User altered.
	
	SQL> alter profile default limit failed_login_attempts unlimited;
	
	Profile altered.
	
	SQL> -- now test explicitly locking the SYS account
	SQL> alter user sys account lock;
	
	User altered.
	
	SQL> select username, account_status from dba_users
	  2  where username='SYS';
	
	USERNAME                       ACCOUNT_STATUS
	------------------------------ --------------------------------
	SYS                            LOCKED
	
	SQL> -- so it is locked, but we can still log in
	SQL> connect sys/change_on_install@sans as sysdba;
	Connected.
	SQL> 
						

OK, that's it, the above tests show that it is not possible to lock the SYS account out with a failed_login_attempts setting in your profile.



Back