19
Access control
⚫ Manage users' access control rights to the database, including database system rights and object rights.
⚫ Support role-based access control mechanism, associate roles and permissions, and manage user access control permissions by assigning permissions to corresponding roles and then granting roles to users. Among them, the login access control is realized through user identification and authentication technology, and the object access control is based on the user's rights on the object, and the object access control is realized through the object rights inspection. Users are assigning the relevant database users the minimum privileges required to complete their tasks to minimize the risk of database usage.
⚫ Supports the access control model of three rights separation authority, database roles can be divided into system administrator, security administrator and audit administrator. The security administrator is responsible for creating and managing users, the system administrator is responsible for granting and revoking user rights, and the audit administrator is responsible for auditing the behavior of all users.
⚫ By default, a role-based access control model is used. Customers can choose whether to open the three-weight separation control model by setting parameters.
role-based access control
⚫ What is role-based user management?
The role-based user management (Role-Based Access Control, referred to as RBAC) is to assign permissions to roles, and users get the permissions of these roles by becoming appropriate roles.
Using RBAC can greatly simplify the management of permissions.
⚫ What is the RBAC model?
Give the role the appropriate permissions.
Assign the user to the corresponding role.
⚫ RBAC authorization is actually a relationship between Who, What, and How triples.
Who: the owner or subject of the permission (such as a user);
What: the object (such as table, function) for which the permission is directed;
How: specific permissions (positive authorization, negative authorization).
⚫ The relationship between users, roles, and permissions in the RBAC model.
A user can correspond to multiple roles;
A role can correspond to multiple users;
A role can have multiple permissions;
A privilege can be assigned to many roles.
⚫ Other access control models Access control lists (ACL)
Attribute-Based access control (ABAC)
Policy-Based Access Control (PBAC)
⚫ Features and advantages of RBAC
Indirect relationship
Separation of duties
Easy authorization management
Can support the principle of least privilege, separation of responsibilities, and data abstraction
Row-level access control
⚫ The row-level access control feature makes the database access control precise to the row level of the data table, so that the database can achieve the capability of row-level access control.
⚫ Different users execute the same SQL query operation, and the read results are different.
⚫ Users can create a row access control (Row Level Security) policy in the data table, which is an expression that takes effect for a specific database user and specific SQL operations:
When the database user accesses the data table, if the SQL satisfies the specific Row Level Security policy of the data table, in the query optimization stage, the expressions that meet the conditions will be spliced by AND or OR according to the attribute (PERMISSIVE | RESTRICTIVE) type, and applied. to the execution plan.
⚫ The purpose of row-level access control is to control the visibility of row-level data in the table. By pre-defining Filter on the data table, the expression that meets the conditions is applied to the execution plan in the query optimization stage, which affects the final execution result.
⚫ Currently affected SQL statements include SELECT, UPDATE, DELETE.
⚫ Turn on the row access control policy switch:
ALTER TABLE tablename ENABLE ROW LEVEL SECURITY;
⚫ Create a row access control policy, the current user can only view the user's own data:
CREATE ROW LEVEL SECURITY POLICY tablename_rls ON tablename USING(role = CURRENT_USER);
Note: tablename is the name of the created table, and tablename_rls is the name of the created row-level access control policy.
