Securing Objects


  • Define object-level security, including column-level permissions, by using GRANT, REVOKE , and DENY .

SQL Server provides an auditing facility as a way to trace and record activity. SQL Server 2000 also provides the SQL Profiler for performing more in-depth monitoring. Auditing can be enabled or modified only by system administrators, and every modification of an audit can also be audited to prevent administrative abuse. The ability to create, use, alter, and remove objects can also be tightly controlled through the use of statement and object permissions.

SQL Server can accommodate two types of auditing: standard auditing and C2 auditing. Standard auditing provides some level of auditing but does not require the same number of policies as C2 auditing. C2 auditing requires that you follow very specific security policies. C2 security is more than just a machine standard, but has aspects to cover the whole computer facility. You can use SQL Profiler to perform both types of auditing.

C2 Security

Permission sets determine which network identifiers, groups, and roles can work with specific objects, and what degree of interaction is allowed. Chapter 6 covered security, and because permission sets are more an Administrator concern at this level, this chapter touches on a few more considerations but does not go into too much depth. If you are preparing for the companion exam, it is recommended that you learn more about this broad topic.

For more information about C2 certification, see the C2 Administrator's and User 's Security Guide. This guide is in downloadable form from the Microsoft Technet site. URL information is provided at the end of this chapter under "Suggested Readings and Resources."

Statement and Object Permissions

Activities involved in creating a database or an item in a database, such as a table or stored procedure, require a class of permissions called statement permissions. Careful control over who can create and alter objects is very important. Essentially, statement permissions allow for the creation and alteration of objects. Statement permissions are:

  • BACKUP DATABASE

  • BACKUP LOG

  • CREATE DATABASE

  • CREATE DEFAULT

  • CREATE FUNCTION

  • CREATE PROCEDURE

  • CREATE RULE

  • CREATE TABLE

  • CREATE VIEW

Because object ownership is established upon creation of objects, and too many owners can add unnecessary overhead, you should ensure that anyone creating objects is doing so under dbo ownership.

Object permissions determine to what level data can be accessed or whether a procedure can be executed. The list of object permissions is as follows :

  • SELECT , INSERT , UPDATE , and DELETE permissions applied to data from a table and/or view

  • SELECT and UPDATE statement permissions, which can be selectively applied to individual columns of a table or view

  • SELECT permissions, which may be applied to user-defined functions

  • INSERT and DELETE statement permissions, which affect the entire row, and therefore can be applied only to the table and view and not to individual columns

  • EXECUTE statement permissions, which affect stored procedures and functions

Setting of permissions can allow audit processes to determine successful and failed attempts to use the permissions.

Security Audits

You use SQL Profiler for auditing events in several categories. A list of these categories and their descriptions is as follows:

  • End user activity. Login, SQL, and enabling of application roles

  • DBA activity. Includes configuration of database and server

  • Security events. grant/revoke/deny, add/remove/configure

  • Utility events. backup/restore/bulk insert/BCP/DBCC

  • Server events. shutdown, pause, start

  • Audit events. add audit, modify audit, stop audit

With the SQL Profiler you can determine the date and time of an event, who caused the event to occur, the type of event, the success or failure of the event, the origin of the request, the name of the object accessed, and the text of the SQL statement.

Auditing can have a significant performance impact. Before you select any object for auditing, balance the trade-off between the likelihood of a security breach and the overhead of the audit. Carefully consider an audit strategy before merely turning on auditing for all objects. If SQL Server is started with the -f flag, auditing does not run.



MCSE Training Guide (70-229). Designing and Implementing Databases with MicrosoftR SQL ServerT 2000 Enterprise Edition
MCSE Training Guide (70-229). Designing and Implementing Databases with MicrosoftR SQL ServerT 2000 Enterprise Edition
ISBN: N/A
EAN: N/A
Year: 2003
Pages: 228

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net