Database-Database role

Database Roles

Database role: A named set of permissions related to database operations
A role is a collection of permissions
You can create a role for a group of users with the same permissions
The process of simplifying authorization

First, the creation of the role

CREATE  ROLE  

Second, to the role of authorization

GRANT  < permissions >[,< permissions; 
 On < object type > object name to  
 < role >[,< role;

Third, grant a role to other roles or users

GRANT  < role 1>[,< role 2>] ...
To  < role 3>[,< user 1>] ... 

Iv. Recovery of role privileges

REVOKE < permissions >[,< permissions;
On < object type > < object name > from
< role >[,< role;

[Example 11] a role is implemented to grant a set of permissions to a user. The
steps are as follows:
1. Start by creating a role R1 create role  R1;
2. Then use the GRANT statement to make the role R1 the Select, UPDATE, and insert permissions
    of the student table GRANT Select,update,insert on 
    TABLE Student to 
    R1;

3. Give this role to Wang Ping, Zhang Ming, zhaoling. So that they have all the rights that the role R1 contains.
    GRANT  R1 to 
    Wang Ping, Zhang Ming, zhaoling;
4. Can be recycled through R1 at once Wang Ping's 3 permissions
     REVOKE  R1 from 
     Wang Ping;

[Example 12] permissions modification of the role
      GRANT DELETE on 
      TABLE Student to
      R1

[Example]
    REVOKE SELECT on 
    TABLE Student
    from  R1;

There may be an "unintentional disclosure" of data
: This mechanism only uses access to data to secure control, and the data itself is not security tagged
resolution: Enforce access control strategy for all subject and object under System control

forced access Control (MAC)

Ensure a higher level of security
Whether the user can directly perceive or control
Suitable for departments with strict and fixed classification of data
Military sector
Government Departments
The subject is the active entity in the system
Actual users managed by the DBMS
Processes that represent users

The object is the passive entity in the system, which is manipulated by the main body.
File
base table
Index
View

Sensitivity mark (label)
Top Secret (Secret)
Confidential (Secret)
Trusted (confidential)
public

The sensitivity of the body is labeled as the license level (clearance levels)
The sensitivity of the object is labeled as a classification (classification level)
Mandatory access control rules
(1) Only if the subject's license level is greater than or equal to the object's classification, the subject can read the corresponding object
(2) The subject can write the corresponding object only when the license level of the subject is equal to the category of the object.
Remediation Rules
The license level of the subject <= object of the subject can write the object
The common denominator of rules
A lower number of principals with high license levels are prohibited
According to the object
DAC and Mac form the security mechanism of DBMS
Implement a DAC first when implementing a Mac
Cause: Security security at a higher level includes all protection at a lower level
Keep confidential data hidden from users who do not have access to the data and provide a level of security for the data
The main function is to provide data independence, can not fully meet the requirements
Indirect implementation of user permission definitions that support access predicates

[Example 14] Establish a view of a computer department student, grant the SELECT permission to Wang Ping to the view, and grant all operational rights on that view to Zhang Ming 

         first establish the view of the computer department student cs_student
              CREATE VIEW cs_student
    As 
    SELECT  *
    from   Student
    WHERE  sdept= ' CS ';

Further defining access rights on the view
     Grant  SELECT
     on  cs_student to  
     Wang Ping;

     Grant all
     priviliges  on Cs_student  
     to  Zhang Ming; 

What is Audit
audit log (Audit log)
   records all operations of a user on a database above the
DBA uses the audit log to
   identify the person, time, and content that illegally accesses the data
C2 the DBMS above the security level must have

Audits are divided into
user-level audits that
audit the database tables or views that you create to 
record all users ' success and/or unsuccessful access requirements for these tables or views, and various types of SQL Operations 
System-level audit 
DBA settings 
Monitoring for successful or failed login requirements 
monitoring of grant and revoke operations and other database-level permissions

Audit statement: Set Audit function 

Noaudit statement: Canceling audit function 

[Example 15] to modify the SC table structure or modify the SC table data operation to audit
           AUDIT alter,update on  SC;

[Example 16] Cancel all audits of the SC table
           noaudit  Alter,update  
           on  SC;

Data Encryption

Effective means of preventing data from being compromised in storage and transmission in database

The basic idea of encryption

Encryption method
Replacement method
Replacement method
Blending methods

Data encryption Statistics Database security in DBMS

Statistics Database
Allows users to query information about the aggregation type (such as totals, averages, etc.)
Do not allow querying of individual record information

Special security issues in the statistical database
Covert information Channels
The ability to derive illegal information from legitimate queries.

Rule 1: Any query involving at least N (n large enough) more than one record
Rule 2: Intersect data items for any two queries cannot exceed M
Rule 3: The number of queries for any one user cannot exceed 1+ (N-2)/M
The design objectives of the database security mechanism are:
The cost >> benefits of trying to undermine the safety of the person

Data sharing is increasing, and data security is becoming more and more important.
DBMS is the core of management data, so it must have a complete and effective security mechanism TCSEC and CC

The technology and method of realizing the security of database system
Access Control Technology
View Technology
Audit technology
Autonomous access control function
Implemented by the GRANT statement and the REVOKE statement of SQL
Role
Using roles to manage database permissions can simplify the authorization process
Create ROLE statement creating roles
Grant statement to role authorization