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

Original: http://blog.csdn.net/dba_huangzj/article/details/39639365. Featured folder:http://blog.csdn.net/dba_huangzj/article/details/37906349

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

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

Objective:

The database-level role agrees to manage database permissions as well as the server-level roles. You can configure fixed server roles, create user-defined roles, and so on.

Realize:

1. In SSMs, enter a node for a database. Click on "Security". Then click on the Database Roles node:

Fixed role	Descriptive narrative
Db_accessadmin	Members of the db_accessadmin fixed database role can join 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 are able to 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 whatever data is in the user tables in the database.
Db_denydatawriter	Members of the Db_denydatawriter fixed database role cannot join, alter, or delete any data in the user tables in the database.
db_owner	Members of the db_owner fixed database role can run all configuration and maintenance activities of the database and can also delete the database.
Db_securityadmin	Members of the db_securityadmin fixed database role can change role memberships and administrative permissions.
db_ddladmin	Members of the db_ddladmin fixed database role are able to run whatever data definition language (DDL) commands in the database.

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

3. Can also be implemented using T-sql:

ALTER ROLE db_accessadmin ADD MEMBER test;

4. Assume that you need to create your own defined roles. You can right-click the Database Roles node and select New Database role, enter the role name and owner to control all permissions for this role. It is recommended that you use 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 go to the second ListBox, "members of this role." Ability to join role members.

7. Then go to the "securable" page, where you can select a securable object and be able to implement it with 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 agree to grant global permissions to all objects in the database, and most of the time, there is no need to use Db_datareader/db_datawriter. Because of the limited permissions, Db_denydatareader/db_denydatawriter is very practical and can restrict users from not agreeing to access tables or 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.

The ability to 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.

Many others:

If you use the WITH GRANT option to authorize to a Windows group, the members of this role choose Yo to use Askeyword 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. You must use as contactreaders.

msdb role:

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

Role	Descriptive narrative
Db_ssisadmin	You can manage SSIS on the server as an administrator.
Db_ssisoperator	Ability to view all SSIS packages. However, it cannot be imported or altered.
Db_ssisltduser	Ability to view and run SSIS packages that belong to it.
Dc_admin	Ability to manage Data collector collection sets and attributes, with all Dc_operator permissions.
Dc_operator	Ability to read and update DC collection set and properties.
Dc_proxy	Ability to read DC collection sets and corresponding properties.
Policyadministratorrole	Ability to run all configuration and maintenance operations on policy-based management.
Servergroupadministratorrole	Ability to register and use central Management Server groups.
Servergroupreaderrole	Ability to connect to a central Management Server group.
Dbm_monitor	Agree to monitor 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: http://blog.csdn.net/dba_huangzj/article/details/39927713

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