Enable Auditing on MYSQL and MSSQL :)

For MYSQL Auditing can be done by enabling general Log
There are two ways to store the log 
A) Table 
B) File

if we will store in table it will be heavy for Database and if we put it in file we can move file to NAS and can be analysed if required

To enable general Log in MYSQL add below line in [mysqld] section of my.cnf
———————————————————————————
general_log_file = /var/log/mysql/<Program /Project name>.log
general_log = 1

———————————————————————————
stop and start MYSQL service

Secondly we can make AUDITING ON only whenever it is required by command

SET GLOBAL general_log = ‘on’
and make it OFF by below command

SET GLOBAL general_log = ‘off’

We can rotate the general log file with below script 
———————————————————————————
MYSQL_CONN=”-uroot prootpassword” 
DT=`date +”%Y%m%d”`
OLDLOG=mysql_general_${DT}.log
cd /var/log
mysql ${MYSQL_CONN} -e”SET GLOBAL general_log = ‘OFF’;” 
cp mysql_general.log ${OLDLOG}
echo -n > mysql_general.log
——————————————————————————–

FOR MSSQL we need to follow below steps

1) take the remote / connect using Management studio 
2) in new Query window past below text

USE MASTER
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N’Audit_DB’)
CREATE DATABASE [Audit_DB]
GO
IF NOT EXISTS ( SELECT [name] FROM sys.tables WHERE [name] = ‘Audit_log’ )
CREATE TABLE [AUDIT_DB].[dbo].[Audit_log](
[session_id] [int] NOT NULL,
[host_name] [varchar](128) NOT NULL,
[login_name] [varchar](128) NOT NULL,
[STATUS] [varchar](50) NOT NULL,
[text] [varchar](max) NULL,
[login_time] [datetime] NOT NULL,
[program_name] [varchar](128) NULL
) ON [PRIMARY]
GO

SET ANSI_PADDING OFF
GO

create Proc StartAudit
as
set nocount on
begin
insert into [AUDIT_DB].[dbo].[Audit_log]
SELECT c.session_id, s.host_name, s.login_name, s.STATUS, st.text, s.login_time, s.program_name
FROM sys.dm_exec_connections c
INNER JOIN sys.dm_exec_sessions s ON c.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) AS st
where [text] not like ‘select%’ and
login_time not in(select login_time from [AUDIT_DB].[dbo].[Audit_log])
end

3) Press F5 , it will create audit related procedure on SQL server 
4) now create a SQL job as below in SQL server new query window

USE [msdb]
GO
DECLARE @jobId BINARY(16)
EXEC msdb.dbo.sp_add_job @job_name=N’Audit_SQL_Job’,
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=2,
@notify_level_page=2,
@delete_level=0,
@category_name=N'[Uncategorized (Local)]’,
@owner_login_name=N’sa’, @job_id = @jobId OUTPUT
select @jobId
GO
EXEC msdb.dbo.sp_add_jobserver @job_name=N’Audit_SQL_Job’, @server_name = N’LOCALHOST’
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_jobstep @job_name=N’Audit_SQL_Job’, @step_name=N’AUDIT_SQL_JOB’,
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_fail_action=2,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N’TSQL’,
@command=N’StartAudit’,
@database_name=N’master’,
@flags=0
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_update_job @job_name=N’Audit_SQL_Job’,
@enabled=1,
@start_step_id=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=2,
@notify_level_page=2,
@delete_level=0,
@description=N”,
@category_name=N'[Uncategorized (Local)]’,
@owner_login_name=N’MKCLPROD\backupadmin’,
@notify_email_operator_name=N”,
@notify_netsend_operator_name=N”,
@notify_page_operator_name=N”
GO
USE [msdb]
GO
DECLARE @schedule_id int
EXEC msdb.dbo.sp_add_jobschedule @job_name=N’Audit_SQL_Job’, @name=N’Audit_SQL_JOB’,
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=4,
@freq_subday_interval=3,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=20141210,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959, @schedule_id = @schedule_id OUTPUT
select @schedule_id
GO

5) Ensure the SQL JOB is disabled as we can make it ON as and when it is needed

That’s All

With Regards,

Prashant Deshpande

 


 

Advertisements

1 Comment

Filed under Uncategorized

One response to “Enable Auditing on MYSQL and MSSQL :)

  1. Pankaj Saboo

    Nice blog Sirji… 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s