Information Security Roles

Two entities have been added to QHB: the DBMS Administrator role and Database Administrator role, which are implemented as shared catalogs (schema pg_catalog tables):

qhb_dbms_admin for DBMS administrators;
qhb_db_admin for specific database administrators.

Information About Information Security Administrator Roles

Availability of DBMS administrators and specific database administrators roles in the system allows you to assign Information Security Administrators, not including the superuser attribute on the corresponding entries in the user and role catalog table pg_authid.

Information Security Administrator Privileges

DBMS Object	DBMS Administrator	Database Administrator
Database table	Default privileges	Full privileges in the managed database: insert, read, edit, delete
Sequence	Default privileges	Full privileges in the managed database: use, read, edit
Database	Has the right to create databases	Full privileges in the managed database: create objects, login, work with the scheduler
Foreign data wrapper	Default privileges	Privilege to use in the managed database
Functions/procedures	Default privileges	Privilege to use in the managed database
Procedure language	Default privileges	Privilege to use in the managed database
Large objects	Default privileges	Full privileges in the managed database: read and modify
`ALTER SYSTEM`	Full privileges in DBMS	Default privileges
Scheme	Default privileges	Full privileges in the managed database: use and create
Tablespace	Full privileges to create	Default privileges
Data type	Default privileges	Full privileges in the managed database, including creation

Implementation Features

In this implementation, the assignment of the DBMS administrator and the administrator of specific databases roles is performed individually for each user, bypassing the hierarchy of roles in the DBMS. It is impossible to assign the roles of information security administrators to group DBMS roles.

Assigning Information Security Roles

The superuser has the privileges to assign DBMS administrators and specific database administrators using built-in procedures.

Assigning a DBMS Administrator

The built-in procedure qhb_make_dbms_admin allows you to assign a user as one of the DBMS administrators, which will grant him additional privileges.

SELECT qhb_make_dbms_admin(<user_oid>);

To pass a user ID as a parameter to qhb_make_dbms_admin function, you should find the oid value in the pg_authid catalog that matches to the required user name. For example, using this SQL query:

SELECT oid FROM pg_authid WHERE rolname LIKE '<username>';

Superusers have the privileges to assign a user as a DBMS administrator.

There are no privileges to assign a user as a DBMS administrator:

for ordinary users;
for DBMS and database administrators who are not superusers.

Assigning a Database Administrator

The built-in procedure qhb_make_db_admin allows you to assign a user as one of the given database administrators, which will grant him additional privileges for the selected database and its objects.

SELECT qhb_make_db_admin(<user_oid>, <database_oid>);

To pass a user ID as a parameter to qhb_make_db_admin function, you should find the oid value in pg_authid catalog that matches to the required user name. For example, using this SQL query:

SELECT oid FROM pg_authid WHERE rolname LIKE '<username>';

Similarly, you can find the database identifier to pass as a parameter using the following SQL query:

SELECT oid FROM pg_database WHERE datname LIKE '<database_name>';

The privileges to assign a user as a database administrator have:

superusers;
DBMS administrators.

There are no privileges to assign a user as a database administrator:

for ordinary users;
for database administrators who are not superusers.

SQL Syntax for Managing Administrator Roles

In addition to managing administrator roles via the specified procedures, SQL syntax is provided in the form of the CREATE QHBSECUREROLE and DROP QHBSECUREROLE commands.

Views for Examining Current Settings

role_db_admins and role_dbms_admins SQL views are available.

The role_db_admins view allows the DBMS administrator to view the current DBMS role assignments for users in the system. The DBMS administrator will see his own assignment.
The role_dbms_admins view allows the DBMS administrator to view the current DBMS administrator role assignments for users of the system.

Example of the view using:

SELECT * FROM role_db_admins; -- examination database administrators
SELECT * FROM role_dbms_admins; -- examination DBMS administrators

Examination Current DBMS and Database Administrators

Current information security administrators are displayed in the tables of the shared catalogs qhb_dbms_admin and qhb_db_admin.

You can execute the following queries using built-in procedures to determine the current user status:

SELECT qhb_is_dbms_admin(<user_oid>);

and

SELECT qhb_is_db_admin(<user_oid>, <database_oid>);

Deleting Information Security Administrator Roles

To cancel the assignment of the DBMS administrator and database administrator roles, you should use the qhb_drop_dbms_admin and qhb_drop_db_admin procedures.

To delete the assignment of a user as a DBMS administrator, you can use the query:

SELECT qhb_drop_dbms_admin(<user_oid>);

To remove a user's assignment as a specific database administrator, you can use the query:

SELECt qhb_drop_db_admin(<user_oid>, <database_oid>);

To pass the user ID as a parameter to qhb_drop_dbms_admin and qhb_drop_db_admin functions, you should find the oid value in pg_authid catalog that matches to the required user name. For example, using this SQL query:

SELECT oid FROM pg_authid WHERE rolname LIKE '<username>';

Similarly, you can find the database identifier to pass as a parameter using the following SQL query:

SELECT oid FROM pg_database WHERE datname LIKE '<database_name>';