Database Privileges

The Privileges tab on the Table Details page allows you to view what privileges users have on a table and its schema. Database privileges determine what types of actions a user is able to perform on tables and schemas in a database. For example, if you want to run a query like “SELECT * FROM demo.user_table”, you must have the USAGE privilege on the schema “demo” and you must have the SELECT privilege on the table “user_table”.

How to Access Database Privileges 

You can access the Data Catalog by clicking on the database icon in the navigation pane on the left. 

1. Select your desired Table from the Data Catalog to open the Data Pane on the left hand side.
2. Next, click “View Table Details” (highlighted in red in the screenshot below). This will open the Table Details Page.
3. Your table permissions are listed on the “Privileges” tab (highlighted in red in the screenshot below). 

About Database Privileges

Privileges can be granted to individual users, to groups, or to all users on a cluster via the PUBLIC keyword. Privileges granted to a group are available to all members of that group. Please note that groups in your database cluster are not tied to Platform groups.

Table and schema owners, superusers, and users who have been given explicit granting privileges are all able to grant privileges to other users. For each privilege type, Table Details shows which users are able to grant that privilege to other users. For more about how to grant privileges, see Granting Permissions to Database Objects.

The below screenshot shows an example of database privileges. The top table shows privileges on the schema and the bottom table shows privileges on the table. Each row lists a user or database group which has privileges on the table or schema. Each column indicates whether the user or group has a given privilege, and whether the user or group can grant that privilege. The “Privilege Source” column shows whether the user was granted the privilege directly, or if they inherit the privilege from a database group which they belong to. Users may appear in the table multiple times if they receive privileges from multiple sources, e.g. a group and a direct grant, or two different groups. 

To learn about best practices for organizing database privileges using groups, see Managing Database Permissions.

For more information about privileges and granting, please see the relevant external documentation for Redshift or Postgres.

Deactivated Database Users

The database privileges view indicates expired database users by displaying “(deactivated)” after their username. Expired users are hidden by default, and can be shown by clicking the
“Show Deactivated Users” checkbox (highlighted in red in the screenshot below).

Specifically, “deactivated” status refers to database users with an expired password. Database users are distinct from Platform users, and as such password expiration for database users is not tied to password expiration for Platform users. Unlike Platform passwords which expire yearly, database passwords only expire if an expiration date has been set. By default, database passwords have no expiration date, and are valid indefinitely.

When a Platform user is deactivated, Platform will automatically expire the associated database user’s password for most databases, thereby deactivating the database user. If Platform cannot automatically expire the database user, then the Organization’s admins will be informed via email, so that they can take action manually. Database superusers are able to manually set password expiration dates; instructions can be found here for Redshift and Postgres.

Expired vs. Dropped Database Users

Expiration is different from dropping a user entirely. Expired users still exist in the database, but are unable to login, hence we use the term “deactivated” to describe them.

Dropped users are removed entirely from the database and therefore will not appear in the database privileges view at all. If you want a user to be completely removed from the database and no longer appear in the database privileges view, they must be dropped by a superuser. Instructions on dropping users can be found here for Redshift and Postgres.