Design an application strategy to support security.
This objective may include but is not limited to: application roles, schema ownership, execution context, Windows vs. SQL authentication, permissions and database roles
We need to understand the implications of how we manage security in the server. For this we need to understand how the various methods of securing the server can be applied, what the pro’s and con’s are for each, and how it is that each piece fits together versus those that we can use in an either or (XOR) situation.
Note: I believe that I get most of these items in theory. However, our security at work in using many of these items are absolutely AWFUL. Nearly everything is owned by dbo and the only real security is whether or not a user has execute, read, or write to the database as a whole. But, there are many other things that we need to work out first. As such, this information is presented as I know it from reading articles and practicing at home, not from hard won experience on the job (although we have recently started to look into some of these issues, I hope to push it more).
Application Roles are a security mechanism tied to a particular database which will allow for running under a separate set of user-like permissions. Access to application roles is open for both windows and sql authentication. Application roles can be used to enable access to data only when connecting through a specific application. This roles contain no members and are inactive by default. When using app roles the users own security context is completely negated and only the security context of the app role is valid. This has consequences in that the connection can now only access other databases as the guest account and server level data is inaccessible.
Security Note: The odbc option is not supported by SqlClient. Make sure that the password for the application role is being secured by using an encrypted channel like SSL or IPsec when transmitting passwords in clear text.
In order to use application roles, the user will execute an application which will connect to the instance as that user. This application will then execute sp_setapprole with a password supplied by the application (i.e. unknown to the user), which will enable the application role, and in effect disable all current user permissions.
Schemas are an important part of the permissions hierarchy of the database engine and provide a further level of separation within a database and are the containers for objects, such as tables, sprocs, functions, etc. For everyone who learned everything they know about SQL in the 2000 or prior years, Users and Schemas were separated in 2005.
Schemas must have an owner. This owner can be a database user, database role or application role. The simple database objects underneath (or contained by) the schema do not have to have an owner (the principal_id is null). They are assumed to be owned BY the schema. However, each of these objects CAN be set such that they are owned (the principal_id is not null). It should be noted that the schema level owner will always have CONTROL permission of the objects which it contains, even if the owner has been set to another principal.
A look at the Understanding Execution Context page shows us that execution context is determined by the user or login connected to the session, or executing a module. This is important as the context determines the identity to check for permissions used on the execute statements or the other actions which are being performed.
To fully understand execution context, one must understand the security structure of the login and user security tokens. For each of these security tokens, there are one or more principals serving as the primary identity, one or more principals serving as the secondary identity (Permissions inherited from server or database specific roles and groups), zero or more authenticators (principals, certificates or asymmetric keys that vouch for the authenticity of the token) and the privileges and permissions associated with each of the primary and secondary identities.
Principals are individuals, groups or processes that can request resources; they are categorized at the windows level, server level and database level; Principals have SIDs and IDs; a database user is a principal at the database level.
A login token is valid across the instance. A user token is valid in the specific database.
All of these are relevant when looking into Context Switching to control which permissions are being run under.
See the Encryption Hierarchy for an idea of how complex some things can get as they relate to security through certificates, loginless accounts, etc.
Windows vs. SQL Authentication
The question of Windows vs SQL Authentication is described quite well in Choosing an Authentication Mode article.
There has been a big push towards windows authentication, as it is generally seen as a much more secure means of authentication (the validation is provided through the operation system, there is no need for a separate password). With windows authentication you get the benefit of Kerberos security protocol, password policy enforcement (complexity and expiration) and lockout. This mode is often called a "trusted" connection.
With SQL Authentication, you create the users in the server. You need both the name and the password (which are both stored in the server), and these will need to be supplied every time the user is to connect to the server.
Pros and Cons of SQL Authentication
With sql authentication, a user must remember both the system login information as well as the sql server specific login information. Furthermore, Kerberos security protocol is not supported, and the sql server login password policies does not support all of the password policies available in windows.
However, sql server authentication allows support for legacy and third party applications, mixed operating systems (i.e. not necessarily windows), connections from unknown or untrusted domains, support of web-based apps where users create their own identities and allows development of preset logins for complex permission hierarchies in application distributed by software developers.
Permissions and Database Roles
Database Level Roles are a lot like groups in windows. The roles themselves grant a collection of one or more permissions to all principals who are associated with that role. There are two types of roles, fixed database roles (such as db_owner, db_ddladmin, etc) and flexible roles (which are roles created by the user). You can add any database user or other sql server role into a database-level role.
A best practice is to use database roles to establish permissions to all database objects so that if users need to be added or deleted from the database, they can be added or deleted from the roles rather than from the individual objects within the database. These roles can be used to establish a clear definition of the permissions level associated with users or roles.
Security Hint: Do not add grant fixed roles to the user created flexible roles. This can create unintended permission escalation.
Fixed database-level roles:
- db_owner – perform all configuration and maintenance activities within the database, can also drop the database
- db_securityadmin – can modify role membership and manager permissions.
- db_accessadmin – can add or remove access to the database for logins and groups
- db_backupoperator – can back up the database
- db_ddladmin – can run and ddl command in the database
- db_datawriter – can add, delete or change data in all user tables
- db_datareader – can read data from all user tables
- db_denydatawriter – cannot add, modify or delete data in the user tables
- db_denydatareader – cannot read any data in the user tables.