Information schema for object_privileges

The information_schema.object_privileges view provides information about permissions granted to each role.

To be able to access this information, you must have role privileges, ownership of the role, ownership of the object to which the role is granted, or be the member of the role.

All object privileges are explicitly listed in information_schema.object_privileges. If the ANY privilege is granted on an object, it also shows the corresponding privilege on the object’s descendants.

For example, if you grant USAGE ANY ENGINE privilege on the account, you will be able to see USAGE privilege for all of the engines in the account.

The following code example creates two engines, a role, and grants that role permission to use any engine in the specified account:

CREATE ENGINE engine1;
CREATE ENGINE engine2;
CREATE ROLE developer_role;
GRANT USAGE ANY ENGINE ON ACCOUNT account_name TO developer_role;

Then, the following code example retrieves all privileges granted to the developer_role on Firebolt objects, showing the grantor, grantee, object name, object type, and privilege type:

SELECT
  grantor, grantee, object_name, object_type, privilege_type
FROM
  information_schema.object_privileges
WHERE 
  grantee = 'developer_role';

grantor	grantee	object_name	object_type	privilege_type
admin_user	role_name	account_name	account	USAGE ANY ENGINE
admin_user	role_name	engine1	engine	USAGE
admin_user	role_name	engine2	engine	USAGE

View account, role, user, engine, and database permissions

To view account, role, user, engine and database permissions, make sure that current database is not selected. Then, query the information_schema.object_privileges view as shown in the following examples:

View privileges directly under an account

To view all privileges directly under an account, ensure that no database is selected, and query the information_schema as follows:

SELECT
  *
FROM
  information_schema.object_privileges;

You can also deselect the current database in the Firebolt Develop Space user interface (UI), by choosing None in the current database selector.

View privileges in a specific database

To view all privileges under a user defined database db, specify the database in the query as follows:

SELECT
  *
FROM
  db.information_schema.object_privileges;

View object permissions in the current database

When the current database is selected,information_schema.object_privileges only shows permissions for objects within that database. It does not show permissions for accounts, roles, users, engines, databases, and objects in other databases.

To view permissions for schemas, tables and views in the current database, set the current database with USE DATABASE, then select and view privileges in a query as follows:

USE DATABASE db;

SELECT
  *
FROM
  information_schema.object_privileges;

You can also use the database selector in the UI.

Columns in information_schema.object_privileges

Each row in information_schema.object_privileges contains the following columns:

Column Name	Data Type	Description
grantor	TEXT	The name of the user that granted the privilege.
grantee	TEXT	The name of the role that the privilege was granted to.
object_catalog	TEXT	The database containing the object on which the privilege is granted.
object_schema	TEXT	The schema containing the object on which the privilege is granted.
object_name	TEXT	The name of the object on which the privilege is granted.
object_type	TEXT	The type of the object on which the privilege is granted.
privilege_type	TEXT	The type of the privilege granted on the object.
is_grantable	TEXT	Specify YES if the privilege is grantable, and NO otherwise.
created	TIMESTAMPTZ	The creation time of the privilege.