vicker313 tech blog

April 8, 2011

Turn on MYSQL Trace Feature

Filed under: MySQL — Tags: , — vicker313 @ 2:34 pm

To turn on MySQL trace feature, simply issue following command in MySQL console (OFF to turn off)

SET GLOBAL general_log = 'ON';

Or add following option to MySQL start up command (to turn off use 0):

mysqld --general_log=1

You can find the trace log at the database folder. The file name is something like host_name.log.

Advertisements

Setup Customized User Access Trace Log in Oracle Database

Filed under: Oracle — Tags: , , — vicker313 @ 2:26 pm

Here is a simple way to trace user access (log in and log out) in Oracle, by using trigger and table.

  1. Create a trace log table. For example:
    CREATE TABLE TRACELOG
    (
       SESSION_ID        NUMBER(20),
       SESSION_USER      VARCHAR2(30 BYTE),
       OS_USER           VARCHAR2(30 BYTE),
       HOST              VARCHAR2(30 BYTE),
       IP_ADDRESS        VARCHAR2(30 BYTE),
       TERMINAL          VARCHAR2(30 BYTE),
       MODULE            VARCHAR2(30 BYTE),
       LAST_ACTION       VARCHAR2(32 BYTE),
       LAST_ACTION_DATE  DATE,
       LOGON_DATE        DATE,
       LOGOFF_DATE       DATE
    )
  2. Grand privilege to public to update the TRACELOG table
    GRANT INSERT, UPDATE, SELECT ON TEMP_TRACE TO PUBLIC;
  3. Create Log On Trigger. For example:
    CREATE OR REPLACE TRIGGER on_logon_trigger
    after logon on database
    begin
       insert into tracelog(session_id, session_user, os_user, host, ip_address, terminal, module, logon_date)
       SELECT sys_context('USERENV', 'sessionid'),
       user,
       sys_context('USERENV', 'os_user'),
       sys_context('USERENV', 'host'),
       sys_context('USERENV', 'ip_address'),
       sys_context('USERENV', 'terminal'),
       sys_context('USERENV', 'module'),
       sysdate
       FROM dual;
       commit;
    end;
  4. Create Log Off Trigger. For example:
    CREATE OR REPLACE TRIGGER on_logoff_trigger
    before logoff on database
    begin
       update tracelog
       set logoff_date = sysdate
       where session_id = sys_context('USERENV', 'sessionid')
       and logoff_date is null;
       commit;
    end;

Now you can trace user access by querying the TRACELOG table.

Blog at WordPress.com.