Roles and Privileges
OmniSci supports data security using a set of database object access privileges granted to users or roles.
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.
- 1.Grant roles access privileges on database objects.
- 2.Grant roles to users.
- 3.Grant roles to other roles.
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.
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 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 OmniSci session is interrupted.
There are four database object types, each with its own privileges.
Database
Table
View
Dashboard
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.
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.
DROP - Drop a table from the database.
DROP VIEW - Drop a view for this database.
DELETE DASHBOARD - Delete a dashboard for this database.
SELECT, INSERT, TRUNCATE, UPDATE, DELETE - Allow these operations on any table in the database.
SELECT VIEW - Select a view for this database.
EDIT DASHBOARD - Edit a dashboard for this database.
VIEW DASHBOARD - View a dashboard for this database.
VIEW SQL EDITOR - Access the SQL Editor in Immerse for this database.
SELECT, INSERT, TRUNCATE, UPDATE, DELETE - Allow these SQL statements on this table.
DROP - Drop this table.
SELECT - Select from this view. Users do not need privileges on objects referenced by this view.
DROP - Drop this view.
VIEW - View this dashboard.
EDIT - Edit this dashboard.
DELETE - Delete this dashboard.
Privileges granted on a database-type object are inherited by all tables of that database.
SQL | Description |
Create role. | |
Drop role. | |
Grant role to user or to another role. | |
Revoke role from user or from another role. | |
Grant role privilege(s) on a database table to a role or user. | |
Revoke role privilege(s) on database table from a role or user. | |
Grant role privilege(s) on a database view to a role or user. | |
Revoke role privilege(s) on database view from a role or user. | |
Grant role privilege(s) on database to a role or user. | |
Revoke role privilege(s) on database from a role or user. | |
Grant role privilege(s) on dashboard to a role or user. | |
Revoke role privilege(s) on dashboard from a role or user. |
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 ther_select
role. Any user granted ther_select
role gains the SELECT privilege.GRANT SELECT ON TABLE table1 TO r_select; - 3.Grant the
r_select
role touser1
, givinguser1
the SELECT privilege ontable1
. - 4.Directly grant
user1
the INSERT privilege ontable1
.GRANT INSERT ON TABLE table1 TO user1;
Create a role. Roles are granted to users for role-based database object access.
This clause requires superuser privilege and <roleName> must not exist.
CREATE ROLE <roleName>;
<roleName>
Name of the role to create.
Create a payroll department role called payrollDept.
CREATE ROLE payrollDept;
Remove a role.
This clause requires superuser privilege and <roleName> must exist.
DROP ROLE <roleName>;
<roleName>
Name of the role to drop.
Remove the payrollDept role.
DROP ROLE payrollDept;
Grant role privileges to users and to other roles.
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.
This clause requires superuser privilege. The specified <roleNames> and <userNames> must exist.
GRANT <roleNames> TO <userNames>, <roleNames>;
<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.
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;
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.
REVOKE <roleNames> FROM <userNames>, <roleNames>;
<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.
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;
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.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.
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.
GRANT <privilegeList> ON TABLE <tableName> TO <entityList>;
<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. |
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;
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.
REVOKE <privilegeList> ON TABLE <tableName> FROM <entityList>;
<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. |
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;
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.
GRANT <privilegeList> ON VIEW <viewName> TO <entityList>;
<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. |
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;
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.
REVOKE <privilegeList> ON VIEW <viewName> FROM <entityList>;
<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. |
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;
Define the valid privileges a role or user has on the specified database. You can specify any combination of privileges, or specify all privileges.
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.
This clause requires superuser privileges.
GRANT <privilegeList> ON DATABASE <dbName> TO <entityList>;
<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>. |
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 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>. |
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 |
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;
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.
REVOKE <privilegeList> ON DATABASE <dbName> FROM <entityList>;
<privilegeList>
Parameter Value | Descriptions |
ACCESS | Remove ACCESS (connection) privilege on <dbName> to <entityList>. |
ALL | Remove all possible privileges on <dbName> to <entityList>. |
ALTER TABLE | Remove ALTER TABLE privilege on <dbName> to <entityList>. |
CREATE TABLE | Remove CREATE TABLE privilege on <dbName> to <entityList>. Previously CREATE . |
CREATE VIEW | Remove CREATE VIEW privilege on <dbName> to <entityList>. |
CREATE DASHBOARD | Remove CREATE DASHBOARD privilege on <dbName> to <entityList>. |
CREATE | Remove CREATE privilege on <dbName> for <entityList>. |
DELETE | Remove DELETE privilege on <dbName> to <entityList>. |
DELETE DASHBOARD | Remove DELETE DASHBOARD privilege on <dbName> to <entityList>. |
DROP | Remove DROP privilege on <dbName> to <entityList>. |
DROP VIEW | Remove DROP VIEW privilege on <dbName> to <entityList>. |
EDIT DASHBOARD | Remove EDIT DASHBOARD privilege on <dbName> to <entityList>. |
INSERT | Remove INSERT privilege on <dbName> to <entityList>. |
SELECT | Remove SELECT privilege on <dbName> to <entityList>. |
SELECT VIEW | Remove SELECT VIEW privilege on <dbName> to <entityList>. |
TRUNCATE | Remove TRUNCATE privilege on <dbName> to <entityList>. |
UPDATE | Remove UPDATE privilege on <dbName> to <entityList>. |
VIEW DASHBOARD | Remove VIEW DASHBOARD privilege on <dbName> to <entityList>. |
VIEW SQL EDITOR | Remove VIEW SQL EDITOR privilege in Immerse on <dbName> to <entityList>. |
<dbName>
Name of the database.
<entityList>
Parameter Value | Descriptions |
role | Name of role. |
user | Name of user. |
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;
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.
GRANT <privilegeList> [ON DASHBOARD <dashboardId>] TO <entityList>;
<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 omnisql, run the
\dash
command when logged in as superuser.<entityList>
Parameter Value | Descriptions |
role | Name of role, which must exist. |
user |
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;
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.
REVOKE <privilegeList> [ON DASHBOARD <dashboardId>] FROM <entityList>;
<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 |