To maintain a history of user logins

How can I maintain a history of user logins?


Is there a quick easy way of finding out who’s been logging on to the database and how much work they’ve done?

If you want to get some idea of which users spend most time and consume most resources on the system, you don’t necessarily have to do anything subtle and devious to find out what’s been happening. There has been a (simple) audit trail built into the database for as long as I can remember. (The 8.1 – 10.1 in the banner simply covers the fact that I’ve only recently checked the following comments against those versions)

The init.ora file contains an audit_trail parameter. This can take the values true, false, none, os, db (the true/false options are for backwards compatibility). If you set the value to db (or true), then you have enabled auditing in the database. Once you have restarted the database (the parameter is not modifiable online), you can decide what events you want to audit.For a quick cheap audit of connections, connect as a suitably privileged account (which typically means as a DBA), and issue the command:

audit create session;

If you need to turn this audit off, the corresponding command is:

noaudit create session;

The older syntax for the same level of audit is:

audit connect; noaudit connect;

With this level of audit turned on, every session that logs on (except the SYS sessions) will insert a row into the table sys.aud$ giving various details of who they are and what time they connected. When the session ends, its last action is to update this row with various session-related details, such as log-off time, and the amount of work done. To make the results more readable, Oracle has superimposed the view dba_audit_session on top of the aud$ table; the 9.2 version of this view is as follows:

Name Null? Type----------------------- -------- ----------------OS_USERNAME VARCHAR2(255) WhoUSERNAME VARCHAR2(30)USERHOST VARCHAR2(128) WhereTERMINAL VARCHAR2(255)TIMESTAMP NOT NULL DATE logon date/timeACTION_NAME VARCHAR2(27) LOGOFF_TIME DATE log off date/timeLOGOFF_LREAD NUMBER v$sess_io.consistent_getsLOGOFF_PREAD NUMBER v$sess_io.physical_readsLOGOFF_LWRITE NUMBER v$sess_io.block_changesLOGOFF_DLOCK VARCHAR2(40) Number of deadlocksSESSIONID NOT NULL NUMBERRETURNCODE NOT NULL NUMBERCLIENT_ID VARCHAR2(64)SESSION_CPU NUMBER Session statistic. CPU used by this session

As you can see, there is quite a lot of helpful information here – perhaps good enough for most monitoring purposes. It is also a very light-weight tool, as it requires just one insert on logon, and an index access to update one row on log off.

There are a couple of administrative points. The aud$ table is in the system tablespace and is the one table in the sys schema that you are told you can delete data from. You may want to run a regular purge job to delete data that is more then N days old from this table.

You might consider moving this table to a separate tablespace – but there have been reports of problems with media recovery if you do this (possibly because the recovering processes tries to insert its own audit records and can’t because the tablespace needs recovery) and it is not supported by Oracle.

Finally, if you are running a physical standby database and open it in read only mode, you may find that you can’t connect to it as anyone other than sys. Auditing requires an insert/upate on aud$ - so can’t be allowed to on a read only database. You will have to remember to change the audit_trail parameter to none on your standby before you start it up.