Database Review 5--Security

Database Review CH7 Security 7.1 Introduction to Database Security

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

Security issues involve a lot of layers, and illegal users can steal or tamper with the database from the following levels:

level	steal or tamper behavior	Coping measures
Database system	Get advanced User Database permissions	Authentication, permissions, and access to data equivalence
Operating system	Get operating system root permissions what you want to do is not limited to the operation of the database.	Increase operating system security level
Internet	Network eavesdropping and identity spoofing can also steal online confidential data.	Authentication and encrypted transport
Physical	Physically stealing and destroying storage devices	Cabinet lock and other security protection, backup measures

7.2 Rights Management

General database security management starts from three angles:

• Server OS login Authentication
• Database DBMS Login Authentication
• Database DBMS Rights Management

Rights Management is a custom (discretionary) access control, and a mechanism for enforcing (Mandatory) access control (from the perspective of security model, data object label, etc.)

We mainly discuss the database permissions management, it is not difficult to think that the database has the following permissions:

• Read Data permissions
• Insert Data permission
• Modify Data permissions
• Delete Data permissions
• Create Delete Index permission
• Relationship Creation Permissions
• Relationship Modification Permissions
• Relationship Delete Permissions
• Wait a minute

The previously mentioned view is a rights management mechanism (user access to an authorized view), in addition to the authorization graph to represent the level of privilege management is also a common method (user and DBA participation, slightly)

7.3 SQL Security

The conceptual part ends with a review of the underlying SQL database security management operations

(1) Authorization

The syntax for the SQL Basic authorization statement is as follows, and the user executing the authorization statement must have the authorization authority to obtain the appropriate action to authorize (DBA has all the Power):

listonnameornametolist>;

This includes:

• Select
• Insert
• Update
• Delete
• References, which is the foreign key declaration permission
• Usage, allow special domains (some custom fields?) ）
• All privileges, that is, all possible permissions
• With GRANT option (suffix), which is the authorization authority for an operation

It can be in the following three formats:

• User-id: User Unique identification number
• Public: All Valid users
• Role: Roles

Role is the concept introduced in SQL-99, which is used for user group Rights Management and permission hierarchy Management (by the means, these two are based on my understanding of the words I made)

Let's look at an example of Role Assignment permissions:

 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;And create role manager; create two roles for regular employees and managers, then grant the normal Employee SELECT permission on table Staff_account, and then grant the manager update, insert, and delete permissions on table Staff_account

Next grant worker to manager; is the role of authorization, that is, all the power of the worker to the manager, and then finally Grant U1, U2, U3 the role of ordinary employees and the role of granting U0 manager (that is, the right to give them the appropriate role)

So in the example above,

用户组别权限管理是指把现实身份相同的U1、U2、U3分成一组，直接分配给他们worker的权力。那么如果新来一个普通员工U4就不必再去思考赋予给他什么权力而是直接授予他worker的身份即可。涉及多个表的不同的权限分配可能会很复杂，我们只用对同一个组别的统一的身份授权，再去管理用户身份即可管理复杂的权限分配。而权限层次管理是利用用户现实身份的层次关系，借助面对对象设计中继承的思想来划分权限的层次，让权限层次清晰分明的管理方法。具体到上例来说就是经理也是员工，只是是特殊身份的员工，那么manager身份的人可以继承worker身份已经分配的权力。权限层次管理的好处在于让我们对待manager角色时只需要考虑他的特殊权限即可。

It is also important to note that granting permissions on views does not imply that they are granted response permissions on their base tables

(2) Cancellation of authorization

The underlying syntax for SQL de-authorization is:

listonnameornamefromlist> [restrict|cascade];

is consistent with the previous section definition, and the meaning of the Restrict (limit) and cascade (cascading) suffixes is the same as when we learn foreign keys: Restrict is not backward, cascade means that user x grants Y permission A, Once revoke the a permission of X then Y's a permission is also canceled

(3) Limitations of SQL Rights Management

The rights Management of SQL has its own flaw, which mainly include:

• The granularity of the permissions is not fine enough, that is, the permissions of a tuple in a relationship cannot be refined: for example, students can only view their scores in the score table, the need for SQL Rights management can not be satisfied
• All end system users of an application are mapped to the same database user, that is, the role division is difficult to customize according to user

7.4 Other safety management measures

There is also a common database security management measure called Audit trail (Audit Trail), where the DBMS records the specific actions of each user in a particular file or database, then the DBA can see if the record is over-privileged, etc., in the database recovery We also often use the means of audit trails (see subsequent chapters)

Database Review 5--Security