Security: Setting an “Impossible” Password for SYS

I am currently busy trying to lock down (secure wise) APEX, which is, to say the least, very hard… Anyway one of my tricks from the old days (the days you couldn’t lock an account / database schema), was to lock an user account via the following alternative use of the ALTER USER statement

ALTER USER {username} IDENTIFIED BY VALUES ' {String} '

of course nowadays you can use the more complete syntax

ALTER USER {username} IDENTIFIED BY VALUES ' {String} ' ACCOUNT LOCK

Most of the time this method is used to reset the password to its original value.

I noticed in my (secured) Oracle 11g EE database version that passwords are not shown anymore via DBA_USERS

SQL> SHOW parameter sec_
 
sec_case_sensitive_logon             BOOLEAN     TRUE
sec_max_failed_login_attempts        INTEGER     10
sec_protocol_error_further_action    string      CONTINUE
sec_protocol_error_trace_action      string      TRACE
sec_return_server_release_banner     BOOLEAN     FALSE
sql92_security                       BOOLEAN     FALSE
 
SQL> SHOW USER
USER IS "SYSTEM"
 
SQL> SELECT * FROM v$version;
 
BANNER
--------------------------------------------------------------------------------
Oracle DATABASE 11g Enterprise Edition Release 11.1.0.7.0 - Production
PL/SQL Release 11.1.0.7.0 - Production
CORE    11.1.0.7.0      Production
TNS FOR Linux: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production
 
SQL> ALTER USER sys IDENTIFIED BY locked account UNLOCK;
 
USER altered.
 
SQL> SELECT username, account_status,  password FROM dba_users
  2  WHERE username IN ('SYS','SYSTEM');
 
USERNAME   ACCOUNT_STATUS  PASSWORD
---------- --------------- ------------------------------
SYSTEM     OPEN
SYS        OPEN

Thats not an big issue, because passwords are stored in the base table SYS.USER$. So you can query that one as well… If the TYPE# has the value 1 then these are users, if TYPE# has the value 0, then it is a ROLE or “PUBLIC”. An other old trick (but now idea if it still works) was to update TYPE#=1 where NAME=’PUBLIC’. Via EXP you could now export all “PUBLIC” objects…

Anyway…

 
SQL> DESC SYS.USER$
 Name                                      NULL?    TYPE
 ----------------------------------------- -------- ----------------------------
 USER#                                     NOT NULL NUMBER
 NAME                                      NOT NULL VARCHAR2(30)
 TYPE#                                     NOT NULL NUMBER
 PASSWORD                                           VARCHAR2(30)
 DATATS#                                   NOT NULL NUMBER
 TEMPTS#                                   NOT NULL NUMBER
 CTIME                                     NOT NULL DATE
 PTIME                                              DATE
 EXPTIME                                            DATE
 LTIME                                              DATE
 RESOURCE$                                 NOT NULL NUMBER
 AUDIT$                                             VARCHAR2(38)
 DEFROLE                                   NOT NULL NUMBER
 DEFGRP#                                            NUMBER
 DEFGRP_SEQ#                                        NUMBER
 ASTATUS                                   NOT NULL NUMBER
 LCOUNT                                    NOT NULL NUMBER
 DEFSCHCLASS                                        VARCHAR2(30)
 EXT_USERNAME                                       VARCHAR2(4000)
 SPARE1                                             NUMBER
 SPARE2                                             NUMBER
 SPARE3                                             NUMBER
 SPARE4                                             VARCHAR2(1000)
 SPARE5                                             VARCHAR2(1000)
 SPARE6                                             DATE
 
SQL> SELECT name, password, astatus
  2  FROM sys.USER$
  3  WHERE name IN ('SYS','SYSTEM')
  4  AND TYPE#=1;
 
NAME       PASSWORD                          ASTATUS
---------- ------------------------------ ----------
SYS        477C2D3AE566D7F1                        0
SYSTEM     C8E32F716C57E38A                        0

The ACCOUNT_STATUS from dba_users can be found via the SYS.USER_ASTATUS_MAP base table.

 
CREATE OR REPLACE FORCE VIEW "SYS"."DBA_USERS" 
 ("USERNAME", 
  "USER_ID", 
  "PASSWORD", 
  "ACCOUNT_STATUS", 
  "LOCK_DATE", 
  "EXPIRY_DATE", 
  "DEFAULT_TABLESPACE", 
  "TEMPORARY_TABLESPACE", 
  "CREATED", 
  "PROFILE", 
  "INITIAL_RSRC_CONSUMER_GROUP", 
  "EXTERNAL_NAME", 
  "PASSWORD_VERSIONS", 
  "EDITIONS_ENABLED"
 ) 
AS
SELECT u.name, u.USER#,
       decode(u.password, 'GLOBAL', u.password,
                          'EXTERNAL', u.password,
                          NULL
             ),
       m.STATUS,
       decode(u.astatus, 4, u.ltime,
                         5, u.ltime,
                         6, u.ltime,
                         8, u.ltime,
                         9, u.ltime,
                         10, u.ltime, to_date(NULL)
             ),
       decode(u.astatus,
              1, u.exptime,
              2, u.exptime,
              5, u.exptime,
              6, u.exptime,
              9, u.exptime,
              10, u.exptime,
              decode(u.ptime, '', to_date(NULL),
                              decode(pr.LIMIT#, 
                                     2147483647, to_date(NULL),
                                     decode(pr.LIMIT#, 
                                            0,decode(dp.LIMIT#, 
                                                     2147483647, to_date(NULL),
                                                     u.ptime +dp.LIMIT#/86400
                                                    ),
                                            u.ptime + pr.LIMIT#/86400
                                           )
                                    )
                    )
             ),
       dts.name, 
       tts.name, 
       u.ctime, 
       p.name,
       nvl(cgm.consumer_group, 'DEFAULT_CONSUMER_GROUP'), u.ext_username,
       decode(LENGTH(u.password),16,'10G ',NULL)||NVL2(u.spare4, '11G ' ,NULL),
       decode(bitand(u.spare1, 16), 16, 'Y','N')
FROM   sys.USER$ u LEFT OUTER JOIN sys.resource_group_mapping$ cgm            
       ON (cgm.attribute = 'ORACLE_USER' 
           AND cgm.STATUS = 'ACTIVE' 
           AND cgm.VALUE = u.name
          ),
       sys.ts$ dts, 
       sys.ts$ tts, 
       sys.profname$ p,
       sys.user_astatus_map m, 
       sys.profile$ pr, 
       sys.profile$ dp
WHERE  u.datats# = dts.ts#
   AND u.resource$ = p.profile#
   AND u.tempts# = tts.ts#
   AND u.astatus = m.STATUS#
   AND u.TYPE# = 1
   AND u.resource$ = pr.profile#
   AND dp.profile# = 0
   AND dp.TYPE#=1
   AND dp.resource#=1
   AND pr.TYPE# = 1
   AND pr.resource# = 1
 
1 ROW selected.
 
SQL> DESC SYS.USER_ASTATUS_MAP
 Name                                      NULL?    TYPE
 ----------------------------------------- -------- ----------------------------
 STATUS#                                   NOT NULL NUMBER
 STATUS                                    NOT NULL VARCHAR2(32)
 
SQL> SELECT * FROM SYS.USER_ASTATUS_MAP;
 
   STATUS# STATUS
---------- --------------------------------
         0 OPEN
         1 EXPIRED
         2 EXPIRED(GRACE)
         4 LOCKED(TIMED)
         8 LOCKED
         5 EXPIRED & LOCKED(TIMED)
         6 EXPIRED(GRACE) & LOCKED(TIMED)
         9 EXPIRED & LOCKED
        10 EXPIRED(GRACE) & LOCKED
 
9 ROWS selected.

Pete Finnigan (who else) has a good post and the principles behind it about USER_ASTATUS_MAP called: SYS.USER_ASTATUS_MAP missing values solved

As Pete is also discussing in his post “is it possible to lock out SYS using FAILED_LOGIN_ATTEMPTS in a profile“, it IS possible to lock the SYS account, but it hasn’t a big effect if you have an password file.

By the way I do not have an sqlnet.ora file with for example values for the parameter SQLNET.AUTHENTICATION_SERVICES set.

As Pete has demonstrated in his post the following is still possible if the SYS account is locked:

 
SQL> SHOW parameter pass
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile            string      EXCLUSIVE
 
SQL> ALTER USER SYS IDENTIFIED BY locked account LOCK;
 
USER altered.
 
SQL> -- I have an password file...
SQL> -- From the LOCAL machine (Linux as the Oracle software owner - ORACLE_SID has been set)
 
SQL> conn sys/locked AS sysdba
Connected.
 
SQL> SELECT username, account_status
  2  FROM dba_users
  3  WHERE username IN ('SYS','SYSTEM')
  4  ;
 
USERNAME   ACCOUNT_STATUS
---------- --------------------------------
SYSTEM     OPEN
SYS        LOCKED
 
2 ROWS selected.
 
SQL> -- From a REMOTE machine via a SQL*Plus client
 
SQL> conn sys/locked@stress11g AS sysdba
Connected.
 
SQL> SELECT username, account_status
  2  FROM dba_users
  3  WHERE username IN ('SYS','SYSTEM')
  4  ;
 
USERNAME   ACCOUNT_STATUS
---------- --------------------------------
SYSTEM     OPEN
SYS        LOCKED
 
2 ROWS selected.

The only way to block this “remote” behavior is to set an “impossible password”.

SQL> SELECT name, astatus, password
  2  FROM sys.USER$
  3  WHERE name IN ('SYS','SYSTEM')
  4  AND TYPE#=1
  5  ;
 
NAME          ASTATUS PASSWORD
---------- ---------- ------------------------------
SYS                 8 477C2D3AE566D7F1
SYSTEM              0 C8E32F716C57E38A
 
2 ROWS selected.
 
SQL> ALTER USER sys IDENTIFIED BY VALUES '-- LOCKED --' account LOCK;
 
USER altered.
 
SQL> SELECT name, astatus, password
  2  FROM sys.USER$
  3  WHERE name IN ('SYS','SYSTEM')
  4  AND TYPE#=1
  5  ;
 
NAME          ASTATUS PASSWORD
---------- ---------- ------------------------------
SYS                 8 -- LOCKED --
SYSTEM              0 C8E32F716C57E38A
 
2 ROWS selected.
 
SQL> -- My REMOTE client SQL*Plus 10.2.0.1.0 version on Windows now gives...
 
SQL> conn sys/locked@stress11g AS sysdba
ERROR:
ORA-00600: internal error code, arguments: [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s]
 
Warning: You are no longer connected TO ORACLE.

The database on the server didn’t crash. But this is a good example that you should be careful with unsupported statements. To be honest this is also the first time I see an ORA-00600 error while applying this “method”. I will have to check if this is also happening on “unsecured” older versions like Oracle 10gRx.

For normal users the following happens

SQL> CREATE USER LOCKED IDENTIFIED  BY LOCKED account UNLOCK;
 
USER created.
 
SQL> GRANT dba TO LOCKED;
 
GRANT succeeded.
 
SQL> conn LOCKED/LOCKED
Connected.
SQL> ALTER USER LOCKED IDENTIFIED BY VALUES 'LOCKED' account UNLOCK;
 
USER altered.
 
SQL> SELECT name, astatus, password
  2  FROM sys.USER$
  3  WHERE name IN ('SYS','SYSTEM','LOCKED')
  4  AND TYPE#=1;
 
NAME          ASTATUS PASSWORD
---------- ---------- ------------------------------
SYS                 8 -- LOCKED --
SYSTEM              0 C8E32F716C57E38A
LOCKED              0 LOCKED
 
3 ROWS selected.
 
SQL> conn LOCKED/LOCKED
ERROR:
ORA-01017: invalid username/password; logon denied
 
 
Warning: You are no longer connected TO ORACLE.
 
SQL> conn LOCKED/"LOCKED"
ERROR:
ORA-01017: invalid username/password; logon denied
 
SQL>

So there is no way in anymore. Locked or not…

Luckily no ORA-00600 error here.

Is this information useful? Not really, anymore at least… In the old days (Oracle 7) this was the only way to lock an account. Nowadays there are a lot off methods you can apply (LOCK, EXPIRE etc). Removing the password file so remote login isn’t allowed anymore is one off those methods.

For me this was a nice exercise on a TEST environment (a virtual machine I always can reset to its snapshot) to actually see what the consequences are / could be too lock the SYS account and what still works and doesn’t work in such an Oracle database environment…

HTH

8-)

PS

I noticed that when the database parameter remote_login_passwordfile is set and has the value EXCLUSIVE, that you are still allowed to login REMOTELY with “the old password” (the one used before setting the “impossible” one). To avoid this behavior you can set the database parameter remote_login_passwordfile to NONE, but you could ask yourself if setting an “impossible” password still makes sense.