Video : Database Auditing in SQL Compliance Manager

Transcript

00:08

SQL Compliance Manager

Welcome to the idea of virtual education. In this video, we’re going to talk about defining database-level audit events within SQL Compliance Manager. In a previous video, we talked about server-level audit configuration. In this video, we’re going to get down to the database level.

SQL Server Auditing

Now, when you right-click on a database and you click on Properties, the Audit Event tab, very similar to the server level, is going to walk you through what types of audit events you want to see in relation to this database. S in here that I think are important to understand. For example, DML and select Auditing. Right? DML and select generally are going to be the most prevalent types of events within the database. It really that you need all of them? Most likely you do not. That being said, you have the option to audit every DML and select on the database if that’s the direction you want to go.

00:59

Trusted Users

What I want to offer in this video is some suggestions or some ideas of ways to minimize events that you maybe don’t need to see. One good way to do that is the option of trusted users. Trusted users is the ability for you to filter out both roles and or SQL logins from having any of their activity audited. A good example of a trusted user is a user that you have good control over, such as a service account from an application or some maintenance-type service account, right? You may have an ERP system that has very deep internal auditing built into it. When that ERP service account hits the database, that’s not really something that you’re concerned with. Therefore I can go ahead and add them as trusted and therefore omit them from being audited. On the other hand, you may have a group of developers.

01:57

Developers

For example, I have a group called Outside Dev. By the way, these accounts are grayed out because I’ve already defined these users at the server level. Therefore they’re being pushed down to the database. The idea is my outside development team has access to this database. They are going to be in there from time to time doing schema changes, maybe viewing data, et cetera. Therefore, we want to track them wholesale generically across this database. For all other users though, I have the option to also audit their DML and select activities. If we look at the DMO and select Filters tab, one thing to notice is that I don’t have to audit the whole database, right? I don’t have to look at all database objects on this particular DB. I’m looking at all user tables. You can go further and filter to these specific tables that you need to audit.

02:49

I do encourage that often databases will have look-up tables and things like that have no real relevance to any kind of personal identifying information or credit card numbers. And those sorts of things. It’s best when an option to omit that type of irrelevant data from the auditing. It saves you space, it saves you resources, etc.

Sensitive Column Auditing

Now you also have the option to do what we call sensitive column auditing. Sensitive Column Auditing is literally where I go in and add a table and then define the columns within those tables that we want to audit. When we define something as sensitive, you have the option to audit select only all activity, selecting DML. This gives you a lot more granularity. If you know that you have an HR database with a salary table, there may be only three or four columns within that table that need to be audited.

03:48

The rest of them can be ignored, right? That’s the idea behind sensitive column auditing. You can also expand this DML auditing, for example, to before and after. Before and after is really talking about what was the value before the DML change occurred, what is the value after the DML change occurred? This is column specific, or it can be column specific. You can also identify how many changes per statement that you want to see. If someone runs a statement that updates a million rows, do you want to see all 1 million changes, or is it a subset of that data that you want, or those data changes that you want to see? The idea here is that in SQL Compliance Manager, you have different ways of defining the audit. You can leverage privileged and trusted users to identify auditing based on groups or roles or people.

04:40

You also have server-level auditing where you can do higher-level events like failed logins and security changes. At this level, obviously, you can get down to the database where you can identify certain tables and columns that need to be audited and focus directly on the content within your DBS that matter most. Hopefully this was useful for you guys today. Thank you very much for your time.