Third SQL Server security principals and securable objects

This article is the third of the SQL Server Security series, please refer to the original text for more information.

In general, you implement the security of users and objects on SQL Server by assigning the principal permissions to objects. In this series, you will learn to perform operations and access securable objects in a SQL Server instance through permission authorization. The important subject in SQL Server is the role, and you will learn that roles can make security management easier than using individual users. You will also learn about the security objects of SQL Server.
Authorized
Authentication is an accessing all of the goodies in a database server. Certification is a bit like having a passport to prove who you are but no visa. You need a visa to visit other countries. In this article you will learn about authorization and how it is accessed as a Visa database object.
A principal is a securable object that a user or process can access to one or more SQL server/databases. A securable object is a protected resource that only certain people or processes can view or change, such as data in a table. A permission allows the principal to gain access to a particular type of securable object.
Continuation of the passport analogy, the subject is the holder of the passport, the security object is the subject to visit the country, the authority is the visa can cross national boundaries and enjoy access.
Main body
The principal, in which the security context is any user, group (called Role in SQL Server), or code running in the process, can request access to a securable object and is granted or denied access. All windows and SQL Server logins are principals, and they are mapped to users in the database. The following list shows the hierarchy of important principals in most SQL Server, from the server level to the SQL Server instance level, to the database-level principal.
Windows-level principals
->windows Domain Login
->windows Group
->windows Local Login
SQL Server-level principals
->sql Server Login
->sql Server login Map to Certificate
->sql Server logon mapping to Windows logon
->sql server logins mapped to asymmetric keys
database-level principals
Application roles
Database role
Database users
Database user mapping to a certificate
Database user mapping to Windows logon
Database user mapping to an asymmetric key
->public role
It is important to understand this level, because the scope of a subject determines the scope of the permissions granted to it. For example, a database user
Only permissions are available in the database to which permissions are granted. A SQL Server-level principal can have permissions on SQL Server, and the Windows-level principal has permissions beyond the limits of SQL Server, on both the local instance of Windows and the entire network.
A principal can be a login (or user) or a role. The SQL Server role acts like a Windows group. The members in the role inherit the permissions assigned to the role. Roles make security management easier because you don't need to manage complex permissions for individual users. SQL Server supports the following types of roles:
fixed server role: SQL Server built-in role performs server-level tasks
user-defined server roles: custom server roles that you create, assign server-level permissions, and specify logins so that logins inherit permissions from server Objects
fixed database role: Built-in roles perform database tasks and assign basic permissions
user-defined database roles: youCreate a custom database role, assign permissions, and then add users so that users inherit permissions from database objects
You can assign users to multiple roles. Roles can also be nested, but if your nesting is too complex, you will suffer a performance penalty, and it may become a nightmare for maintenance and diagnostics.
fixed server role
fixed server roles are SQL Server built-in roles that you cannot change in any way, and you can only add logins to them. They exist at the server level and are used only to perform administrative tasks. The fixed server roles in SQL Server are as follows (the actual role names in parentheses):
System administrator (SysAdmin): Perform any activity on the instance of SQL Server. This role contains all the other roles, and once the user is a member of the sysadmin, they do not need any other roles. Members of the sysadmin can do anything, so it is necessary to restrict member users to only those users who need and can be trusted to access
--Bulk INSERT Administrator (bulkadmin): Execute BULK INSERT statement to quickly import data into the database
Create DATABASE (dbcreator): Create and change databases
Disk Administrator (Diskadmin): Managing Disk files for storage databases
Process Administrator (processadmin): Manage processes running on SQL Server
Server Administrator (serveradmin): Configure server-wide settings. Although similar to the name of the system administrator, serveradmin is a very different and very limited role
Setup administrator (setupadmin): Install Replication and Management extensions
Security Administrator (securityadmin): The login name of the Management Server
The flexibility and security provided by the fixed server role allows you to divide the server's tasks into sections. In other words, if they just need to create a database, you don't have to make them a member of the system administrator. Let them become dbcreator members and have all the permissions they need.
You can use Management Studio or T-SQL to specify a login name to a fixed server role. To use management Studio, perform the following steps:
1. Expand,ssms> Object Explorer > Security > Logins
2. Right-click the Topaz login and choose Properties from the pop-up menu
3. In the Login Properties dialog box, select Server roles. This will list all the available server roles. Note Topaz, like all logins, is already a member of the public role
4. Specify login to dbcreator and diskadmin roles. Figure 3.1 Displaying the Topaz Login dialog box
Figure 3.1 Assigning Topaz logins to dbcreator and diskadmin fixed server roles
5. Click OK to save the changes
Alternatively, you can add logins to the corresponding roles through Object Explorer > Security > Server Roles. Add the topaz to the securityadmin server role below:
1. Expand, Object Explorer > Security > Server Roles
2. Right-click the securityadmin server role and select Properties, which opens the Server Role Properties window
3. Click the Add button at the bottom right of the dialog box to open the Select Server Login dialog box. You can type topaz, click Check Names, or click the browse button to get a list of logins. Once you type Topaz, dialog box 3.2 shows

Figure 3.2 Selecting Topaz to add to the server role
4. Click OK to add the Topaz to the server role. The Server Role Properties dialog box looks like Figure 3.3
Figure 3.3 Adding Topaz to securityadmin server role
5. Click OK to save the changes
Another way to add a login to a server role is T-SQL, which leverages the sp_addsrvrolemember system stored procedure. The following code is added to the existing login name topaz to the sysadmin role:

EXEC ' Topaz ' ' sysadmin ';

Code 3.1 adding logins to server roles
You can find information about fixed server roles by running the sp_helpsrvrole and sp_helpsrvrolemember stored procedures. If you pass a valid server role name to Sp_helpsrvrole, it displays a description of the role, otherwise all server roles are displayed. Figure 3.4 shows how these two processes are used and how they are returned.
Figure 3.4 Using system stored procedures to obtain server role information
User-defined server roles
A long-awaited security feature in SQL Server 2012 is a user-defined server role. SQL Server has a database-level user-defined database role early on, but you can finally get granular and server-level permissions for custom server roles.
In older versions of SQL Server, certain permissions granted to users can be limited only by assigning them to the built-in fixed server role, which usually has too much permissions. Making each user a system administrator is a scary but common practice, a special problem because you can't restrict what the system administrator does. This violates the principal of least privilege in a big-to-a, but is often a practical necessity. After SQL Server 2005, this makes it all finer, allowing you to specify any specific server-level user rights, but missing the ability to place permissions in a server role.
SQL Server 2012 resolves this issue and supports user-defined server roles. Create a new server role by using creating server roles:

CREATE SERVER ROLE limiteddba;

Code 3.2 Creating a server role
You can grant and deny any desired server-level permissions. The following code grants control server permissions to the new role, and then denies partial permissions to narrow the permissions of the members of the server role. This is a very flexible way to grant user specific permissions.

 UseMaster;GO--Grant The role virtual sysadmin permissionsGRANTCONTROL SERVER tolimiteddba;--And take some permissions awayDENY ALTER  anyLOGIN tolimiteddba;DENY ALTER  anySERVER AUDIT tolimiteddba;DENY ALTER  anySERVER ROLE tolimiteddba;DENY CREATESERVER ROLE toLIMITEDDBA;--Covered by ALTER any SERVER ROLEDENYUNSAFE ASSEMBLY toLIMITEDDBA;

Code 3.3 granting/denying permissions to server roles
To test the role, code 3.4 creates a login that maps to the Windows group and adds a new login to the LIMITEDDBA role.

-- Create a login for DBAs Windows Group CREATE [marathon\dbas]  from WINDOWS; -- ADD to the server role ALTER ADD [marathon\dbas];

Code 3.4 Creating logins and adding to server roles
In code 3.5, you create a SQL Server Authentication login carol, and you do not have permission to assign any instances of SQL Server. Then, try a variety of actions in the carol security context that require server-level permissions: Create another login, view system information, and create another server role. All of these actions fail, as you can see in Figure 3.5, because the carol principal does not have any permissions to perform these operations.

--Create Carol LoginCREATELOGIN Carol withPASSWORD= 'crolpwd123%%%';EXECUTE  asLOGIN= 'Carol';--Verify User ContextPRINT suser_sname();--Can Carol alter logins?CREATELOGIN donkiely withPASSWORD= '[email protected] #fW ^mnya';--No--Other server-level permissions?SELECT *  fromSys.dm_exec_cached_plans;--no,requires VIEW USER StateCREATESERVER ROLE Carolrole;--NoREVERT;

Code 3.5 Create a login and test if it has special permissions

Figure 3.5 Execution failed because Carol has no permissions
The next code adds carol to the LIMITEDDBA user-defined server role and attempts to perform the same operation again. As shown in 3.6, Carol can now obtain system information (query operations) because the role grants control server permissions. However, Carol still cannot create logins or server roles because these permissions are denied by the LIMITEDDBA role display.

ALTERSERVER ROLE LIMITEDDBAADDMEMBER Carol;--Now does Carol has permissions?EXECUTE  asLOGIN= 'Carol';CREATELOGIN donkiely withPASSWORD= '[email protected] #fW ^mnya';--still not possibleSELECT *  fromSys.dm_exec_cached_plans;--Yes, CONTROL SERVER covers VIEW USER stateCREATESERVER ROLE Carolrole;--Not possibleREVERT;

Code 3.6 Try Carol again if you have special permissions
Figure 3.6 limiteddba role member execution results

In order to view server-level permissions that you can grant and deny to the server role, execute the following code. Figure 3.7 shows the results.

SELECT *  from sys.fn_builtin_permissions ('SERVER')  ORDER  by Permission_name;

Code Listing 3.7 View all available server-level rights

Figure 3.7 Partial list of server-level permissions
You can create user-defined server roles that grant special permissions based on user and group work requirements, which is more flexible than earlier versions of SQL Server, making security management easier in SQL Server 2012, and easier management necessarily means a more secure server.
fixed database role
Fixed database roles exist at the database level, not at the server level, and only control permissions in the database. Each database has its own set of fixed database roles, so you can configure the roles in each database individually. Fixed database roles are similar to fixed server roles, in which they cannot be deleted, modified, or changed, but can add database users and user-defined roles. Fixed database roles are:
->db_accessadmin: You can add or remove Windows logins and groups and SQL Server logins from the database.
->db_backupoperator: Can back up the database
->db_datareader: You can view data for all user tables in the database
->db_datawriter: You can add, modify, or delete data from all user tables in the database
->db_ddladmin: You can add, modify, or delete objects in the database. (DDL represents a data definition language, a set of T-SQL commands that make a database structure change)
->db_denydatareader: Unable to view any data in the database
->db_denydatawriter: Unable to change any data in the database
->db_owner: All database roles and maintenance and configuration activities can be performed. This role contains all the other roles, so it is basically the administrator of this database
->db_securityadmin: You can manage role members, statements, and object permissions in the database
Fixed database roles can simplify permissions assignment in a database. For example, suppose you want to allow only users to back up a particular database. You do not want users to be able to read data as long as the backup. You can easily accomplish this by making the user a member of the Db_backupoperator and db_denydatareader roles. Use the sp_helprole and sp_helprolemember system stored procedures to view information about database roles.
The public Role and Guest User
There are some special subjects that need to be reminded. You may not use these principals in any meaningful way, but they do affect security, so you need to know what they are.
The public role is a special server role that cannot be deleted. Each database user belongs to this public role, so you don't need to assign users, groups, or roles to it. Each SQL Server database contains the public roles, including master, msdb, tempdb, and model. However, you can grant or restrict permissions for the public role, as determined by your security needs.The important thing to remember is that the permissions you grant to the public role apply to all database users.
The guest user exists in each database, including the system database. As a user, it inherits the permissions of the public role. It is used when a login is not mapped to a user in the database. By default, the guest user does not have permissions, but you can grant access to the database objects and perform operations in the database. As you might expect, this is a very dangerous thing to do in a well designed database server that is rarely needed and you should avoid assigning permissions to guest users. Although you cannot delete the guest user, you should disable it in the user database.

 Use Northwind; GO REVOKE  from Guest; GO

Code 3.8 Disabling the guest user by reclaiming connection permissions
Note: Do not disable the guest user in the system database, which may cause problems that you do not want to deal with! These databases require the various functions of the guest user.
dbo User and schema
The dbo is a special user account that maps to the sysadmin fixed server role in each database. This means that if you are a member of the sysadmin role and you create any database objects, the owner of the object will be dbo, not you. You cannot delete the dbo user, it maps only to sysadmin, not to the database owner (db_owner).
Each database has a dbo schema and is the default schema for the dbo user. Therefore, when you access a database as a sysadmin and create an object without specifying a schema, its two-part name will be dbo.objectname. If no schema name is specified when other users access the data, the DBO schema is their second default schema. If the user Joe tries to access the table Sales,sql server will first check if there is a sales table for Joe's default schema, and if not, it will check for Dbo.sales. An error message is returned only if the sales table is not found in both architectures. A best practice is to specify a schema name for each object.
user-defined database roles
Database roles are not limited to pre-defined roles, and you can create your own roles. A user can define a database role of type:
-Standard role: Use this role to simplify permissions for user groups. You can nest fixed database roles or other user-defined roles and assign users to roles, in which case they inherit permissions from the role.
Application roles: Applications use this role to allow applications or connections to log on to the database and to activate application roles by providing a role name and password. You cannot add users to application roles as other roles add users, and once activated, the life cycle of application permissions exists during the connection. Any individual permissions of the user may be questioned until the application permissions are checked.
You can add a user-defined database role to a fixed database role, similar to adding a user to a fixed database role by pinning the Properties window of a database role.
Security Object
A securable object is a protected resource that you can access to control. Usually it is a physical thing, or at least a numerical object that can be physically. But a securable object can also be an operation that can make some changes to a database or DB instance. For example, an administrator can grant a principal the ability to have an object. Granting this permission does not immediately change the owner of the object, it just gives the subject the ability to do it in the future.
Figure 3.8 shows most of the securable objects in the instance of SQL Server. Server-level security objects have the broadest range of including permissions that affect a principal ' s ability to make changes to all databases. The database-level scope includes all objects in a particular database, such as those used to manage users and create encryption keys. The schema scope includes all objects within the schema, basically the data structure of the database, including the tables and their data. A database can contain multiple schemas, and each schema contains a subset of a complete set of database objects. The power of the schema feature is that you can specify and deny schema permissions, and these permissions apply to all objects that the schema contains.
Figure 3.8 Security objects in SQL Server
NOTE: Granting server-level permissions usually affects the small/low range of permissions. For example, granting database-level permissions might mean that principals have implicit permissions on objects in one or all of the database schemas.
Summary
This article learns about authorization, principals, and securable objects. In the next article you'll learn about permissions, giving or denying the subject the ability to do something to an object. By understanding, you can effectively leverage SQL Server's authentication and authorization granularity to tightly control database resources while allowing authorized users and processes to do their job.

Third SQL Server security principals and securable objects