Security is very important in today's world, but it is very hard to secure SQL data if you do not understand the control options available. In this article I will share how to set database roles and explain how they can help you tighten down security within your enterprise.
Database roles
Database roles allow you to define a given set of permissions to a database role. Server logins (either AD groups, AD users, or SQL Server logins) are then added to the server and added to the database role. Any permissions that need to be modified because of added, deleted or other object changes only need to be made to the database role and each login/group assigned to that role will then get the defined permissions.
[IMAGE]
Step 1: Right click over "Database Roles", select "New Database Role".
[IMAGE]
Click on image for larger version
Step 2: You can click on "Securables" and select other database roles, objects, etc to be assigned to that role. After that click "OK." The database role should now appear in the "Database Roles" listing under your database/security/roles.
[IMAGE]
Step 3: Assign permissions to your database role. Only assign permissions to the database role that are absolutely necessary. I am a strong advocate that all database access should be via stored procedures, because this gives a company the following benefits:
- Compiled stored procedures reduce the opportunity for SQL Server injection attacks which could cripple your operations.
- Compiled stored procedures allow for the caching and reuse of execution plans, which reduces IO/CPU/RAM usage because one execution plan will be stored for parameterized statements rather then a separate plan for each execution.
- Easier access control. Rather then granting select/delete/insert/update access to multiple individual tables that might be accessed during a given process (that could easily be 10+ tables), all you need to do is grant execute rights to the appropriate stored procedures which then grants appropriate rights on the underlying objects.
Here are some ...
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
sample grants that we will use for this scenario:
- grant execute on [dbo].[Proc_test1] to [TestdbRole]
- grant view definition on [dbo].[Proc_test1] to [TestdbRole]
The first statement grants members of the "TestdbRole" database role rights to execute the Proc_test1 stored procedure. The second statement grants view definition rights for the stored procedure to TestdbRole. Basically this means that members of the role can now execute the proc as well as view its contents. However, they have no other rights, since Proc_test1 selects from the table Table_1 it has been implicitly granted select access rights to table. However, members of the TestdbRole cannot select, view the definition or perform any activities on Table_1.
If you choose to grant rights for direct object access, it's as simple as doing "grant select on [object] to [TestdbRole]". However, as stated before, I do not recommend this approach.
Step 4: Create/use the user/group that you want to add to the database role. In our case, we will create the login/user "roleaccess" in the figure below.
[IMAGE]
Click on image for larger version
At this point you can also assign the user to a database and the role that has been created as you see below.
[IMAGE]
Click on image for larger version
Verifying role access:
You can verify the access by reviewing the database role in the database as shown in the figure below. Right-click over the database role, hit properties, and it will show the window below with the new role member added.
[IMAGE]
Click on image for larger version
You can review the stored procedures/other objects secured by reviewing the securables as seen below.
[IMAGE]
Click on image for larger version
Testing security with database roles
To see what the role access settings we've made actually do, log into SSMS with the "roleaccess" user. Reviewing the figure below, we can see all the databases, but cannot access them. We also don't see all the tables or stored procedures, just the ones we have access to.
[IMAGE]
We can also right click over Proc_test1 and click modify, view the code, but we can't modify it as shown below.
[IMAGE]
Click on image for larger version
Finally, we can execute the stored procedure, but we cannot select against the Table_1 table.
[IMAGE]
Click on image for larger version
My hope is that you will have learned a lot from this article. I have explained why it's best to keep all database access coming through stored procedures and how that helps you improve security/performance. We have also showed you how to define a database role, how database roles increase security/manageability, how to assign object permissions to the database role, create a login, assign a user to the database and the database role and how to verify the permissions defined. Finally, I demonstrated how it limits the user's access to the database. Please keep in mind that it's easier to define database security from the start of the project rather then trying to add it on later on.
ABOUT THE AUTHOR:
[IMAGE]
Matthew Schroeder is a senior software engineer who works on SQL Server database systems ranging in size from 2 GB to 3+ TB, with between 2k and 40+k trans/sec. Matt currently works for the gaming vendor, IGT, providing services to gaming companies. He also works as an independent consultant, specializing in SQL Server, Oracle and .NET for industries such as gaming, automotive, e-commerce, entertainment, banking and non-profit. Matt specializes in OLTP/OLAP DBMS systems as well as highly scalable processing systems written in .NET. He is a Microsoft certified MCITP, Database Developer, has a master's degree in computer science and more than 12 years of experience in SQL Server/Oracle. He can be reached at cyberstrike@aggressivecoding.com.
