Schema-Based Access Control for SQL Server Databases
Database access controls are based on the principle that the users of databases should have permissions to view, modify or delete only the data in databases relevant to their job. This is also known as the principle of least privilege. No users including Database Administrators (DBA) should be exempt from this principle. For example; DBA’s should only use an administrative account when required and use a less privileged account at all other times (Factor, 2015).
A simple structured schema-based approach to access control uses the permission hierarchy in ANSI SQL to create database roles that have the minimum necessary privileges at the highest possible level. This approach simplifies assigning permissions because they are inherited from the schema by all objects within the schema. When a database user is assigned to a particular database role, they obtain permissions to access every object within any schema to which that role that has been assigned permissions. This simplifies access control and also assists in the development of shared databases (Factor, 2015).
The Microsoft TechNet web site provides some additional information on access control and server security in SQL systems. All versions of SQL Server use role-based security, which allows you to assign permissions to a role, or group of users, instead of to individual users. Fixed server and fixed database roles have a fixed set of permissions assigned to them. Fixed server roles have a fixed set of permissions and server-wide scope. Fixed database roles have a pre-defined set of permissions that are designed to allow you to easily manage groups of permissions. Logins must be mapped to database user accounts in order to work with database objects. Database users can then be added to database roles, inheriting any permission sets associated with those roles (MSDN Overview of SQL Server Security, n.d.).
Factor, P. (2015, April 09). Schema-Based Access Control for SQL Server Databases. Retrieved March 21, 2016, from https://www.simple-talk.com/sql/sql-training/schema-based-access-control-for-sql-server-databases/
MSDN Overview of SQL Server Security. (n.d.). Server and Database Roles in SQL Server. Retrieved March 27, 2016, from https://msdn.microsoft.com/en-us/library/bb669065(v=vs.110).aspx