Database principles and applications-security and integrity constraints

2018-02-21 17:40:14

There are several reasons why database data is corrupted:

1. system crashes;
2. Improper control of concurrent access;
3. Man-made destruction (intentional, or unintentional);
4. Errors in the input data itself;

First, two items can be resolved through database recovery technology, the third one needs to use the security of the database, the fourth is undoubtedly need data integrity constraints.

First, database security

1) Protect data in the database from unauthorized access

2. overrides for Views and query languages : For example, users can only view and access computer college data, and when a user submits a query, the system automatically adds and CS to the WHERE clause to filter the database data.
   
4. access Control : classify users into ordinary users; a user with certain resource privileges; DBA.
6. user identification and authentication : Assign an identifier to each legitimate user in the database, and the authentication can be a password or passphrase; key, U-shield, IC card, fingerprint, voice print, Iris, etc.
8. authorization and permission checks : For example,grant CONNECT to JOHN identified by XYZABC; Grant SELECT on TABLE S to U1 with GRANT OPTION.
10. role: Roles can be delegated to multiple users, avoiding the inefficiency of each single user authorization.
12. Data Encryption: File encryption, only through the database to access the file, and not directly using the operating system's file layer for access, so it has to be encrypted.
14. Audit trail : In case the database is accessed illegally, it can be viewed from the content recorded in the audit trail to see who did what to the database and what the camera did. For example,AUDIT SELECT, INSERT, DELETE, UPDATE on EMP whenever successful;

2) Security of statistical databases

In many cases, the statistics for a database are public, but a single piece of data is private.

At this point, if only by authorized login to ensure the security of the database, it is likely that a legitimate user through legal means to obtain statistical results, and then through the mathematical way to get the data should not be obtained.

Statistics Database security is used to solve this problem, in fact, this problem is very troublesome, so far has not found a good solution.

Someone has proposed a generic tracker that can only find a tuple if it finds a predicate that matches the criteria.

Second, the integrity of the database constraints

The integrity constraint of the data means that for each valid instance in the database, its value must satisfy the current constraint.

• Inserts/deletes/updates operations that violate any one of the integrity constraints will be discarded
• Can be used to ensure that all data entering the database is consistent.

1) Integrity constraints can be divided into two main categories: static constraints, dynamic constraints

• Static constraints: Constraints on the static state of a database

(i) data model inherent constraints : for example, INF;

(ii) implied constraints : the constraints of the pattern definition, such as Integer, the number of shapes can not give floating-point numbers, data domain requirements, the primary key can not be empty, can not repeat the constraints, foreign key constraints, that is, referential integrity constraints;

(iii) display constraints : Constraints using statements such as assertions;

• Dynamic constraints: A constraint in which a database transitions from one state to another state. Generally associated with trigger.

2) Referential integrity constraints

3) Definition of the display integrity constraint

(i) check clause when creating a table

(ii) assert

Similar to C + +, assertions can be asserted using Assert, for example: Assert balancecons on account:balance>=0;

For the operation of the left figure, the constraint effect cannot be achieved by simply using the check clause, and the constraint should not have effect on the boats table. You can use assertions at this point.

4) Triggers

Triggers are used for dynamic constraints, which are automatic triggering actions when a DBMS encounters a particular event.

Active database system is a system composed of a set of ECA rules, in particular, the database will dynamically detect the status of the current database, when encountered an event, and meet the appropriate conditions, the corresponding behavior began to execute.

Database principles and applications-security and integrity constraints