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.
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.
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).
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
EXEC sp_droprolemember N'db_datareader', N'Domain\UserName'
EXEC sp_addrolemember N'db_datawriter', N'Domain\UserName'
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.)
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.
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.
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.
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.
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).
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
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.
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.
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.
About the Author
A 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.