Chapter 3 Protecting the Data (3): Creating and Using Database roles

Original source: http://blog.csdn.net/dba_huangzj/article/details/39639365, featured catalogue:http://blog.csdn.net/dba_huangzj/article/details/37906349

No person shall, without the consent of the author, be published in the form of "original" or used for commercial purposes, and I am not responsible for any legal liability.

Previous article: http://blog.csdn.net/dba_huangzj/article/details/39577861

Objective:

Database-level roles allow database permissions to be managed as a combination of server-level roles. You can configure fixed server roles, create user custom roles, and so on.

Realize:

1. In SSMs, go to the node of a database, click "Security", then click on the "Database Roles" node:

Fixed role	Describe
Db_accessadmin	Members of the db_accessadmin fixed database role can add or remove database access permissions for Windows logins, Windows groups, and SQL Server logins.
Db_backupoperator	Members of the Db_backupoperator fixed database role can back up the database.
Db_datareader	Members of the db_datareader fixed database role can read all data from all user tables.
Db_datawriter	Members of the Db_datawriter fixed database role can add, delete, or change data in all user tables.
Db_denydatareader	Members of the Db_denydatareader fixed database role cannot read any data from user tables in the database.
Db_denydatawriter	Members of the Db_denydatawriter fixed database role cannot add, modify, or delete any data from user tables in the database.
db_owner	Members of the db_owner fixed database role can perform all configuration and maintenance activities for the database, and can also delete the database.
Db_securityadmin	Members of the db_securityadmin fixed database role can modify role memberships and administrative permissions.
db_ddladmin	Members of the db_ddladmin fixed database role can run any data definition language (DDL) commands in the database.

2. If you add a member to a role, you can double-click the role and on the Properties page, in members of this role, add:

3. You can also use T-SQL implementations:

ALTER ROLE db_accessadmin ADD MEMBER test;

4. If you need to create a custom role, you can right-click the Database Roles node and select New Database role, enter the role name and owner to control all permissions for the role, and suggest using dbo or leave blank.

5. Do not confuse the GUI interface, on the "General" page, there are two listboxes, the first is "This role has a schema", listing the schema of this database, described in the following.

6. Now forget the first ListBox, and to the second ListBox, "members of this role", you can add role members.

7. Then go to the securable page, where you can select a securable object, or you can implement it in T-sql:

Use marketing; CREATE ROLE Prospectmanager; ALTER ROLE  prospectmanager ADD MEMBER Fred; GRANT INSERT, UPDATE, DELETE, SELECT on dbo. Prospect to Prospectmanager; GRANT INSERT, SELECT on dbo. Contact to Prospectmanager;

Principle:

Fixed roles allow you to grant global permissions to all objects in the database, most of the time, without using Db_datareader/db_datawriter. Because there are too few permissions, and Db_denydatareader/db_denydatawriter is useful, you can restrict users from accessing tables and views.

All users are members of the public database role and cannot be removed, and members of the db_owner role appear as dbo users in the database.

You can use the following statement to see if a login belongs to a member of a database role:

SELECT is_rolemember (' Prospectmanager ', ' Fred ');

Returning 1 means Fred is a member of the Prospectmanager role.

More:

If you use with GRANT option to authorize to a Windows group, the members of this role choose to use the AS keyword when authorizing.

Use marketing; CREATE USER Fred without LOGIN; CREATE USER Mary without LOGIN; GO CREATE ROLE contactreaders; ALTER ROLE contactreaders ADD MEMBER Fred; GO GRANT SELECT on Dbo.contact to contactreaders with GRANT OPTION; GO EXECUTE as USER = ' Fred '; --This does does work GRANT SELECT on Dbo.contact to Mary; --This works GRANT SELECT on Dbo.contact to Mary as Contactreaders; REVERT;

When Fred uses the grant command, the as contactreaders must be used.

msdb role:

msdb because of its importance, the number and function of roles are different from the regular libraries:

Role	Describe
Db_ssisadmin	You can manage SSIS on the server as an administrator.
Db_ssisoperator	You can view all SSIS packages, but you cannot import or modify them.
Db_ssisltduser	You can view and execute the SSIS package that belongs to it.
Dc_admin	You can manage the Data collector collection set and attributes, with all the Dc_operator permissions.
Dc_operator	The DC collection set and properties can be read and updated.
Dc_proxy	The DC collection sets and corresponding properties can be read.
Policyadministratorrole	You can perform all configuration and maintenance operations on policy-based management.
Servergroupadministratorrole	You can register and use a central Management Server group.
Servergroupreaderrole	You can connect to a central Management Server group.
Dbm_monitor	Allows monitoring of database mirroring.

For SSIS roles and Data Collector security, you can view the following articles:

• Integration Services roles:http://msdn.microsoft.com/en-us/library/ms141053.aspx
• Data Collector security:http://msdn.microsoft.com/en-us/library/bb630341.aspx

Next article:

Chapter 3 Protecting the Data (3): Creating and Using Database roles