Manage SQL Server database security

Time:2021-4-14

The data reading in the database can be set with permissions. Previously, super administrator (SA) permissions have been used to access the database. In fact, in SQL Server database, if a user wants to access the objects in the database, he must go through three passes of authentication, and the objects here include the tables, views, stored procedures and so on mentioned in the previous article.

security management
SQL The authority authentication in the server database is similar to the community management in real life. Whether you can enter the community is the first way of identity confirmation, and it is preliminarily judged as the owner of the community. After entering the community, which building you live in is the second way of identity confirmation, and you need to brush the access card to enter. After entering the corridor door, there are many households in each building, and the number of households on which floor is the second way The third one is confirmed.

Only the person who really opens the door with the key is the real owner of the house. The entrance of the community, the unit door of the building and the household door are just like the database server, database and data object.

1. Connect to the server

The first authentication is login permission, which determines whether the user has the right to access the database server; as identity authentication, completing this step is equivalent to obtaining the permission to enter the cell.

2. Access database

The second authentication mainly determines whether the user has access to the database, that is, whether he is a legal database user. There are many buildings in a community, and there are also many databases on a server. This step is to determine which building users can enter, that is, which database they can operate.

3. Operation permission of data object

The third authentication is to determine the user’s access to the data object and whether they have access to the data object. That is to say, if you can enter the household door, you will be recognized as the real owner. Here, the access rights include add, delete, modify, query and other operations.

Practical operation
1. Create login

This step is to get the connection right. Including: create, modify, enable, disable, delete login account and other operations.

Figure: create a login account of “Xiaoming” with the password of “xiaoming123”. Then you can use this account to log in to the database server, and readers can try other functions by themselves.

Disable account means that the account exists but cannot be used; delete means that the account completely disappears from the server. A login account can connect to the database server, but it cannot login to the database. Just like people who can enter the community may not be able to enter the unit door of the building.

2. Create user

This step is to get access to the database. Including: create, modify, delete, etc. It should be emphasized that:

First, the database user must locate to the specified database;
Second: a login account for a database can only allow one database user; that is, the database user and login account is a one-to-one relationship.

With database users, but still do not have access to the database data object permissions. It’s like entering the unit door of the building. If you don’t have the door key, you still can’t enter the house.

3. Grant

There are three kinds of permissions: data object permission, statement permission and implied permission. Data object authority mainly refers to the statement authority of data operation, that is, add, delete, change, query and other operations.

There are three ways of operation: authorization, recall and disable

Grant: grant a certain permission to a user
Revoke: revoke the granted permission to the user
Deny: forbid users to have certain rights

The code in line 34 in the figure grants zuxia to the user for the cardinfo table_ There are four permissions in netbar: query, add, modify and delete. The keyword on is followed by the table name and to is followed by the user name.

The 37th line of code, for cardnumber and cardbalance fields in cardinfo table, grants the user zuxia_ The permissions of netbar query. That is, you can only query specific fields of a specific table.

Line 40, grant user zuxia_ The query authority of netbar to all tables in the database.

The syntax of reclaiming and disabling classes is the same as that of authorization above, which will not be repeated here.

This work adoptsCC agreementReprint must indicate the author and the link of this article