Database review 5-security and database review security

Database review 5-security and database review security
Database review CH7 Security 7.1 database security Introduction

If integrity is a database protection measure for authorized users, security is a database protection measure for unauthorized users.

Security issues involve many layers. illegal users can steal or tamper with databases from the following layers:

Level	Theft or tampering	Countermeasures
Database System	Obtain database permissions of advanced users	Authentication, permission and data retrieval are equal
Operating System	You can do whatever you want to get the root permission of the operating system, not only for database operations.	Improve Operating System Security
Network	Attackers can steal confidential online data through network eavesdropping and identity disguise.	Authentication and encrypted transmission
Physical	Physically stealing and damaging storage devices	Cabinet locks and other security protection and backup measures

7.2 permission management

Generally, database security management starts from three perspectives:

• Server OS login authentication
• Database DBMS login Identity Authentication
• Database DBMS permission management

Permission management is a custom access control (Discretionary). Another mechanism is Mandatory access control (from the security model, data object tags, etc)

We mainly discuss database permission management. It is easy to think that the database has the following permissions:

• Read data permission
• Data insertion permission
• Modify data Permissions
• Delete data permission
• Create and delete an index
• Link creation permission
• Link modification permission
• Delete link permission
• And so on.

The view mentioned above is a permission management mechanism (User Access complies with the authorized view ).Authorization DiagramTo indicate the permission management level is also a common method (both the user and DBA participate, omitted)

7.3 SQL Security

The conceptual part ends. Next we will review the basic SQL database security management operations.

(1) Authorization

The syntax of the basic SQL authorization statement is as follows. The user who executes the authorization statement must have obtainedAuthorization power for corresponding operationsCan be authorized (DBA has all permissions ):

grant <privilege list> on <relation name or view name> to <user list>;

Including:

• Select
• Insert
• Update
• Delete
• References, that is, the foreign key declares the permission
• Usage, which allows special domains (some custom domains ?)
• All privileges, that is, all possible Permissions
• With grant option (suffix), that is, the authorization power for an operation

It can be in the following formats:

• User-id: Unique user id
• Public: all valid users
• Role:Role

A Role is a concept introduced in the SQL-99 and is usedUser Group permission managementAndPermission level management(By the way, these two words are based on my understanding)

Let's first look at an example of role's permission assignment:

create role worker;create role manager;grant select on Staff_Account to worker;grant update, insert, delete on Staff_Account to manager;grant worker to manager;grant worker to U1, U2, U3;grant manager to U0;

create role worker;Andcreate role manager;Create two roles: common employee and manager, then grant the select permission of common employee on the table Staff_Account, and then grant the update, insert, and delete permissions to the manager on the table Staff_Account.

Nextgrant worker to manager;It is the authorization between roles, that is, assigning all the worker's ownership to the manager, then, grant the roles of U1, U2, and U3 ordinary employees and the roles granted to U0 managers (that is, grant them the corresponding role permissions)

In the above example,

User Group permission management refers to dividing U1, U2, and U3 with the same real identity into one group and directly assigning them the power of worker. Then, if a new employee U4 does not have to think about the power given to him, it simply grants him a worker identity. The assignment of different permissions involving multiple tables may be very complicated. We only need to authorize the Uniform Identity of the same group, and then manage the user identity to manage the complex assignment of permissions. Permission level management is a management method that uses the hierarchical relationship of the user's real identity to divide the permission level based on the inheritance idea in the object design, so that the permission level is clearly defined. Specifically, in the above example, the manager is also an employee, but an employee with a special identity. The manager identity can inherit the power assigned by the worker identity. The benefit of permission level management is that we only need to consider the special permissions of a manager role.

In addition, granting view permissions does not mean granting the basic table response permissions.

(2) Cancel authorization

The basic syntax for canceling SQL authorization is:

revoke <privilege list> on <relation name or view name> from <user list> [restrict|cascade];

It is consistent with the definition in the previous section, while the meanings of the restrict and cascade suffixes are the same as those when we learn foreign keys: restrict means not to spread backward, cascade means that user X grants permission A to user Y. Once the revoke has the permission of user X, the permission of user Y is also canceled.

(3) Restrictions of SQL permission management

SQL permission management has its own defects, including:

• The granularity of permissions is not detailed enough, that is, the permissions of a tuples in a certain relationship cannot be detailed. For example, if a student can only view his or her own scores in the two-dimensional table, the SQL permission management cannot meet this requirement.
• All end system users of an application are mapped to the same database user, that is, role division is difficult to customize according to the user.

7.4 other security management measures

Another common database security management measure isAudit Trail)That is, if the DBMS records the specific operations of each user in a special file or database, the DBA can check whether the record is unauthorized.Database recoveryWe also frequently use audit tracking methods (see the subsequent sections)