Securing Access to Application Schemas - Oracle

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.

>>> More Oracle Articles          >>> More By McGraw-Hill/Osborne

blog comments powered by Disqus