Securing (Default) User Accounts

A new Oracle database typically comes installed with over 20 default database schemas (the actual number will vary because some of these schemas are optionally installed during the database creation).

As a Google search on “Default Oracle Users” illustrates, the names, passwords, and privileges of these accounts are anything but secret. These accounts are often used to store metadata and procedures for specific database options, such as the Text Option and the Spatial Option. Consequently, many of these accounts have very significant privileges. They may also have well-known passwords listed both in the Oracle product documentation and on the Internet. This combination creates a risk that an unauthorized person will connect to one of these privileged accounts and access, or manipulate, your sensitive data.

During database creation, you can use the Database Configuration Assistant (DBCA) to choose which default accounts (directly associated with database options) to install. It’s important to be selective in your decision about the options you need for your database. Installing options that you’ll not be using creates an unnecessary risk.

Keep in mind that commercial applications, as well as Oracle applications, will also have associated and well-known schemas. They all represent targets of opportunity for a hacker. These accounts should also be closely guarded.

While the number of accounts and the associated privileges vary from release to release, it’s important to ensure these accounts are secured to limit the risk stated above. This section offers suggestions on how to ensure these accounts are secure. Whether you are securing an Oracle created account, or one that you have created, the process for securing these accounts is the same.

Securing Access and Logon

The following suggestions offer ways of controlling access to database accounts. The actions range from restricting logins to the account to removing the account entirely. Combining several of these suggestions together is good practice as it supports a defense in depth approach.

• Change the default passwords and create a strong password. The DBCA provides a shortcut for creating the initial passwords during the database creation. It allows you to use the same password for all the accounts. Do not choose this option. Create a strong and different password for each schema!

• Create an impossible password. After installation, this little trick, which is covered in the upcoming “Oracle Passwords” section, maintains the account objects and privileges but prevents anyone from directly logging in because the password can’t be supplied.

• Create a database log-on trigger to check for specific users that you don’t want to log in, and fail the trigger if one tries. A failed log-on trigger prevents a user from logging in. This technique doesn’t work for certain privileged users, such as SYS (SYSDBA) and users with the ADMINISTER DATABASE TRIGGER system privilege. Nevertheless, it may be advantageous to only allow these privileged users into the database. As such, this is an excellent little trick for locking out all other users.

• Revoke CREATE SESSION and/or the CONNECT role. Removing the privilege to log in to the database is an obvious way to prevent someone from logging in to an account. Note this will prevent both hackers and legitimate users and applications from logging in to the account. It doesn’t matter if they know the password; the privilege to log on has been removed.

• Lock the account. This is a preferred option because it keeps all the data objects and associated procedures while preventing people from logging in. The effect to this is similar to revoking the privilege to log on to the database but no privileges have to be revoked. This capability was introduced with the Oracle9i Database.

• Revoke all privileges and roles. Revoking the schema’s privileges allows you to maintain all the existing data while helping to ensure that if the account is compromised, the hacker will not be able to use privileges to access or manipulate data in other schemas. This suggestion is applicable to the schemas that you believe are no longer being used but are reluctant to remove completely. It’s particularly useful for the very privileged default schemas installed with the database. Note that revoking privileges could break procedures that are defined within the schema. It’s a good idea to capture all the privileges and role grants before revoking them in case you later need to undo this action.

• Drop the schema. You can drop the schemas that you don’t need. However, there is significant risk to doing this. Dropping schemas is very destructive. Not only can the user no longer connect, but all of the tables, data, and procedures are gone, too.

For certain database options, such as the Oracle Label Security, there’s an officially supported process for removing the option and schema. The Oracle Universal Installer is the best tool for removing already installed database options. Before dropping any Oracle installed schemas, consult the Oracle product documentation to ensure that your removal of the schema is done correctly. Although dropping schemas is the most certain measure you can take to guarantee the account will not be compromised (because it no longer exists), it should be used with caution.

Lock Down Example

This example illustrates how you might accomplish the task of securing a default account. The following code snippet shows this process as done for the MDSYS schema. MDSYS is the schema that supports the Oracle Spatial technology and as such has been granted access (by way of role privileges) to many powerful procedures and data.

Securing Access to Default Accounts

In the default installation, the MDSYS account is locked and the password is expired. This means that the account is made accessible by unlocking the schema and providing the initial password that just happens to be “mdsys.” A user with the ALTER USER system privilege only has to unlock the MDSYS account to gain access. Since you may never actually need to log in to this account, there are a couple things you can do to further secure it.

First, revoke the CREATE SESSION privilege and the CONNECT role from MDSYS. You do this because the CONNECT role has been granted the CREATE SESSION privilege, too, so the MDSYS has the privilege twice—once as a direct grant, and once as an indirect grant received via the CONNECT role. Revoking only the role or only the privilege will not prevent someone from logging in as this user.

After you revoke the privileges, modify the password. The default password is mdsys, which could be easily guessed. In the following example, the privileges are revoked from MDSYS and the password is altered.

sec_mgr@KNOX10g> REVOKE CONNECT, CREATE SESSION FROM MDSYS;  Revoke succeeded.  sec_mgr@KNOX10g> ALTER USER MDSYS IDENTIFIED BY ti1hp2r4m;  User altered.  

This process helps to secure the account while still making it usable. That is, the spatial data features can still be used.

Securing Access to Application Schemas

For an example of a schema that represents one you have created, let’s look at securing the SCOTT schema. Prior to Oracle Database 10g, the SCOTT schema was often created and available through the well-known password tiger, and the account was not locked.

Assume your requirement is to maintain the schema’s data while preventing someone from logging into the account. In Oracle9i Database and beyond, you can lock the account. Our approach is to augment this with a few additional measures. First, build a database log-on trigger to prevent someone from logging into the SCOTT schema.

sec_mgr@KNOX10g> CREATE OR REPLACE TRIGGER logon_check     2      AFTER LOGON ON DATABASE    3  BEGIN    4      IF (SYS_CONTEXT ('USERENV', 'SESSION_USER') = 'SCOTT')    5      THEN    6        raise_application_error (-20001,    7                                 'Unauthorized Login');     8      END IF;    9  END;    10 /    Trigger created.   

An attempt to connect as SCOTT results in the following:

sec_mgr@KNOX10g> conn scott/tiger  ERROR:  ORA-00604: error occurred at recursive SQL level 1  ORA-20001: Unauthorized Login  ORA-06512: at line 4    Warning: You are no longer connected to ORACLE. 

Two words of caution with the log-on trigger approach. First, while log-on triggers can be a security ally, they will fire for every user log on and can subsequently degrade the database connection time. In the previous example, the time would be unperceivable. However, if your trigger code queries tables or makes an external call, the degradation could become very noticeable and make this an unviable alternative.

When database log-on triggers were initially released with Oracle8i Database, an exception thrown in the trigger would prevent the user from logging in. Often the exceptions were neither intentional nor handled gracefully. The result was that all users were unable to log on to the database. The only way to get back in to the database was to connect as SYSDBA (internal) and drop or disable the log-on trigger.

To prevent this inadvertent lock out, the Oracle9i Database was altered to not expel users with the ADMINISTER DATABASE TRIGGER system privilege if the log-on trigger throws an exception. This privilege has been granted to the DBA role, so any user with the DBA role will also bypass the previous log-on trigger technique. You can determine who will be exempt from the log-on trigger exceptions with the following query, which lists users and roles that have been granted the privilege.

SELECT grantee    FROM dba_sys_privs  WHERE PRIVILEGE = 'ADMINISTER DATABASE TRIGGER';   

Removing Privileges

Another technique for securing an account is to revoke all privileges and roles that have been granted to the schema. To do this efficiently, use the following procedure, which accepts the username as a parameter and removes system privileges, object privileges, and roles. The procedure also prints the undo statements that can be used to recreate the privileges on the schema.

CREATE OR REPLACE PROCEDURE deactivate_user (    p_username IN VARCHAR2)   AS      TYPE l_role_list_type IS TABLE OF VARCHAR2 (30)        INDEX BY BINARY_INTEGER;       l_role_list l_role_list_type;     l_role_index BINARY_INTEGER := 1;     l_role_string VARCHAR2 (32767);     l_username VARCHAR2 (30) := upper(p_username);  BEGIN   -- revoke System priviliges granted directly to the user  FOR rec IN (SELECT PRIVILEGE, admin_option                 FROM dba_sys_privs              WHERE grantee = l_username)  LOOP     IF (rec.admin_option = 'NO')    THEN       DBMS_OUTPUT.put_line ( 'grant '                             || rec.PRIVILEGE                            || ' to '                            || l_username                            || ';');     ELSE       DBMS_OUTPUT.put_line ( 'grant '                             || rec.PRIVILEGE                            || ' to '                            || l_username                            || ' WITH ADMIN OPTION;');    END IF;      EXECUTE IMMEDIATE    'REVOKE '                       || rec.PRIVILEGE                      || ' FROM '                      || l_username;  END LOOP;    -- revoke Object priviliges granted directly to the user  FOR rec IN (SELECT owner,                     table_name,                     PRIVILEGE,                     grantable                  FROM dba_tab_privs              WHERE grantee = l_username)   LOOP     IF (rec.grantable = 'NO')    THEN       DBMS_OUTPUT.put_line ( 'grant '                              || rec.PRIVILEGE                             || ' ON '                             || rec.owner                             || '.'                             || rec.table_name                             || ' to '                             || l_username                             || ';');     ELSE       DBMS_OUTPUT.put_line ( 'grant '                              || rec.PRIVILEGE                             || ' ON '                             || rec.owner                              || '.'                             || rec.table_name                             || ' to '                             || l_username                             || ' WITH ADMIN OPTION;');    END IF;      EXECUTE IMMEDIATE     'REVOKE '                       || rec.PRIVILEGE                      || ' ON '                      || rec.owner                      || '.'                      || rec.table_name                      || ' FROM '                      || l_username;    END LOOP;      -- revoke roles granted directly to the user    FOR rec IN (SELECT *                   FROM dba_role_privs                WHERE grantee = l_username)   LOOP      IF (rec.admin_option = 'NO')     THEN          DBMS_OUTPUT.put_line (   'grant '                               || rec.granted_role                              || ' to '                              || l_username                              || ';');      ELSE        DBMS_OUTPUT.put_line ( 'grant '                               || rec.granted_role                              || ' to '                              || l_username                              || ' WITH ADMIN OPTION;');     END IF;       IF (rec.default_role = 'YES')     THEN       l_role_list (l_role_index) := rec.granted_role;       l_role_index := l_role_index + 1;      END IF;       EXECUTE IMMEDIATE      'REVOKE '                         || rec.granted_role                        || ' FROM '                        || l_username;      END LOOP;      IF l_role_index > 1     THEN       l_role_string :=             'alter user ' || l_username ||' default roles '         || l_role_list (1);         FOR i IN 2 .. l_role_index – 1       LOOP         l_role_string :=         l_role_string || ', ' || l_role_list (i);       END LOOP;         DBMS_OUTPUT.put_line (l_role_string || ';');     END IF;     END;  /  

Executing the procedure for SCOTT results in the following output:

sec_mgr@KNOX10g> SET serveroutput on  sec_mgr@KNOX10g> SPOOL scottPrivs.sql  sec_mgr@KNOX10g> EXEC deactivate_user('scott')  grant UNLIMITED TABLESPACE to SCOTT;  grant CONNECT to scott;  grant RESOURCE to scott;  alter user SCOTT default roles CONNECT, RESOURCE;    PL/SQL procedure successfully completed.    sec_mgr@KNOX10g> spool off   

Locking the account will add another layer of defense:

system@KNOX10g> ALTER USER scott ACCOUNT LOCK;  User altered.  system@KNOX10g> conn scott/tiger  ERROR:  ORA-28000: the account is locked  Warning: You are no longer connected to ORACLE. 

Combining several different techniques as shown here is a good idea. This approach is consistent with defense in depth. A hacker now has several hurdles to overcome before an account can be accessed. First, the account has to be unlocked. Next, the privilege to create a session has to be restored. Then the trigger has to be disabled, dropped, or altered. Finally, the password has to be altered or guessed.

You can vary the actual steps used in securing your accounts as relevant to your particular use of the schema. However, you should consider some action(s) for every schema in your database. Even a single schema, left unsecured, could create a foothold for an unauthorized user. Using a combination of techniques will help to harden the database and prevent unauthorized access to it.

Throw Out Anything Stale

Over time, it often happens that users and applications no longer require access to the database. Perhaps the user got a new job and will no longer be logging in to your database—at least, not through legitimate means!

A security best practice for operating systems, networks, and databases is to remove unused or unneeded accounts. This is a simple concept that can be simply accomplished, yet it represents one of the most common and serious security risks and bad practices. Stale accounts should be locked at the very least, and preferably dropped. I’ve seen many instances when former employees still had active accounts on production systems years after their termination. Whether this is due to laziness or simply a lack of a good process, it’s a huge security risk!

To remove unused and unneeded accounts successfully, it’s important to know who is and who should be accessing the database. The schemas may not belong to just end users. A previously installed application, used and then abandoned on your database, also creates a security risk.

Caution 

Be careful dropping schemas even if no one has logged in to them in months. These schemas may hold data and procedures needed by an application.

This again emphasizes the importance of knowing which schemas are doing what. If you’re not sure whether an account still has relevant information, at the very least, export the data and procedures first!

How do you know if the account is being accessed? Auditing. You can audit connections to user schemas and access to objects within the schemas. You can even run a batch job to query the audit trail to look for unauthorized accesses and notify an administrator immediately upon detection. For more ideas and auditing examples, see Chapter 8.