Shulou(Shulou.com)05/31 Report--

This article shows you how to carry out the discussion of database permission allocation, the content is concise and easy to understand, it can definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

First of all, let's learn about the permissions of the database.

Main body

A principal is an entity that can request SQL Server resources. Like other components of the SQLServer authorization model, principals can be arranged in a hierarchy. The scope of influence of a principal depends on the scope of the principal definition (Windows, server, or database) and whether the principal is inseparable or a collection. For example, the Windows login is an indivisible principal, while the Windows group is a collection principal. Each principal has a security identifier (SID).

Principal at Windows level

Windows domain login name

Windows local login

SQL Server--level principal

SQL Server login name

Server role

Database-level principal

Database user

Database role

Application role

SQLServer sa login name

The SQL Server sa login is the principal at the server level. By default, this login is created when the instance is installed.

Public database role

Each database user belongs to the public database role. When a user has not been granted or denied specific permissions on the security object, the user inherits the permissions granted to the public role of the security object.

INFORMATION_SCHEMA and sys

Each database contains two entities:

INFORMATION_SCHEMA and sys, which both appear in the catalog view as users. These two entities are required for SQL Server. They are not principals and cannot be modified or deleted.

Certificate-based SQL Server login

The server principal whose name is enclosed by a double pound sign (#) is for internal system use only. The following principals were created from the certificate when SQL Server was installed and should not be deleted.

# # MS_SQLResourceSigningCertificate##

# # MS_SQLReplicationSigningCertificate##

# # MS_SQLAuthenticatorCertificate##

# # MS_AgentSigningCertificate##

# # MS_PolicyEventProcessingLogin##

# # MS_PolicySigningCertificate##

# # MS_PolicyTsqlExecutionLogin##

Guest user

Each database includes a guest. The permissions granted to guest users are inherited by users who have access to the database but do not have a user account in the database. You cannot delete a guest user, but you can disable it by revoking the user's CONNECT permissions. You can revoke CONNECT permissions by executing REVOKE CONNECTFROM GUEST in any database other than master or tempdb.

Client and database server

By definition, clients and database servers are security principals and can be protected. These entities can authenticate with each other before establishing a secure network connection. SQLServer supports the Kerberos authentication protocol, which defines how clients interact with web authentication services.

Create a database user

Eleven user types are supported in SQL2016:

This is the most common user type in master based on the login name.

User based on login name based on Windows Active Directory account

CREATE USER [Contoso\ Fritz]

A user based on the login name of the Windows group. CREATE USER [Contoso\ Sales]

Users based on logins that use SQLServer authentication. CREATE USER Mary

Users who authenticate in the database are advised to help make your database portable.

Always allowed in SQL Database. Only SQL Server is allowed in the database contained in.

Users based on Windows users without login names

CREATEUSER [Contoso\ Fritz]

Users of the Windows group based on no login name. CREATE USER [Contoso\ Sales]

Users in SQLDatabase or SQL data warehouse Azure Active Directory-based users. CREATE USER [Contoso\ Fritz] FROMEXTERNAL PROVIDER

Contains the database user with the password. (SQL data warehouses are not available in.) CREATE USER Mary WITHPASSWORD ='*'

Users who connect through the Windows group login based on the Windows principal

A user based on a Windows user who has no login but can connect to the database engine through membership in the Windows group

CREATE USER [Contoso\ Fritz]

Users who are based on Windows groups that do not have a login but can connect to the database engine through membership in other Windows groups. CREATE USER [Contoso\ Fritz]

Users who cannot authenticate these users cannot log in to SQL Server or SQL Database.

Users who do not have a login name. Cannot log in, but can be granted permission

CREATE USER CustomAppWITHOUT LOGIN

Certificate-based users. Cannot log in, but can be granted permissions, or you can sign the module. CREATE USERTestProcess FOR CERTIFICATE CarnationProduction50

Users based on asymmetric keys. Cannot log in, but can be granted permissions, or you can sign the module. CREATE User TestProcessFROM ASYMMETRIC KEY PacificSales09

The following picture shows the meaning of the options needed to create a database user:

You can use the interface to create the user:

You can also use T-SQL to create

-- create login name: Test password is: '123456 password.

CREATELOGIN Test

WITH PASSWORD = '123456'

GO

After talking about the users above, let's talk about the roles and permissions of the database below.

Server-level permissions

SQL Server provides server-level roles to help you manage permissions on the server. These roles are security principals that can be combined with other principals. The scope of permissions for server-level roles is server-wide. (roles are similar to groups in Windows operating systems.)

SQL Server provides nine fixed server roles. The permissions granted to the fixed server role cannot be changed. Starting with SQL Server 2012, you can create user-defined server roles and add server-level permissions to user-defined server roles.

You can add server-level principals (SQL Server login, Windows account, and Windows group) to server-level roles. Each member of the fixed server role can add another login to that same role. Members of a user-defined server role cannot add other server principals to the role.

The following table shows the fixed roles at the server level and their permissions

The following table shows the fixed database roles and the actions they can perform. These roles are found in all databases. Cannot change permissions assigned to fixed database roles

The permissions assigned to the fixed database role cannot be changed. The following figure shows the permissions assigned to the fixed database role:

SQL 2016 has some special permissions for databases

Msdb role

The msdb database contains the special-purpose roles shown in the following table.

Use R Services

SQL Server (starting with SQL Server vNext)

Other database roles can be used to manage packages when R Services is installed

Here's how to implement the requirements mentioned earlier in the article:

Give a user the right to query all databases

Give a user the right to back up the database only

Give a user permission to specify only the database

Give a user permission to only a certain table

Give a user the right to query all databases

Create a user

USE [master] GO CREATE LOGIN [Test1] WITH PASSWORD=N'password@123'

Connect to a database instance using Test1

You can see the list of databases, but you cannot access the database

Give test1 read access to FinaceDemo

USE [FinaceDemo] GO CREATE USER [Test1] FOR LOGIN [Test1] ALTER ROLE [db_datareader] ADD MEMBER [Test1] GO

This gives test1 users read access to finacedemo.

But test1 does not have write permission

This allows you to view the read permissions granted to the database by test1 separately.

Give a user the right to back up the database only

Test1 does not have backup permission for finacedemo

Give backup permissions

ALTER ROLE [db_backupoperator] ADD MEMBER [Test1]

Give a user permission to specify only the database

We need Test1 to see only FinanceDemo, not all other databases.

Execute the following script

USE [master] Deny VIEW any DATABASE TO Test1; go

The effect after running

Cannot see any database after Test1 connection

Execute:

ALTER AUTHORIZATIONON DATABASE::FinanceDemo TO test1

The result after completion:

Test1 can see the database that is granted permission

Give a user permission to only a certain table

Create a test user test3

USE [master] GO CREATE LOGIN [Test3] WITH PASSWORD=N'password@123'-gives test2 access to testDB USE [testdb] GO CREATE USER [Test3] FOR LOGIN [Test3] GO

Give test3 update and select permissions for the T2 table grant update on dbo.t2to test3 grant select on dbo.t2to test3 use testDB to view the permissions obtained by the test3 user exec sp_helprotect @ username='test3'

You can see that user test3 has select and update permissions for T2

Execute select * from T2

Failed to perform insert operation.

Rights management is very complex, the above is only a brief introduction. Need more detailed content, need to study by yourself. More detailed information can be found on technet.

The above content is the discussion of how to assign database permissions. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.