Skip to Main Content
  • Questions
  • Tracking User logins between 7:00 pm and 7:00 am

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, DATTA KUMAR K.

Asked: January 12, 2018 - 3:17 pm UTC

Last updated: January 14, 2018 - 9:44 am UTC

Version: 11.2.0.4

Viewed 1000+ times

You Asked

Hello Sir,

I have a requirement to track and generate a report of the users logging into the database after office hours, i.e., between 7:00 pm and 7:00 am on a daily basis.

We have audit_trail set to 'DB'

I appreciate if you can help me in accomplishing with this task.

Thanks
Datta

and Connor said...

You can just run 'audit session' to get a record of when a user logs in and out.

SQL> audit session;

Audit succeeded.

SQL> select  * from DBA_PRIV_AUDIT_OPTS;

USER_NAME
----------------------------------------------------------------
PROXY_NAME
----------------------------------------------------------------
PRIVILEGE                                SUCCESS    FAILURE
---------------------------------------- ---------- ----------


CREATE SESSION                           BY ACCESS  BY ACCESS

SQL> conn scott/tiger
Connected.

SQL> conn / as sysdba
Connected.

SQL> select * from DBA_AUDIT_TRAIL
  2  @pr
==============================
OS_USERNAME                   : XPS13\hamcdc
USERNAME                      : SCOTT
USERHOST                      : WORKGROUP\XPS13
TERMINAL                      : XPS13
TIMESTAMP                     : 14-JAN-18
OWNER                         :
OBJ_NAME                      :
ACTION                        : 100
ACTION_NAME                   : LOGON
NEW_OWNER                     :
NEW_NAME                      :
OBJ_PRIVILEGE                 :
SYS_PRIVILEGE                 :
ADMIN_OPTION                  :
GRANTEE                       :
AUDIT_OPTION                  :
SES_ACTIONS                   :
LOGOFF_TIME                   :
LOGOFF_LREAD                  :
LOGOFF_PREAD                  :
LOGOFF_LWRITE                 :
LOGOFF_DLOCK                  :
COMMENT_TEXT                  : Authenticated by: DATABASE
SESSIONID                     : 1137338
ENTRYID                       : 1
STATEMENTID                   : 1
RETURNCODE                    : 0
PRIV_USED                     : CREATE SESSION
CLIENT_ID                     :
ECONTEXT_ID                   :
SESSION_CPU                   :
EXTENDED_TIMESTAMP            : 14-JAN-18 05.38.26.244000 PM +08:00
PROXY_SESSIONID               :
GLOBAL_UID                    :
INSTANCE_NUMBER               : 0
OS_PROCESS                    : 8584:6944
TRANSACTIONID                 : 0000000000000000
SCN                           :
SQL_BIND                      :
SQL_TEXT                      :
OBJ_EDITION_NAME              :
DBID                          : 872342268
RLS_INFO                      :
CURRENT_USER                  : SCOTT
==============================
OS_USERNAME                   : XPS13\hamcdc
USERNAME                      : SCOTT
USERHOST                      : WORKGROUP\XPS13
TERMINAL                      : XPS13
TIMESTAMP                     : 14-JAN-18
OWNER                         :
OBJ_NAME                      :
ACTION                        : 101
ACTION_NAME                   : LOGOFF
NEW_OWNER                     :
NEW_NAME                      :
OBJ_PRIVILEGE                 :
SYS_PRIVILEGE                 :
ADMIN_OPTION                  :
GRANTEE                       :
AUDIT_OPTION                  :
SES_ACTIONS                   :
LOGOFF_TIME                   : 14-JAN-18
LOGOFF_LREAD                  : 58
LOGOFF_PREAD                  : 0
LOGOFF_LWRITE                 : 13
LOGOFF_DLOCK                  : 0
COMMENT_TEXT                  :
SESSIONID                     : 1137338
ENTRYID                       : 2
STATEMENTID                   : 11
RETURNCODE                    : 0
PRIV_USED                     :
CLIENT_ID                     :
ECONTEXT_ID                   :
SESSION_CPU                   : 1
EXTENDED_TIMESTAMP            : 14-JAN-18 05.38.31.617000 PM +08:00
PROXY_SESSIONID               :
GLOBAL_UID                    :
INSTANCE_NUMBER               : 0
OS_PROCESS                    : 8584:6944
TRANSACTIONID                 :
SCN                           :
SQL_BIND                      :
SQL_TEXT                      :
OBJ_EDITION_NAME              :
DBID                          : 872342268
RLS_INFO                      :
CURRENT_USER                  : SCOTT


Now if it were me, I would simply leave that on all the time, and then query for the records of interest. If you want to only have it enabled between 7 and 730, you could setup two simple jobs

SQL> create or replace
  2  procedure audit_control(p_enable boolean) is
  3  begin
  4    execute immediate
  5      case when not p_enable then 'no' end||'audit session';
  6  end;
  7  /

Procedure created.

begin
    dbms_scheduler.create_job (
       job_name           =>  'AUDIT_ON',
       job_type           =>  'PLSQL_BLOCK',
       job_action         =>  'audit_control(true);',
       start_date         =>  trunc(sysdate)+19/24,
       repeat_interval    =>  'FREQ=DAILY',
       enabled            =>  true,
       comments           =>  'Audit ON');
       

    dbms_scheduler.create_job (
       job_name           =>  'AUDIT_OFF',
       job_type           =>  'PLSQL_BLOCK',
       job_action         =>  'audit_control(false);',
       start_date         =>  trunc(sysdate)+7/24,
       repeat_interval    =>  'FREQ=DAILY',
       enabled            =>  true,
       comments           =>  'Audit OFF');
end;
/



Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Security

All of the vital components for a secure database are covered in the Security guide.