SYSMONITOR Role

An organization's database administrator may have many responsibilities outside of maintaining Vertica as a DBADMIN user. In this case, as the DBADMIN you may want to delegate some Vertica administrative tasks to another Vertica user.

The DBADMIN can assign a delegate the SYSMONITOR role to grant access to system tables without granting full DBADMIN access.

The SYSMONITOR role provides the following privileges.

View all system tables that are marked as monitorable. You can see a list of all the monitorable tables by issuing the statement:

=> select * from system_tables where is_monitorable='t';
If WITH ADMIN OPTION was included when granting SYSMONITOR to the user or role, that user or role can then grant SYSMONITOR privileges to other users and roles.

Grant a SYSMONITOR Role

To grant a user or role the SYSMONITOR role, you must be one of the following:

a DBADMIN user
a user assigned the SYSMONITOR who has the ADMIN OPTION

Use the GRANT (Role) SQL statement to assign a user the SYSMONITOR role. This example shows how to grant the SYSMONITOR role to user1 and includes administration privileges by using the WITH ADMIN OPTION parameter. The ADMIN OPTION grants the SYSMONITOR role administrative privileges.

=> GRANT SYSMONITOR TO user1 WITH ADMIN OPTION;

This example shows how to revoke the ADMIN OPTION from the SYSMONITOR role for user1:

=> REVOKE ADMIN OPTION for SYSMONITOR FROM user1;

Use CASCADE to revoke ADMIN OPTION privileges for all users assigned the SYSMONITOR role:

=> REVOKE ADMIN OPTION for SYSMONITOR FROM PUBLIC CASCADE;

Example

This example shows how to:

Create a user
Create a role
Grant SYSMONITOR privileges to the new role
Grant the role to the user

=> CREATE USER user1;
=> CREATE ROLE monitor;
=> GRANT SYSMONITOR to monitor;
=> GRANT monitor to user1;

Assign SYSMONITOR Privileges

This example uses the user and role created in the Grant SYSMONITOR Role example and shows how to:

Create a table called personal_data
Log in as user1
Grant user1 the monitor role. (You already granted the monitor SYSMONITOR privileges in the Grant a SYSMONITOR Role example.)
Run a SELECT statement as user1

The results of the operations are based on the privilege already granted to user1.

=> CREATE TABLE personal_data (SSN varchar (256));
=> \c -user1;
user1=> SET ROLE monitor;
user1=> SELECT COUNT(*) FROM TABLES;
 COUNT
 -------
 1
(1 row)

Because you assigned the SYSMONITOR role, user1 can see the number of rows in the Tables system table. In this simple example, there is only one table (personal_data) in the database so the SELECT COUNT returns one row. In actual conditions, the SYSMONITOR role would see all the tables in the database.

Check if a Table is Accessible by SYSMONITOR

Use the following command to check if a system table can be accessed by a user assigned the SYSMONITOR role:

=> select table_name, is_monitorable from system_tables where table_name='<table_name>';

Example

This example checks whether the current_session system table is accessible by the SYSMONITOR:

=> select table_name, is_monitorable from system_tables where table_name='current_session';
table_name       | is_monitorable
--------------------------------
current_session  | t

The t in the is_monitorable column indicates the current_session system table is accessible by the SYSMONITOR.