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.
Latest comments (0)