Oracle 11g Security – Those Pesky Predefined Accounts

After a
normal creation of an Oracle database, 22+ Predefined Oracle Database
Administrative Accounts, 10+ Predefined Oracle Database Non-Administrative User
Accounts, and 6 Default Sample Schema User Accounts that are automatically
created for you. Each of these accounts have specific duties within the
database and for the most part are turned off for general use. Predefined
administrative accounts, as the name suggests, have special privileges that
normal users would not typically have. For instance, we are all aware of the
SYS and SYSTEM users that are loaded with high-level database privileges to
perform every database administrative task imaginable. For this reason, yet not
often adhered to, the SYS and SYSTEM accounts should nearly never be used but
instead grant specific privileges to pseudo database administrative
accounts—protecting the use and miss-use of these two important accounts. Likewise,
the other predefined administrative accounts should be handled with the same
amount of caution. Non-administrative accounts, while they don’t have the
high-level privileges as administrative accounts still have privileges that
should not be overlooked. Keep in mind that a SELECT statement can be just as
dangerous as a DELETE statement when the data selected is used maliciously. Then
there are the sample schema accounts that no one really cares much about. These
accounts have great power within their own sample schema but limited, if any,
privileges outside their schema. Just beware, as with any account, DBAs and
developers often, in an attempt to hurry testing, will grant higher levels of
privileges to these accounts. This has the potential to create great harm if
any form of migration were to happen from say development or test into a
production environment and these accounts were propagated as well. You can
quickly find out just how many of these default accounts have been defined
within your system by executing the following SQL statement.

SELECT ‘Predefined Administrative User Accounts’, count(*)
  FROM dba_users
 WHERE username in
      (‘ANONYMOUS’, ‘CTXSYS’,   ‘DBSNMP’, ‘EXFSYS’, ‘LBACSYS’,
       ‘MDSYS’,     ‘MGMT_VIEW’,’OLAPSYS’,’OWBSYS’, ‘ORDPLUGINS’,
       ‘ORDSYS’,    ‘OUTLN’,    ‘SI_INFORMTN_SCHEMA’,’SYS’,
       ‘SYSMAN’,    ‘SYSTEM’,   ‘TSMSYS’, ‘WK_TEST’, ‘WKSYS’,
       ‘WKPROXY’,   ‘WMSYS’,    ‘XDB’)
UNION
SELECT ‘Default Sample Schema User Accounts’, count(*)
  FROM dba_users
 WHERE username in (‘BI’,’HR’,’OE’,’PM’,’IX’,’SH’)
UNION
SELECT ‘Predefined Non-Administrative User Accounts’, count(*)
  FROM dba_users
 WHERE username in
      (‘APEX_PUBLIC_USER’,’DIP’,   ‘FLOWS_30000′,’FLOWS_FILES’,’MDDATA’,
       ‘ORACLE_OCM’,      ‘PUBLIC’,’SPATIAL_CSW_ADMIN_USER’,
       ‘SPATIAL_WFS_ADMIN_USR’,    ‘XS$NULL’);
‘PREDEFINEDADMINISTRATIVEUSERACCOUNTS’        COUNT(*)
——————————————- ———-
Default Sample Schema User Accounts                  1
Predefined Administrative User Accounts             21
Predefined Non-Administrative User Accounts          7

While the
SQL isn’t that exciting it does give you a quick glimpse into the number of
accounts you need to keep your eyes on if you decide to allow them to exist
within your Oracle database. If you were to look at the 2 Day + Security Guide
you would immediately rest comfortably knowing that all but a very small
handful of these accounts are Expired and Locked after database creation.
Expiration on an account, after initial database creation, means that there is
no password assigned on the account so connection is impossible. To un-expire
an account simply requires an administrator to assign a password to it. If an
account is locked that means that there is a password assigned but makes the
account unavailable for anyone who would like to connect to the database using
it. So the fact that these accounts are Expired and Locked would to me suggest
that they are initially created in the database without a password but also are
locked; somewhat of a double-edged sword for those attempting to use them. We
easily verify the expiration and locks on accounts by yet another simple query.

SELECT ‘Administrative User    : ‘, username, account_status, lock_date, expiry_date
  FROM dba_users
 WHERE username in
      (‘ANONYMOUS’, ‘CTXSYS’,   ‘DBSNMP’, ‘EXFSYS’, ‘LBACSYS’,
       ‘MDSYS’,     ‘MGMT_VIEW’,’OLAPSYS’,’OWBSYS’, ‘ORDPLUGINS’,
       ‘ORDSYS’,    ‘OUTLN’,    ‘SI_INFORMTN_SCHEMA’,’SYS’,
       ‘SYSMAN’,    ‘SYSTEM’,   ‘TSMSYS’, ‘WK_TEST’, ‘WKSYS’,
       ‘WKPROXY’,   ‘WMSYS’,    ‘XDB’)
UNION
SELECT ‘Sample Schema User     : ‘, username, account_status, lock_date, expiry_date
  FROM dba_users
 WHERE username in (‘BI’,’HR’,’OE’,’PM’,’IX’,’SH’)
union
SELECT ‘Non-Administrative User: ‘, username, account_status, lock_date, expiry_date
  FROM dba_users
 WHERE username in
      (‘APEX_PUBLIC_USER’,’DIP’,   ‘FLOWS_30000′,’FLOWS_FILES’,’MDDATA’,
       ‘ORACLE_OCM’,      ‘PUBLIC’,’SPATIAL_CSW_ADMIN_USER’,
       ‘SPATIAL_WFS_ADMIN_USR’,    ‘XS$NULL’);
‘ADMINISTRATIVEUSER:’     USERNAME              ACCOUNT_STATUS    LOCK_DATE EXPIRY_DA
————————- ——————— —————– ——— ———
Administrative User    :  ANONYMOUS             EXPIRED & LOCKED  27-AUG-08 27-AUG-08
Administrative User    :  CTXSYS                EXPIRED & LOCKED  27-AUG-08 27-AUG-08
Administrative User    :  DBSNMP                OPEN                        06-DEC-09
Administrative User    :  EXFSYS                EXPIRED & LOCKED  27-AUG-08 27-AUG-08
Administrative User    :  MDSYS                 EXPIRED & LOCKED  27-AUG-08 27-AUG-08
Administrative User    :  MGMT_VIEW             OPEN                        06-DEC-09
Administrative User    :  OLAPSYS               EXPIRED & LOCKED  27-AUG-08 27-AUG-08
Administrative User    :  ORDPLUGINS            EXPIRED & LOCKED  27-AUG-08 27-AUG-08
Administrative User    :  ORDSYS                EXPIRED & LOCKED  27-AUG-08 27-AUG-08
Administrative User    :  OUTLN                 EXPIRED & LOCKED  27-AUG-08 27-AUG-08
Administrative User    :  OWBSYS                EXPIRED & LOCKED  27-AUG-08 27-AUG-08
Administrative User    :  SI_INFORMTN_SCHEMA    EXPIRED & LOCKED  27-AUG-08 27-AUG-08
Administrative User    :  SYS                   OPEN                        06-DEC-09
Administrative User    :  SYSMAN                OPEN                        06-DEC-09
Administrative User    :  SYSTEM                OPEN                        06-DEC-09
Administrative User    :  TSMSYS                EXPIRED & LOCKED  27-AUG-08 27-AUG-08
Administrative User    :  WKPROXY               EXPIRED & LOCKED  27-AUG-08 27-AUG-08
Administrative User    :  WKSYS                 EXPIRED & LOCKED  27-AUG-08 27-AUG-08
Administrative User    :  WK_TEST               EXPIRED & LOCKED  27-AUG-08 27-AUG-08
Administrative User    :  WMSYS                 EXPIRED & LOCKED  27-AUG-08 27-AUG-08
Administrative User    :  XDB                   EXPIRED & LOCKED  27-AUG-08 27-AUG-08
Non-Administrative User:  APEX_PUBLIC_USER      EXPIRED & LOCKED  27-AUG-08 27-AUG-08
Non-Administrative User:  DIP                   EXPIRED & LOCKED  03-AUG-07
Non-Administrative User:  FLOWS_FILES           EXPIRED & LOCKED  27-AUG-08 27-AUG-08
Non-Administrative User:  MDDATA                EXPIRED & LOCKED  27-AUG-08 27-AUG-08
Non-Administrative User:  ORACLE_OCM            EXPIRED & LOCKED  27-AUG-08 27-AUG-08
Non-Administrative User:  SPATIAL_WFS_ADMIN_USR EXPIRED & LOCKED  27-AUG-08 27-AUG-08
Non-Administrative User:  XS$NULL               EXPIRED & LOCKED  27-AUG-08 27-AUG-08
Sample Schema User     :  HR                    OPEN                        02-SEP-09

As you
can see, most of the accounts on my system are still under the Expired and
Locked status. The only OPEN accounts are those that Oracle’s default
installation opened or I played around with. To expire/un-expire and
lock/un-lock an account is quite easy. Use of the following (altered) ALTER
USER syntax clearly shows what we need to do in each instance.

ALTER USER
  { user
    { IDENTIFIED BY password
    | PASSWORD EXPIRE
    | ACCOUNT { LOCK | UNLOCK }
    }
  } ;

So if we
play around with the ANONYMOUS account:

1. 
Connection is
restricted since the status is EXPIRED & LOCKED

SQL> connect anonymous/abc
ERROR:
ORA-28000: the account is locked

2. 
Remove the
EXPIRED status by assigning a password

SQL> ALTER USER anonymous IDENTIFIED BY abc;
User altered.

And if we look at the status, it has removed the EXPIRED
part:

USERNAME              ACCOUNT_STATUS    LOCK_DATE EXPIRY_DA
——————— —————– ——— ———
ANONYMOUS             LOCKED            27-AUG-08 10-MAR-10

But ANONYMOUS user still can’t connect:

SQL> connect anonymous/abc;
ERROR:
ORA-28000: the account is locked

3. 
Remove the
LOCKED status by UNLOCKing the account

SQL> ALTER USER anonymous ACCOUNT UNLOCK;
User altered.

And if we look at the status, it has removed the EXPIRED
part:

USERNAME              ACCOUNT_STATUS    LOCK_DATE EXPIRY_DA
——————— —————– ——— ———
ANONYMOUS             OPEN                        10-MAR-10

User ANONYMOUS can now connect:

SQL> connect anonymous/abc
Connected.

It should serve as a warning that when the account has a status of EXPIRED & LOCKED after initial database creation it is much different than if that account is altered to that status after an account has been assigned a password and unlocked. Some tools, such as SQL*Plus will allow the user the chance to change the password if the old password is properly supplied. For example:

1.  We can easily expire the password for the ANONYMOUS account

SQL> ALTER USER anonymous PASSWORD EXPIRE;
User altered.

And the status would clearly be:

USERNAME              ACCOUNT_STATUS    LOCK_DATE EXPIRY_DA
——————— —————– ——— ———
ANONYMOUS             EXPIRED                     11-SEP-09

2. 
The ANONYMOUS
account would be prompted for a password and allowed to connect after entering
a new password:

SQL> connect anonymous/abc
ERROR:
ORA-28001: the password has expired
Changing password for anonymous
New password: xyz
Retype new password: xyz
Password changed
Connected.

By contrast, if you really wanted to lock out a user from connecting in the future, regardless of password expiration, you should LOCK the account. For example:

3.  We can easily lock the ANONYMOUS account

SQL> ALTER USER anonymous ACCOUNT LOCK;
User altered.

And the status would clearly be:

USERNAME              ACCOUNT_STATUS    LOCK_DATE EXPIRY_DA
——————— —————– ——— ———
ANONYMOUS             LOCKED            11-SEP-09 10-MAR-10

4. 
The ANONYMOUS
account is totally locked out and unable to connect:

SQL> connect anonymous/abc
ERROR:
ORA-28000: the account is locked

Expiring
and locking user accounts are one of the first steps to securing who can
connect to an Oracle database. The expiration of account passwords is not an
effective lock-out mechanism but should instead be used to encourage users to
just change their passwords over time. Locking an account though has the effect
of truly locking out an account so no one can use it to connect to the
database. If you need to disable an account this is the preferred method other
than dropping it. Just be warned that expiring an account leaves the prior
password intact. Under this condition if you were to lock the account and
someone came up behind your work and unlocked the account, the user could still
log in by changing the password. It is therefore suggested to alter the
password before expiring and locking. That way even if the lock were removed
the user would have no way of ever connecting again.

»


See All Articles by Columnist James Koopmann