Detailed Explanation of Database Principles Chapter 4~5
Chapter 4: Database Security
1. Overview of database security
1. Insecure factors of the database
① Malicious access and damage to the database by unauthorized users
② The important or sensitive data in the database is leaked
③ The vulnerability of the security environment
2. Introduction to safety standards
① Safety standards
TCSEC : U.S. Department of Defense, Orange Book
ITSEC : France, Britain, the Netherlands, and Germany, European white paper
CC : The United States, Canada, Britain, France, Germany, and the Netherlands jointly proposed common standards for information technology security evaluation
ISO 15408 : In 1999, CC 2.1 was adopted by ISO as the international standard ISO 15408.
② Security level
C1: Autonomous access control
B1: Security mechanisms such as mandatory access control (MAC) and auditing are implemented on the subject and object of the mark. B1 level products are truly security products.
③ CC security classification
Two, database security control
1. User identification
① Static password authentication
② Dynamic password authentication
The password changes dynamically, and a new password is used for each authentication
③ Identification of biological characteristics
Similar to fingerprints, face recognition
④ Smart card identification
Similar access card
2. Access control technology (DAC/MAC)
① Define user permissions
DBMS provides appropriate language to define user permissions, which are stored in the data dictionary, called security rules or authorization rules
② Legal authority inspection
When a user sends a database operation request, the DBMS searches the data dictionary to check the legal authority
③ Define the user authority check sequence:
3. Autonomous Access Control (DAC)
① Autonomous access control method
Implementation: through SQL GRANT statement and REVOKE statement
User authority composition:
- Data object
- Operation type
Defining user access rights: the user can define which database objects for which types of operation . Defining access rights is called authorization.
② Objects of access control and operation types
③ Authorization: GRANT X ON TX TO X
④ Take back: REVOKE X ON TX FROM X
⑤ Permission to create database schema
⑥ Role and authorization to role
4. Mandatory Access Control (MAC)
5. View mechanism
7. Data encryption
Chapter 5: Database Integrity
1. Entity integrity
① Definition of entity integrity
Defined with PRIMARY KEY in CREATE TABLE.
A code composed of a single attribute can be defined as a column-level constraint or a table-level constraint.
There is only one way to explain the code composed of multiple attributes, which is the table-level constraint.
② Entity integrity check and breach of contract
When inserting or updating the main code column, the relational database management system will install entity integrity rules to automatically check.
Entity integrity rules :
- Check if the main code value is unique, refuse to insert or modify if it is not unique
- Check whether each attribute of the main code is empty, and refuse to insert or modify if one is empty
The only way to check whether the master code is to perform a full table scan is very time-consuming, so generally the core of RDBMS automatically builds an index on the master code.
2. Referential integrity
① Definition of referential integrity
- Use the FOREIGN KEY phrase in CREATE TABLE to define which columns are foreign codes
- Use the phrase REFERENCES to indicate the master codes of which tables these foreign codes refer to
② Referential integrity rules
The value of a foreign key can only be determined by the following two situations :
- Is empty
- Equal to the primary key value of a tuple in the relationship it refers to
③ Circumstances that may destroy referential integrity
- Add a tuple to the referenced table, the value of the outer code may not exist in another table
- Modify a tuple in the referenced table, the value of the outer code may be modified
- Delete a tuple of the referenced table, causing the foreign code in the referenced table to be illegal
- Modify the main code of a tuple in the referenced table, causing the foreign code in the referenced table to be illegal
④ Referential integrity check and handling of violations
3. User-defined completeness
① Attribute constraints
- The column value is not empty ( NOT NULL )
- The column value is unique ( UNIQUE )
- Check whether the column value satisfies a conditional expression ( CHECK )
② Tuple-level restrictions
③ Treatment of breach of attribute constraints
When inserting a tuple or modifying the value of an attribute, the relational database management system checks whether the constraint conditions on the attribute are met, and if not, it refuses to execute
Four, integrity constraint naming words
① Integrity constraint naming clause
In SQL, you can use the CONSTRAINT clause to define integrity constraints in the CREATE TABLE statement, thereby flexibly adding or deleting integrity constraints.
CONSTRAINT<integrity constraint condition name><integrity constraint condition>
② Modify the integrity restrictions in the table
You can use the CREATE ASSERTION statement in SQL to specify more general constraints through declarative assertions.
Any operation on the relationship involved in the assertion will trigger the relational database management system to check the assertion. Any operation that makes the assertion not true will be rejected .
① Statement format for creating assertion
② Delete the assertion
Six, trigger (Trigger)
A trigger is a special event-driven process defined by the user in the relational table.
- The trigger is saved in the database server
- Any user's addition, deletion, or modification of the table will be automatically activated by the server by the corresponding trigger
- Triggers can implement more complex inspections and operations, with more refined and powerful data control capabilities