SQL Server Security (5/11): Architecture and Security (Schemas)

Keep your servers and data confidential, and SQL Server has everything you need to prepare for the current complex attacks. But before you can effectively use these security features, you need to understand the threats you face and some basic security concepts. This article provides the basics, so you can take advantage of the security features in SQL Server without wasting time on features that don't protect your data against specific threats.

The schema is essentially another database object, which makes it easier to manage object groups in a complex database for containers of other objects. However, the architecture also has important security features. In this article you will learn how to access a group of objects to a principal, by assigning permissions on the schema, rather than individual tables, code modules (stored procedures), and other objects. You'll also learn about the benefits of user architecture, how it improves object security, and how to use the default schema for users and groups to simplify object access management and security.

architecture with roles and permissions

In SQL Sever, the relationship between architecture and roles and permissions is an important security concept. The full database object name contains four parts:

Server name. Database name. Schema name. Object Name (Server.database.schema.object)

Typically you need to use schema and object names to refer to objects in the current database context. Schemas are collections of objects, such as tables and code modules, as illustrated in Figure 5.1. This organizational structure simplifies user management, especially if you need to modify the owner of an object. But more important for security, it simplifies license management.

Illustration 5.1: Contains database objects and schema samples owned by the user

You can assign permissions to all objects in the schema that are applied to the schemas. For example, if you assign a select license to a principal in Dogscheme, all tables in that schema will have that permission. Like all user-defined database objects, the schema has an owner that can be fully controlled on the object.

It is also possible to set permissions on the objects in the schema, but if you have a schema in the database, and in some functional category, it makes more sense for the database, you can set permissions on the schema to apply to dozens of or even hundreds of objects. The biggest benefit is that the licenses assigned on the schema are automatically applied to the objects added to the schema later. Continuing with the Select example, if you add DogTable4 to Dogscheme after 1 years, all principals that have a select license on the schema will automatically have a select license in the new table.

Multiple users or roles can have the same default schema, and if a principal does not have a default schema set, SQL sever looks for or creates objects in the DBO schema.

Now you'll see how you can use schemas to assign permissions to objects. Use the following procedure to grant Select,update,delete and insert licenses to AdventureWorks2012 on the Purchasing schema The DataEntry user custom database role in the database. This role was created in the 4th article, and if you did not create it, execute the following code in SSMS:

1  Use AdventureWorks2012; 2 GO 3 CREATE AUTHORIZATION dbo;

Code Listing 5.1: Create the DataEntry role in the AdventureWorks2012 database.

The following steps use graphical tools in SSMs to assign the required licenses.

1. For the AdventureWorks2012 database, expand the Security node in the Object Browser. Expand Roles, Database role, DataEntry.
2. Right-click the "DataEntry" character and select "Properties" from the popup menu. In the Database Role Properties dialog box, select the securable object page.
   If you follow the steps in the 4th chapter, you should see the table and stored procedure permissions assigned to the role at that time.
3. Click the "Search" button to open the "Add Objects" dialog box.
4. In the Add Object dialog box, select the all objects of a specific type option, as shown in Figure 5.2, and click OK to open the Select Object Type dialog box.
   
   Fig. 5.2: Select all objects of a specific type
5. In the Select Object Type dialog box, swipe down to the schema item and select the Previous check box. The dialog box should look like Figure 5.3. Click "OK" to save the selection and close the dialog box.
   
   Fig. 5.3: Select the Schema object type
6. Go back to the Database Role Properties dialog box, swipe down on the list of securable objects at the top of the page, and click Purchasing schema. Available licenses are displayed in the lower part of the page.
7. For the Purchasing section of the form, on the explicit page, in the grant column of the license, select Insert, UPDATE, delete, and select License. The Database Role Properties dialog box is shown in illustration 5.4.
   
   Figure 5.4: Setting table access permissions for the purchasing schema
8. Click "OK" to submit your changes.

Now all members of the DataEntry role have select,update,delete and insert permissions on the table on the Purchasing schema of the AdventureWorks2012 database. Only when the individual members of the character reject any permission will the error be made. This prevents them from being denied permission to inherit membership in the role.

Alternatively, you can use code 5.2来 to grant schemas to these licenses.

1 GRANT DELETE  on SCHEMA::P urchasing toDataEntry;2 GRANT INSERT  on SCHEMA::P urchasing toDataEntry;3 GRANT SELECT  on SCHEMA::P urchasing toDataEntry;4 GRANT UPDATE  on SCHEMA::P urchasing toDataEntry;5 GO

Code Listing 5.2: Adding Delete,insert,select and update licenses to the purchasing schema code.

These techniques show that you can create different schemas, put different objects in each schema, and then assign licenses on the schema. This saves all the work that is assigned to a license on each table. If you assign a license to a role, here we can use the DataEntry role, where you can efficiently assign licenses to many licenses-all members of the role-once done. This allows you to divide your database, like in AdventureWorks2012 , by department, simplifying any design and database security.

Default Schema

As defined by the SQL-99 specification, the schema is essentially a container for objects in the database. Then it may belong to a body in turn, as shown in Figure 5.5. One benefit of using schemas as containers for database objects is that when Woodytu leaves the company, the administrator does not have to modify the owners of hundreds or thousands of objects owned by Woodytu, and as long as the owners of those schemas are modified, each schema will have thousands of objects. This method is more concise, easier, and safer.

Illustration 5.5:woodytu with Treeschema architecture

SQL Server lets you assign a default schema to users and groups. The ability to set the default schema is convenient and has some important business benefits. In fact, when the object is named and accessed, it removes the differences.

User Default Schema

When you create a user, SQL Server does not automatically create a schema name that is the same as the user's name. Instead, you need to create a schema, assign membership to the user, and then create and add objects to the schema. You can (usually) assign a default schema to the user, so that all objects created by the user--not assigned to another schema--become part of the default schema.

The code in this article shows how it all happens and shows what happens when the user defaults to the schema. Here I'll explain what happens at each step, but you'd better do it with me so that you can see what's going on more clearly.

Code 5.3 makes some configuration necessary for the demo. You may have created the carol login in the 3rd article, so if it exists , the code is first removed and a different license is started. After creating the Defaultschema database, modify the database context for it, the code creates the carol login, maps it to the login carol in the database, and grants it the ability to create the database. Then run the context to the new user Carol.

1 IF Suser_sid('Carol') is  not NULL DROPLOGIN Carol;2 GO3 CREATE DATABASEDefaultschema;4 GO5  UseDefaultschema;6 GO7 8 CREATELOGIN Carol withPASSWORD= 'crolpwd123%%%';9 Ten CREATE USERCarol forLOGIN Carol; One GRANT CREATE TABLE  toCarol; A  - EXECUTE  asLOGIN= 'Carol'; - GO

Code Listing 5.5: Creating a Defaultschema database and setting code for user Carol

Tips:

In the 6th chapter you will learn more about the execution context and the EXECUTE AS statement. Now, you just need to know that this feature provides a way to execute statements using the security context (the user and the license of the login bundle that you specify). So after you execute the EXECUTE AS statement in Code 5.3, the next statement is executed with Carol's permission until you revoke your own permission with the revert statement.

The following code 5.4 creates a new Table1 table. However, the default schema was not assigned when the code was created Carol. SQL Server tries to use the DBO schema, which is the default fallback schema. However, Carol does not have membership permissions in the database, so she cannot create objects in the DBO schema.

1 CREATE TABLE int);

Code Listing 5.4: Explaining the creation of the Table1 table in the Carol runtime context

Because Carol does not have the required CREATE TABLE statement permission, this execution will fail with the following error message.

Msg 2760, Level 16, State 1, line 1th

The specified schema name "dbo" does not exist, or you do not have permission to use the name.

code 5.5 Returns the administrator login that originally started the session, and then creates a schema and Carol the membership to the user. You'll see a lot of authorization in SQL Server because it lets you assign membership to the same statements that create or modify objects.

1 REVERT; 2 CREATE SCHEMA AUTHORIZATION Carol;

Code Listing 5.5: Creating code for Dogschema schemas that belong to Carol

The code then modifies the execution context again to Carol and tries to create the Table1 again. But once again it failed.

1 CREATE TABLE int);

The problem now is that the user-owned schema does not imply that it is the user's default schema. Users can have hundreds of schemas, but SQL Server does not take one as the default. But what happens when you create a table in the schema. The statement in code 5.6 creates a table1 in the dogschema schema, and finally it succeeds.

1 CREATE TABLE int);

Code Listing 5.6: Creating a table for the specified schema.

Execute successfully!

The second attempt to create the table, once Dogschema exists, executes successfully. The code therefore assigns the default schema when the user is first created, or modifies it later, as shown in code 5.7.

1 CREATE USER  for  with = Dogschema; 2 -- or 3 ALTER USER  with = Dogschema;

Code Listing 5.7: When creating a user, set the default schema for carol or modify its code later

If you run the ALTER USER statement to set the default schema for Carol, you can execute code 5.8 successfully, and you do not need to specify a schema to create the Table2 table. The CREATE TABLE statement creates a Dogschema. table2 table. Because Dogschema is the default schema for Carol, it is the schema that SQL Server uses.

 1  execute  as  LOGIN =   "  Carol   "  2  3  create  table  table2 (TID int  );  4  5  select  *    table2;  6  REVERT; 

Code Listing 5.8: After you set the default schema for Carol, do not specify a schema to create the Table2 table.

Another interesting thing is that after withdrawing your own security context-using the revert statement-you can't run code 5.9. Unless you set your default schema to be Dogschema, SQL will look for the Dbo.table2 table, which does not exist.

1 SELECT *  from Table2;

Code Listing 5.9: This code causes an error when it is not running in the execution context of the carol

correctly, you need to use the schema to identify the source of the table for the data you want to read, as shown in code 5.10. This code succeeds and returns the contents of the dogschema. Table2 (now still empty).

1 SELECT *  from Dogschema.table2;

Code Listing 5.10: Select operation when user default schema is not Dogschema

The separation of users and schemas in SQL Server or you can maintain complete control over your security architecture for your databases and applications. It makes it easier to manage databases and SQL Server. You should not let dbo have everything, this is common practice before SQL Server 2005.

Group Default Schema

The default schema introduced for users in SQL Server 2005 addresses the problem of hierarchical determinism, using the correct objects in the correct schema when querying, creating objects, and other operations. But the corresponding side effect of these default schemas is that you can easily leave a lot of implicitly created schemas in the database. The default schema for Windows groups introduced in SQL Server 2012 addresses these issues.

Follow the steps to explore potential issues for the user's default schema only. These steps assume that the login for the DBAs group and woodytu in your windows on-premises instance is a member of this group. In the code you also want to modify the WIN10 for your local machine name. Finally, theDefaultschema database should already exist, and if not, you can refer to the previous code to create it.

You can follow these steps to practice. The steps are a bit tricky because you'll be practicing in 2 ssms instances. Execute each code snippet as a whole.

1. Modify the database context toDefaultschemaAfter that, execute code 5.11. It creates a login that maps to the DBAs group in Windows and a user maps to this login, and then creates the DBAs role to add dataadmins users to it.
   

   [Win10\dbasfrom WINDOWS;  [Win10\dbas];  CREATE ROLE DBAs;  ADD MEMBER dataadmins;         

   Code Listing 5.11: Creating required logins, users, and roles
3. The next code, 5.12, grants the CREATE table and control permission to the DBAs role on the dogschema schema.
   

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

   Code Listing 5.12: Granting permissions to the DBAs role

5. Now open another instance of SSMs to Woodytu user login (Woodytu belongs to the DBAs group). Hold down the SHIFT key and right-click SSMs in the Start menu. Select "Run as a different user" from the pop-up menu and enter the Woodytu credentials in the dialog box that pops up.
7. In SSMs Rilian the dialog box to the server, use Windows Authentication to Woodytu login, as shown in Figure 5.6. Click "Connect" and now you run SSMs with Woodytu.
   
   Figure 5.6: Logging in to SQL Server with Woodytu
9. Switch the database context to Defaultschema.
11. Execute the following code 5.13, which will successfully create the Table1 table, but what is its schema?
    

    1 CREATE TABLE int)

    Code Listing 5.13: Executing the CREATE TABLE statement with Woodytu

13. In the Object Browser, in the defaultschema database, expand the user and schema three nodes under tables, security. As illustrated in Figure 5.7, the CREATE TABLE statement creates a win10\woodytu.table1 table that win10\woodytu the database user. Creating a table in the schema brings too much fragmentation, not what we want.
    
    Figure 7: Results of a table not created with the default schema
15. Return to the same instance of SSMs that the administrator logged in to. Delete the table, schema and user that you just said from the Object Browser, in this order.
17. Or just the SSMs instance, run code 5.14来 to set the default schema for dataadmins users, using the Dogschemayou just created.
    

    1 ALTER USER  with = Dogschema;

    Code Listing 5.14: Setting the default schema code for the Dataadmins role

19. Go back to Woodytu's SSMs instance and run code 5.15来 create a new table again. This time this code is created named Dogschema. Table3, Woodytu does not create a user and schema with its own name.
    

    1 CREATE TABLE int)

    Code Listing 5.15: Creating the Table3, now in the Dogschema architecture.

You can also set the user's default schema in the "Properties" of the Dataadmins in the "security" user, as shown in Figure 5.8, in the "Database User" dialog box.

Illustration 5.8: Use the Database user dialog box to set the default schema for the user.

SQL Server 2012 adds the ability to define default schemas for groups and users, which provides a great help in resolving default schema issues and makes administration easier. For the same reason, you create non-licensed users, add them to a group that has the permissions they need, and you can specify a default schema for the group to apply to its members instead of for each user. You can use the CREATE user or ALTER USER statement to specify the default schema for the group.

Summary

Architecture is a good feature of SQL Server that puts database objects in a convenient container for management, and it also provides important security features. By setting a license on the schema instead of the objects it contains, you can more easily manage database schema licensing. This is especially important when you have a large number of principals that need to be granted permission.

Always assign schemas to users and groups at the same time, so you can avoid unnecessary object creation and simplify code and database maintenance at the same time. With the ability to set a default schema for a group, Microsoft has the benefit of a security-enriched architecture.

Original link:

http://www.sqlservercentral.com/articles/119183/

SQL Server Security (5/11): Architecture and Security (Schemas)