Detailed explanation of the database principle, save your final exam four parts [2]: the security and integrity of the database

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
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-QXX0yzmd-1622774733985) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210603094141100.png)]

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
[External link image transfer failed. The source site may have a hotlink prevention mechanism. It is recommended to save the image and upload it directly (img-aJdxMSPQ-1622774733988) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210603094327142.png)]

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
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-Y0RQjKp8-1622774733989) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210603094806211.png)]

Two, database security control

1. User identification

① Static password authentication

User-made

② 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

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:
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-wnqunlfU-1622774733991) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604091636131.png)]

3. Autonomous Access Control (DAC)

C2级,用户对不同的数据对象有不同的存取权限,用户可以授权给其它用户

① 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
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-hOKRNihH-1622774733993) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604090157310.png)]
③ Authorization: GRANT X ON TX TO X
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-VyWE5nSg-1622774733995) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604090218269.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-jlZArmie-1622774733996) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604090237606.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-EUbKIuhJ-1622774733996) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604090304601.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-1hNjWV7V-1622774733997) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604090316413.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-iGxb8v72-1622774733998) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604090326978.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-LZJvmlBN-1622774733998) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604090337281.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-O0FpkBNn-1622774733999) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604090353935.png)]
④ Take back: REVOKE X ON TX FROM X
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-73CN4mzP-1622774734000) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604090451768.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-SU944OOQ-1622774734001) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604090501395.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-3Tao7gcU-1622774734001) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604090510214.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-FLBplhSZ-1622774734002) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604090551347.png)]
⑤ Permission to create database schema
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-s6i8IJmx-1622774734003) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604090850861.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-Vielvrnl-1622774734003) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604090916030.png)]
⑥ Role and authorization to role
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-9kUXIb9A-1622774734004) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604090957462.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-wzyIrROD-1622774734005) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604091025188.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-aM7rZfZX-1622774734005) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604091041273.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-PNb51nps-1622774734006) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604091100974.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-H4RNAmVa-1622774734007) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604091109424.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-Pkp78Yb1-1622774734008) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604091122458.png)]

4. Mandatory Access Control (MAC)

B1级,对数据对象标以密级,对用户授予级别许可证,只有许可证级别高于密级才能拥有存取权限

[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-7E5TQWoN-1622774734009) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604091545951.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-ZwbJKqlw-1622774734009) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604091559100.png)]

5. View mechanism

[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-8oosvnbD-1622774734011) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604092116670.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-XZqKjHtc-1622774734012) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604092130327.png)]

6. Audit

[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-IZXZ8Q7n-1622774734013) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604092154707.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-HyYo8FcF-1622774734013) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604092214463.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-LmYHDjGB-1622774734014) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604092221697.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-d8y5ubZ4-1622774734014) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604092245045.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-GvIfmF05-1622774734015) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604092312272.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-TbWiHBSl-1622774734016) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604092321171.png)]

7. Data encryption

[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-O4Quc6ne-1622774734016) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604092343984.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-jtqjd3Cq-1622774734017) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604092353762.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-hCd4oduZ-1622774734018) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604092401391.png)]

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.

[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-olDxIEVV-1622774734018) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604093637970.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-9lDZiT6D-1622774734019) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604093647424.png)]
② 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
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-Sj3QC3QP-1622774734021) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604095322320.png)]
② 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
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-g95QteVe-1622774734022) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604100341099.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-7lmF2VX0-1622774734023) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604100347208.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-gadOM9Wf-1622774734023) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604100432687.png)]

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 )
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-zOJG2sv3-1622774734024) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604101414380.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-aLWP93We-1622774734025) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604101422590.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-01Vg1lD0-1622774734026) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604101436349.png)]
② Tuple-level restrictions
[External link image transfer failed. The origin site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-2QFT5ojf-1622774734031) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604101711948.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-LAwEnYy4-1622774734031) (C:\Users\Leizi of the official second\Desktop\Future Village Chief\image -20210604101719592.png)]
③ 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>

[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-5yfDp0mX-1622774734032) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604102641852.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-gpOFe5ou-1622774734032) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604102737550.png)]
② Modify the integrity restrictions in the table
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-VXzkNksH-1622774734033) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604102822891.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-3Qskud35-1622774734033) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604102830838.png)]

Five, assertion

在断言中设置检查语句,涉及该语句的操作触发断言进行判断

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
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-CKNVRXbH-1622774734034) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604103448758.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-xjLbHbfy-1622774734034) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604103644939.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-cKRfkIo4-1622774734035) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604103700061.png)]
② Delete the assertion
[External link image transfer failed. The source site may have a hotlink prevention mechanism. It is recommended to save the image and upload it directly (img-VuxicJHl-1622774734035) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604103741869.png)]

Six, trigger (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
② Definition of trigger
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-TtXIGyVO-1622774734036) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604104114689.png)]
[External link image transfer failed. The source site may have an anti-hotlinking mechanism. It is recommended to save the image and upload it directly (img-RKS3nHIY-1622774734036) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604104127314.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-mZsFvwyd-1622774734037) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604104158300.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-pvls7Mp3-1622774734037) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604104216343.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-oJ6MMCgj-1622774734038) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604104227438.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-kMpUzxF7-1622774734039) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604104241578.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-r6xcuEQC-1622774734039) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604104318389.png)]
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-0vvQEIps-1622774734040) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604104337917.png)]
③ Delete the trigger
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-H7Fb3dqC-1622774734041) (C:\Users\Leizi of the official second\Desktop\Future village chief\image -20210604104402611.png)]