openGauss Separation of Duties
Tags: opengauss
Published: 2023-03-21 02:57:16

Descriptions in Default Permission Mechanism and Administrator are about the initial situation after the database system is created. By default, a system administrator with the SYSADMIN attribute has the highest-level permissions.

In actual service management, you can set separation of duties to prevent system administrators from having excessive centralized permissions, which may cause high risks. Some permissions of the system administrator are transferred to the security administrator and audit administrator, implementing separation of duties among the system administrator, security administrator, and audit administrator.

After separation of duties is enabled, a system administrator does not have the CREATEROLE attribute (security administrator) and AUDITADMIN attribute (audit administrator). That is, the system administrator does not have the permissions to create roles and users and the permissions to view and maintain database audit logs. For details about the CREATEROLE and AUDITADMIN attributes, see CREATE ROLE.

After separation of duties is enabled, system administrators have the permissions only for the objects owned by them.

Separation of duties does not take effect for an initial user. Therefore, you are advised to use an initial user as a database administrator only for database management other than service running.

To enable separation of duties, set enableSeparationOfDuty to on.

For details about permission changes before and after enabling separation of duties, see Table 1 and Table 2.

Table 1 Default user permissions


Object Name

Initial User (ID: 10)

System Administrator

Security Administrator

Audit Administrator

Common User

Tablespaces

Has all permissions except the one to access private tables.

Can create, modify, delete, access, or grant permissions for tablespaces.

Cannot create, modify, delete, or grant permissions for tablespaces and can access tablespaces if the access permission is granted.



Tables

Has permissions for all tables.

Has all permissions for their own tables, but does not have permissions for other users' tables.



Indexes

Can create indexes on all tables.

Can create indexes on their own tables.



Schemas

Has all permissions for all schemas except dbe_perf.

Has all permissions for their own schemas, but does not have permissions for other users' schemas.



Functions

Has all permissions for all functions except those in the dbe_perf schema.

Has permissions for their own functions, has the call permission for other users' functions in the public schema, but does not have permissions for other users' functions in other schemas.



Customized views

Has all permissions on all views except the dbe_perf schema view.

Has permissions for their own views, but does not have permissions for other users' views.



System catalogs and system views

Has permissions to query all system catalogs and views.

Has permissions to query only some system catalogs and views. For details, see System Catalogs and System Views.



Table 2 Changes in permissions after separation of duties


Object Name

Initial User (ID: 10)

System Administrator

Security Administrator

Audit Administrator

Common User

Tablespaces

N/A

Has all permissions except the one to access private tables.

N/A

N/A

Tables

Permissions reduced

Has all permissions for their own tables and other users' tables in the public schema, but does not have permissions for other users' tables in other schemas.

N/A

Indexes

Permissions reduced

Can create indexes for their own tables and other users' tables in the public schema.

N/A

Schemas

Permissions reduced

Has all permissions for their own schemas, but does not have permissions for other users' schemas.

N/A

Functions

Permissions reduced

Has all permissions for their own functions and other users' functions in the public schema, but does not have permissions for other users' functions in other schemas.

N/A

Customized views

Permissions reduced

Has all permissions for their own views and other users' views in the public schema, but does not have permissions for other users' views in other schemas.

N/A

System catalogs and system views

N/A

N/A

 Notice:

PG_STATISTIC system table and PG_STATISTIC_EXT system table store some sensitive information of statistical objects, such as high frequency value MCV. After the separation of powers is carried out, the system administrator can still obtain the information in the statistical information by accessing the two system tables.