Database Study Notes

Database Study Notes (2)

1. Database Security

1.1 TCSEC/TDI security level classification

B1 level: "secure" or "trusted" products, truly secure products

1.2 Database management system security control model

Access control process:

  1. The database management system authenticates the database users who make SQL access requests to prevent untrustworthy users from using the system.
  2. Discretionary access control, mandatory access control, and even speculative access control at the SQL processing layer
  3. It can also audit user access behavior and key system operations, and perform simple intrusion detection on abnormal user behavior

1.3 Common methods of database security control

  1. User Identification and Authentication
  2. access control
  3. view
  4. audit
  5. data encryption

1.4 Database Security Control

1.4.1 User identity authentication

  • Static password authentication
  • Dynamic password authentication
  • biometric authentication
  • smart card authentication

1.4.2 Access Control

Discretionary access control:

  • C2 level
  • Users have different access rights to different data objects
  • Different users have different permissions on the same object
  • Users can grant the access they have to other users

Mandatory Access Control:

  • B1 level
  • Each data object is marked with a certain security level
  • Each user is also granted a certain level of license
  • For each object, only users with valid permissions can access it

1.4.3 Authorization: Grant and Revocation

grant

Format:

grant <permissions>[, <permissions>]...
on <object type> <object name>[, <object type> <object name>]...
to <user>[, <user>]...
[with grant option]	# If this is specified, it means that the permission can be granted again. If it is not specified, it will not work.
copy

example:

grant select
on table Student
to U1;	# Grant the permission to query the Student table to user U1

grant all priviliges
on table Student, Course
to U2, U3; # Grant all permissions on the Student table and Course table to users U2 and U3

grant select
on table Student
to public;	# Grant permission to query the Student table to all users

grant update(Sno), select
on table Student
to U4	# Grant the permission to query the Student table and modify the student ID to user U4
with grant option; # Allow user U4 to grant this permission to other users

grant select
on table Student
to U5;	# User U4 can grant his select permission to U5, and U5 can no longer propagate this permission
copy

revoke

Format:

revoke <permissions>[, <permissions>]...
on <object type> <object name>[, <object type> <object name>]...
from <user>[, <user>]...[cascade | restrict]
copy

example:

revoke select
on table Student
from public； # Take back all users' query rights to the standard Student

revoke select
on table Student
from U4 cascade; # When withdrawing the select authority of user U5, use cascade if necessary, otherwise refuse to execute the statement, because user U4 has already granted the select authority to U5, and when withdrawing the select authority of U4, cascade is required to withdraw the authority of user U5 , because U5's authority comes from U4,U4 is gone, U5 will naturally be gone
copy

DBA:

  • has all permissions on all objects
  • Grant different permissions to different users according to the actual situation

user:

  • Have all the operation rights of the objects created by themselves
  • You can use grant to grant permissions to other users

Authorized users:

  • If you have the "Continue Authorization" permission, you can grant the obtained permissions to other users

Permission to create database schemas

The database administrator implements when creating a user

grammar:

create user <username>
[with][DBA|RESOURCE|CONNECT];
copy

CONNECT permission: Users with CONNECT permission cannot create new users, schemas, and basic tables, and can only log in to the database

RESOURCE permission: Can create basic tables and views, but cannot create schemas, new users

DBA authority: Can create new users, create schemas, create basic tables and views. The DBA has access rights to all database objects, and can also grant these rights to ordinary users

1.4.4 Database roles

Database role: a named set of permissions related to database operations

  • A role is a collection of permissions
  • A role can be created for a group of users with the same permissions to simplify the authorization process

character creation

create role <character name>;
copy

Authorize the role

grant <permissions>[, <permissions>]...
on <object type> <object name>
to <Role>[, <Role>]...
copy

Grant a role to other roles or users

grant <character 1>[, <character 2>]...
to <character 3>[, <user 1>]...
[with admin option] # If you specify with admin option, you can grant permissions to other roles or users
copy

Revocation of role permissions

revoke <permissions>[, <permissions>]...
on <object type> <object name>
from <Role>[, <Role>]...
copy

example:

create role R1;  # Create a role R1

grant select, update, insert
on table Student
to R1;			# Grant select, update, and insert permissions to the Student table to role R1

grant R1
to clz, czh;	# Grant role R1 to clz and czh, so that they have all the permissions included in role R1

revoke R1
from clz;		# Take back these 3 permissions of clz at one time through R1

grant delete
on table Student
to R1;			# Make the role R1 increase the delete permission of the Student table on the original basis

revoke select
on table Student
from R1;		# Take back the select permission of R1
copy

1.4.4 Mandatory Access Control Methods

Mandatory Access Control (MAC):

  • Guaranteed a higher degree of security
  • Users cannot directly perceive or control
  • Applicable to departments with strict and fixed classification of data

In mandatory access control, all entities managed by the database management system are divided into two categories: subject and object.

A principal is an active entity in the system

  • Includes actual users managed by the database management system
  • Also includes individual processes representing users

Objects are passive entities in the system that are controlled by the subject

  • Files, base tables, indexes, views, etc.

Sensitivity flag

  • For subjects and objects, the DBMS assigns each instance of them a sensitivity flag
  • Sensitivity flags can be divided into several levels
    • Top Secret (TS)
    • Confidential (S)
    • credible (C)
    • Public (P)

    TS>=S>=C>=p

  • A principal's sensitivity flag is called a license level
  • Classification

Mandatory access control rules:

  • Only if the permission level of the subject is greater than or equal to the confidentiality level of the object, the subject can read the corresponding object
  • Only if the permission level of the subject is less than or equal to the security level of the object, the subject can write the corresponding object

Autonomous access control and mandatory access control together constitute the security mechanism of database management system

  • The autonomous access control check (DAC check) is performed first, and the data objects that pass the autonomous access control check are then subjected to a mandatory access control check (MAC check) by the system. Only data objects that pass the MAC check can be accessed.

1.5 View Mechanism

  • Hide the data to be kept secret from users who do not have the right to access the data, and provide a certain degree of security protection for the data
  • Indirect implementation of user rights definitions that support access predicates

example:

create view CS_Student
as
select * from Student
where Sdept = 'CS';	# Build a view of computer science students

grant select
on CS_Student
to clz;

grant all priviliges
on CS_Student
to czh;				# Further define access rights on the view
copy

1.6 Audit

concept:

  • Enable a dedicated audit log to record all user operations on the database
  • Auditors can use audit records to monitor various behaviors in the database and find out who, when and what is illegally accessing data
  • DBMS with a security level above C2 must have an audit function

Only those with a high level of confidentiality need to set up the audit function, because auditing is time-consuming and space-consuming, and will increase a lot of overhead

User level auditing:

  • Any user configurable auditing
  • Mainly users audit the database tables and views created by themselves

System-level auditing:

  • Can only be set by the database administrator
  • Can monitor successful or failed login requests, monitor grant and revoke operations, and other operations under database privileges

1.6.1 audit statement

audit statement: set the audit function

audit alter, update
on Student;		# Audit the operation of modifying the Student table structure or modifying table data
copy

1.6.2 noaudit statement

noaudit statement: cancel the audit function

noaudit alter, update
on Student;		# Cancel the audit of the Student table
copy

1.7 Data Encryption

1.7.1 Storage Encryption

Transparent storage encryption:

  • Kernel-level encryption protection is completely transparent to users
  • The data is encrypted when it is written to the disk, and it is decrypted when the authorized user reads the data
  • The management application program of the database does not need to make any modifications, only need to specify the fields to be encrypted in the table creation statement

Good performance, high security completeness

Non-transparent storage encryption:

  • Implemented through multiple encryption functions

1.7.2 Transmission Encryption

Link encryption:

  • Encryption at the link layer
  • The transmission information consists of two parts: the header and the message
  • Both message and header are encrypted

End-to-end encryption:

  • Encrypted at the sender, decrypted at the receiver
  • Only encrypt the message, not the header
  • It is easy to be discovered by illegal monitors and obtain sensitive information from them

Tags: Database SQL

Posted by speedyslow on Mon, 02 Jan 2023 10:48:29 +0300

Hot Tags

Java Python Javascript Algorithm Front-end Linux C++ data structure Spring Back-end Spring Boot MySQL Database C Android Design Pattern Vue Vue.js Docker leetcode Go Machine Learning Big Data programming language Deep Learning Operation & Maintenance Redis Interview Programming Kubernetes html AI C# SQL css
©2023 Algorithm Zone