Oracle Authentication Control

 * Stop TOAD, SqlPlus and other SQL utilities logins.
 * Restrict connections to specified host or programs and DBA's
 * Send email notices detailing blocked login attempts.
 * Log all connections by OS user, Oracle user, program,
    client machine name in a database table.
 * Start Oracle Traces for specified connections,
 * Process Oracle trace files into report and have them emailed to the developers.
 * Record all of the SQL executed for specific connections and post it to a historical
    database where it can be searched.
 

This is database code and shell script that allow you to configure rules to control Oracle logins to prevent connections from the likes of SqlPlus and TOAD and it can automatically start trace sessions and log connections.  Any trace that is started will be automatically processes when the connection is closed and emailed to a list of recipients.  It runs 9i + databases.  The script portion is written for AIX UNIX, but could be modified to run on Windows under "mks"   It's completely free and completely your responsibility if you choose to use it.

How it Works

An "after logon on" trigger fires with each new connection.  That trigger calls a procedure which checks a list of rules you define in a table.  The rules can utilize the following variables:

USERNAME   - The name of the Oracle account.
OSUSER         - The name of the user authenticated to the client PC or server.
MACHINE      - The name of the client PC or server.
PROGRAM     - The name of the executable program on the client. For example: sqlplusw.exe or TOAD
MODULE        - The module name for the program on the client, if the application assignees it.
LINK               - The name of the database link if one is used.  PROGRAM works for this too.
ROLE              - The name of any role granted to the Oracle username can be verified.

For example: "USERNAME like DJACKSON". The argument can also contain wild cards, such as "PROGRAM like %SQL%", and all arguments are case insensitive.

Multiple conditions can be combined using Boolean logic. For example:
 "PROGRAM like %TOAD% and (MACHINE like %TULSA or USERNAME like %READONLY)"

Each rule will evaluate to either True or False.  If a rule is False, then it is ignored and the procedure moves on to the next rule in the list. If the rule is True, then it is further porcessed.

Each rules is either AUTHORIZED or UNAUTHORIZED and each rule is assigned a level of priority. Should a connection be True for multiple AUTHORIZED and UNAUTHORIZED rules, the highest AUTHORIZED rule level will be compared to the highest UNAUTHORIZED rule level. If the levels are the same, the connection will be UNAUTHORIZED.  The action taken for either AUTHORIZED or UNAUTHORIZED connections is also configurable.  Most of the time a connection that ends up as UNAUTHORIZED would be assigned the "BLOCK" action with will kill the session.

The following actions can be defined. In addition each rule can specify that an email notice should be sent.

NONE        - No logging, or trace but as with any rule an email notice can be specified.
LOG           - Log access to a database table including the username, osuser, machine, program, and module.
TRACE       - Start a SQL Trace and email the resulting trace file once the session disconnects.
RECORD    - Same as TRACE, but the resulting trace file will only contain the SQL statements executed during the session.
                      Details regarding execution time and rows counts are not included.
BLOCK       - Blocks the connection and raises an application error message with "Access Denied" or with a custom message
                       configured in the rule.

For example; if the following rules were in the auth_control table:

If  OS User:  "VENDER_01" attempts to connect as "APP_ADMIN" using "SqlPlus.exe", the connection will be blocked unless the APP_ADMIN user is assigned to the DBA role.  All rules will be "True" but the first rule is an UNAUTHORIZED rule, and the PERMISSION_LEVELs of the AUTHORIZED rules are not greater.

The LOG and TRACE actions as well as the EMAIL_NOTICE can be set by any rule with a qualifying condition, it does not have to be set by the condition with the highest level. In the example above, the third rule will set the EMAIL_NOTICE so each enabled row in the auth_email table will get a notification, even if the APP_ADMIN has not been granted the DBA role and the connection is blocked.

The auth_control table below also provides for a rule to be disabled, and for an expiration time to be set. Once a rule has expired it will automatically be deleted.

Download

Download and unzip: auth.zip

The download contains the following files:

Installing the AUTH Schema

1) The schema is named AUTH, and it needs a tablespace that will be somewhere between 20 meg to 6 gig on average depending on how many connections you log and how long you plan to keep the log data. Create a tablespace named AUTH_DATA, or your welcome to use a name of your choosing and just modify the cr_auth.sql script.

2) Open the cr_auth.sql script .  You'll likely need to adjust the script to fit your standards so you may want to run it one command at a time and change it as needed.  The default password for AUTH is "authpass".  Feel free to change that too.

3) If your database is 8i, then run:  auth_connection_8i.sql

4) WARNING - This step creates the SYS trigger that calls the auth_connection procedure that starts using the auth_control table rules.  If only have a blocking rule or a badly formatted rule in the auth_control table then you will prevent logins to the database. So make sure the auth_control table is correct or empty.

connect as SYS and run:

create or replace trigger sys.auth_connection_trig
after logon on database
declare
   v_return varchar2(200);
begin
   auth.auth_connection(v_return);
end;
/

5) If you are using 11i or greater then AUTH will need access to the email relay host.

You can test email with the following command.  Just modify it to your email and domain:
exec auth.auth_sendmail('oracle@yourCompany.com','yourEmail@yourCompany.com','TEST','test',null);

Read More Here: http://www.oracle-base.com/articles/11g/FineGrainedAccessToNetworkServices_11gR1.php

The following will open access for AUTH to any host.

BEGIN
  DBMS_NETWORK_ACL_ADMIN.create_acl (
      acl => 'acl_file.xml',
      description => 'ACL access for the AUTH user',
      principal => 'AUTH',
      is_grant => TRUE,
      privilege => 'connect',
      start_date => SYSTIMESTAMP,
      end_date => NULL);
   commit;
END;
/

BEGIN
   DBMS_NETWORK_ACL_ADMIN.assign_acl (
      acl => 'acl_file.xml',
      host => '*',
      lower_port => NULL,
      upper_port => NULL);
   commit;
END;
/
 

Configuring the AUTH Schema

There are only two files that you need to configure:  auth_email and auth_control

auth_email is a list of the email recipients:

** The LIMIT column can be use to filter the email sent to a recipient.  If you enter a username in the limit in the column, then the recipient will only be sent an email if the connection was for the specified username.  You can also filter by the action for the connection. If the limit column contains LOG, BLOCK, RECORD or TRACE, then the recipient will only get an email if the for rules that have an action that matches the limit value. For example if you want the auditor to only receive and email if it was for a RECORD action, then specific RECORD in the limit field for the auditors email record.

auth_control as mentioned earlier contains the connection rules.  See: How it Works above for more detail.

WARNING!  It is easy to commit a blocking rule before you have the added any rules that would allow legitimate connections in.  There is also no syntax checking for the rule's condition and if you add a rule with a improperly formatted condition it can block all connections.   When changing the auth_control table, be sure to get two connections to the database.  Once you add or change a rule you need to test it immediately, and the temptation is great to log off and then try connecting back to the database.  But if the changed rule is preventing you from connecting, then you will have no way to fix the problem without finding another DBA that is connected or shutting down the database.  A second connection is your insurance policy where you can quickly undo the change.

LOCKED OUT?  -- Or what to do if you ignored the Warning above. 

Move fast!  If your database has heavy connection activity then you only have a couple of minutes before the works will be so jammed up that you are forced to shutdown the database and piss off everyone. 

Find another DBA that has a connection to the database, and have them fix the rule or disable the trigger.

A quick way to buy time to fix a problem is to disable all of the rules:

      update auth.auth_control set enabled = 'N';
      commit;

Or you can disable the on login trigger:  

      alter trigger sys.auth_connection_trig disable;
 

Making Rules

There are two basic strategies for securing access to the database.  The first is to set a rule with a BLOCK action and a condition that will always be True, and then add additional rules that only allow access from the host or users you know are safe.  The second is to allow access to anything unless you have a blocking rule specifically set for it.  

Example #1 - Log Everything

Before you take either path on an active database you will most likely want to just LOG all the connections for a while. For example:

-- Log Everything
insert into auth.auth_control (condition,permission,permission_level,action,email_notice,description)
values ('USERNAME LIKE %','AUTHORIZED',1,'LOG','N','Log Everything');

Once you have some log data you can review it and decide on a good approach to securing your database.

Example #2 - Block Everything Except DBA's and Specified Client Machines

If you choose to block everything except DBA connection and connections from specific client machines, then you might start with the rules below.  This approach can be the most secure way to lock down your database because you can restrict access from a specific set of clients.  If the end users have no access to the clients, then they can not utilize them to access the database with an unsecured application like SqlPlus.

-- Block All
insert into auth.auth_control (condition,permission,permission_level,action,email_notice,description)
   values ('ROLE NOT LIKE DBA','UNAUTHORIZED',1,'BLOCK','Y','Block all but DBAs');
-- Allowed Clients
insert into auth.auth_control (condition,permission,permission_level,action,email_notice,description)
   values ('MACHINE LIKE PROD_SEVER_01','AUTHORIZED',1,'LOG','N','Allow access from the production client');
insert into auth.auth_control (condition,permission,permission_level,action,email_notice,description)
   values ('MACHINE LIKE TEST_SEVER_01','AUTHORIZED',1,'LOG','N','Allow access from the test client');

You could combine the following two rules:

insert into auth.auth_control (condition,permission,permission_level,action,email_notice,description)
   values ('MACHINE LIKE PROD_SEVER_01 OR MACHINE LIKE TEST_SEVER_01','AUTHORIZED',1,'LOG','N','');

Example #3 - Allow Everything Except Specified Programs and DBA's

If you choose to just block specific connections the you could start with a logging rule and add rules to block the connections you do not want.  For example:

-- Log Everything
insert into auth.auth_control (condition,permission,permission_level,action,email_notice,description)
values ('USERNAME LIKE %','AUTHORIZED',1,'LOG','N','Log Everything');

-- Block SqlPlus, SqlDeveloper, and Java (Oracle Enterprise Manager)
insert into auth.auth_control (condition,permission,permission_level,action,email_notice,description)
values ('PROGRAM LIKE %SQL% or PROGRAM LIKE %Java%','UNAUTHORIZED',2,'LOG','N','Block SQL utilities');

--Make an exception for DBAs
insert into auth.auth_control (condition,permission,permission_level,action,email_notice,description)
values ('ROLE LIKE DBA','AUTHORIZED',3,'NONE','N','Allow DBAs with any program');

Complex Rule Conditions

You can make rules more complex or often simplify readability by using multiple rules.  You can us LIKE without the % as you would equal, "=".  And you can use parentheses, as well as AND and OR combinations. All conditions are case insensitive so use the case in the rule to improve it's readability if you wish.  Here is an example of a complex condition:

'PROGRAM LIKE %SQL% and (USERNAME NOT LIKE READONLY_USER and ROLE NOT LIKE DBA and MACHINE NOT LIKE %prod% and MACHINE NOT LIKE %test%)

Starting Oracle Traces

You can automatically start Oracle traces for a specific connection.  For example:

insert into auth.auth_control (condition,permission,permission_level,action,description)
values ('PROGRAM like post_month.exe','AUTHORIZED',1,'TRACE','Trace post_month.exe run');

You may also want to add a user to the Test and Development databases that developers can use to start their own traces.  For example, I commonly create a user named TRACE_USER and add the rule below.  I can then add the developers to the auth_email table so they automatically get a copy of the trace report.

insert into auth.auth_control (condition,permission,permission_level,action,description)
values ('USERNAME like TRACE_USER','AUTHORIZED',2,'TRACE','Trace User');

You can also start a trace for a specific session by calling the auth.auth_trace_on(<SID>) procedure.  The advantage to using this procedure is that it will enter the trace into the auth_trace table so that it is automatically processed and emailed after the session is disconnected or the auth.auth_trace_off(<SID>) procedure is called.  You can also grant execution of these procedures to the developers so they can safely start traces, even in a production database.   For example, to start a trace on session with SID 12:

exec auth.auth_trace_on(12);

To get the traces to be processed automatically and the results emailed out, you must install and configure the auth_trace.sh script.

Installing the auth_trace.sh Script

If you want to be able to automatically start and process Oracle trace files then copy the three auth_trace* files to an appropriate scripts and utilities directory on your UNIX system.  The auth_trace.sh script was written for AIX korn shell, so you make need to modify it for your OS.  If you are running on a Windows platform, then you can modify it to run under "mks". 

The auth_trace_sid.txt  is a configurable list of databases that you want to run auth_trace.sh against when it is called, and it also contains the username and password for the account that should generate the execution plans in the trace report.

The auth_trace_email.txt is a configurable list of email recipients for errors generated by the auth_trace.sh script.  Add your email address to this file.

Open the auth_trace.sh script scroll down the "#Configuration" section, under "MAIN PROGRAM".  There may be a couple of settings you need to change. 

Once you have configured auth_trace.sh to suite your environment and test it, then you can place it on as many of your host as needed and schedule it to run periodically.  

Auditing

The RECORD action is often used for auditing administrative access to databases.  In addition to having RECORD actions emailed to specific individuals;  you can optionally have the collected sessions and SQL stored in a centralized database. This will make it easy for auditors to search the data and review the executed SQL.

To configure a database for storing all of the RECORD data you need to add the following two tables below the AUTH schema, and then set the AUTH_RECORD_DB variable in the auth_trace.sh scrip to the name of the central database. 

create table auth.auth_record ( database varchar2(12),   username varchar2(30),   login_date date,    osuser varchar2(30),
  program varchar2(48),  sql number,  line number,   text varchar2(4000) );

create table auth.auth_record_source ( database varchar2(12),  username varchar2(30),  login_date date, 
  table_name varchar2(30),  used_count number);
 

Reporting

Below are some examples:

--Connections in the past hour.
column connection_date format a14
column program format a30
column machine format a20
column username format a15
column osuser format a20
select to_char(connection_date,'Mon DD HH:MIAM') connection_date, program, machine, username, osuser, action
from auth.auth_log where connection_date > sysdate - 1/24
order by connection_date;

--Blocked Connections
select to_char(connection_date,'Mon DD HH:MIAM') connection_date, program, machine, username, osuser, action
from auth.auth_log where action = 'BLOCK'
order by connection_date;

--Connections from User - Great to have before you change a password!
select username, osuser, machine, program, to_char(max(connection_date),'Mon DD HH:MIAM') max_connection_date
from auth.auth_log where username in ('QDOD_QFC','QRMTIPS_QFC')
group by username, osuser, machine, program
order by username, osuser, machine, program;

--Active Traces
select sid, status, substr(l.username,1,10) username, osuser, substr(l.program,1,15) program,
substr(l.module,1,15) module, to_char(t.logon_time,'MON-DD HH24:MI') login_time
from auth.auth_log l, auth.auth_trace t
where l.log_id = t.log_id
and status in ('IN-PROGRESS','READY')
order by t.logon_time ;

-- Provided you have configured a centralized database for the recorded SQL;
-- Find every recorded update statement against the workorder table in the MAX database over the past 30 days.
column database format a6
column username format a6
column sql format 9999 heading 'SQL'
column line format 999 heading 'LN'
column text format a90
select sql.database, sql.username, sql.login_date, sql.sql, sql.line, sql.text
from auth.auth_record sql
where exists
(select 'x' from auth.auth_record_source src
where table_name = ('WORKORDER')
and sql.database='MAX'
and src.database = sql.database
and src.username = sql.username
and src.login_date = sql.login_date)
and upper(sql.text) like '%UPDATE%'
and login_date > sysdate - 30
order by sql.database, sql.username, sql.login_date, sql.sql, sql.line;