Fourth SQL Server security permissions

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

The permission grants the principal access to the object to perform certain operations. SQL Server has a large number of permissions that you can grant to principals, and you can even deny or reclaim permissions. This may sound a bit complicated, but in this series, you'll know how SQL Server permissions work, and you can control object creation, data access, and other types of operations on database and server objects very finely.
Permissions
Permission like a visa allows you to visit a foreign country, usually with some basic conditions. For example, you only have a six-month deadline, and you are limited to 3/7 of the area to travel. Similarly, SQL Server permissions give the principal access to database objects to do something or perform operations. Permissions can allow a principal to read a table's data or a portion of a table, or to run a specific piece of code. Even allow the principal to grant permissions to other logins. You can grant hundreds of different permissions to different subjects.
When granting permissions, you follow the principle of least privilege. The least privilege means that you give a subject the authority to complete a task--no more and no less. Adhering to the principle of least privilege is an important step of database security. If the only thing the principal can do in the database is to read the product information, the principal should not be able to intentionally or unintentionally delete the contents of the table. Essentially creating a tight container restricts what the body can do.
Permission Types
There are many kinds of SQL Server permissions that you can grant to principals to control access to securable objects. The most common permissions are listed below:
Control: Gives all possible permissions to a securable object, making the principal the virtual owner of the securable object. This includes granting permissions to other principals for securable objects.
Create: gives the ability to create a particular object, depending on the scope it is granted. For example, the CREATE DATABASE permission allows principals to create new databases in an instance of SQL Server.
Change: Grants permission to change the properties of securable objects, in addition to changing the owner. This permission contains the same scope of the alter, create, drop object permissions (for example, the user has changed permissions on table A, then it can add/alter/drop columns, create/drop index constraints, etc.) for example, a database-level change permission, Includes change table and schema permissions.
Delete: Allows a principal to delete any or all data stored in the table. is a very dangerous privilege!
Impersonation < login > or simulation < user name;: Give the principal impersonate another login/user. Typically used to alter the execution context of a stored procedure.
Insert: Allow the principal to insert new records into the table
Select: Grant the principal to read data from a specific table. This is the most common permission that the user needs so that they can execute the query on the table.
Take over ownership: confers on a principal permission to takes ownership of an object. Granting this permission does not immediately transfer ownership. Instead, it allows the principal to take ownership at some future time.
Update: Allows the principal to update the data in the table.
View definition: Give the principal permission to view the definition of a securable object. This is an important permission because the structure information is very useful in database attacks. Without this privilege, an attacker may find that the ability of a database or server instance to have a value target is severely constrained.
Create and Change permissions can use the ANY keyword: Create any <object type> and ALTER any <object type> These permissions are granted to create or change any of the specified type securable objects. For example, granting the ALTER any schema at the database level allows you to change the properties of any schema in the database. At the server level, alter ANY login allows the principal to change any login name on the server. With the Any keyword, you have the flexibility to have the body create or modify an entire class of objects, rather than a single object. Just be aware that there are some subtle differences between creating and modifying permissions.
When you consider the number of SQL Server securable objects and an object can have the number of potential permission types, you begin to realize that granular permissions are available. You can apply the principle of least privilege to implement the set of permissions for any user or role by giving the user the appropriate permissions to complete a task without exposing other objects.
Tip: If you use an earlier version of SQL Server, you will realize that SQL Server 2005 and later versions have completely modified the available permissions. You no longer have to assign a user to a role that may have dozens of unnecessary permissions, thereby violating the least privilege and exposing the database to intentional or accidental abuse.
One of the main considerations here is that granting a license does not necessarily grant the ability to perform an operation effectively. Sometimes additional permissions are required to provide a comprehensive security context to perform sensitive operations. A very common example is to create TABLE permissions. It is quite possible to grant permissions, which theoretically allows a principal to create tables under a particular database. But the subject also needs to be able to create tables in one schema. If the principal does not have permission to change any schema, it will not be able to create a table.
Permission Statements
Even if you are assigning permissions through a graphical tool, the underlying or EXECUTE TSQL permission statement.
Grant: Granting permissions to a securable object or operation to the principal
REVOKE: "un-grant" permission to cancel an earlier grant statement. Revoked permissions can still be inherited by groups or roles that have permissions. When you create a new object, REVOKE is the default permission state, so specific permissions are not granted, but can be inherited.
Deny: denies revoking permissions so that it cannot be inherited. This is the most restrictive permission and takes precedence over all other permissions. Deny does not apply to members of the sysadmin role or to the owner of an object.
Do not underestimate the importance of deny permissions. For example, suppose you have a temporary employee who comes in to do data entry, and you don't want him to be able to edit or delete existing records. You have sufficient permissions on some tables that you have assigned to the Editor role (within the company). You can create a special login name, and then deny update and delete permissions on the appropriate table. Temporary employees can inherit sufficient permissions from the editor role to enter new records, but cannot modify/delete existing records.
granting Permissions
The most flexible way to grant permissions under management Studio is to modify the properties of a database user or role. You can also grant permissions by modifying the properties of individual objects, but this approach is inflexible and has a higher maintenance cost.
The following exercise creates a custom database role in the ADVENTUREWORKS2012 database. Members of this role need the necessary permissions to insert and update some HR-related tables, and to be able to execute related stored procedures, but with no other special permissions.
Tip: Once you have configured permissions for one user and then need to repeat the process for another user, grant the same permissions as the first user, assign the permissions to a role, and then simply add the user to the role.
You can take advantage of the second user topaz created under the AdventureWorks2012 database. If you do not create a user, execute code 4.1 to create the login and user.

 use   master;  go  create  LOGIN Topaz with  PASSWORD  '  

View Code

Code 4.1 Creating a Create name and mapping to a database user
To create a DataEntry custom database role in the AdventureWorks2012 database, refer to the following steps:
1. SSMs is connected to an instance that has a AdventureWorks2012 database installed. Database roles, database->adventureworks2012-> Security, Object Explorer
2. Right-click Database role, pop-up menu select New Database Role
3. Role name Type DataEntry, owner dbo
4. Click the Add button to add the user Topaz to the role (if the user does not exist, create it first). After you close the Select Database User or Role dialog box, the Database Role-New dialog box should look like 4.1

Figure 4.1 Creating a DataEntry database role and adding Topaz users
5. Click OK to save the changes and create the role
The current dataentry role does not allow members in the role to do anything because you have not assigned any permissions to the role. Members of the DataEntry role need to be able to insert and update data to table employee, Address, JobCandidate, They also need to execute uspupdateemployeehireinfo and uspupdateemployeepersonainfo stored procedures. However, they cannot view the definition of a stored procedure.
Use the following steps to add the appropriate permissions to the DataEntry role
1. Database role, Database->adventureworks2012-> Security, Object Explorer->dataentry
2. Right-click DataEntry, pop-up menu select Properties. Open Database Role Properties dialog box
3. Select the security object on the left, this page lets you select the securable that the role has permission to manipulate and specify the permissions on the securable object
4. Click the "Search" button to add the security object. This opens the Add Object dialog box, which provides options for a specific object, all objects of a particular type, and all objects that belong to the schema. In this example, because you want to add permissions for tables and stored procedures, keep the default selection in Figure 4.2-specific objects, and then click OK

Figure 4.2 Selecting the Add Object
5. Open the Select Objects dialog box. Click the Object Type button to open the Select Object Type dialog box, and then select stored Procedure and table from the list, as shown in 4.3. Click OK to close the dialog box and return to the Select Object dialog box, which now looks like Figure 4.4. You will see the stored procedures and tables listed in the Object type box

Figure 4.3 Selecting an object type

Figure 4.4 Selecting an Object
6. Click the Browse button to view a list of stored procedures and tables in the database. This opens the Find Objects dialog box and scrolls down to find and select objects.

Figure 4.5 Selecting a Stored Procedure and table
7. Click OK to close the Find Objects dialog box. At this point, the objects you select are separated by semicolons in the Select Objects dialog box, shown in 4.6. Click OK to close the dialog box and save your changes

Figure 4.6 Selecting Object Results
8. Now the DataEntry Database Role Properties dialog box lists the securable objects that you have selected, and lists the permissions that are available for each object. DataEntry role members need to be able to insert and update data into these tables, select the table individually, and tick the checkbox for the Insert/Update grant column at the bottom of the dialog box. Figure 4.7 shows the permissions for the HumanResources.Employee table

Figure 4.7 Granting Insert/UPDATE permissions on a table
Tip: Grant (Grant) allows the specified permission, with grant permission (with Grant) to allow the user or role to grant permissions to other principals. Be careful with GRANT permissions!
9. For each stored procedure, grant execute permission and deny View definition permissions. Figure 4.8 Shows the permissions for the useupdateemployeehireinfo stored procedure

Figure 4.8 Granting Execute procedure permissions, denying View definition permissions
10. Click OK in the Database Role Properties dialog box to save your changes and commit to the database. Depending on the number of objects and permissions selected, this may take some time.
Of course, you can use T-SQL code to create objects and assign permissions. Code 4.2 creates the DataEntry role, adds the Topaz user, and then assigns the same permissions as the graphical interface.

--Create The DataEntry role and assign Topaz to itCREATEROLE[DataEntry] AUTHORIZATION [dbo];ALTERROLE[DataEntry] ADDMEMBER[Topaz];--Assign permissions to the DataEntry roleGRANT INSERT  on [HumanResources].[Employee]  to [DataEntry];GRANT UPDATE  on [HumanResources].[Employee]  to [DataEntry];GRANT INSERT  on [HumanResources].[jobcandidate]  to [DataEntry];GRANT UPDATE  on [HumanResources].[jobcandidate]  to [DataEntry];GRANT INSERT  on [ Person].[Address]  to [DataEntry];GRANT UPDATE  on [ Person].[Address]  to [DataEntry];GRANT EXECUTE  on [HumanResources].[Uspupdateemployeehireinfo]  to [DataEntry];DENY VIEWDEFINITION on [HumanResources].[Uspupdateemployeehireinfo]  to [DataEntry];GRANT EXECUTE  on [HumanResources].[Uspupdateemployeepersonalinfo]  to [DataEntry]DENY VIEWDEFINITION on [HumanResources].[Uspupdateemployeepersonalinfo]  to [DataEntry];GO

Code 4.2 statement Create a role and assign permissions
Check and test permissions
If you want to check the permissions of the DataEntry role, you can use the GUI tool or execute T-SQL code to access the metadata of the database object. Using the GUI, Object Explorer, Database->adventureworks2012-> security, role-and database role->dataentry-> properties. This opens and creates the same database Role Properties dialog box as the role, and you can use the securable object to review the permissions of the role.
Or you can use the T-SQL in code 4.3 to view the permissions of the DataEntry role, leveraging the sys.database_permissions and sys.database_principals secure catalog views and sys.objects catalog views.

SELECT db_name() as 'Database', P.name, P.type_desc, Dbp.state_desc, Dbp.permission_name, So.name, So.type_desc fromsys.database_permissions DBP Left JOINsys.objects so ondbp.major_id=So.object_id      Left JOINSys.database_principals p ondbp.grantee_principal_id=p.principal_idWHEREP.name= 'DataEntry'ORDER  bySo.name, Dbp.permission_name;

Code listing 4.3 Viewing permissions for the DataEntry role
When you execute the above code, you will see the result 4.9 shown

Figure 4.9 Permissions for dataentry roles
You can test these permissions with the Topaz login by opening a new query, and then changing the connection. Then try to insert a new row in the HumanResources.Employee, humanresources.jobcandidate, person.address table, or update existing data, and execute stored procedures that assign permissions. These operations should succeed. Then try to insert or update data from other tables, and these actions should fail. You should only be able to perform the permissions that have been granted.
You can perform the same test in code 4.4. The execution context for the code start setting is a member of the Topaz,dataentry role. Then insert a new line into the Person.Address table. This operation succeeds because the role allows data to be inserted in the Person.Address table. Then the code tries to insert a new row into the HumanResources.Department table, and the insert fails because the role does not have INSERT permissions on this table. The following code executes the Humanresources.uspupdateemployeepersonalinfo stored procedure successfully, and execution dbo.uspgetmanageremployees fails because the role does not have this procedure execution permission. Finally, the code cuts the execution context back to the security context at logon.

EXECUTE  as USER = 'Topaz';--succeedsINSERT  intoperson.address (AddressLine1, City, StateProvinceID, PostalCode)VALUES    ('8 Hazelnut','Irvine',9,'92602');GO--failsINSERT  intohumanresources.department (Name, GroupName)VALUES    ('Advertising','Sales and Marketing');GO--succeeds (doesn ' t actually change to any data)DECLARE @RC INT;EXECUTE @RC =Humanresources.uspupdateemployeepersonalinfo1,'295847284','1963-03-02','S','M';GO--failsEXECUTEDbo.uspgetmanageremployees1;GOREVERT;

Code 4.4 Testing permissions for dataentry roles
Summarize
The ability to assign granular permissions in securable objects throughout SQL Server to various types of principals gives You very fine control over the security of a SQL Server instance. It allows you to take full advantage of the least privileged safety principles to limit the ability of principals to perform operations and access data to the minimum they need.

Fourth SQL Server security permissions