Skip to main content

Database Security Best Practices for the Vigilant Database Administrator and Developer

Published: November 11, 2010

Author: Hugo Shebbeare - Microsoft MVP, SQL Server: Systems Administration

The security of corporate databases and related issues have become increasingly important as enterprises consider the data they store, purchase, or acquire as assets that are critical to operations. Consequently, those involved with responsibility and control over these resources must assume the role of stewards of the data; otherwise, the security of the data, and, in turn, business operations are at risk.

Over the past couple of years, I have followed Erasmus's adage to leave no stone unturned—reflecting upon, deliberating over, studying, and blogging about data security. I believe the responsibility to help protect data is under-acknowledged by executives and management: While it is truly up to database administrators (DBAs) principally, and developers additionally, to handle data responsibly, these duties are often left to ineffectual auditors. The points I address below should enable the individuals tasked with data security to achieve the necessary level of responsibility, even if those at the higher ranks of the organization do not realize the direct link between proper data management and the organization's reputation.

Secure Your Data with an Effective Disaster Recovery Plan and Test It Regularly

This subject of an effective disaster recovery plan can easily fill an entire book, thus I dedicated an extensive article to the topic. The article provides a complete open-source disaster recovery plan, and includes significant additional resources. The article will be updated, to include database mirroring as an alternative, before the release of Microsoft SQL Server 2011, in which new functionalities will enhance high-availability features.

Back to top

Groom Your Database of Sensitive Data to Reduce the Risk of It Being Disseminated

If you do not want those who have access to the data to be able to see all archived information—for example, human resources data that includes salary information—take the time to properly archive it. Simultaneously, however, you will want to be sure not to lose information required by regulation.

Back to top

Separation of Duties with Respect to Internal Control: Maintain Compliance on Databases Used for Public Reporting

If you are a developer, it’s important to be aware of the segregation of duties within your respective organization, and to ensure that internal controls fall under the general  Committee of Sponsoring Organizations (COSO) of the Treadway Commission framework, which is more explicitly defined in the  Sarbanes-Oxley (SOX) Act. One of most important points of SOX compliance is the segregation, or separation, of duties, which basically means developers do not have access during production (to prevent the tendency to test there) and that specific procedures are followed when developing code before placing it in production. These procedures satisfy risk management practices under the COBIT framework (or ITIL) to avoid data being inadvertently, mistakenly, or maliciously changed due to ad hoc changes. Territorial as it may seem, it is necessary for physical DBAs to control production databases from development, and, without fail, to follow change management practices as defined by the industry. 

By following procedure and documentation by the COBIT or ITIL standards, we govern ourselves in a controlled environment, with respect to the production systems. Each task has to be validated (code walkthrough/fresh eyes) by a third person who is not writing the actual code. The database developer should not be able to execute anything in production without an independent review of the documentation/code for the work that is being performed. Typically, the role of the developer is to pass code to a DBA; however, given the cutbacks that have resulted from the current economic climate, a DBA might not be readily available. If a DBA is not involved, it is important, at minimum, for a peer to conduct a code review. This ensures that the role of the developer is clearly separate. 

If your organization lacks understanding of internal controls, please remind management that databases that fall under internal controls (that is, data used for public reporting, annual reports, etc.) are subject to the separation of duties. Furthermore, to track all changes and have recoverability, a bare minimum for a SQL Server database recovery is Bulk-Logged Recovery. Make sure to back up all your transaction logs and keep them indefinitely, which, in addition to providing a backup, will satisfy auditors (below I describe a way to read operations from the transaction log files directly).

Another point of internal control is adherence to the principle of providing the least amount of privileges, especially in production. To allow developers more access to get their work done, it is much safer to use impersonation for exceptions that require elevated privileges (see EXECUTE AS to do that temporarily). Often developers may dismiss this as “overhead” while on their path to coding glory. Please be aware, however, that DBAs must do all that is considered responsible because they are the de facto data stewards of the organization and must comply with regulations and the law.

Back to top

Group Your Objects Together Logically

You have likely noticed a change since SQL Server 2000 with respect to object ownership (formerly DB.ObjectOwner.Object, since SQL Server 2005, this is DB.Schema.Object). With schemas, we can benefit from the grouping of objects to make sense of often complicated relational database management systems. While a user can still create an object within the database as UserName.Object, it is a little messy to clean up when that person leaves the company, ( Sp_rename can be used to fix those problems).

In order to grant schema rights to a user:

Grant select, insert, update, execute on Schema::SchemaName to [UserName]

This is assuming that you have a specific user UserName generated and SchemaName exists. If you are using the built-in roles, then see below

CREATE USER [Domain\UserName] FOR LOGIN [Domain\UserName] WITH DEFAULT_SCHEMA= [db_datareader]

If you want a user to have read-only access, make the default schema db_datareader and not database owner (DBO).

If you grant the DBO rights, then you have granted DBO rights with control to all roles and schemas.

USE [DatabaseName] -- if you have to elevate their rights, then use the db_datawriter role first, instead of DBO

 GO

EXEC sp_droprolemember N'db_datareader', N'Domain\UserName'

GO

USE [DatabaseName]

 GO

EXEC sp_addrolemember N'db_datawriter', N'Domain\UserName'

 GO 

When we create an object, we can simply associate it within a schema (Create SchemaName.Object), which, in turn, makes managing the security of that group of objects easier. The other advantage is that we can place a schema within the database by the name of the application that uses it, which is a logical separation. Likewise, if multiple applications depend on a single database, schemas make it easy to understand which objects are used by what/whom and how within the database. 

As you can see in the above example, it's easier to manage the security of the group of objects, too, since you set the permissions on schema level and not for each individual object, which was noticeably tedious beforehand. If you wish to maintain the principle of least privilege, giving DBO access to users is not the best solution in regard to security. A preferred workaround for elevated privileges would be by means of EXECUTE AS instead (for more, see a great article on that here).

(Note: You can also use stored procedures and views to minimize data access, but because this is more commonly known than security facets of schemas, I will not be expanding on that subject here.)

Back to top

Use Activity Monitor to Easily Watch Processes in Your Database

To best serve as a steward, and to comply with regulations—particularly if your organization is public or governmental—DBAs should be familiar with Activity Monitor, one of my favorite SQL Server Management Studio enhancements. To use Activity Monitor, right click on an instance of SQL Server 2005 or SQL Server 2008 in Object Explorer, or click on the farthest right icon in the SQL Server Management Studio toolbar.

SQL Server Management Studio Toolbar

The latest version of Activity Monitor helps you see the processes, users, and applications that are currently using the database in the sortable/filterable Processes pane, and, of course, whether they are blocking transactions.

Even if you do not have SQL Server 2008 instances, it is still worth it to install SQL Server 2008 Management Studio complete client tools. Activity Monitor has built-in backward compatibility; thus, you can take advantage of the dynamic management views already existing in SQL Server 2005 while connecting from the SQL Server 2008 Management Studio Activity Monitor. Previously, to view the equivalent information in the Activity Monitor, was, for example, loading information directly from the dynamic management views, or in SQL Server 2000 exec sp_who2, in Excel sheets to understand what was really going on across the specific servers’ activity. Thankfully, this is now all built into SQL Server Management Studio because Activity Monitor provides sortable columns, which enable exceptionally swift pinpointing of problematic operations by database, through information on execution time/frequency, reads/writes, and CPU usage.

Since the initial version of SQL Server 2008 was released, you have been able to view real-time critical SQL Server process details and even sort by the worst-performing queries—whether it be by the number of times the offending code is run per minute, which login is running it, which database it is in, which application it is running from, etc. In Activity Monitor, practically everything you need to fix SQL Server issues is available, including the option to right click on a line in Recent Expensive Queries to optimize the problematic code right away. There are four panes with graphs for each, plus collapsible details, so you can view/filter processes, resource waits, disk activity, and Recent Expensive Queries. 

If you cannot update your instances to SQL Server 2008 for a while, then you can use the updated client tools in SQL Server Management Studio to benefit from this updated feature—in my opinion, a critical step in remediation.

Back to top

Set Your Database Instance Security to Audit Successful and Failed Logins

The SQL Server log file may become significant in size if many users and/or applications connect to your database server, but now that disk space is cheap, do not skimp on your auditing capabilities. After all, you want to know the who/what/when of logins to your particular database instance.

Server Properties window

Back to top

Keep the Integrity of Your Database Secure by Querying Your Transaction Log File

All operations are recorded to your database transaction log file, especially if you are using Full Recovery Model; therefore, you can take advantage of a few simple queries to double-check who is running which command in the database. For more on this, read up on how to view the important information within your transaction log file.  Some would consider this transaction log file forensics; I prefer to think of it as doing your job as a vigilant DBA. In the linked blog post, I describe how you can set up jobs to alert you of unwanted data manipulation language (DML) statements (such as inserts, updates, delete) or data definition language (DDL) (such as create, drop, alter).

Back to top

Hack Your Databases Before Some Unwanted Access Occurs

As mentioned by Don Kiely in his great article “ Hack Your Database Before the Hackers Do,” one should use a series of tools—at least three in my opinion (Microsoft Best Practices Analyzer, SQL Password Checker from Idera, and Microsoft Baseline Security Analyzer)—to ensure due diligence.

A word of caution: If you are not the DBA, please make sure you have authorization to do so before proceeding.  Even if you are on a project, do not assume that you can simply try to gain unauthorized access into production, as this would be considered an intrusion by the database administration team or inept auditors

Back to top

Maintain Strong Passwords: There is No Excuse; the DBA Is Accountable

Use a password generator such as Password Manager Pro from ManageEngine, and keep it in a password vault. Password strength is facilitated by free online password generators that use a password length of 12 alphanumeric characters. Avoid default passwords or cutting and pasting a user name (or a using a password name that describes the level of access). Needless as it may be to say, these are not shortcuts you want to take, especially since simple software (e.g. SQL Password Checker, mentioned above) can verify password strength.

Back to top

Validate User Access: Attempt to Evaluate Potential Risks to Database Security

Not only should you fully understand what roles, or specific permissions, are given for a specific user and/or application, but you can also confirm that access is at the desired level by logging on as that specific user. If you are making big changes to your systems that involve several user access changes, be sure to go through a significant deployment management process.

Back to top

Summary: Databases Are Not Places to Be Lax on Security

DBAs play an increasingly crucial role in security, and security is a vital aspect of our job. The points above should help guide you in the right direction as you work to help protect your organization’s assets and customer data. The consequences of not safeguarding data or failing to comply with regulations for data security can include significant fines and can jeopardize business operations and the reputation of your organization.

Back to top

About the Author

Hugo Shebbeare photoA database administrator since 1999, Microsoft SQL Server MVP Hugo Shebbeare (MCDBA, MCITP) has a long history of working with SQL Server and is currently a part-time independent consultant with his own company,  Intellabase Solutions, and permanent at Canadian printing giant Transcontinental. He manages geographically redundant databases with to his favorite relational database management system (RDBMS) and  MySQL 5.x and Oracle 10/11.

Hugo has spoken at events like Microsoft TechDays, SQLteach/DevTeach, Montreal Dot Net User Group, Roman Rehak's Vermont SQL PASS Chapter, and maintains a  blog on SQLServerCentral.

When not providing technical guidance for quick, safe infrastructure rebuilds and expansions, he is an  avid proponent for fiscal accountability in the Quebec political and judicial system.

Microsoft Security Newsletter

Sign up for a free monthly roundup of security news, bulletins, and guidance for IT pros and developers.