OmniSci 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.
Grant roles access privileges on database objects.
Grant roles to users.
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.
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 OmniSci session is interrupted.
Database Object Privileges
There are four database object types, each with its own privileges.
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.
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.
Create the r_select role.
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;
Grant the r_select role to user1, giving user1 the SELECT privilege on table1.
Directly grant user1 the INSERT privilege on table1.
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.
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 omnisci to marketingDeptEmployee1, marketingDeptEmployee2, marketingDeptManagerEmployee3;
grant access on database omnisci 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 Omnisci:
Dashboard ID | Dashboard Name | Owner
1 | Marketing_Summary | omnisci
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.