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 expiredChanging 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.
