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

原文出处：http://blog.csdn.net/dba_huangzj/article/details/39639365，专题目录：http://blog.csdn.net/dba_huangzj/article/details/37906349

No person shall, without the consent of the author, publish in the form of "original" or be used for commercial purposes without any liability.

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

Preface:

Database-level roles allow database permissions to be grouped together like server-level roles. You can configure fixed server roles, create user-defined roles, and more.

implementation:

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

Fixed role	description
db_accessadmin	db_accessadmin A member of a fixed database role can Add or remove database access permissions for Windows logins, Windows groups, and SQL Server logins. A member of the
db_backupoperator	db_backupoperator fixed database role can back up the database. The members of the
db_datareader	db_datareader fixed database role can read all the data from all user tables. The members of the
db_datawriter	db_datawriter fixed database role can add, delete, or change data in all user tables. The members of the
db_denydatareader	db_denydatareader fixed database role cannot read any data in the user table in the database. The members of the
db_denydatawriter	db_denydatawriter fixed database role cannot add, modify, or delete any data in the user table in the database. The members of the
db_owner	db_owner fixed database role can perform all the configuration and maintenance activities of the database, and can also delete the database. The members of the
db_securityadmin	db_securityadmin fixed database role can modify role membership and administrative permissions. The members of the
db_ddladmin	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 to add it to members of this role in the Properties page:

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 the full permissions of the role, suggest using dbo or leave blank.

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

6. Now forget the first listbox and go to the second ListBox, "Members of this role," to add role members.

7. Then go to the Security Objects page, where you can select a security object or use T-SQL to implement:

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. Contacts to Prospectmanager;

principle:

The fixed role allows the authorization of global permissions to all objects in the database, most of the time, without the need to use Db_datareader/db_datawriter. Because there are too few permissions restrictions, 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 in the database as dbo users.

You can use the following statement to see whether a logon name belongs to a member of a database role:

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

The return of 1 would represent Fred as a member of the Prospectmanager role.

MORE:

If you use the WITH GRANT option to authorize to a Windows group, the member of this role chooses 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 in Dbo.contact to contactreaders with GRANT OPTION; 
Go 
EXECUTE as USER = ' Fred '; 
--This does is not work 
GRANT SELECT on Dbo.contact to Mary; 
--This works 
GRANT SELECT ' 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 functionality of roles are not the same as regular libraries:

Role	Describe
Db_ssisadmin	You can administer 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 SSIS packages that belong to it.
Dc_admin	You can manage the Data collector collection set and properties, with all Dc_operator permissions.
Dc_operator	DC Collection set and properties can be read and updated.
Dc_proxy	You can read the DC collection sets and corresponding properties.
Policyadministratorrole	All configuration and maintenance operations on policy-based management can be performed.
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 article: 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: http://blog.csdn.net/dba_huangzj/article/details/39927713