Friday, December 2, 2011

SQL Server Execution Context and Security

What is Execution Context?

Whenever a user connects to SQL Server, it will create the User Session called Execution Content which has the all the user access details  and whenever that user executes a SQL statement, SQL Server uses these details to validate the user permissions before executing  the query.

Execution context is represented by a pair of security tokens: a login token and a user token. The tokens identify the primary and secondary principals against which permissions are checked and the source used to authenticate the token. A login connecting to an instance of SQL Server has one login token and one or more user tokens, depending on the number of databases to which the account has access.

What is Principals in SQL Server ?

Principals are the individuals, groups, and processes that can request SQL Server resources. Principals are categorized by their scope. Every principal has security identifier

  • Windows level
  • SQL Server level
  • Database level

Windows-level principals

  • Windows Domain Login
  • Windows Local Login

SQL Server-level principal

  • SQL Server Login

Database-level principals

  • Database User
  • Database Role
  • Application Role

What are SQL Server Securable?

SQL Server Securable contains three scopes, which are used to assign permissions to users. The securables are nested and each securable contains various other securables. The securable scopes are as follows:

· Server: It includes server roles, logins, etc.

· Database: It includes database users, application roles, database roles, etc.

· Schema: It includes various database objects such as tables, views, procedures, etc.

The securables are used to assign permissions to the users based on scope and the tasks assigned. The issues related to the connectivity to databases, accessing database objects, etc., can be resolved by granting or denying the permissions to the users.

What is the use of the Public database role in SQL Server?

Every database user belongs to the public database role. When a user has not been granted or denied specific permissions on a securable, the user inherits the permissions granted to public on that securable.

Explain Certificate based SQL Server Logins/Principals?

Server principals with names enclosed by double hash marks (##) are for internal system use only. The following principals are created from certificates when SQL Server is installed, and should not be deleted.

  • ##MS_SQLResourceSigningCertificate##
  • ##MS_SQLReplicationSigningCertificate##
  • ##MS_SQLAuthenticatorCertificate##
  • ##MS_AgentSigningCertificate##
  • ##MS_PolicyEventProcessingLogin##
  • ##MS_PolicySigningCertificate##
  • ##MS_PolicyTsqlExecutionLogin##

0 comments:

Post a Comment