Securing the Database

If you work with Oracle databases, you will want to know how to secure them. This article focuses on a number of steps you can take, representing the best practices used in organizations today, to secure an Oracle database. It is excerpted from chapter 2 of the book Effective Oracle Database 10g Security by Design, written by David C. Knox (McGraw-Hill/Osborne, 2004; ISBN: 0072231300).

This chapter focuses on the steps you’ll use to help secure your OracleDatabases. You’ll see how applying the best practice principles (explored in Chapter 1) to an Oracle database will help to further secure it.

This chapter looks at securing database schemas by limiting their privileges, providing good password support, restricting access using multiple defenses, and securing the network channels to and from the database. These steps represent many of the best practices used by organizations today. These are the actions you should also be taking to configure and operate a secure Oracle Database.

The remaining chapters of this book discuss how to effectively apply technology features and capabilities to the task of building secure database applications. For this to happen successfully, youll first have to apply the lessons taught in this chapter. Youll need to take certain actions and practice certain behaviors to ensure a good security foundation.

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.

  1. 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!
  2. 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.
  3. 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.
  4. 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 doesnt matter if they know the password; the privilege to log on has been removed.
  5. 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.
  6. 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.
  7. Drop the schema. You can drop the schemas that you dont 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, theres 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 twiceonce 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.

{mospagebreak title=Securing Access to Application Schemas}

For an example of a schema that represents one you have created, lets 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.

For an example of a schema that represents one you have created, lets look at securing the SCOTT schema. Prior to Oracle Database 10g, the SCOTT schema was often created and available through the well-known password, 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.

{mospagebreak title=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. Ive 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.

Oracle Passwords

The Oracle Database stores user passwords in the data dictionary. For database authenticated users, the values stored aren’t actually the plaintext passwords themselves but the password verifiers. Password verifiers are hashed (see Chapter 13 for more details on hashing) representations of plaintext passwords. The value is stored in a hexadecimal representation (numbers 0–9 and letters A–F).

The authentication process is performed by computing a password verifier for the plaintext password a user has submitted for authentication and comparing the resulting value with the one stored in the data dictionary. If they match, the user has supplied the same password and is authenticated.

Application Password Authentication Using Oracle’s Native Password Store

Application user authentication is an important step to ensuring security for database applications. There are times when the application requires authentication but a default mechanism isn’t provided. There are three possible methods for implementing your own user authentication for your application. First, you could build, maintain, and/or synchronize your own password repository and authentication scheme. You’ll see an example of how to implement a password authentication solution in Chapter 13, which discusses the use of the new DBMS_CRYPTO package.

Second, assuming the application users are also database users, the application could authenticate users by trying to connect to the database as the respective user(s) . This is a bad alternative because it can be costly–from a time and performance perspective–to create and destroy database connections just to authenticate.

The final method, which also assumes the application users are database users, utilizes the database’s internal password store. Unfortunately, Oracle supplies no password verifier program for developers to use, so you must build your own interface.

You can do this with a simple trick, commonly referred to as identified by values, that uses a syntactical variation of the ALTER USER DDL. The algorithm is similar to the one the database uses to authenticate its users. You simply need to compute a password verifier for the plaintext password a user submits and compare that value to the one stored in the database.

The following function is based on a program originally written by Tom Kyte. To start the function, obtain the user’s current password verifier from the data dictionary:

sec_mgr@KNOX10g> CREATE OR REPLACE FUNCTION is_auth_password
 2    p_username  IN VARCHAR2,
 3    p_password  IN VARCHAR2)
 4    RETURN BOOLEAN
 5  AS
 6    l_orig_password_verifier dba_users.PASSWORD%TYPE;
 7    l_new_password_verifier dba_users.PASSWORD%TYPE;
 8  BEGIN
 9    SELECT PASSWORD
10      INTO l_orig_password_verifier
11      FROM dba_users
12     WHERE username = UPPER (p_username);

Next, alter the user’s password and set it to the password you want to verify. When you do this, the database recomputes the password verifier based on this password. The result is stored in the database dictionary:

13   EXECUTE IMMEDIATE    ‘alter user ‘
14                     || p_username
15                     || ‘ identified by ‘ 16                     || p_password;

Select this second verifier out and compare it to the original that you’ve stored. If they match, the passwords must have been the same.

17  SELECT PASSWORD
18    INTO l_new_password_verifier
19    FROM dba_users
20   WHERE username = UPPER (p_username);

This works well when the passwords are identical. However, the problem is that if the passwords don’t match, you’ve changed the users password (perhaps to one that they don’t know). To resolve this, set the password back to its original value. Since you don’t have the original plaintext password, you can’t use the traditional ALTER USER syntax. Instead, reset the password by issuing an ALTER USER <username> IDENTIFIED BY VALUES ‘<original password verifier’>. Passing the original password verifier in single quotes after the values clause resets the password back to the user’s original password. The database sees the word “values” and doesn’t recompute the password verifier, but it stores the value specified in the quotes directly in the password column for the user.

21  EXECUTE IMMEDIATE    ‘alter user ‘
22                    || p_username
23                    || ‘ identified by values ”’
24                    || l_orig_password_verifier
25                    || ””;
26  RETURN l_orig_password_verifier =
27                 l_new_password_verifier; 28 END;
29 /
Function created.

To test the program, check the Boolean return value of the function:

sec_mgr@KNOX10g> BEGIN
  2    IF (is_auth_password (‘scott’, ‘tiger’) = TRUE)
  3    THEN
  4      DBMS_OUTPUT.put_line (‘scott/tiger is valid’);
  5    ELSE
  6      DBMS_OUTPUT.put_line
  7                        (‘scott/tiger is NOT valid’);
  8    END IF;
  9
 10    IF (is_auth_password (‘scott’, ‘lion’) = TRUE)
 11    THEN
 12      DBMS_OUTPUT.put_line (‘scott/lion is valid’);
 13    ELSE
 14      DBMS_OUTPUT.put_line
 15                         (‘scott/lion is NOT valid’);
 16    END IF;
 17  END;
 18 /
scott/tiger is valid
scott/lion is NOT valid

This function allows you to authenticate users against the database password store without having to know or manage the user’s actual password.

{mospagebreak title=Checking for Weak or Default Passwords}

Passwords are often the weak link in the security chain. A poorly chosen password, or well-known default password that has not been changed, is one of the greatest security risks to a database. To help manage this risk use the following program, which compares a list of known usernames and password verifiers to the users and verifiers actually being used in the database. The list is created by a helper program.

To start, a table is created that stores usernames, their plaintext passwords, and the respective computed password verifiers:

sec_mgr@KNOX10g> CREATE TABLE passwords
 
2    (
  3    username VARCHAR2(30),
  4    passwd   VARCHAR2(30),
  5    verifier VARCHAR2(30)
  6    )
  7 /
Table created.
sec_mgr@KNOX10g> ALTER TABLE PASSWORDS ADD (
  2    CONSTRAINT PWD_PK PRIMARY KEY (PASSWD, USERNAME));
Table altered.
sec_mgr@KNOX10g> CREATE INDEX VERIFIER ON PASSWORDS
  2  (USERNAME, VERIFIER);
Index created.

To populate the table, a procedure is created that utilizes the identified by values clause introduced in the IS_AUTH_PASSWORD function shown earlier. The procedure takes a password parameter. The program iterates through the DBA_USERS view and sets the passwords for all the users to the value passed as the parameter. The program then inserts the resulting password verifier into the PASSWORDS table. When the parameter is null, the password is set to the username. This has the benefit of allowing you to check for passwords that are the same as the username.

sec_mgr@KNOX10g> CREATE OR REPLACE PROCEDURE populate_passwords_tab (
  2    p_password  IN  VARCHAR2 DEFAULT NULL)
  3  AS
  4    l_new_password_verifier dba_users.PASSWORD%TYPE;
  5    l_password     dba_users.PASSWORD%TYPE
  6                    := UPPER (p_password);
  7 BEGIN
  8 FOR rec IN (SELECT username, password
  9               FROM dba_users)
 
10 LOOP
 11   IF (p_password IS NULL)
 12   THEN
 13     — password is either passed as parameter
 
14     — or set to user’s name
 15     l_password := rec.username;
 16   END IF;
 17
 18   — create new password verifier
 19   EXECUTE IMMEDIATE    ‘alter user ‘
 20                     || rec.username
 21                     || ‘ identified by ‘
 22                     || l_password;
 23   — retrieve new verifier
 24   SELECT password
 25     INTO l_new_password_verifier
 26     FROM dba_users
 27    WHERE username = rec.username;
 28   — insert value into passwords table 
 29   INSERT INTO passwords
 30        VALUES (rec.username,
 31               l_password,
 32               l_new_password_verifier);
 33   — set password back to its original value
 34   EXECUTE IMMEDIATE    ‘alter user ‘
 35                     || rec.username
 36                     || ‘ identified by values ”’
 37                     || rec.password
 38                     || ””;
 39   END LOOP;
 40 END;
 41 /
Procedure created.

Next, execute the above procedure to seed the table first with usernames equal to passwords. Since the procedure modifies user passowords, you should consider running this on a non-production database so this process doesn’t interfere with your production applications. Then try some common passwords associated with the default Oracle accounts (there may be additional common and default usernames within your organization). Any standard applications you’ve installed on Oracle may also carry default schemas and well-known default passwords, so you should consider all default schemas and their passwords, too. Oracle Metalink (http://metalink.oracle.com) lists some of the default usernames and passwords in Note:160861.1.

sec_mgr@KNOX10g> — sets all passwords to that of user’s name
sec_mgr@KNOX10g> EXEC populate_passwords_tab
PL/SQL procedure successfully completed.
sec_mgr@KNOX10g> — check for manager, a common password for DBA accounts sec_mgr@KNOX10g> EXEC populate_passwords_tab (‘manager’)
PL/SQL procedure successfully completed.
sec_mgr@KNOX10g> — check for your company’s name here
sec_mgr@KNOX10g> EXEC populate_passwords_tab (‘oracle’)
PL/SQL procedure successfully completed.
sec_mgr@KNOX10g> — SCOTT’s default password sec_mgr@KNOX10g> EXEC populate_passwords_tab (‘tiger’)
PL/SQL procedure successfully completed.
sec_mgr@KNOX10g> — SYS’ default password sec_mgr@KNOX10g> EXEC populate_passwords_tab (‘change_on_install’)
PL/SQL procedure successfully completed.
sec_mgr@KNOX10g> — common password people use
sec_mgr@KNOX10g> EXEC populate_passwords_tab (‘password’)
PL/SQL procedure successfully completed.
sec_mgr@KNOX10g> COMMIT ;
Commit complete.

Now that you have a data set of precomputed password verifiers, you’re ready to run checks against your production database.


NOTE

Once the password table has been created, the same data can be used against any Oracle Database because the password verifiers for the usernames are always the same in every Oracle Database.


For the procedure that actually performs the checking, simply iterate through the database users comparing the password verifier computed there with the one actually stored in the PASSWORDS table. When you find a match, print the matched value and the accounts status:

sec_msr@KNOX10g>> CREATE OR REPLACE PROCEDURE check_passwords
  2 AS
  3 BEGIN
  4   FOR rec IN (SELECT username,
  5                      PASSWORD,
  6                      account_status
  7                 FROM dba_users)
  8   LOOP
  9     FOR irec IN (SELECT *
 
10                    FROM passwords
 11                   WHERE username = rec.username
 12                     AND verifier = rec.PASSWORD)
 13     LOOP
 14       DBMS_OUTPUT.put_line
 15                 (‘——————————‘);
 16       DBMS_OUTPUT.put_line ( ‘Password for ‘
 17                             || rec.username
 
18                             || ‘ is ‘
 19                             || irec.passwd);
 20        DBMS_OUTPUT.put_line
 21                            ( ‘Account Status is ‘
 22                             || rec.account_status);
 23      END LOOP;
 24 END LOOP;
 25 END;
 26 /
Procedure created.

sec_mgr@KNOX10g> set timing on sec_mgr@KNOX10g> SET serveroutput on sec_mgr@KNOX10g> EXEC check_passwords
——————————
Password for CTXSYS is CHANGE_ON_INSTALL Account Status is LOCKED
——————————
Password for DIP is DIP
Account Status is LOCKED
——————————
Password for OLAPSYS is MANAGER Account Status is LOCKED
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.03

The output from the procedure’s execution was truncated in the previous output. It simply shows that the procedure was successful in finding passwords (not all successful findings are printed). Because changing default passwords is a best practice, this program can assist you in ensuring your Oracle Databases are compliant with best practices and are thus better secured.

{mospagebreak title=Impossible Passwords}

The Oracle database user’s password verifier is stored as a 16-character hexadecimal string. If you query the DBA_USERS view, you’ll see some passwords that aren’t hexadecimals. For example, there’s a database user named ANONYMOUS with a password of anonymous. How can that be? If you created a user by the name of ANONYMOUS with a password of anonymous, the password verifier wouldn’t say anonymous; it would be a hexadecimal representation of a hash of the password, not a plaintext string. You can check the passwords table previously created to verify this:

sec_mgr@KNOX10g> select * from passwords
2    where username = ‘ANONYMOUS’
 
3    and pwd = ‘ANONYMOUS';
USERNAME   PWD                  VERIFIER
———- —————- —————- ANONYMOUS  ANONYMOUS       FE0E8CE7C92504E9

The reason the string anonymous is present, as opposed to the verifier you see above, is that  the user wasn’t created with the standard CREATE USER syntax but with the identified by values clause, as shown here:

SQL> CREATE USER anonymous IDENTIFIED BY VALUES ‘anonymous';
User created.
SQL> select username, password
 
2  from dba_users
 
3 where username = ‘ANONYMOUS’
 
4 /
USERNAME            PASSWORD
——————- ————————
ANONYMOUS           anonymous

This is a simple trick you can use to ensure users don’t log in to an account. Its similar to creating a very strong password, but it’s better because you can’t log in to the account with anonymous or any other string in the universe!

The reason that no password is possible is because Oracle, on authentication, will compute the password verifier, which will be some 16-character hexadecimal string. This is compared with the one stored for the user. Because you know the password verifiers are stored in hexadecimal format, any values outside of the hexadecimal set (0–9, A–F) will not match the one computed. The result: there is no password the user can provide that will allow them to log in.

Anytime you are creating a database schema to which no one should connect, you should use an impossible password. The account also should be locked and privileges to connect to the database should not be given.

Managing and Ensuring Good Passwords

Passwords are the most prevalent form of authentication to Oracle Databases. Oracle provides the ability to enforce the choice of good, strong passwords through the use of password complexity routines. Oracle also provides a way to ensure good password management practices are also being followed through password profile enforcement.

Password Complexity

Oracle supports user-defined password complexity routines that allow you to validate the strength of passwords when they are set. Password complexity routines are critical to ensuring that password best practices are obeyed. The complexity routine technically implements the official password policy in your organization (assuming you have such a policy, and you should). You can check for many things within the routine. The biggest exception is case-sensitivity. Database authenticated user passwords are case insensitive. Here are a few common best practice checks you can administer within the complexity routine:

  1. Password isn’t the same as the username
  2. Password contains at least one digit
  3. Password is greater than some specified length
  4. Password isnt the same as the old password
  5. Password isn’t an easy to guess word, such as manager, oracle, or your company’s name

The function that administers the password check has to be implemented in the SYS schema. The password complexity function returns a Boolean value. The value TRUE means the password is okay. However, a good trick is to raise an exception in the function to notify the user of exactly what condition failed during their password change. Otherwise, they will get a generic error. A sample function that implements some of the above checks would look as follows:

sys@KNOX10g> CREATE OR REPLACE FUNCTION is_password_strong (
  2    p_username      VARCHAR2,
  3    p_new_password  VARCHAR2,
  4    p_old_password  VARCHAR2)
  5    — return TRUE if password is strong enough
  6  RETURN BOOLEAN
  7  AS
  8   l_return_val BOOLEAN := TRUE;
  9  BEGIN
 
10    — Check to be sure password is not the same as username
 11   IF UPPER (p_new_password) = UPPER (p_username)
 12   THEN
 13     l_return_val := FALSE;
 14     raise_application_error
 15                     (-20001,
 16                      ‘Password same as user name’);
 17   END IF;
 18
 19   — force user to change password to something new
 20   IF UPPER (p_new_password) =
 21                             UPPER (p_old_password)
 22   THEN
 23     l_return_val := FALSE;
 24     raise_application_error
 25       (-20004,
 26        ‘Password has to be different than old password’);
 27   END IF;
 28
 29   — Check for list of predictable passwords
 30   IF LOWER (p_new_password) IN
 31        (‘manager’,
 32         ‘change_on_install’,
 33         ‘oracle’,
 34         ‘password’)
 35   THEN
 36     l_return_val := FALSE;
 37     raise_application_error
 38                    (-20002,
 39                     ‘Password is too predictable’);
 40   END IF;
 41
 
42   — make sure password contains at least one digit
 43   IF (regexp_like (p_new_password, ‘[0123456789]‘) =
 44       FALSE)
 45   THEN
 46    
l_return_val := FALSE;
 47    
raise_application_error
 48              (-20003,
 49              
‘Password needs at least one digit’);
 50   END IF;
 51
 52   — make sure password is at least six characters
 53   IF LENGTH (p_new_password) <= 6
 54   THEN
 55     l_return_val := FALSE;
 56     raise_application_error
 57                          (-20005,
 58                           ‘Password is too short’);
 59   END IF;
 60
 61   RETURN l_return_val;
 62 END;
 63 /
Function created.

To enforce the password complexity routine, assign it to a Password Profile and then assign the profile to the user(s). Examples are shown in the following section.

{mospagebreak title=Password Profiles}

Oracle allows you to create Password Profiles that govern the behavior of the database with respect to passwords and authentication. To do this, create a profile with values set for the attributes you wish to use. The profile then can be enforced on your users. Oracle supports the following attributes for password profiles:

  1. Password lifetime   Allows a password to exist for a specific period of time
  2. Grace period   Time at which Database begins to warn users to change their password
  3. Reuse time/max   Supports password history and forces users to use new passwords
  4. Failed login attempts   Locks the account if the incorrect password is given after specified number of times
  5. Account lockout   Disables the account (combined with failed attempts to help prevent brute force attempts into user accounts)
  6. Password Verify Function   Defines the password complexity function that will be called when the user changes the password

Chapter 7 of the Oracle Database Security Guide gives more detailed explanations of these attributes. A sample profile that sets values for these attributes and assigns the complexity function defined previously would look as follows:

sec_mgr@KNOX10g> CREATE PROFILE strong_pwd LIMIT
  2    PASSWORD_LIFE_TIME 90
  3    PASSWORD_GRACE_TIME 15
  4    PASSWORD_REUSE_TIME 180
  5    PASSWORD_REUSE_MAX UNLIMITED
  6    FAILED_LOGIN_ATTEMPTS 5
  7    PASSWORD_LOCK_TIME .5
  8    PASSWORD_VERIFY_FUNCTION is_password_strong;
Profile created.

Now assign this profile to your users. Testing this on the user SCOTT yields the following results:

sec_mgr@KNOX10g> — assign profile sec_mgr@KNOX10g> ALTER USER scott PROFILE strong_pwd;
User altered.
sec_mgr@KNOX10g> — test profile sec_mgr@KNOX10g> — reset scott’s password sec_mgr@KNOX10g> ALTER USER scott IDENTIFIED BY scott;
ALTER USER scott IDENTIFIED BY scott
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20001: Password same as user name
sec_mgr@KNOX10g> ALTER USER scott IDENTIFIED BY manager;
ALTER USER scott IDENTIFIED BY manager
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20002: Password is too predictable
sec_mgr@KNOX10g> ALTER USER scott IDENTIFIED BY nodigit;
ALTER USER scott IDENTIFIED BY nodigit
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20003: Password needs at least one digit
sec_mgr@KNOX10g> — since this is not SCOTT, old password is null
sec_mgr@KNOX10g> ALTER USER scott IDENTIFIED BY tiger;
ALTER USER scott IDENTIFIED BY tiger
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20003: Password needs at least one digit
sec_mgr@KNOX10g> ALTER USER scott IDENTIFIED BY short1;
ALTER USER scott IDENTIFIED BY short1
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20005: Password is too short

Normally, after creating a password profile, you’ll force your users to change their passwords to ensure all passwords being used comply with the profile. To do this, you expire their existing password. Upon their next login, the database prompts them to reset their password. The new password is checked against the complexity routine and the other password profile values will also be enforced.

When administering this for SCOTT, set the new password to tiger, which you can’t see when looking at the output below. You may have noticed in the previous example that the complexity function didn’t indicate that the new tiger password matched the old password. The following output shows a different behavior when SCOTT changes his password. The complexity routine now informs him that the new password is the same as the old password. Note that this is done for security. If a user other than SCOTT received this message, then the database would have divulged the user’s password.

sec_mgr@KNOX10g> ALTER USER scott PASSWORD EXPIRE;
User altered.
sec_mgr@KNOX10g> conn scott/tiger
ERROR:
ORA-28001: the password has expired
Changing password for scott
New password:
Retype new password:
ERROR:
ORA-28003: password verification for the specified password failed
ORA-20004: Password has to be different than old password
Password unchanged
Warning: You are no longer connected to ORACLE.

Keep the Password Policies Practical

A password profile is a great way to ensure that good password management practices are being used. Once again, however, you have to balance security with usability. While using password profiles is generally a good idea, it can backfire. For example, forcing users to choose a new password each week (that is, expiring passwords too frequently) may in fact force the user to use easy passwords or worse, write down their passwords.

As another example, you may decide after three failed logins, youll lock the user account for a day. There are unintended consequences to this. The failed login and account locking can aid someone launching a denial of service (DoS) attack. The attack is made easy because a malicious person can intentionally lock all the database accounts by simply providing an incorrect password for each database user.

Limiting Database Resources

Whether intentional or malicious, a computer’s resources can be monopolized without much effort. Generally, when this is done maliciously, it is known as a denial of service (DoS) attack.

DoS attacks are easy to implement and hard to defend against. The defense challenge arises from the fact that there are numerous ways to trigger such attacks. The result is simple: exhaust computing resources to the point that the database can no longer provide adequate service. Fortunately, there are some actions you can take in the database to mitigate the risk of DoS attacks.

Resource Limits

In addition to the password profile capabilities, Oracle supports the use of resource profiles to limit the use of precious database resources. Resource limits help ensure that the application or user doesn’t intentionally, or inadvertently, take over the database and system’s resources. You can view the various resources that can be managed as well as their values by querying the DBA_PROFILES view:

sec_mgr@KNOX10g> SELECT resource_name, LIMIT
 
2    FROM dba_profiles
  3   WHERE PROFILE = ‘DEFAULT’
  4     AND resource_type = ‘KERNEL';
RESOURCE_NAME                  LIMIT
—————————— ————
COMPOSITE_LIMIT                UNLIMITED
SESSIONS_PER_USER              UNLIMITED
CPU_PER_SESSION                UNLIMITED
CPU_PER_CALL                   UNLIMITED LOGICAL_READS_PER_SESSION      UNLIMITED LOGICAL_READS_PER_CALL         UNLIMITED IDLE_TIME                      UNLIMITED CONNECT_TIME                   UNLIMITED PRIVATE_SGA                    UNLIMITED
9 rows selected.

Notice that the default values are all set to unlimited. A best practice is to actually define as many of these values as possible. Some general guidelines on the parameters are as follows:

  • Set the SESSIONS_PER_USER to the size of your application server connection pool. If you aren’t using a connection pool (or have no idea what that means), then set the value to something reasonable. You should consider that an application may lock or a computer may freeze with the connection open, so a value of one may be too restrictive.

TIP

It’s possible to create a denial-of-service attack by utilizing the CREATE SESSION privilege and connecting to the database over and over until the database server exhausts all memory. Setting this parameter helps to ensure this will not happen.


  • IDLE_TIME can be set to help ensure that users don’t leave a connected terminal in the database while they step out for a lunch break. If their machine is left unlocked, then someone can simply walk up and start accessing the user’s data without having to worry about breaking passwords or subverting privileges. This value is more applicable to client-server applications than to web applications; if the latter is using a connection pool, the server shouldn’t disconnect the pooled connections.
  • CPU_PER_CALL is a hard parameter to guess, but it helps to ensure the availability of the database. Often CPU monopolization occurs not by a malicious user, but by a bad programmer who inadvertently sends the database into a recursive loop (I’m speaking from experience here)!

Refer to the “Create Profile” section in the Oracle Database SQL Reference 10g document for specific definitions on all the settings. Setting the profile parameters to the logical and correct values may take a few tries before the best values are selected. Start with a least privilege mentality by setting the values very conservatively. If you find that you need to relax a few privileges for legitimate reasons, do so only after you have determined the values need to be relaxed.

A best practice is to create a profile for each application or class of users in the database. This includes administrators at all levels.

{mospagebreak title=Default Roles}

Just as the Oracle Database comes with default schemas, it also comes with several default roles. These roles exist mostly for legacy reasons and according to the Oracle documentation will one day be removed. This section discusses these roles and how to securely interact with them.

CONNECT

The first default role you should understand is the CONNECT role. This is one of the most misused roles probably because its name implies that it’s a necessary privilege. Many DBAs grant users CONNECT thinking that it’s only the simple privilege to log on to the database. It’s not! The actual privilege required to log on to the database is the CREATE SESSION privilege. As you can see from the following code, the CONNECT role has more than this single privilege:

sec_mgr@KNOX10g> SELECT PRIVILEGE
 
2    FROM dba_sys_privs
 
3   WHERE grantee = ‘CONNECT';
PRIVILEGE
————————————–
CREATE VIEW
CREATE TABLE
ALTER SESSION
CREATE CLUSTER
CREATE SESSION
CREATE SYNONYM
CREATE SEQUENCE
CREATE DATABASE LINK
8 rows selected.

Some of these privileges, such as CREATE VIEW, CREATE TABLE, and CREATE DATABASE LINK, are probably more powerful privileges than you want your users to have. Therefore, in the spirit of least privileges, you shouldn’t be granting the CONNECT role to your users as the means by which they are privileged to log on to the database.

RESOURCE

The next default role is RESOURCE. This role has also been in existence for many years and its use should be limited for the same reasons cited above. You can see this role, like the CONNECT role, also has many privileges granted to it:

sec_mgr@KNOX10g> SELECT PRIVILEGE
 
2    FROM dba_sys_privs
 
3   WHERE grantee = ‘RESOURCE';
PRIVILEGE
————————————–
CREATE TYPE
CREATE TABLE
CREATE CLUSTER
CREATE TRIGGER
CREATE OPERATOR
CREATE SEQUENCE
CREATE INDEXTYPE
CREATE PROCEDURE
8 rows selected.

There is also a hidden system privilege that is granted to users with the RESOURCE role: UNLIMITED TABLESPACE. This can be dangerous because users with this privilege have no effective quota and can use up all available disk space (see how to curtail such use in the previous section, “Limiting Database Resources”).

In the following example, a user is created and granted the RESOURCE role, and by checking the user’s privileges, you’ll notice that the user has an unrestricted quota.

sec_mgr@KNOX10g> CREATE USER unlim IDENTIFIED BY VALUES ‘noPassword';
User created.
sec_mgr@KNOX10g> — user has no privileges
sec_mgr@KNOX10g> SELECT *
 
2    FROM dba_sys_privs
  3   WHERE grantee = ‘UNLIM';
no rows selected
sec_mgr@KNOX10g> — grant resource role to user
sec_mgr@KNOX10g> GRANT RESOURCE TO unlim;
Grant succeeded.
sec_mgr@KNOX10g> — note the user now has unlimited quota
sec_mgr@KNOX10g> SELECT PRIVILEGE
 
2    FROM dba_sys_privs
 
3   WHERE grantee = ‘UNLIM';
PRIVILEGE
————————————
UNLIMITED TABLESPACE

It’s common to see grants to both the CONNECT and the RESOURCE roles within Oracle example code as well as actual deployed commercial applications. Don’t assume that this is a best practice.


NOTE

Complying with the least privilege principle is a best practice and relying on the CONNECT and RESOURCE roles as an easy way to grant privileges to your users is a bad practice.


DBA

Another important role that is commonly granted is the DBA role, which has every system privilege known to the database either directly granted or inherited through another role. It’s not unusual for this role to be granted to the data or procedural schemas used for an application. This is a gross misuse of privileges.

Granting the DBA role abides by the most privilege principle, rather than the preferred least privilege principle. Chapter 7 shows how to effectively manage privileges and roles. When it comes to ensuring the default roles aren’t abused, there are three actions you can take. However, I suggest you only do the last:

  1. Revoke all privileges assigned to default roles. This can be useful in an application where you are checking to see if a user is a member of a role, but you don’t want there to be associated privileges with that role, such as with the DBA role.
  2. Drop the default roles. Check with support first, because this may have negative effects on your default applications. Be sure to make a sound backup of the database and test it on your development system first!
  3. Don’t grant the DBA, CONNECT, or RESOURCE roles to users.

PUBLIC Privileges

One of the principle techniques for securing an Oracle Database involves the careful analysis of the use of the user group PUBLIC. The user group PUBLIC, as the name implies, represents every user in the database; therefore, a grant to PUBLIC is a grant to everyone in the database. This shorthand way of granting and revoking privileges can be a very useful feature. It also can create huge security risks especially when trying to ensure the database is operating in a least privileges manner.

When to Grant Privileges to PUBLIC

There are many occasions when grants to PUBLIC are sensible and don’t create security risks. For example, most Oracle database application developers recognize that the DUAL table is both very useful and contains absolutely no sensitive information. This is true of other procedures and functions as well–the SYSDATE function is a good example of a useful function that doesn’t have security risks associated with it. Therefore, PUBLIC access to the DUAL table and the SYSDATE function don’t represent a security risk.

Unfortunately, it’s difficult to know whether a grant to PUBLIC is really a security risk. As you develop your applications, you should carefully decide what, if anything, is granted to PUBLIC.

You also should consider what may not appear to be a risk today, could be a risk tomorrow. For example, suppose you have a table that stores user preferences for a web application. Initially, you allow users to save their preferences for the foreground and background colors as well as the font style that will be used in creating a personalized web page for them. Since none of this information is sensitive, you decide that it can be viewed by anyone.

scott@KNOX10g> CREATE TABLE user_prefs
 
2 (background_color VARCHAR2(6),
 
3 foreground_color VARCHAR2(6),
 
4 font_style VARCHAR2(20));
Table created.
scott@KNOX10g> GRANT SELECT ON user_prefs TO PUBLIC;
Grant succeeded.

Later, you might add a sensitive attribute. For example, you may want to allow the user to store hyperlinks to their favorite web sites and applications.

scott@KNOX10g> ALTER TABLE user_prefs ADD favorite_links VARCHAR2(250);
Table altered.

The addition of this attribute changes the overall sensitivity of the table. The grant to PUBLIC should now be removed. The security rule for governing PUBLIC privileges is: when in doubt, do not grant access to PUBLIC.

{mospagebreak title=Oracle Supplied Objects}

In efforts to secure the Oracle database, you also have to consider the privileges that already have been granted to PUBLIC by both the applications you develop, or purchase, and the Oracle-supplied database objects.

There are two areas you should be concerned with respecting default grants to PUBLIC on Oracle objects:

  • Access to data dictionary views   There are several data dictionary views that will give a user information that could be used to aid in a database attack.
  • Execute on procedures   This includes PL/SQL functions and procedures and packages, as well as any Java procedures. These procedures perform many useful functions–such as opening network connections, reading files from the operating system, and setting identifier information about the user or application–all of which might be used in subsequent security processes, such as access control and auditing.
PUBLIC Access to Dictionary Views

The Oracle database already provides some security to the database dictionary metadata by restricting access to the sensitive data. Over time, the definition of “sensitive data” has evolved. Originally, sensitive data referred to items such as the encrypted user passwords. Today, even the list of all usernames in the database is considered sensitive. However, some of this data is still available to PUBLIC.

As an example, the ALL_USERS view is accessible to PUBLIC and it lists the username of every database schema. A technique often used by hackers is to obtain and use a list of valid user accounts to try to access those accounts. Privileged database option schemas (such as MDYS), default application accounts, and user accounts will be listed by the ALL_USERS view as valid targets to a nefarious user. The list of valid database users then becomes a list of valid database targets. A malicious user could easily say, “Oh look, the <insert option name or your application here> is installed. Let me use the default password and try to access this privileged account.

Therefore, you should consider revoking PUBLIC access to certain database metadata. Looking at SYS objects that start with ALL is a good place to start:

SELECT table_name
  FROM dba_tab_privs
 WHERE grantee = ‘PUBLIC’
   AND owner = ‘SYS’
   AND PRIVILEGE = ‘SELECT’
   AND table_name LIKE ‘ALL%';

Broken Objects

Before revoking PUBLIC access to default database objects, you should know that the revocation may break existing programs or applications. The following example shows 20 database objects that become invalid after the PUBLIC privileges have been removed from the ALL_USERS view.

sys@KNOX10g> SELECT count(*) FROM all_objects
  2   WHERE status = ‘INVALID';
  COUNT(*)
———-
         0
1  row selected.
sys@KNOX10g> REVOKE SELECT ON all_users FROM PUBLIC;
Revoke succeeded.
sys@KNOX10g> SELECT count(*) FROM all_objects
  2   WHERE status = ‘INVALID';
  COUNT(*)
———-
        20
1 row selected.

The damage isn’t unrepairable. If an application relies on a revoked privilege that was once granted to PUBLIC, it can be fixed by granting the privilege directly to the application. To do this for the data dictionary views, simply list the schemas that require the direct grant.

sys@KNOX10g> — Show whose objects are broken.
sys@KNOX10g> SELECT distinct owner
 
2    FROM all_objects
 
3   WHERE status = ‘INVALID';
OWNER
———————
DMSYS
EXFSYS
LBACSYS
SYS
SYSMAN
XDB
6 rows selected.

Of these schemas, some have the system privilege SELECT ANY DICTIONARY, which already provides access to the ALL_USERS view. The objects in those schemas will recompile without requiring any grants; however, the other schemas will require a direct grant on the ALL_ USERS view. You can display the list of remaining schemas that require the direct grant by using the SQL minus function bolded in the following code. This code wraps the result set in the DDL you’ll use to issue the grants:

sys@KNOX10g> — create list of users who require
sys@KNOX10g> — direct select privileges on ALL_USERS
sys@KNOX10g> SELECT DISTINCT    ‘grant select on all_users to ‘
 
2                  || owner
  3                  || ‘;’ sql_command
  4             FROM (SELECT DISTINCT owner
  5                        FROM all_objects
  6                       WHERE status =
  7                                ‘INVALID’
  8                    AND owner != ‘SYS’
  9              MINUS
 10              SELECT grantee
 11                FROM dba_sys_privs
 12              WHERE PRIVILEGE =
 13                       ‘SELECT ANY DICTIONARY’);
SQL_COMMAND
——————————————–
grant select on all_users to DMSYS;
grant select on all_users to EXFSYS;
grant select on all_users to LBACSYS;
grant select on all_users to XDB;
4 rows selected.

Using copy and paste technology for the values in the SQL_COMMAND, issue the direct grant to the users that require it. After the grant has been made, the invalid objects in those schemas will recompile.

Unfortunately, the consequences of the revocation are nearly impossible to predict. This is why Oracle hasn’t already removed PUBLIC privileges to the database metadata views. The Oracle Database Security Guide also warns that revoking DML privileges from PUBLIC can be nontrivial:

Revoking a privilege from PUBLIC can cause significant cascading effects. If any privilege related to a DML operation is revoked from PUBLIC, all procedures in the database, including functions and packages, must be reauthorized before they can be used again. Therefore, exercise caution when granting and revoking DML-related privileges to PUBLIC.

PUBLIC Privileges on Programs

Next, analyze the execute privileges on programs granted to PUBLIC. Again, there are too many specific programs to list and the programs will constantly be changing. The same principle applies to securing these programs as to the preceding views. That is, knowing what the programs do is important to understanding what risks, if any, are present.

The programs you should be most concerned with are the ones that start with DBMS% and UTL%:

SELECT table_name
   
FROM dba_tab_privs
  
WHERE     grantee = ‘PUBLIC’
        
AND owner = ‘SYS’
        
AND PRIVILEGE = ‘EXECUTE’
        
AND table_name LIKE ‘DBMS%’
     
OR table_name LIKE ‘UTL%’
ORDER BY 1;


CAUTION

Don’t limit your evaluation to just these programs or SYS-owned objects. All options and applications in your database should be evaluated.


The Oracle Database Security Guide suggests revoking execute privileges on UTL_SMTP, UTL_TCP, UTL_HTTP, and UTL_FILE from PUBLIC. You should not only do this, but also remember the point of this exercise is to restrict access to procedures to only those applications, users, and objects requiring access.

Just as in the metadata example, there are often application dependencies created upon the PUBLIC grants to these programs. To successfully revoke privileges, you need to understand the dependencies and be able to rectify any problems that are created by the revocation process. Chapter 6 provides an example of this process to revoking the execute privileges on the DBMS_ SESSION package.

{mospagebreak title=Securing the Network}

For most databases, security begins even before the users gain access to the database. The network that links together the users, applications, and databases is critical in the security chain. There are a few actions you should take to strengthen this link.

Encryption

Today, some believe that network encryption isn’t necessary. After all, antivirus software and firewalls are already in place.

This is wrong. While there is some level of security afforded by these technologies, the assumption that network traffic is totally secure is false. Anyone can place a network packet sniffer (readily available on the Internet) on the application server that connects to the database. Both are behind the firewall. With the sniffer they can easily capture all the traffic to and from the application server. Packets can be spooled to a file. Later, after the important data has been collected, the spool file can be sent via e-mail to an “anonymous” Internet account. This is not fiction; it really happens. Why bother breaking into the database with all its security when you can easily capture all the important data as it enters and leaves the database? This scenario illustrates the need for network encryption.


NOTE

When deploying applications that communicate with an Oracle Database, the Oracle network encryption capabilities provide seamless and transparent encryption of all your database data as it moves through the network. While it may not be needed in all situations, it should always be considered and is strongly recommended.


 

There are three benefits to implementing Oracle’s network encryption capabilities:

  1. The algorithm negotiation feature supports the concurrent use of different encryption algorithms with different key sizes for various clients. This flexibility means that security and performance can be accomplished simultaneously.
  2. The encryption remains transparent to the applications that utilize it.
  • Independent lab tests show little overhead costs, which makes it an acceptable trade-off in most cases.

Configuring the network for encryption is simple. Either edit the SQLNET.ORA file with a text editor or use the Oracle Net Manager. A view into the file shows how easy it is to instruct the Oracle network software to secure the channel:

SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT= (SHA1) SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER= (SHA1) SQLNET.CRYPTO_SEED =
 
thisistheencryptionseed(S)DLKDk0(*)(*#IUI%$,k9r80dsa0__llk098 09cxf-08 SQLNET.ENCRYPTION_SERVER = requested SQLNET.ENCRYPTION_CLIENT = requested SQLNET.ENCRYPTION_TYPES_CLIENT= (AES256) SQLNET.ENCRYPTION_TYPES_SERVER= (AES256)

Using these settings, the network software will employ the SHA-1 algorithm to ensure data integrity and also will encrypt all data using the AES algorithm with 256-bit key sizes.

Database Listener

The database listener is the process that handles connections over the network destined for the database. Two important points about the listener:

  1. It is a critical program and needs to be run much of the time.
  2. It is the forward application, which means that it stands the biggest risk of attack. User programs can interact with the listener directly even if they don’t have database accounts.

As with most network processes, there are numerous attacks that can occur against the database listener. Securing the database listener is a top priority. The first task is to password protect the listener process. Limiting status information is critical to securing the listener since the listener will happily explain everything it knows when prompted. This information is useful to both DBAs and to hackers.

The database listener also comes defaulted to a well-known network port or two. While it’s by no stretch of the imagination a “robust” security maneuver, changing the default port is nevertheless a good idea. If the listener is on a different port, someone who is scanning the network for open ports will detect a process listening on that port but may not know what it is. Some hackers only probe for well-known ports because the full port scans are obvious and can set off the intrusion detection alarms.


TIP

Changing the listening ports (and not only from 1521 to 1522) is a best practice.


To configure the listener, either edit the LISTENER.ORA file with a text editor or use the Oracle Net Manager.

External Calls

The database supports a useful capability whereby PL/SQL programs running inside the database can make external calls to a program running on the operating system. The benefit is that the OS programs will either execute faster (because they are C programs optimized to perform a specific task) or pass information about the operating system back into the database (such as uptime, currently executing processes, and logged in users).

However, the external procedure call capability is a high security risk; the process runs with the privileges of the database listener. If the external procedure is successfully compromised, the hacker may find themselves sitting in a privileged shell.

If youre using, or need to use, this capability be sure to keep it in check; otherwise, disable it. To do this, modify the Oracle network configuration files (the PLSExtProc service). Removing the binary that allows this, extproc.exe, is also a good idea.

If you need to support external procedures, it’s best to configure the extproc listener to run as an unprivileged user; for example, the “nobody” user on UNIX. By default, the process runs with the privileges of the database listener. By following this configuration suggestion, the risks associated with a compromised external procedure are significantly diminished.

IP Validation

IP addresses are the network method for naming entities. While the actual network protocols function at a lower layer (based on the MAC address), IP addresses remain a valuable identification asset. The main drawback with IP-based security is that it’s not too difficult, relative to other tricks such as trying to break encryption, to impersonate another computer’s IP Address (spoofing). The ability to successfully spoof depends on the network topology and the abilities of the network administrators to enforce strict IP addresses. Many network intrusion detection systems will alert administrators to duplicate IP addresses on their networks.

Assuming the IP address can be used to accurately identify the client, the IP address can then be incorporated into the database security implementations.

The Oracle database listener can be configured to allow or disallow access based on the client’s IP address. This is an easy way to begin shielding your database from unwanted users.

The configuration can again be completed using the Oracle Net Manager. The settings are placed in the SQLNET.ORA file (prior to Oracle9i Database, this was the PROTOCOL.ORA file). For example, the following configuration will only allow a network connection from a single computer with the IP address of 192.168.1.21:

TCP.VALIDNODE_CHECKING = YES TCP.INVITED_NODES= (192.168.1.21)

If your security policy dictates that the database only should be accessed from the application server, which has the previous IP address, these two lines can be added to your SQLNET.ORA file to ensure the database listener process will not accept any other connection requests. You can alternatively specify which specific nodes you want to exclude by setting the TCP.EXCLUDED_ NODES value.

Using the valid node checking capability is a good practice because it helps ensure that the only connections coming in over the network are from computers that are authorized.

Summary

Building secure database applications is done on the assumption that the database is already operating securely. To ensure this happens, you often have to perform certain tasks to create a tighter security implementation. There are many important lessons in this chapter.

Securing database schemas means ensuring not only new schemas are created and managed properly but also that the default schemas are secured. The default schemas and their passwords are well known. There are several ways to prevent unwanted and unauthorized users from connecting to these well known and highly privileged accounts. Several techniques were shown for applying the defense in depth principle.

An understanding of Oracle’s use of passwords is necessary because password authentication represents the most common authentication mechanism to the database. The database supports both password complexity routines and password profiles to support the secure and proper use of passwords.

Oracle’s default roles exist today for legacy reasons and should rarely be used, and revoking existing privileges and limiting grants to the user group PUBLIC is essential for securing the database.

A final necessary piece of the security puzzle is network security. The entire security of an application and database can be subverted through poor network security. The database provides several ways to prevent this from happening. Applying security at the network tier ensures all the links in the security chain are strong.

[gp-comments width="770" linklove="off" ]

antalya escort bayan antalya escort bayan Antalya escort diyarbakir escort