Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Sep 16, 2009

Oracle 11g Security - Those Pesky Predefined Accounts

By James Koopmann

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



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM


















Thanks for your registration, follow us on our social networks to keep up-to-date