SQL Server Rights Management

SQL Server Rights Management

2014-11-10 08:37 by Pursuer.chen, 24214 reading, 40 Reviews, Favorites, compilation

Tags: SQL server/mssql server/Database/dba/rights control/Management/assignment/login name/database user/role

Overview

For the database system, ensuring the security of data is always one of the most important problems. A good database environment, you must define the responsibilities of each user and assign their corresponding permissions. At the same time the problem can also be found in the root.

Are you going to have this requirement:

1. To query a user for permissions on all databases
2. Give a user only the right to back up the database
3. Give a user permission to specify only the database
4. Give a user permission to have only one table
5. Give a user permission to view only certain objects (for example, views)
6. Give a user permission to execute only a few stored procedures

Directory

• Elements
  • Log In Name
  • Role
  • User
  • Architecture
• Permission Assignment
  • New sign-in name
  • Assigning database View permissions to users
  • To query a user for permission on an object
  • Permissions granted to the user schema
• Query permissions
• Reclaim Permissions
• Summarize

Elements

The article may be a bit dull, but also look patiently, believe that there should be what you want.

Log In Name

You can access an instance (SQL Server) only if you have a login name.

Role

A role is a combination of a class of permissions.

• The owner of a database role can be either a user or a database role itself, an administrator can create a database role, or you can barely understand a database role as a group of users with the same permissions, why do you say that because database roles and database users do not allow the same name to exist.

Note: Do not add user-created database roles to the fixed server database role, which will cause permissions escalation for fixed database roles.

• The owner of the server role has only the login name, the server role is fixed, and the user cannot create the server role.

Note: It is generally not recommended to assign a server role directly to a user because the server role is global, that is, you have server-level permissions, it is generally recommended to assign a database to the user, and then assign database role permissions to the corresponding database.

User

The user is a database-level concept, the database user must bind the specific login name, you can also be in the new login name to bind the login database, when you bind the login database, the database created by default the login name of the database user, the login name and database users there is an association between the The database user is the owner of the schema and database role, that is, you can assign a schema to the user and that user has the objects that the schema contains, and you can assign a database role to the user who has permission to the database role.

Architecture

The schema is the owner of the object, and the schema itself has no permissions, and the schema contains database objects such as tables, views, stored procedures, and functions, usually the most common default schema, dbo. If you do not specify a schema by default, the database object is created with the dbo. Beginning with the database user, database role, application role. User-created schemas and roles can only be used for the current library.

After understanding these concepts, we can then practice, and then we will test the server role to choose Public, only to test the control of database permissions.

Permission Assignment New login name

Create a new login person, assign public permissions only to the login server role, and do not assign a database

Next, the person user will be logged in to the instance, and no database can be accessed because we have not assigned any database to the user.

Assigning database View permissions to users

Allow users to view ADVENTUREWORKS2008R2 databases only

At this point the user can query all objects, but cannot modify the object.

To query a user for permission on an object

If you feel that viewing permissions to the user is too large, recycle the Da_datareader database role permissions, and you will find that the user has access to the database, but no objects are visible.

View Person.Address table only for users

Use ADVENTUREWORKS2008R2; GRANT SELECT on OBJECT::P erson.address to person;--or using use ADVENTUREWORKS2008R2; GRANT SELECT on person.address to ROSAQDM; GO

Extended Functionality

--These are the DML permissions given to the table by the user

---Grant the user the Modify permissions on the table person.address use ADVENTUREWORKS2008R2; GRANT UPDATE on person.address to person; GO---Grant the user the Insert permission to the table person.address use ADVENTUREWORKS2008R2; GRANT INSERT on person.address to person; GO---Grant the user person the Delete permission on the table person.address use ADVENTUREWORKS2008R2; GRANT DELETE on person.address to person;

--Grant execution permissions to user stored procedure Dbo.prc_errorlog
GRANT EXECUTE on Dbo.prc_errorlog to Person

Scalar function permissions: EXECUTE, REFERENCES.

Table-Valued Function permissions: DELETE, INSERT, REFERENCES, SELECT, UPDATE.

Stored procedure permissions: EXECUTE.

Table permissions: DELETE, INSERT, REFERENCES, SELECT, UPDATE.

View permissions: DELETE, INSERT, REFERENCES, SELECT, UPDATE.

Permissions granted to the user schema

New Database Role Db_persons

New schema

Database-Security-architecture

Schema contains database objects

Create Schema Persons table

---Creating a schema Persons table CREATE TABLE persons.sutdent (ID int not NULL)

You will find that the user has access to the Persons.sutdent table at the same time because the user is the owner of the database role Db_person, and Db_person is the owner of the schema persons.

Create a view of some persons schemas, stored procedures

---Create a view use Adventureworks2008r2gocreate view Persons.vwsutdentasselect * from Persons.sutdentgouse Adventureworks2008r2go---Creating a stored procedure create PROCEDURE persons.sp_sutdent (@OPTION NVARCHAR) asbegin    SET NOCOUNT on    IF @OPTION = ' Select '    BEGIN    SELECT * from Persons.sutdent    

The detailed grant feature can query 2008r2 connection books:

Ms-help://ms. Sqlcc.v10/ms. Sqlsvr.v10.zh-chs/s10de_6tsql/html/a760c16a-4d2d-43f2-be81-ae9315f38185.htm

Query permissions

  ---login list  select *   from master.sys.syslogins---login name and Server Role association table  SELECT * FROM Sys.server_role_members  --- Server Role Table  SELECT * FROM sys.server_principals  ----Query login name owned server role  Select srvrole = g.name, membername = U.name, Membersid = U.sid from  sys.server_role_members m  inner join sys.server_principals g on  g.principal_id = m.role_principal_id   INNER JOIN sys.server_principals u on u.principal_id = m.member_principal_id  ---database user table C13/>select * FROM sysusers  ---Database User Table Role association table  SELECT * FROM sysmembers   ---Database role table  SELECT * FROM sys.database_principals  ----Querying database user-owned roles  select Ta.name as username,tc.name as DatabaseRole  from sysusers ta inner join sysmembers TB on Ta.uid=tb.memberuid  inner join  sys.database_principals TC on TB.GROUPUI d=tc.principal_id    

Querying the relationship between a login name and a database user

--Query the login name associated with the current database user  adventureworks2008r2  select Ta.name as loginname,tb.name as DatabaseUserName from Master.sys.syslogins ta inner join sysusers TB on TA.SID=TB.SID/     * If you restore the current database to another server instance, there is also a person logged in user on that server, You will find that the SID is different,  because the SID is not the same, so the login user does not have access to the current database, we have to find ways to associate them.  */  ---Associated login name with database user (the SID of the database user is brushed to the login name) use    adventureworks2008r2    EXEC sp_change_users_login ' Update_One ', ' person ', ' person '    Go

Querying the permissions that a database user is granted

exec sp_helprotect @username = ' person '

Querying the person database user right discovers that the database user has permissions that were previously granted by Grant, and that the schema object that is assigned to the user is not displayed in this, but only the permissions that are granted, and the schema is the permissions that the database user has.

Reclaim Permissions

If the securable object is a database, corresponding to backup databases, backup LOG, CREATE DATABASE, create DEFAULT, create FUNCTION, create PROCEDURE, create RULE, Create TABLE and create VIEW.

If the securable object is a scalar function, it corresponds to EXECUTE and REFERENCES.

If the securable object is a table-valued function, it corresponds to DELETE, INSERT, REFERENCES, SELECT, and UPDATE.

If the securable object is a stored procedure, represents EXECUTE.

If the securable object is a table, corresponding to DELETE, INSERT, REFERENCES, SELECT, and UPDATE.

If the securable object is a view, corresponding to DELETE, INSERT, REFERENCES, SELECT, and UPDATE.

Reclaim execution permissions for dbo.prc_errorlog stored procedures

Use ADVENTUREWORKS2008R2; REVOKE EXECUTE on dbo.prc_errorlog from person    ;

Retrieve person.address Table query, modify, delete permissions

--Recycle and modify use ADVENTUREWORKS2008R2; REVOKE Update on   person.address by person; Use ADVENTUREWORKS2008R2; REVOKE alter on   person.address from person;--recycle delete use adventureworks2008r2; REVOKE Delete on   person.address from person;--recycle query use ADVENTUREWORKS2008R2; REVOKE Select on   person.address from person;

Finally, the owner is the '. ' is the database-level permissions

Last permission to reclaim a database

Use ADVENTUREWORKS2008R2; REVOKE CREATE TABLE from person; GO
The CONNECT permission is the user's right to access the database, and the user will not be able to access the database after this permission is--use Adventureworks2008r2;--revoke CONNECT from Person;--go

Execute exec sp_helprotect @username = ' person ', leaving Action=connect database access rights

After the permissions are reclaimed, the database user also has permissions to the schema persons, and if the permission needs to be reclaimed, only the user is required to disassociate the corresponding Db_person database role permissions.

For detailed revoke permission recycling, refer to 2008r2 Books Online:

Ms-help://ms. Sqlcc.v10/ms. Sqlsvr.v10.zh-chs/s10de_6tsql/html/9d31d3e7-0883-45cd-bf0e-f0361bbb0956.htm

Add

One of the most common users who create an application access for a production database server is the ability to grant a user a database: query, delete, modify, insert, execute, and use the SQL statement as follows (User: person, Database: News):

Use [master]go---Creating login name Create login [person] with password=n ' people ', default_database=[news], Check_expiration=off, Check_policy=offgouse [News]go---Create the database user associated with the login name under the specified database-the "create user [person] for LOGIN [Person]gouse [News] GO---Grant the user select,delete,update,insert,execute permissions under the specified database. GRANT Select,delete,update,insert,execute to person;

Note: Create a login name under the master database, both the CREATE database user and the grant database permission are operating under a specific database.

Summarize

So if you want to subdivide a user's permissions on a database, you can grant a specific object to the user by using grant (you can also revoke the permissions of the collection), or you can add permissions to the schema to the user by adding the permission to the one.

What permissions a user has depends on the role, and what objects are owned depends on the schema that owns the object, whether the owner of the schema can be a database user or a database role or an application role, and it is clear that you are managing your permissions.

Although the heart is still unable to tell the whole point of knowledge, writing the article although the entire framework to organize, but in the process of writing found to write too much, such as grant permissions involved in the table, database, application roles, functions, certificates, roles, schemas, stored procedures, synonyms and many At the same time, the table has the right to be precise to a specific field, so there are too many, and the next revoke is the same. This article can play a leading role, let you understand that there are these features, understand the functional breakdown of permissions; If interested friends can delve deeper, this article written down or very tired, write this article to see the time is two o'clock in the morning, mainly thinking do not want to be interrupted so a breath to write, I hope to give you some help.

Note: Pursuer.chen Blog: http://www.cnblogs.com/chenmh This site all the essays are original, welcome to reprint, but reprint must indicate the source of the article, and at the beginning of the article clearly to the link, otherwise reserves the right to hold responsibility. Welcome to the exchange of discussions

The permissions of SQL Server are fairly good. But in the application process also have their own rights management, database is the user of the database, application system is the user of the application system.
There are 9 types of AC elements: Account, Organization, Role, Group, Function, Menu, Appsystem, ResourceType, Privilege.
privilege is defined as any 22 combination of these 9 types of AC elements, and a dollar of two yuan is the "subject", one dollar is the "object" and the subject is responsible for perceiving the object. The distinction between the principal and the object is to define the direction for these 22 combinations. Altogether is (9 * 8)/(2*1) = 36 + 9 = 45 results.
There is only one combination of these 45 combinations of function-level permissions that is most critical: (Account,function), the subject is the account, the object is a function. The purpose of the remaining 44 combinations is ultimately to derive the combination of the subject as the function of the account object.
Each combination instance is called a privilege,privilege is also a member of 9 kinds of AC elements, there is a special combination, it is (Privilege,privilege), this combination is composed of Privilege inheritance chain, similar to object-oriented inheritance. In addition, 9 of the above 36+9 are (Account,account), (organization,organization), (Role,role), (Group,group), (function,function), ( Menu,menu), (Appsystem,appsystem), (Resourcetype,resourcetype), (Privilege,privilege).
The function-level permissions are resident memory.
When entering data permission, another element is added, the 10th element is entity or resource record, and data level permission is a combination of 10 kinds of AC elements. Data-level permissions and resource records are stored in the same physical location, loaded into memory along with access to the resource records, and discarded at any time after they are exhausted.
According to the RBAC Standard is able to complete any functional level of permission control, then the multi-AC element is ultimately to get (account,function) combination, the system runs into a controlled area when nothing is account,function combination, Only ask if the current user can perform the current function.

To be authenticated, you only need to know what the current account and the function you are currently trying to access, where the account can be read directly from the usersession, and how to recognize the relative twists and turns of a function, for ASP. MVC can be based on this convention: Controllername=resourcetypecode,actionname=functioncode, Knowing that Resourcetype.code and Function.code can be indexed directly to the function in Functionset.

In order to start building data-level permissions on layer 100, Anycmd needs to make a good section of the first 100 levels of functional privileges as soon as possible.

Both the dbo and the person are schema names, and the default schema is the one that starts with the dbo and we call the database
The name of the database. The name of the schema, which is in a different database.
When the database name can be omitted in the same database, only the schema name is required. Table name, which is only in the case of the same schemas in the table, only need to directly use the table name on the SELECT * from table, if there are many schemas there is a need in the call with the schema name. Table Name SELECT * FROM Schema name. Table.
They play the recognition function, for example, the name of the table is called a, but one is the DBO schema, and the person is not the same during the invocation, select * FROM dbo. Table
SELECT * FROM person. Table  does not write the schema name defaults to dbo.

SQL Server Rights Management