SQL Server 2005 built-in Tool building review system

Source: Internet
Author: User
Tags insert sql new features access

As a database administrator for billing applications (DBA), the company's chief information Officer (CIO) often invites me to meet with Sarbanes-oxley reviewers to discuss the security and consolidation of corporate data. Simply put, he wants us to answer a number of questions through the right documents: who has access to our data, how to authorize access, and how we monitor access to prevent someone from invading, logging into data, or doing something they shouldn't.

My CIO and I have learned some business solutions that demonstrate compliance with the Sarbanes-oxley Act. However, we decided to apply the SQL Server 2005 built-in tools to build our "self-produced" review system.

Server 2005 New Features

Before using SQL Server 2005, we applied Data Manipulation language (DML) triggers to understand when data changes would occur in the database. We can point to the review log saying that there is a list of all the insert (insert), update (update) and delete (Detele) statements we have published since the last review, including the time, date, person, or program that executed the statement.

A DML trigger is a dedicated stored program that the database engine starts, and it has a significant role in establishing a review index for data changes. However, a DML trigger will only start if the data has changed. Before using SQL Server 2005, there is no better way to track the structure or security changes in the database server.

SQL Server 2005 supports data definition language (DDL) triggers. These triggers are not established and started when a server or database-level event occurs. DDL triggers allow us to track important changes in the database-intentional, unintentional, or malicious.

Track all Registrations

The following is a typical example of a DDL trigger that improves database monitoring capabilities. In previous versions of SQL Server, it was difficult to see when new registrations were established, when a new database was established, or when new licenses were assigned to different users.

In SQL Server 2005, it is relatively easy to track such security changes. To illustrate this point, no matter who the user is, let's design a solution that tracks all the behavior on the server. We will use the DDL trigger event ddl_login_events to establish the audit index. This event trigger will look for any registered events on the server, including all established registrations (create login), modify registration (ALTER LOGIN), and delete registration (delete login) events.



Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.