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:
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.
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>
Back