My interpretation on this subject of data security will consider all the issues I can reflect upon, deliberated over, have studied and blogged about over the past couple of tumultuous years, following Erasmus’ adage to leave no stone unturned. I believe the responsibility to protect data is under acknowledged by executives and management, leaving the bureaucratic obligation left in the hands of ineffectual auditors, when it is truly up to the database administrators (DBAs) principally and developers additionally, to handle data responsibly. Hence, the points I address below should enable the individuals tasked with the data security role, indirectly or directly, to straightforwardly achieve the indispensable level of responsibility, even if those imaginably at the higher ranks of the organization do not realize the direct link between proper data management (since data is a valuable asset in itself) and the organisation’s reputation.
Secure Your Data with an Effective Disaster Recovery Plan and Test It Regularly This subject apropos an effective disaster recovery strategy can easily fill an entire book: consequently I dedicated an extensive article of over four thousand words to the topic last summer, after months of editing. The article provides a complete open-source disaster recovery plan, where readership from all sources is at almost forty thousand (40k), cumulatively from Blog sources and French translation. It will be updated shortly to include Database Mirroring as an alternative, before SQL Server 2011’s functionalities are released, in which new features will enhance High Availability greatly.
If your organisation is in denial (worst case scenario) or lacks understanding (best case scenario) of internal controls, please remind management that databases which fall under internal controls (meaning the data used for public reporting, annual reports, et al.) 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 (or out of lethargy :) 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.
In order to grant schema rights to a user, here are a few examples:
Grant select, insert, update, execute on Schema::SchemaName to [UserName]-- this is assuming that you have used a specific user UserName generated and SchemaName existsCREATE SCHEMA [SchemaName] AUTHORIZATION [dbo] – members of DBO have control over the Schema
-- if you are using the built-in roles, then see belowCREATE USER [Domain\UserName] FOR LOGIN [Domain\UserName] WITH DEFAULT_SCHEMA= [db_datareader]
If you want a user to have read 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.
EXEC sp_droprolemember N'db_datareader', N'Domain\UserName'
EXEC sp_addrolemember N'db_datawriter', N'Domain\UserName'
Easily Watch Over Processes Within Your Database Thanks to Activity Monitor
As part of a vigilant DBAs’ duty to best serve as a steward, and to comply with regulations—particularly if your organization is public or governmental—I would like to introduce one of my favorite SQL Server Management Studio enhancements with SQL Server 2008 that DBAs should be familiar with: Activity Monitor (AM). To use AM, right click on a SQL Server 2005/8 instance in the object explorer and take a look (or click on icon just below Tools/Window in SSMS, the toolbar icon that is the farthest right):
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 (except SQL Server 2000 and older); 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, for example, to view the equivalent information in the Activity Monitor, one had to load information directly from the dynamic management views, or in SQL Server 2000 (exec sp_who2), in Excel sheets using an external data source connection to understand what was really going on across the a 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.
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. To set your server to audit logons to the SQL Server Instance, right-click on the server instance in SQL Server Management Studio, select properties, choose Security and click the radio button under Login auditing ‘Both failed and successful logins.’
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 SQL Server 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 explicit authorization to gain access into production 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 miguided auditors.
Use a password generator such as Password Manager Pro from ManageEngine, and keep the password in a password vault. Password strength is facilitated by free online password generator that uses 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.
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 (short and long templates can be downloaded for free).
Account Management: Keep the Access List Updated
As mentioned by Sean Smith, who has helped to contribute to this article, it is essential to tidy up by means of disabling accounts, either for termed employees, or for employees who no longer require database access. It is amazing how many accounts remain active for such people whose role or mandate has changed.
Protect Against SQL Injection
Lack of proper protection against SQL Injection, fully described here by SQL MVP Adam Machanic, can cause immeasurable damage, whether intentional or accidental. In the previous link to his book Expert SQL Server Development, Adam describes the ways to protect against this, although it is unfortunately seldom considered or implemented.
As mentioned by Ericka Chickowski, in her article regarding DBAs Playing [an] Increasingly Crucial Role In Security, it 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, of up to one million dollars (yes $1M), as is done by the Information Commissioner’s Office in the United Kingdom vis-à-vis violations of the Data Protection Actand can jeopardize business operations and the reputation of your organization. Failing to be vigilant with data could also result in the loss of billions of dollars of public money, a situation I unfortunately witnessed first-hand six months after I had internally provided a report detailing an audit failure at Canada’s largest public Institutionalized pension fund manager, the Quebec Deposit and Investment Fund (CDP Capital).
Additional Resources for Risk Management, IT Security and Data Governance from Microsoft:
Microsoft and Data Breach Notification
Privacy Guidelines for Developing Software Products and Services