# Roles and Privileges

HEAVY.AI supports data security using a set of database object access privileges granted to users or roles.

## Users and Privileges

When you create a database, the `admin` superuser is created by default. The `admin` superuser is granted all privileges on all database objects. Superusers can create new users that, by default, have no database object privileges.

Superusers can grant users selective access privileges on multiple database objects using two mechanisms: role-based privileges and user-based privileges.

### Role-based Privileges

1. Grant roles access privileges on database objects.
2. Grant roles to users.
3. Grant roles to other roles.

### User-based Privileges

When a user has privilege requirements that differ from role privileges, you can grant privileges directly to the user. These mechanisms provide data security for many users and classes of users to access the database.

You have the following options for granting privileges:

* Each object privilege can be granted to one or many roles, or to one or many users.
* A role and/or user can be granted privileges on one or many objects.
* A role can be granted to one or many users or other roles.
* A user can be granted one or many roles.

This supports the following many-to-many relationships:

* Objects and roles
* Objects and users
* Roles and users

These relationships provide flexibility and convenience when granting/revoking privileges to and from users.

Granting object privileges to roles and users, and granting roles to users, has a cumulative effect. The result of several grant commands is a combination of all individual grant commands. This applies to all database object types and to privileges inherited by objects. For example, object privileges granted to the object of database type are propagated to all table-type objects of that database object.

### Who Can Grant Object Privileges?

Only a superuser or an object owner can grant privileges for on object.

* A superuser has all privileges on all database objects.
* A non-superuser user has only those privileges on a database object that are granted by a superuser.
* A non-superuser user has `ALL` privileges on a table created by that user.

### Roles and Privileges Persistence

* Roles can be created and dropped at any time.
* Object privileges and roles can be granted or revoked at any time, and the action takes effect immediately.
* Privilege state is persistent and restored if the HEAVY.AI session is interrupted.

### Database Object Privileges

There are five database object types, each with its own privileges.

<Tabs>
  <Tab title="Database">
    ACCESS - Connect to the database. The ACCESS privilege is a prerequisite for all other privileges at the database level. Without the ACCESS privilege, a user or role cannot perform tasks on any other database objects.

    ALL - Allow all privileges on this database except issuing grants and dropping the database.

    SELECT, INSERT, TRUNCATE, UPDATE, DELETE - Allow these operations on any table in the database.

    ALTER SERVER - Alter servers in the current database.

    CREATE SERVER - Create servers in the current database.

    CREATE TABLE - Create a table in the current database. (Also CREATE.)

    CREATE VIEW - Create a view for the current database.

    CREATE DASHBOARD - Create a dashboard for the current database.

    DELETE DASHBOARD - Delete a dashboard for this database.

    DROP SERVER - Drop servers from the current database.

    DROP - Drop a table from the database.

    DROP VIEW - Drop a view for this database.

    EDIT DASHBOARD - Edit a dashboard for this database.

    SELECT VIEW - Select a view for this database.

    SERVER USAGE - Use servers (through foreign tables) in the current database.

    VIEW DASHBOARD - View a dashboard for this database.

    VIEW SQL EDITOR - Access the SQL Editor in Immerse for this database.

    <Info>
      Users with SELECT privilege on views do not require SELECT privilege on underlying tables referenced by the view to retrieve the data queried by the view. View queries work without error whether or not users have direct access to referenced tables. This also applies to views that query tables in other databases.

      To create views, users must have SELECT privilege on queried tables in addition to the CREATE VIEW privilege.
    </Info>
  </Tab>

  <Tab title="Table">
    SELECT, INSERT, TRUNCATE, UPDATE, DELETE - Allow these SQL statements on this table.

    DROP - Drop this table.

    <Info>
      Users with SELECT privilege on views do not require SELECT privilege on underlying tables referenced by the view to retrieve the data queried by the view. View queries work without error whether or not users have direct access to referenced tables. This also applies to views that query tables in other databases.

      To create views, users must have SELECT privilege on queried tables in addition to the CREATE VIEW privilege.
    </Info>
  </Tab>

  <Tab title="View">
    SELECT - Select from this view. Users do not need privileges on objects referenced by this view.

    DROP - Drop this view.

    <Info>
      Users with SELECT privilege on views do not require SELECT privilege on underlying tables referenced by the view to retrieve the data queried by the view. View queries work without error whether or not users have direct access to referenced tables. This also applies to views that query tables in other databases.

      To create views, users must have SELECT privilege on queried tables in addition to the CREATE VIEW privilege.
    </Info>
  </Tab>

  <Tab title="Dashboard">
    VIEW - View this dashboard.

    EDIT - Edit this dashboard.

    DELETE - Delete this dashboard.
  </Tab>

  <Tab title="Server">
    DROP - Drop this server from the current database.

    ALTER - Alter this server in the current database.

    USAGE - Use this server (through foreign tables) in the current database.
  </Tab>
</Tabs>

Privileges granted on a database-type object are inherited by all tables of that database.

## Privilege Commands

| SQL                                                                                       | Description                                                     |
| ----------------------------------------------------------------------------------------- | --------------------------------------------------------------- |
| [CREATE ROLE](/installation-and-configuration/security/roles#create-role)                 | Create role.                                                    |
| [DROP ROLE](/installation-and-configuration/security/roles#drop-role)                     | Drop role.                                                      |
| [GRANT](/installation-and-configuration/security/roles#grant)                             | Grant role to user or to another role.                          |
| [REVOKE](/installation-and-configuration/security/roles#revoke)                           | Revoke role from user or from another role.                     |
| [GRANT ON TABLE](/installation-and-configuration/security/roles#grant-on-table)           | Grant role privilege(s) on a database table to a role or user.  |
| [REVOKE ON TABLE](/installation-and-configuration/security/roles#revoke-on-table)         | Revoke role privilege(s) on database table from a role or user. |
| [GRANT ON VIEW](/installation-and-configuration/security/roles#grant-on-view)             | Grant role privilege(s) on a database view to a role or user.   |
| [REVOKE ON VIEW](/installation-and-configuration/security/roles#revoke-on-view)           | Revoke role privilege(s) on database view from a role or user.  |
| [GRANT ON DATABASE](/installation-and-configuration/security/roles#grant-on-database)     | Grant role privilege(s) on database to a role or user.          |
| [REVOKE ON DATABASE](/installation-and-configuration/security/roles#revoke-on-database)   | Revoke role privilege(s) on database from a role or user.       |
| [GRANT ON SERVER](/installation-and-configuration/security/roles#grant-on-dashboard)      | Grant role privilege(s) on server to a role or user.            |
| [REVOKE ON SERVER](/installation-and-configuration/security/roles#see-also-10)            | Revoke role privilege(s) on server from a role or user.         |
| [GRANT ON DASHBOARD](/installation-and-configuration/security/roles#grant-on-dashboard)   | Grant role privilege(s) on dashboard to a role or user.         |
| [REVOKE ON DASHBOARD](/installation-and-configuration/security/roles#revoke-on-dashboard) | Revoke role privilege(s) on dashboard from a role or user.      |

### Example

The following example shows a valid sequence for granting access privileges to non-superuser `user1` by granting a role to `user1` and by directly granting a privilege. This example presumes that `table1` and `user1` already exist, and that `user1` has ACCESS privileges on the database where `table1` exists.

1. Create the `r_select` role.
2. Grant the SELECT privilege on `table1` to the `r_select` role. Any user granted the `r_select` role gains the SELECT privilege.

   ```
   GRANT SELECT ON TABLE table1 TO r_select;
   ```
3. Grant the `r_select` role to `user1`, giving `user1` the SELECT privilege on `table1`.
4. Directly grant `user1` the INSERT privilege on `table1`.

   ```
   GRANT INSERT ON TABLE table1 TO user1;
   ```

See [Example Roles and Privileges Session](/installation-and-configuration/security/roles#example-roles-and-privileges-session) for a more complete example.

## CREATE ROLE

Create a role. Roles are granted to users for role-based database object access.

This clause requires superuser privilege and \<roleName> must not exist.

### Synopsis

```
CREATE ROLE <roleName>;
```

### Parameters

**\<roleName>**

Name of the role to create.

### Example

Create a payroll department role called **payrollDept**.

```
CREATE ROLE payrollDept;
```

### See Also

* [Users and Databases](/sql/data-definition-ddl/users-and-databases)
* [DROP ROLE](/installation-and-configuration/security/roles#drop-role)

## DROP ROLE

Remove a role.

This clause requires superuser privilege and \<roleName> must exist.

### Synopsis

```
DROP ROLE [IF EXISTS] <roleName>;
```

### Parameters

**\<roleName>**

Name of the role to drop.

### Example

Remove the payrollDept role.

```
DROP ROLE payrollDept;
```

### See Also

* [Users and Databases](/sql/data-definition-ddl/users-and-databases)
* [CREATE ROLE](/installation-and-configuration/security/roles#create-role)

## GRANT

Grant role privileges to users and to other roles.

<Info>
  The ACCESS privilege is a prerequisite for all other privileges at the database level. Without the ACCESS privilege, a user or role cannot perform tasks on any other database objects.
</Info>

This clause requires superuser privilege. The specified \<roleNames> and \<userNames> must exist.

### Synopsis

```
GRANT <roleNames> TO <userNames>, <roleNames>;
```

### Parameters

**\<roleNames>**

Names of roles to grant to users and other roles. Use commas to separate multiple role names.

**\<userNames>**

Names of users. Use commas to separate multiple user names.

### Examples

Assign **payrollDept** role privileges to user **dennis**.

```
GRANT payrollDept TO dennis;
```

Grant **payrollDept** and **accountsPayableDept** role privileges to users **dennis** and **mike** and role **hrDept**.

```
GRANT payrollDept, accountsPayableDept TO dennis, mike, hrDept;
```

### See Also

* [Users and Databases](/sql/data-definition-ddl/users-and-databases)
* [CREATE ROLE](/installation-and-configuration/security/roles#create-role)
* [GRANT ON TABLE](/installation-and-configuration/security/roles#grant-on-table)
* [GRANT ON DATABASE](/installation-and-configuration/security/roles#grant-on-database)

## REVOKE

Remove role privilege from users or from other roles. This removes database object access privileges granted with the role.

This clause requires superuser privilege. The specified \<roleNames> and \<userNames> must exist.

### Synopsis

```
REVOKE <roleNames> FROM <userNames>, <roleNames>;
```

### Parameters

**\<roleNames>**

Names of roles to remove from users and other roles. Use commas to separate multiple role names.

**\<userName>**

Names of the users. Use commas to separate multiple user names.

### Example

Remove **payrollDept** role privileges from user **dennis**.

```
REVOKE payrollDept FROM dennis;
```

Revoke **payrollDept** and **accountsPayableDept** role privileges from users **dennis** and **fred** and role **hrDept**.

```
REVOKE payrollDept, accountsPayableDept FROM dennis, fred, hrDept;
```

### See Also

* [Users and Databases](/sql/data-definition-ddl/users-and-databases)
* [CREATE ROLE](/installation-and-configuration/security/roles#create-role)
* [GRANT](/installation-and-configuration/security/roles#grant)

## GRANT ON TABLE

Define the privilege(s) a role or user has on the specified table. You can specify any combination of the `INSERT`, `SELECT`, `DELETE`, `UPDATE`, `DROP`, or `TRUNCATE` privilege or specify all privileges.

<Info>
  The ACCESS privilege is a prerequisite for all other privileges at the database level. Without the ACCESS privilege, a user or role cannot perform tasks on any other database objects.
</Info>

This clause requires superuser privilege, or \<tableName> must have been created by the user invoking this command. The specified \<tableName> and users or roles defined in \<entityList> must exist.

### Synopsis

```
GRANT <privilegeList> ON TABLE <tableName> TO <entityList>;
```

### Parameters

**\<privilegeList>**

| Parameter Value | Descriptions                                                           |
| --------------- | ---------------------------------------------------------------------- |
| `ALL`           | Grant all possible access privileges on \<tableName> to \<entityList>. |
| `ALTER TABLE`   | Grant ALTER TABLE privilege on \<*tableName*> to \<*entityList*>.      |
| `DELETE`        | Grant DELETE privilege on \<tableName> to \<entityList>.               |
| `DROP`          | Grant DROP privilege on \<tableName> to \<entityList>.                 |
| `INSERT`        | Grant INSERT privilege on \<tableName> to \<entityList>.               |
| `SELECT`        | Grant SELECT privilege on \<tableName> to \<entityList>.               |
| `TRUNCATE`      | Grant TRUNCATE privilege on \<tableName> to \<entityList>.             |
| `UPDATE`        | Grant UPDATE privilege on \<tableName> to \<entityList>.               |

**\<tableName>**

Name of the database table.

**\<entityList>**

Name of entity or entities to be granted the privilege(s).

| Parameter Value | Descriptions  |
| --------------- | ------------- |
| `role`          | Name of role. |
| `user`          | Name of user. |

### Examples

Permit all privileges on the `employees` table for the **payrollDept** role.

```
GRANT ALL ON TABLE employees TO payrollDept;
```

Permit SELECT-only privilege on the `employees` table for user **chris**.

```
GRANT SELECT ON TABLE employees TO chris;
```

Permit INSERT-only privilege on the `employees` table for the **hrdept** and **accountsPayableDept** roles.

```
GRANT INSERT ON TABLE employees TO hrDept, accountsPayableDept;
```

Permit INSERT, SELECT, and TRUNCATE privileges on the `employees` table for the role **hrDept** and for users **dennis** and **mike**.

```
GRANT INSERT, SELECT, TRUNCATE ON TABLE employees TO hrDept, dennis, mike;
```

### See Also

* [REVOKE ON TABLE](/installation-and-configuration/security/roles#revoke-on-table)
* [Tables](/sql/data-definition-ddl/tables)
* [CREATE ROLE](/installation-and-configuration/security/roles#create-role)
* [GRANT ON DATABASE](/installation-and-configuration/security/roles#grant-on-database)

## REVOKE ON TABLE

Remove the privilege(s) a role or user has on the specified table. You can remove any combination of the `INSERT`, `SELECT`, `DELETE`, `UPDATE`, or `TRUNCATE` privileges, or remove all privileges.

This clause requires superuser privilege or \<tableName> must have been created by the user invoking this command. The specified \<tableName> and users or roles in \<entityList> must exist.

### Synopsis

```
REVOKE <privilegeList> ON TABLE <tableName> FROM <entityList>;
```

### Parameters

**\<privilegeList>**

| Parameter Value | Descriptions                                                    |
| --------------- | --------------------------------------------------------------- |
| `ALL`           | Remove all access privilege for \<entityList> on \<tableName>.  |
| `ALTER TABLE`   | Remove ALTER TABLE privilege for \<entityList> on \<tableName>. |
| `DELETE`        | Remove DELETE privilege for \<entityList> on \<tableName>.      |
| `DROP`          | Remove DROP privilege for \<entityList> on \<tableName>.        |
| `INSERT`        | Remove INSERT privilege for \<entityList> on \<tableName>.      |
| `SELECT`        | Remove SELECT privilege for \<entityList> on \<tableName>.      |
| `TRUNCATE`      | Remove TRUNCATE privilege for \<entityList> on \<tableName>.    |
| `UPDATE`        | Remove UPDATE privilege for \<entityList> on \<tableName>.      |

**\<tableName>**

Name of the database table.

**\<entityList>**

Name of entities to be denied the privilege(s).

| Parameter Value | Descriptions  |
| --------------- | ------------- |
| `role`          | Name of role. |
| `user`          | Name of user. |

### Example

Prohibit SELECT and INSERT operations on the `employees` table for the **nonemployee** role.

```
REVOKE ALL ON TABLE employees FROM nonemployee;
```

Prohibit SELECT operations on the `directors` table for the **employee** role.

```
REVOKE SELECT ON TABLE directors FROM employee;
```

Prohibit INSERT operations on the `directors` table for role **employee** and user **laura**.

```
REVOKE INSERT ON TABLE directors FROM employee, laura;
```

Prohibit INSERT, SELECT, and TRUNCATE privileges on the `employees` table for the role **nonemployee** and for users **dennis** and **mike**.

```
REVOKE INSERT, SELECT, TRUNCATE ON TABLE employees FROM nonemployee, dennis, mike;
```

### See Also

* [GRANT ON TABLE](/installation-and-configuration/security/roles#grant-on-table)
* [GRANT ON DATABASE](/installation-and-configuration/security/roles#grant-on-database)

## GRANT ON VIEW

Define the privileges a role or user has on the specified view. You can specify any combination of the `SELECT`, `INSERT`, or `DROP` privileges, or specify all privileges.

This clause requires superuser privileges, or \<viewName> must have been created by the user invoking this command. The specified \<viewName> and users or roles in \<entityList> must exist.

### Synopsis

```
GRANT <privilegeList> ON VIEW <viewName> TO <entityList>;
```

### Parameters

**\<privilegeList>**

| Parameter Value | Descriptions                                                          |
| --------------- | --------------------------------------------------------------------- |
| `ALL`           | Grant all possible access privileges on \<viewName> to \<entityList>. |
| `DROP`          | Grant DROP privilege on \<viewName> to \<entityList>.                 |
| `INSERT`        | Grant INSERT privilege on \<viewName> to \<entityList>.               |
| `SELECT`        | Grant SELECT privilege on \<viewName> to \<entityList>.               |

**\<viewName>**

Name of the database view.

**\<entityList>**

Name of entities to be granted the privileges.

| Parameter Value | Descriptions  |
| --------------- | ------------- |
| `role`          | Name of role. |
| `user`          | Name of user. |

### Examples

Permit SELECT, INSERT, and DROP privileges on the `employees` view for the **payrollDept** role.

```
GRANT ALL ON VIEW employees TO payrollDept;
```

Permit SELECT-only privilege on the `employees` view for the **employee** role and user **venkat**.

```
GRANT SELECT ON VIEW employees TO employee, venkat;
```

Permit INSERT and DROP privileges on the `employees` view for the **hrDept** and **acctPayableDept** roles and users **simon** and **dmitri**.

```
GRANT INSERT, DROP ON VIEW employees TO hrDept, acctPayableDept, simon, dmitri;
```

### See Also

* [REVOKE ON VIEW](/installation-and-configuration/security/roles#revoke-on-view)
* [DDL-VIEWS](/sql/data-definition-ddl/views)
* [CREATE ROLE](/installation-and-configuration/security/roles#create-role)
* [GRANT ON DATABASE](/installation-and-configuration/security/roles#grant-on-database)

## REVOKE ON VIEW

Remove the privileges a role or user has on the specified view. You can remove any combination of the `INSERT`, `DROP`, or `SELECT` privileges, or remove all privileges.

This clause requires superuser privilege, or \<viewName> must have been created by the user invoking this command. The specified \<viewName> and users or roles in \<entityList> must exist.

### Synopsis

```
REVOKE <privilegeList> ON VIEW <viewName> FROM <entityList>;
```

### Parameters

**\<privilegeList>**

| Parameter Value | Descriptions                                                  |
| --------------- | ------------------------------------------------------------- |
| `ALL`           | Remove all access privilege for \<entityList> on \<viewName>. |
| `DROP`          | Remove DROP privilege for \<entityList> on \<viewName>.       |
| `INSERT`        | Remove INSERT privilege for \<entityList> on \<viewName>.     |
| `SELECT`        | Remove SELECT privilege for \<entityList> on \<viewName>.     |

**\<viewName>**

Name of the database view.

**\<entityList>**

Name of entity to be denied the privilege(s).

| Parameter Value | Descriptions  |
| --------------- | ------------- |
| `role`          | Name of role. |
| `user`          | Name of user. |

### Example

Prohibit SELECT, DROP, and INSERT operations on the `employees` view for the **nonemployee** role.

```
REVOKE ALL ON VIEW employees FROM nonemployee;
```

Prohibit SELECT operations on the `directors` view for the employee role.

```
REVOKE SELECT ON VIEW directors FROM employee;
```

Prohibit INSERT and DROP operations on the `directors` view for the **employee** and **manager** role and for users **ashish** and **lindsey**.

```
REVOKE INSERT, DROP ON VIEW directors FROM employee, manager, ashish, lindsey;
```

### See Also

* [GRANT ON VIEW](/installation-and-configuration/security/roles#grant-on-view)
* [GRANT ON DATABASE](/installation-and-configuration/security/roles#grant-on-database)

## GRANT ON DATABASE

Define the valid privileges a role or user has on the specified database. You can specify any combination of privileges, or specify all privileges.

<Info>
  The ACCESS privilege is a prerequisite for all other privileges at the database level. Without the ACCESS privilege, a user or role cannot perform tasks on any other database objects.
</Info>

This clause requires superuser privileges.

### Synopsis

```
GRANT <privilegeList> ON DATABASE <dbName> TO <entityList>;
```

### Parameters

**\<privilegeList>**

| Parameter Value    | Descriptions                                                                     |
| ------------------ | -------------------------------------------------------------------------------- |
| `ACCESS`           | Grant ACCESS (connection) privilege on \<dbName> to \<entityList>.               |
| `ALL`              | Grant all possible access privileges on \<dbName> to \<entityList>.              |
| `ALTER TABLE`      | Grant ALTER TABLE privilege on \<dbName> to \<entityList>.                       |
| `ALTER SERVER`     | Grant ALTER SERVER privilege on \<dbName> to \<entityList>.                      |
| `CREATE SERVER`    | Grant CREATE SERVER privilege on \<dbName> to \<entityList>;                     |
| `CREATE TABLE`     | Grant CREATE TABLE privilege on \<dbName> to \<entityList>. Previously `CREATE`. |
| `CREATE VIEW`      | Grant CREATE VIEW privilege on \<dbName> to \<entityList>.                       |
| `CREATE DASHBOARD` | Grant CREATE DASHBOARD privilege on \<dbName> to \<entityList>.                  |
| `CREATE`           | Grant CREATE privilege on \<dbName> to \<entityList>.                            |
| `DELETE`           | Grant DELETE privilege on \<dbName> to \<entityList>.                            |
| `DELETE DASHBOARD` | Grant DELETE DASHBOARD privilege on \<dbName> to \<entityList>.                  |
| `DROP`             | Grant DROP privilege on \<dbName> to \<entityList>.                              |
| `DROP SERVER`      | Grant DROP privilege on \<dbName> to \<entityList>.                              |
| `DROP VIEW`        | Grant DROP VIEW privilege on \<dbName> to \<entityList>.                         |
| `EDIT DASHBOARD`   | Grant EDIT DASHBOARD privilege on \<dbName> to \<entityList>.                    |
| `INSERT`           | Grant INSERT privilege on \<dbName> to \<entityList>.                            |
| `SELECT`           | Grant SELECT privilege on \<dbName> to \<entityList>.                            |
| `SELECT VIEW`      | Grant SELECT VIEW privilege on \<dbName> to \<entityList>.                       |
| `SERVER USAGE`     | Grant SERVER USAGE privilege on \<dbName> to \<entityList>.                      |
| `TRUNCATE`         | Grant TRUNCATE privilege on \<dbName> to \<entityList>.                          |
| `UPDATE`           | Grant UPDATE privilege on \<dbName> to \<entityList>.                            |
| `VIEW DASHBOARD`   | Grant VIEW DASHBOARD privilege on \<dbName> to \<entityList>.                    |
| `VIEW SQL EDITOR`  | Grant VIEW SQL EDITOR privilege in Immerse on \<dbName> to \<entityList>.        |

**\<dbName>**

Name of the database, which must exist, created by CREATE DATABASE.

**\<entityList>**

Name of the entity to be granted the privilege.

| Parameter Value | Descriptions                                                                                                                                                                                                        |
| --------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `role`          | Name of role, which must exist.                                                                                                                                                                                     |
| `user`          | Name of user, which must exist. See [Users and Databases](https://github.com/omnisci/docs-internal/tree/124aa7f52b9253d65f2b447433cb7629fc06ab6a/installation-and-configuration/security/5_usersanddatabases.html). |

### Examples

Permit all operations on the `companydb` database for the **payrollDept** role and user **david**.

```
GRANT ALL ON DATABASE companydb TO payrollDept, david;
```

Permit SELECT-only operations on the `companydb` database for the **employee** role.

```
GRANT ACCESS, SELECT ON DATABASE companydb TO employee;
```

Permit INSERT, UPDATE, and DROP operations on the `companydb` database for the **hrdept** and **manager** role and for users **irene** and **stephen**.

```
GRANT ACCESS, INSERT, UPDATE, DROP ON DATABASE companydb TO hrdept, manager, irene, stephen;
```

### See Also

* [REVOKE ON DATABAS](/installation-and-configuration/security/roles#revoke-on-database)E
* [GRANT ON TABLE](/installation-and-configuration/security/roles#grant-on-table)
* [CREATE ROLE](/installation-and-configuration/security/roles#create-role)

## REVOKE ON DATABASE

Remove the operations a role or user can perform on the specified database. You can specify privileges individually or specify all privileges.

This clause requires superuser privilege or the user must own the database object. The specified \<dbName> and roles or users in \<entityList> must exist.

### Synopsis

```
REVOKE <privilegeList> ON DATABASE <dbName> FROM <entityList>;
```

### Parameters

**\<privilegeList>**

| Parameter Value    | Descriptions                                                                        |
| ------------------ | ----------------------------------------------------------------------------------- |
| `ACCESS`           | Remove ACCESS (connection) privilege on \<dbName> from \<entityList>.               |
| `ALL`              | Remove all possible privileges on \<dbName> from \<entityList>.                     |
| `ALTER SERVER`     | Remove ALTER SERVER privilege on \<dbName> from \<entityList>                       |
| `ALTER TABLE`      | Remove ALTER TABLE privilege on \<dbName> from \<entityList>.                       |
| `CREATE TABLE`     | Remove CREATE TABLE privilege on \<dbName> from \<entityList>. Previously `CREATE`. |
| `CREATE VIEW`      | Remove CREATE VIEW privilege on \<dbName> from \<entityList>.                       |
| `CREATE DASHBOARD` | Remove CREATE DASHBOARD privilege on \<dbName> from \<entityList>.                  |
| `CREATE`           | Remove CREATE privilege on \<dbName> from \<entityList>.                            |
| `CREATE SERVER`    | Remove CREATE SERVER privilege on \<dbName> from \<entityList>.                     |
| `DELETE`           | Remove DELETE privilege on \<dbName> from \<entityList>.                            |
| `DELETE DASHBOARD` | Remove DELETE DASHBOARD privilege on \<dbName> from \<entityList>.                  |
| `DROP`             | Remove DROP privilege on \<dbName> from \<entityList>.                              |
| `DROP SERVER`      | Remove DROP SERVER privilege on \<dbName> from \<entityList>.                       |
| `DROP VIEW`        | Remove DROP VIEW privilege on \<dbName> from \<entityList>.                         |
| `EDIT DASHBOARD`   | Remove EDIT DASHBOARD privilege on \<dbName> from \<entityList>.                    |
| `INSERT`           | Remove INSERT privilege on \<dbName> from \<entityList>.                            |
| `SELECT`           | Remove SELECT privilege on \<dbName> from \<entityList>.                            |
| `SELECT VIEW`      | Remove SELECT VIEW privilege on \<dbName> from \<entityList>.                       |
| `SERVER USAGE`     | Remove SERVER USAGE privilege on \<dbName> from \<entityList>.                      |
| `TRUNCATE`         | Remove TRUNCATE privilege on \<dbName> from \<entityList>.                          |
| `UPDATE`           | Remove UPDATE privilege on \<dbName> from \<entityList>.                            |
| `VIEW DASHBOARD`   | Remove VIEW DASHBOARD privilege on \<dbName> from \<entityList>.                    |
| `VIEW SQL EDITOR`  | Remove VIEW SQL EDITOR privilege in Immerse on \<dbName> from \<entityList>.        |

**\<dbName>**

Name of the database.

**\<entityList>**

| Parameter Value | Descriptions  |
| --------------- | ------------- |
| `role`          | Name of role. |
| `user`          | Name of user. |

### Example

Prohibit all operations on the `employees` database for the **nonemployee** role.

```
REVOKE ALL ON DATABASE employees FROM nonemployee;
```

Prohibit SELECT operations on the `directors` database for the **employee** role and for user **monica**.

```
REVOKE SELECT ON DATABASE directors FROM employee;
```

Prohibit INSERT, DROP, CREATE, and DELETE operations on the `directors` database for **employee** role and for users **max** and **alex**.

```
REVOKE INSERT, DROP, CREATE, DELETE ON DATABASE directors FROM employee;
```

### See Also

* [GRANT ON DATABASE](/installation-and-configuration/security/roles#grant-on-database)
* [GRANT ON TABLE](/installation-and-configuration/security/roles#grant-on-table)

## GRANT ON SERVER

Define the valid privileges a role or user has for working with servers. You can specify any combination of privileges or specify all privileges.

This clause requires superuser privileges, or \<serverName> must have been created by the user invoking the command.

### Synopsis

```
GRANT <privilegeList> ON SERVER <serverName> TO <entityList>;
```

### Parameters

**\<privilegeList>**

| Parameter Value | Descriptions                                                                                          |
| --------------- | ----------------------------------------------------------------------------------------------------- |
| `DROP`          | Grant DROP privileges on \<serverName> on current database to \<entityList>.                          |
| `ALTER`         | Grant ALTER privilege on \<serverName> on current database to \<entityList>.                          |
| `USAGE`         | Grant USAGE privilege (through foreign tables) on \<serverName> on current database to \<entityList>. |

**\<serverName>**

Name of the server, which must exist on the current database, created by CREATE SERVER ON DATABASE.

**\<entityList>**

| Parameter Value | Descriptions                                                                                                                                                                                                        |
| --------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `role`          | Name of role, which must exist.                                                                                                                                                                                     |
| `user`          | Name of user, which must exist. See [Users and Databases](https://github.com/omnisci/docs-internal/tree/124aa7f52b9253d65f2b447433cb7629fc06ab6a/installation-and-configuration/security/5_usersanddatabases.html). |

### Examples

Grant DROP privilege on server `parquet_s3_server` to user **fred**:

```
GRANT DROP ON SERVER parquet_s3_server TO fred
```

Grant ALTER privilege on server `parquet_s3_server` to role **payrollDept**:

```
GRANT ALTER ON SERVER parquet_s3_server TO payrollDept;
```

Grant USAGE and ALTER privileges on server `parquet_s3_server` to role **payrollDept** and user **jamie**:

```
GRANT USAGE, ALTER ON SERVER parquet_s3_server TO payrollDept, jamie;
```

### See Also

* [REVOKE ON SERVER](/installation-and-configuration/security/roles#grant-on-dashboard-1)

## REVOKE ON SERVER

Remove privileges a role or user has for working with servers. You can specify any combination of privileges or specify all privileges.

This clause requires superuser privileges, or \<serverName> must have been created by the user invoking the command.

### Synopsis

```
REVOKE <privilegeList> ON SERVER <serverName> FROM <entityList>;
```

### Parameters

**\<privilegeList>**

| Parameter Value | Descriptions                                                                                            |
| --------------- | ------------------------------------------------------------------------------------------------------- |
| `DROP`          | Remove DROP privileges on \<serverName> on current database for \<entityList>.                          |
| `ALTER`         | Remove ALTER privilege on \<serverName> on current database for \<entityList>.                          |
| `USAGE`         | Remove USAGE privilege (through foreign tables) on \<serverName> on current database for \<entityList>. |

**\<serverName>**

Name of the server, which must exist on the current database, created by CREATE SERVER ON DATABASE.

**\<entityList>**

| Parameter Value | Descriptions                                                                                                                                                                                                        |
| --------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `role`          | Name of role, which must exist.                                                                                                                                                                                     |
| `user`          | Name of user, which must exist. See [Users and Databases](https://github.com/omnisci/docs-internal/tree/124aa7f52b9253d65f2b447433cb7629fc06ab6a/installation-and-configuration/security/5_usersanddatabases.html). |

### Examples

Revoke DROP privilege on server `parquet_s3_server` for user **inga**:

```
REVOKE DROP ON SERVER parquet_s3_server FROM inga
```

Grant ALTER privilege on server `parquet_s3_server` for role **payrollDept**:

```
REVOKE ALTER ON SERVER parquet_s3_server FROM payrollDept;
```

Grant USAGE and ALTER privileges on server `parquet_s3_server` for role **payrollDept** and user **marvin**:

```
REVOKE USAGE, ALTER ON SERVER parquet_s3_server FROM payrollDept, marvin;
```

### See Also

* [GRANT ON SERVER](/installation-and-configuration/security/roles#grant-on-dashboard)

## GRANT ON DASHBOARD

Define the valid privileges a role or user has for working with dashboards. You can specify any combination of privileges or specify all privileges.

This clause requires superuser privileges.

### Synopsis

```
GRANT <privilegeList> [ON DASHBOARD <dashboardId>] TO <entityList>;
```

### Parameters

**\<privilegeList>**

| Parameter Value | Descriptions                                                             |
| --------------- | ------------------------------------------------------------------------ |
| `ALL`           | Grant all possible access privileges on \<dashboardId> to \<entityList>. |
| `CREATE`        | Grant CREATE privilege to \<entityList>.                                 |
| `DELETE`        | Grant DELETE privilege on \<dashboardId> to \<entityList>.               |
| `EDIT`          | Grant EDIT privilege on \<dashboardId> to \<entityList>.                 |
| `VIEW`          | Grant VIEW privilege on \<dashboardId> to \<entityList>.                 |

**\<dashboardId>**

ID of the dashboard, which must exist, created by CREATE DASHBOARD. To show a list of all dashboards and IDs in heavysql, run the `\dash` command when logged in as superuser.

**\<entityList>**

| Parameter Value | Descriptions                                                                                                                                                                                                        |
| --------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `role`          | Name of role, which must exist.                                                                                                                                                                                     |
| `user`          | Name of user, which must exist. See [Users and Databases](https://github.com/omnisci/docs-internal/tree/124aa7f52b9253d65f2b447433cb7629fc06ab6a/installation-and-configuration/security/5_usersanddatabases.html). |

### Examples

Permit all privileges on the dashboard ID `740` for the **payrollDept** role.

```
GRANT ALL ON DASHBOARD 740 TO payrollDept;
```

Permit VIEW-only privilege on dashboard `730` for the **hrDept** role and user **dennis**.

```
GRANT VIEW ON DASHBOARD 730 TO hrDept, dennis;
```

Permit EDIT and DELETE privileges on dashboard `740` for the **hrDept** and **accountsPayableDept** roles and for user **pavan**.

```
GRANT EDIT, DELETE ON DASHBOARD 740 TO hrdept, accountsPayableDept, pavan;
```

### See Also

* [REVOKE ON DASHBOARD](/installation-and-configuration/security/roles#revoke-on-dashboard)

## REVOKE ON DASHBOARD

Remove privileges a role or user has for working with dashboards. You can specify any combination of privileges, or all privileges.

This clause requires superuser privileges.

### Synopsis

```
REVOKE <privilegeList> [ON DASHBOARD <dashboardId>] FROM <entityList>;
```

### Parameters

**\<privilegeList>**

| Parameter Value | Descriptions                                                               |
| --------------- | -------------------------------------------------------------------------- |
| `ALL`           | Revoke all possible access privileges on \<dashboardId> for \<entityList>. |
| `CREATE`        | Revoke CREATE privilege for \<entityList>.                                 |
| `DELETE`        | Revoke DELETE privilege on \<dashboardId> for \<entityList>.               |
| `EDIT`          | Revoke EDIT privilege on \<dashboardId> for \<entityList>.                 |
| `VIEW`          | Revoke VIEW privilege on \<dashboardId> for \<entityList>.                 |

**\<dashboardId>**

ID of the dashboard, which must exist, created by CREATE DASHBOARD.

**\<entityList>**

| Parameter Value | Descriptions                                                                                                                                                                                                        |
| --------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `role`          | Name of role, which must exist.                                                                                                                                                                                     |
| `user`          | Name of user, which must exist. See [Users and Databases](https://github.com/omnisci/docs-internal/tree/124aa7f52b9253d65f2b447433cb7629fc06ab6a/installation-and-configuration/security/5_usersanddatabases.html). |

Revoke DELETE privileges on dashboard `740` for the **payrollDept** role.

```
REVOKE DELETE ON DASHBOARD 740 FROM payrollDept;
```

Revoke all privileges on dashboard `730` for **hrDept** role and users **dennis** and **mike**.

```
REVOKE ALL ON DASHBOARD 730 FROM hrDept, dennis, mike;
```

Revoke EDIT and DELETE of dashboard `740` for the **hrDept** and **accountsPayableDept** roles and for users **dante** and **jonathan**.

```
REVOKE EDIT, DELETE ON DASHBOARD 740 FROM hrdept, accountsPayableDept, dante, jonathan;
```

### See Also

* [GRANT ON DASHBOARD](/installation-and-configuration/security/roles#grant-on-dashboard)

## Common Privilege Levels for Non-Superusers

The following privilege levels are typically recommended for non-superusers in Immerse. Privileges assigned for users in your organization may vary depending on access requirements.

| Privilege          | Command Syntax to Grant Privilege                              |
| ------------------ | -------------------------------------------------------------- |
| Access a database  | `GRANT ACCESS ON DATABASE <dbName> TO <entityList>;`           |
| Create a table     | `GRANT CREATE TABLE ON DATABASE <dbName> TO <entityList>;`     |
| Select a table     | `GRANT SELECT ON TABLE <tableName> TO <entityList>;`           |
| View a dashboard   | `GRANT VIEW ON DASHBOARD <dashboardId> TO <entityList>;`       |
| Create a dashboard | `GRANT CREATE DASHBOARD ON DATABASE <dbName> TO <entityList>;` |
| Edit a dashboard   | `GRANT EDIT ON DASHBOARD  TO ;`                                |
| Delete a dashboard | `GRANT DELETE DASHBOARD ON DATABASE <dbName> TO <entityList>;` |

## Example: Roles and Privileges

These examples assume that tables `table1` through `table4` are created as needed:

```
create table table1 (id smallint);
create table table2 (id smallint);
create table table3 (id smallint);
create table table4 (id smallint);
```

The following examples show how to work with users, roles, tables, and dashboards.

### Create User Accounts

```
create user marketingDeptEmployee1 (password = 'md1');
create user marketingDeptEmployee2 (password = 'md2');
create user marketingDeptManagerEmployee3 (password = 'md3');

create user salesDeptEmployee1 (password = 'sd1');
create user salesDeptEmployee2 (password = 'sd2');
create user salesDeptEmployee3 (password = 'sd3');
create user salesDeptEmployee4 (password = 'sd4');
create user salesDeptManagerEmployee5 (password = 'sd5');
```

### Grant Access to Users on Database

```
grant access on database heavyai to marketingDeptEmployee1, marketingDeptEmployee2, marketingDeptManagerEmployee3;
grant access on database heavyai to salesDeptEmployee1, salesDeptEmployee2, salesDeptEmployee3, salesDeptEmployee4, salesDeptManagerEmployee5;
```

### Create Marketing Department Roles

```
create role marketingDeptRole1;
create role marketingDeptRole2;
```

### Grant Marketing Department Roles to Marketing Department Employees

```
grant marketingDeptRole1 to marketingDeptEmployee1, marketingDeptManagerEmployee3;
grant marketingDeptRole2 to marketingDeptEmployee2, marketingDeptManagerEmployee3;
```

### Grant Privelege to Marketing Department Roles

```
grant select on table table1 to marketingDeptRole1;
grant select on table table2 to marketingDeptRole1;
grant select on table table2 to marketingDeptRole2;
```

### Create Sales Department Roles

```
create role salesDeptRole1;
create role salesDeptRole2;
create role salesDeptRole3;
```

### Grant Sales Department Roles to Sales Department Employees

```
grant salesDeptRole1 to salesDeptEmployee1;
grant salesDeptRole2 to salesDeptEmployee2, salesDeptEmployee3;
grant salesDeptRole3 to salesDeptEmployee4;
```

### Grant Privilege to Sales Department Roles

```
grant select on table table1 to salesDeptRole1;
grant select on table table3 to salesDeptRole1, salesDeptRole2;
grant select on table table4 to salesDeptRole3;
```

### Grant All Sales Roles to Sales Department Manager and Marketing Department Manager

```
grant salesDeptRole1, salesDeptRole2, salesDeptRole3 to salesDeptManagerEmployee5, marketingDeptManagerEmployee3;
```

### Grant View on Dashboards

Use the `\dash` command to list all dashboards and their unique IDs in HEAVY.AI:

```
heavysql> \dash 
Dashboard ID | Dashboard Name    | Owner 
1            | Marketing_Summary | heavyai
```

Here, the `Marketing_Summary` dashboard uses `table2` as a data source. The role `marketingDeptRole2` has select privileges on that table. Grant view access on the `Marketing_Summary` dashboard to `marketingDeptRole2`:

```
grant view on dashboard 1 to marketingDeptRole2;
```

### Relationships Between Users, Roles, and Tables

The following table shows the roles and privileges for each user created in the previous example.

| User                          | Roles Granted                                                                          | Table Privileges            |
| ----------------------------- | -------------------------------------------------------------------------------------- | --------------------------- |
| salesDeptEmployee1            | salesDeptRole1                                                                         | SELECT on Tables 1, 3       |
| salesDeptEmployee2            | salesDeptRole2                                                                         | SELECT on Table 3           |
| salesDeptEmployee3            | salesDeptRole2                                                                         | SELECT on Table 3           |
| salesDeptEmployee4            | salesDeptRole3                                                                         | SELECT on Table 4           |
| salesDeptManagerEmployee5     | salesDeptRole1, salesDeptRole2, salesDeptRole3                                         | SELECT on Tables 1, 3, 4    |
| marketingDeptEmployee1        | marketingDeptRole1                                                                     | SELECT on Tables 1, 2       |
| marketingDeptEmployee2        | marketingDeptRole2                                                                     | SELECT on Table 2           |
| marketingDeptManagerEmployee3 | marketingDeptRole1, marketingDeptRole2, salesDeptRole1, salesDeptRole2, salesDeptRole3 | SELECT on Tables 1, 2, 3, 4 |

## Commands to Report Roles and Privileges

Use the following commands to list current roles and assigned privileges. If you have superuser access, you can see privileges for all users. Otherwise, you can see only those roles and privileges for which you have access.

<Info>
  Results for users, roles, privileges, and object privileges are returned in creation order.
</Info>

#### \dash

Lists all dashboards and dashboard IDs in HEAVY.AI. Requires superuser privileges. Dashboard privileges are assigned by dashboard ID because dashboard names may not be unique.

**Example**

```
heavysql> \dash database heavyai 
Dashboard ID | Dashboard Name    | Owner 
1            | Marketing_Summary | heavyai
```

`heavysql> \dash database heavyai`  \
`Dashboard ID | Dashboard Name | Owner`  \
`1 | Marketing_Summary | heavyai`

#### \object\_privileges *objectType* \`\_objectName\`\_

Reports all privileges granted to the specified object for all roles and users. If the specified objectName does not exist, no results are reported. Used for databases and tables only.

**Example**

```
heavysql> \object_privileges database heavyai 
marketingDeptEmployee1 privileges: login-access 
marketingDeptEmployee2 privileges: login-access marketingDeptManagerEmployee3 privileges: login-access
salesDeptEmployee1 privileges: login-access 
salesDeptEmployee2 privileges: login-access 
salesDeptEmployee3 privileges: login-access 
salesDeptEmployee4 privileges: login-access 
salesDeptManagerEmployee5 privileges: login-access
```

#### \privileges *roleName* | *userName*

Reports all object privileges granted to the specified role or user. The roleName or userName specified must exist.

**Example**

```
heavysql> \privileges salesDeptRole1 
table1 (table): select 
table3 (table): select
heavysql> \privileges salesDeptManagerEmployee5 
mapd (database): login-access

heavysql> \privileges marketingdeptrole2 
table2 (table): select
Marketing_Summary (dashboard): view
```

#### \role\_list *userName*

Reports all roles granted to the given user. The userName specified must exist.

**Example**

```
heavysql> \role_list salesDeptManagerEmployee5
salesDeptRole3 
salesDeptRole2 
salesDeptRole1
```

#### \roles

Reports all roles.

**Example**

```
heavysql> \roles
marketingDeptRole1 
marketingDeptRole2 
salesDeptRole1 
salesDeptRole2 
salesDeptRole3
```

#### \u

Lists all users.

**Example**

```
heavysql> \u 
heavyai 
marketingDeptEmployee1 
marketingDeptEmployee2 
salesDeptEmployee1 
salesDeptEmployee2 
salesDeptEmployee3 
salesDeptEmployee4 
salesDeptManagerEmployee5 
marketingDeptManagerEmployee3
```

## Example: Data Security

The following example demonstrates field-level security using two views:

* `view_users_limited`, in which users only see three of seven fields: `userid`, `First_Name`, and `Department`.
* `view_users_full`, users see all seven fields.

### Source Data

![](https://files.buildwithfern.com/heavyai.docs.buildwithfern.com/heavyai/e37c23ea48f2794e0b732d03d20ff2c53eedd871181fa641c43aac0d0e3aa63c/docs/assets/5_source_data-1-1-1-2-2-2-2-3-3-4-4-5-4-1-7.png)

### Create Views

```
create view view_users_limited as select userid, First_Name, Department from users;
create view view_users_full as select userid, First_Name, Department, Address, City, State, Zip from users;
```

### Create Users

```
create user readonly1 (password = 'rr1');
create user readonly2 (password = 'rr2');
```

### Grant Access to Users on Database

```
grant access on database heavyai to readonly1, readonly2;
```

### Create Roles

```
create role limited_readonly;
create role full_readonly;
```

### Grant Roles to Users

```
grant limited_readonly to readonly1;
grant full_readonly to readonly2;
```

### Grant Privilege to View Roles

```
grant select on view view_users_limited to limited_readonly;
grant select on view view_users_full TO full_readonly;
```

### Verify Views

User `readonly1` sees no tables, only the specific view granted, and only the three specific columns returned in the view:

```
heavysql> \t
heavysql> \v
view_users_limited
heavysql> select * from view_users_limited;
userid|First_Name|Department
1|Todd|C Suite
2|Don|Sales
3|Mike|Customer Success
```

User `readonly2` sees no tables, only the specific view granted, and all seven columns returned in the view:

```
heavysql> \t
heavysql> \v
view_users_full
heavysql> select * from view_users_full;
userid|First_Name|Department|Address|City|State|Zip
1|Todd|C Suite|1 Front Street|San Francisco|CA|94111
2|Don|Sales|1 5th Avenue|New York|NY|10001
3|Mike|Customer Succes|100 Main Street|Reston|VA|20191
```