Security is an important topic that may not always get the attention it deserves from your organization’s database developers. While the majority of responsibility for securing the company’s SQL Servers falls to database administrators, developers can benefit from having a thorough understanding of the methods used to protect the systems that are running their applications. This post is designed as an overview of the security issues that demand the attention of the development team.
Authentication and Authorization
Confusion can develop around the seemingly similar terms of authentication and authorization. While they work hand-in-hand, there are subtle differences that inform the degree to which developers need to be concerned about them. Basically, authentication describes the ability to get on a server and authorization controls what can be done once you have obtained access.
Authentication is performed with logins, which may be assigned certain permissions that can be used on the server. Authorization is conducted on the user level and can be a bit confusing since users also have logins. Permissions are often assigned on a user level and are retained within the database. This can cause extra work when changes are required, as each user may need to have their permissions modified.
For this reason, groups are the preferred way to assign permissions. Using Active Directory (AD) groups should be the first choice followed by AD users, with an SQL login only used as a last resort. Using an SQL login for applications is not recommended and will attract unwanted attention from auditors surveying the security landscape of your server. You should also strive for one login per function such as SQL_API for your API server and a reporting server login of SQL_Reporting.
One of the most compelling reasons for using AD accounts is to avoid the problem of “orphaned users”. When databases are backed up and restored to new machines, it can result in logins that do not work because their SIDs have changed due to being created in a different order. The SID is stored in the database, and getting the SIDs straightened out when bringing up a database in disaster recovery mode can be challenging and stressful. In the case of AD accounts, the AD SID is used which eliminates this problem when using the same domain.
In SQL Server applications, roles are groups that act as containers for permissions. Multiple users can be assigned to roles or a single user may encompass the complete group. Roles can be given descriptive names that give context to the permissions they possess. This can be extremely helpful when working through a security audit and defending the way permissions have been assigned across the database application. It is also good practice to define permissions in as granular a way as possible to minimize the range of activities or tables that can be accessed by a particular user.
Using Stored Procedures
Employing stored procedures can be another technique that can bolster your application’s security. When you grant execute permission to a stored procedure, the user does not obtain access to the underlying tables and data structures. They can simply execute the proc and let it do its thing. This makes your system less vulnerable to problems like SQL injection attacks. You assign permissions to various roles for a set of stored procedures that allows them to do the necessary work without exceeding their authority. Additional security checks can be added to the procedures and they are another example of methods that will help you successfully pass a security audit.
Encryption should also be considered as a further security mechanism. Encryption at rest will be something that auditors like to see. Backups should be encrypted to protect the database, especially if the backups go offsite. A layered approach is the most effective means of protecting your database, and certain areas of your system may require more granular encryption. In-flight encryption is often minimized due to performance considerations. This trade-off needs to be weighed carefully based on the sensitive nature of the information in question.
Keeping Tabs on Your SQL Servers
IDERA’s SQL Compliance Manager is a tool that assists your database team to stay on top of the security concerns of your SQL Server environment. The tool can be used to scan your databases for sensitive information that needs to be safeguarded. It’s a comprehensive platform that monitors, alerts, and logs access to your databases and servers. Demonstrating compliance during audits is an essential task in these days of increased regulation and SQL Compliance Manager is perfectly suited to the job. Customized reporting that builds on specific regulatory requirements makes it easy to provide evidence when auditors come knocking at your office door.
An informative webcast that talks about the aspects of SQL Server security that pertain to developers is available on IDERA’s website. It takes a more in-depth look at the issues presented above and affords database developers illustrative examples of the methods they should use to protect their applications. It’s worth your time if you care about building a secure system that will stand up to a security audit. That’s probably something that should be high on your to do list, so do yourself a favor and check out the webcast.