Tracking oracle Logons

There are a couple ways of tracking logon and logoff information within an Oracle database, however few offer the simplicity and flexibility of the use of an logon and logoff trigger. Here is how I built a simple set of triggers and accompanying table to track usernames, logon time, logoff time, machine connected from and program used.

I performed these steps as system. If you want to install these as another user you will need to assign the appropriate permissions first.

The first step is to create a table to store the information. This table may grow quickly if this is a very active system so we want to put it somewhere other than the system tablespace. For reference, mine currently has around 3000 records in it and is around 200k in size. Of course mileage may vary.

CREATE TABLE session_audit
(
user_id VARCHAR2(30),
session_id NUMBER,
host VARCHAR2(30),
program VARCHAR2(60),
logon_time DATE,
logoff_time DATE
)
TABLESPACE users;

This will be the table you query to determine login information. Of course you will probably want to purge data from here occasionally to keep it from getting too big.

Next we create the logon trigger which will populate all but the logoff_time of the session_audit table. This uses the sys_context function to lookup the host and session id (different from the SID) of the session. The program is retrieved by a subquery on the V$SESSION table.

CREATE OR REPLACE TRIGGER tr_session_audit_logon
AFTER LOGON ON DATABASE
DECLARE
session_id number;
BEGIN
select sys_context(’USERENV’,'SESSIONID’) into session_id from dual;
IF session_id != 0 –ignore internal connections
THEN
BEGIN
INSERT INTO session_audit (
user_id,
session_id,
host,
program,
logon_time,
logoff_time
)
VALUES(
user,
session_id,
sys_context(’USERENV’,'HOST’),
(SELECT program FROM v$session
WHERE sys_context(’USERENV’,'SESSIONID’) = AUDSID),
sysdate,
NULL
);
END;
END IF;
END;


UPDATE: I have found that the old version of this resulted in an ORA-1427 error when someone made an internal connection. The problem also came up when dbms_jobs were run. The code now ignores internal connections (where the session id is 0).

Finally we create the logoff trigger to fill in the logoff_time.

CREATE OR REPLACE TRIGGER tr_session_audit_logoff
BEFORE LOGOFF ON DATABASE
BEGIN
UPDATE session_audit
SET logoff_time = sysdate
WHERE sys_context(’USERENV’,'SESSIONID’) = session_id;
END;

That’s it. You can now search the session_audit table for logins durring a time window or all logins for a specific user. You could even check who is using a certain application.

I mentioned one possible issue, that you don’t want the session_audit table to fill up your system tablespace, however it is also important to mention that if your logon trigger fails for some reason people will not be able to log in, so watch the space, no matter where you put it.

Donald Burleson (who desperately needs a new portrait for his website) of Burleson Consulting offers these instructions for a similar trigger, however he is grabbing more information and fills most of it in at the end.

oracle, sql, dba, database administration, database development, database security, database, oracle security

Running SQLPlus and PL/SQL Commands From A Shell Script

I got this from Tim Archer..

http://timarcher.com/?q=node/48

The need often arises to run Oracle SQL scripts or PL/SQL procedures from a shell script. For instance, in my environment we run a lot of jobs through cron. Part of the job may be to connect to the database and run a PL/SQL function or procedure. Below I will describe how to do this, and some of the various “extras” that I include in my shell scripts. All of the examples below have been tested on Oracle 10gR2 and RedHat AS 4.

The Basic Syntax

In its simplest format, you can call SQLPlus from a shell script. The basic format of a shell script doing this is:

#!/bin/sh
sqlplus system/manager@prod_db <<ENDOFSQL
select sysdate from dual;
exit;
ENDOFSQL

Try to create that shell script on your server and run it. Be sure to enter in the correct connect string. You’re screen output should show all of the output from the SQL Plus session that executes.

A More Advanced Example

Now I will show you a more advanced script that I run on my servers. It builds off the simple example above, except it does a few extra things:

• Checks command line parameters for the appropriate number of parameters.
• Exports the ORACLE_SID as passed in from the command line parameters.
• Runs the oraenv script for the specified ORACLE_SID to setup all the Oracle specific environment parameters.
• Checks the return code from SQL Plus to determine if the SQL script successfully ran and takes an appropriate course of action.

Create a file named test.sh and put the following code in it:

#!/bin/sh

# The name of this script
SCRIPT_NAME=test.sh

###################################################################
#
# Check for Invalid Command Line Arguments
# $1 = ORACLE_SID
#
###################################################################
if [ $# -lt 1 ]
then
  echo "Usage: $SCRIPT_NAME <ORACLE_SID>"
  echo "Example: $SCRIPT_NAME DWHSE PROD"
  exit
fi

###################################################################
#
# Setup Script Variables
#
###################################################################
#This is the oracle_sid of the instance that the oracle environment
#will be setup for.
export ORACLE_SID=$1

#
# Initially we need some oracle path in the PATH for the dbhome command
# to be found. We reset the PATH variable to the proper oracle_home/bin
# below
#
export ORACLE_HOME=/usr/local/banner/oracle/product/current
export PATH=$ORACLE_HOME/bin:/usr/local/bin:$PATH

export ORAENV_ASK=NO

. /usr/local/bin/oraenv

export ORAENV_ASK=YES

echo "Starting Create PIN for ORACLE_SID: $ORACLE_SID"

###################################################################
#
# Run our SQL Plus Commands
#
###################################################################
sqlplus system/manager <<ENDOFSQL
whenever sqlerror exit sql.sqlcode;
select sysdate from dual;
exit;
ENDOFSQL

ERRORCODE=$?

#Check the return code from SQL Plus
if [ $ERRORCODE != 0 ]
then
  echo "********************"
  echo "ERROR: The SQL Plus Command Failed. ErrorCode: $ERRORCODE"
else
  echo "********************"
  echo "SQL Plus Successfully Ran. ErrorCode: $ERRORCODE"
fi

The format to run this script will then be:

./test.sh ORACLE_SID

You’ll need to modify a few sections of the script to work in your environment as described below:

• In the section labeled Check for Invalid Command Line Arguments you will want to change the script to take an ORACLE_SID appropriate for your environment. The SID’s that are valid in my environment are PROD and DWHSE.
• Change the connect string from system/manager to be what is required for your environment. There is no need to specify the Oracle SID in this part of the connect string since the beginning of the script exports the appropriate SID.
• Plug in your own SQL to run within SQL Plus. In the example I gave you it will always succeed since selecting sysdate from dual will always work.

Running PL/SQL Procedures And Passing In Unix Environment Variables

Within the section specifying the SQL to run you can even call PL/SQL functions and procedures. If you want to call a PL/SQL function or procedure, make sure you prefix the procedure call with the keyword exec. Furthermore, you can pass your Unix environment variables into your SQL Plus session. The following example creates the environment variable named TEST_VAR and then runs SQL Plus calling DBMS_OUTPUT to print out the value in the variable.

export TEST_VAR="HELLO WORLD"
sqlplus usfbanner/webster <<ENDOFSQL
whenever sqlerror exit sql.sqlcode;
set serveroutput on;
exec dbms_output.put_line('${TEST_VAR}');
exit;
ENDOFSQL

Running The Script Through Cron

Once you are able to get a script created that performs all of the appropriate actions that you need to do, you can automate running it through cron. This is the simple part.

• As the user who will run the script (probably the oracle user), edit the crontab.

  crontab –e

• Add a line to run your script at the time you desire. For example, I have the following entry in the crontab for my oracle user that will run a script to send emails to people who new user accounts have been generated for.

  30 13 * * * /usr/local/banner/scripts/send_new_account_email.shl PROD

  The script will run at 1:30pm every day connecting to the PROD database instance. Lastly, the output from running this script will be emailed to the oracle user for my review. The emailing of any output is standard cron functionality.