Fifth SQL Server security architecture and security

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

A schema is essentially a database object, a container for other objects that can easily manage groups of objects in a complex database. The architecture has important security features. In this article you will learn how to give principals access to a set of objects by assigning permissions to the schema instead of individual tables, code modules, and other objects. You'll also learn about the benefits of user-schema separation, how to improve object security, and how to use the default schema for users and groups to simplify object access management and security.
Schemas with Roles and Permissions
The relationship of schemas with roles and permissions are an important security concept in SQL Server. A fully qualified database object name consists of four parts ：

Server. database. schema. Object

Typically, the schema and object names are used to refer to objects under the current database. A schema is a collection of objects, as shown in table, code module, 5.1. This organizational structure simplifies user management, especially when you need to change the ownership of an object. But more importantly, for security, it simplifies rights management.

Figure 5.1 A sample schema that contains database objects
You can assign permissions to all objects under the schema that are applied to the schema. For example, if you grant the SELECT permission of the Dogschema schema to a principal, the principal will be able to query all tables under the Dogschema schema. As with all user-defined database objects, the schema has an owner that can take full control of the object.
Setting object permissions separately in the schema is often an option, but if you have already designed the schemas in your database, you can set permissions on the schema and apply them to objects in a database of some feature types. Best of all, the permissions you assign on the schema are automatically applied to any objects that you add to the schema. To continue the select example, if you add a table DogTable4 to schema Dogschema a year later, all principals that have SELECT permission on the schema can automatically select the new table.
Multiple users and roles can have the same default schema, and if the principal does not have a default schema, SQL Server tries to find or create objects under the DBO schema.
Now you'll see how to use schemas to assign permissions to objects. Use the following steps to grant the query, update, delete, and insert permissions of the purchasing schema to the DataEntry user custom role. The DataEntry role was created in the 4th chapter; If you did not create it, execute code 5.1

 Use AdventureWorks2012; GO CREATE AUTHORIZATION dbo;

Code 5.1 Creating the DataEntry role in the ADVENTUREWORKS2012 database
Then follow the steps below to use the graphical tools in SSMs to assign the required permissions
1. Database role, Database->adventureworks2012-> Security, Object Explorer->dataentry
2. Right-click DataEntry, pop-up menu select Properties. In the Database Role Properties dialog box, select the securable object. If you follow the steps in 4th, you should be able to see the tables and stored procedures that previously assigned permissions to the role
3. Click "Search" button to open the "Add Object" dialog box
4. In the Add Object dialog box, select the "All objects of a specific type" option, as shown in 5.2. Click OK to open the Select Object Type dialog box

Figure 5.2 Adding an object
5. In the Select Object Type dialog box, scroll down to the schema project and select the check box next to it. The dialog box should look like Figure 5.3. Click OK to save the selection and close the dialog box

Figure 5.3 Selecting an object type
6. Return to the Database Role Properties dialog box, scroll down the list of securable objects and click Purchasing Schema. The lower part of the page shows the available permissions
7. Display tab as purchasing schema tick the Delete, insert, select, Update Grant check box. The Database Role Properties dialog box is shown in 5.4

Figure 5.4 Setting access rights to the purchasing schema
All members under this dataentry role have SELECT, Update, delete, and insert permissions for all tables under the ADVENTUREWORKS2012 Library purchasing schema. An exception occurs only if the member of the role is denied any permissions. Deny blocks the permissions they inherit through the role.
Of course, you can also use a TSQL script to grant permissions on the schema to the role

GRANT DELETE  on SCHEMA::P urchasing toDataEntry;GRANT INSERT  on SCHEMA::P urchasing toDataEntry;GRANT SELECT  on SCHEMA::P urchasing toDataEntry;GRANT UPDATE  on SCHEMA::P urchasing toDataEntry;GO

Code 5.2 authorizing the purchasing architecture
These techniques show that you can create different schemas, place different objects in each schema, and then assign permissions on the schema. This saves the task of assigning permissions on a single table. If you grant permissions to a role, as we do with the dataentry role, you can effectively assign permissions to many principals. This allows you to split the database, simplify your design, and implement the security of your database.
Default Schema
In the SQL-99 specification definition, a schema is essentially an object container for a database. It can be owned by a principal, shown in 5.5 (same as Figure 5.1). One of the benefits of using schemas as a Database object container is that when Carol leaves the company, it does not need to change the ownership of the hundreds of or thousands of objects owned by Carol, and administrators only need to change ownership of those schemas, each of which may have thousands of objects. This approach is more concise, easier, and more secure.

Figure 5.5 Carol-owned Dogschema architecture
SQL Server allows you to assign a default schema to users and groups. Setting the default schema is convenient and has some important operational benefits. In particular, it eliminates some ambiguity when naming and accessing objects.
User Default Schema
SQL Server does not automatically create a schema with the same name when you create a user. You must explicitly create a schema, assign the schema owner, and then create and add objects to the schema. You can (and usually should) assign a default schema to the user so that all user-created objects, if not explicitly assigned to another schema, become part of the default schema.
The code in this article shows all the actions that occur when the user does not have a default schema set. I'll explain what happens at each step, but you might want to execute the code on your own to better understand what's going on. Sure, you try it yourself! If you want to see what happens, execute each block of code.
Code 5.3 shows some of the settings required for the demo. You may have created the third log-in name Carol, so if there is a login name Carol first delete it. Create the Defaultschema database and set it as the current database, then create the login carol, map to the database user, and grant it permission to create the table. Then, it changes the execution context to user Carol.

IF Suser_sid('Carol') is  not NULL DROPLOGIN Carol;GOCREATE DATABASEDefaultschema;GO UseDefaultschema;GOCREATELOGIN Carol withPASSWORD= 'crolpwd123%%%';CREATE USERCarol forLOGIN Carol;GRANT CREATE TABLE  toCarol--UserEXECUTE  asLOGIN= 'Carol';GO

Code 5.3 Creating a Defaultschema database and setting up Carol users
The next code tries to create a new table, Table1, as shown in code 5.4. But the previous code created the carol user and did not assign it a default schema. SQL Server tries to use the DBO schema, which is the default fallback schema. However, Carol does not have ownership of the database, so it cannot create objects in the DBO schema.

CREATE TABLE int);

Code 5.4 Attempts to create a table in the carol context
Because Carol does not have the necessary permissions, the CREATE TABLE statement fails and returns an error message.
The specified schema name "dbo" does not exist, or you do not have permission to use the name.
Code 5.5REVERT Log in to the original administrator, then create a schema and set the schema owner as the carol user. You will see many authorization clauses in SQL Server because it allows you to assign ownership when you create or change an object.

REVERT; CREATE SCHEMA AUTHORIZATION Carol;

Code 5.5 Creating the Dogschema schema
Change the execution context again to Carol, and then try to create the table table1 again, but it fails! The problem now is that a user owning a schema does not mean that it is the user's default schema. A user can have hundreds or thousands of schemas, and SQL Server has no responsibility to pick one as the default schema. Finally, the inclusion schema that is displayed when the table is created can pass. The table is explicitly created in the Dogschema schema in code 5.6

EXECUTE  as = ' Carol ' ; GO CREATE TABLE int);

Code 5.6 Creating a table with a display schema
If the Dogschema schema exists, the second way to try to create the table is to assign the default schema when the user is created, or to modify the user default schema later. As shown in code 5.7

CREATE USER  for  with = Dogschema; -- or ALTER USER  with = Dogschema; -- the test can be executed under Carol identity

Code 5.7 Setting default schema for user Carol
If you execute the ALTER USER statement to set the default schema for Carol, then you can execute code 5.8 to successfully create the table without specifying the schema. The statement that creates the table creates a Dogschema.table2 table because Dogschema is the default schema for Carol.

EXECUTE  as = ' Carol ' ; GO CREATE TABLE int ); GO SELECT *  from table2; REVERT;

Code 5.8 Creating a table table2 you do not need to specify a schema
Another interesting phenomenon is that when you use the revert statement to return to your own security context, you cannot execute code 5.9. Unless you set your own default schema to Dogschema, SQL Server will look for Dbo.table2

SELECT *  from Table2;

Code 5.9 Error running outside the carol security context
You need to explicitly use the schema to identify the table from which you want to read data, such as code 5.10. This code succeeds and returns the contents of the Dogschema.table2.

SELECT *  from Dogschema.table2;

Code 5.10 Specifying the schema execution SELECT statement
SQL Server user and schema separation can tightly control the security structure of your databases and applications. This makes it easier to manage a database and SQL Server. You do not need to set the dbo user owner for each object, which is common in versions prior to 2005.
Group Default Schema
The user's default schema, introduced in SQL Server 2005, resolves queries, create objects, and other operations to use the correct objects under the correct schema. But there is a problem with these default schemas, and you can easily create a large number of schemas in the database. The default schema for Windows groups, introduced in SQL Server 2012, resolves these issues.
Use the following steps to explore potential problems with the user's default schema. The procedure assumes that local Windows has a DBAs group, and that the Clearfile user is a member of the DBAs group. You need to change the machine name in the sample code. Finally, the Defaultschema database should already exist.
1, modify the current database is Defaultschema, code 5.11 First create a Windows group login, and then create a dataadmins user mapping to this login, and then create DBAs role and add dataadmins users into the role.

 use   Defaultschema  go  create  LOGIN [ user-67np5r8lgk\dbas   from  Span style= "color: #000000;" > WINDOWS;  create  user  Dataadmins from  LOGIN [ user-67np5r8lgk\dbas    create   ROLE DBAs;  alter  ROLE DBAs add  MEMBER dataadmins; 

Code 5.11 Creating logins, users, roles
2. Grant the CREATE table and control permissions to the Dogschema schema to the DBAs role

GRANT CREATE TABLE  to DBAs; GRANT  on SCHEMA  to DBAs;

Code 5.12 granting permissions to the DBAs role
3. Run another ssms with Clearfile identity. In the Windows Startup menu, hold down the SHIFT key, and right-click SSMs. In the pop-up menu, choose Run as a different user, type Clearfile username and password
4. In the Connect to Server dialog box using Windows Authentication, the user name is shown in clearfile,5.6. Click Connect to run SSMs as Clearfile

Figure 5.6 Logging into SSMs as Clearfile
5. Create a new query in the newly opened SSMS and switch to the Defaultschema database with the available database
6, execute code 5.13, successfully created the table table1, but what is its schema?

CREATE TABLE int)

Code 5.13 Creating a table with Clearfile table1
7. Defaultschema Database expands table, user, schema under Object Explorer. As shown in 5.7, the table created by the preceding statement is called User-67np5r8lgk\clearfile.table1, database user user-67np5r8lgk\clearfile, schema user-67np5r8lgk\clearfile

Figure 5.7 Results of table creation when there is no default schema
8. Back to SSMs you logged in as Administrator, in order to delete the table, schema, and user you just created under Object Explorer
9, or in the original SSMs, execute code 5.14 to the user Dataadmins set the default schema Dogschema

ALTER USER  with = Dogschema;

Code 5.14 to identify the default schema for Dataadmins users
10. Return to Clearfile to log in to SSMs and execute code 5.15 to create a new table Table3. This time the code creates a table called Dogschema.table3, and does not add Clearfile database users, nor does it add a Clearfile schema

CREATE TABLE int)

Code 5.15 Creating a table Table3
You can also set the default schema in the Database User dialog box, as shown in 5.8

Figure 5.8 Setting the default schema using the Database user dialog box
SQL Server 2012 adds the ability to set a default schema for a group, similar to setting a default schema for a user to solve problems, making security management easier. For the same reason, you create users who do not have any permissions, and then add them to the required permission group, and you can specify the default schema for the group instead of each user. As with users, you can specify a default schema by using the CREATE user or alter user.
Summarize
Schemas are a good feature on SQL Server Management database objects and provide important security features. You can more easily manage permissions on a database by setting permissions on one schema instead of the individual objects it contains. This is especially important when you have a lot of subjects that need to be granted the right limit.
Be sure to assign a default schema to users and groups, which avoids accidental object creation while simplifying code and database maintenance. With the ability to set default schemas for groups, Microsoft has enriched the architecture security benefits.

Fifth SQL Server security architecture and security