Monthly Archives: December 2014

2014 in review

The WordPress.com stats helper monkeys prepared a 2014 annual report for this blog.

Here’s an excerpt:

The concert hall at the Sydney Opera House holds 2,700 people. This blog was viewed about 43,000 times in 2014. If it were a concert at Sydney Opera House, it would take about 16 sold-out performances for that many people to see it.

Click here to see the complete report.

Leave a comment

Filed under Uncategorized

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

 


 

1 Comment

Filed under Uncategorized