SQL Server Audit
Why?
Security has become one of the
most important concerns to be focused on in any environment in today’s world.
If we consider the same in SQL, a very well designed Audit feature readily
available for use under Security tab. Especially
handling server level & database level roles, access level of every user is
very much important for a SQL DBA.
There may be time when developers
would request highly privileged access in SQL PROD environment to check the
functionality of their code or to fix some minimal bug etc. But it’s not
possible to keep hawk eye on what the developer is doing to any other SP’s or
accessibility roles of other users, etc. Thus in order to prevent unauthorized
changes & to revert back if any unnecessary role changes that taken place
we need a log. Here comes SQL SERVER AUDIT to help us.
How?
With SQL Server Audit we can
monitor server-level and
database-level activity
– Record audit data to Windows
Application log, Security log, or a binary file as per our requirement.
– Components
•
Server
Audit
•
Server
Audit Specification
•
Database
Audit Specification
We
are going to define one Audit & its corresponding Server Audit
specifications then enable them to audit our SQL server instance & read the
logs from binary file using a Sql function sys.fn_get_audit_file.
Scenario:
Imagine there is a user named
‘User1’ who have read access on a database & another user who have admin
rights in SQL changes the access level from read to write. We are going to
enable audit to find who did that change & to avoid any unnecessary changes
happening in access level in future.
Let’s Begin:
Login to your SQL
instance
Creating New Audit:
Goto Secutityà
Audit , Right click on it to start a new audit
·
I had named it as “MyAudit” it will show a name
with “Audit –Date –file number” by default.
·
Enabled continue on audit Log failure. This
makes the audit to run though it is failing for some reason.
·
We can also select options like Shutdown SQL
server when this audit fails or fail the operation from performing as the audit
got failed.
·
On the other way you may ask SQL to continue on
failure of audit & log the same.
·
I had decided to log my audit into a binary file
so that I can read it later.
·
You may select your audit to get recorded to
Application / Security Logs but ensure the Service Account on which SQL service
runs has access to write on these logs.
·
You may also restrict the maximum file size that
the audit file should grow & restrict the number of audit files to a
specified limit.
·
So finally “My Audit “ properties will be like …
Creating New Audit Specification:
This is the place where we will
select the components & details which we need to monitor in My Audit.
Goto Secutityà
Server Audit Specifications, Right click on it to start a new audit
specification.
·
Give a name to our audit specification. Here I
use “MyAudit Specification”
·
Map it to
My Audit already created.
·
You may select number of actions that you may
need to audit.
·
We are going to select “DATABASE_ ROLE_MEMBER_CHANGE_GROUP”
as we need to monitor any changes made for a user access in database.
·
Finally our MyAuditSpecification will look like
Once we had created our My Audit
& My Audit Specification, We need to enable them as both will be disabled
by default.
Right click on them & select
Enable.
Now I am going to create a login “user1” on SQL server instance.
Then going to create its
corresponding User on “SERVERCOUNTS” database.
At first assign the role of
data_reader to the user then will change it to data_writer.
The Audit we had enables will capture all these events &
will log the same on an audit file.
We will read the audit file from SQL query window with help
of sys.fn_get_audit_file function from the path where it is stored.
The output of the above query will provide us a finest audit
report.
Thus we had seen how to use SQL server Audit to create &
enable an audit through which we can monitor the access level change of any
user.
Thanks for coming till this point!!
VSG
SQL DBA
No comments:
Post a Comment