DDL - Roles and Privileges

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

User-based Privileges

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:

• Database
• Table
• View
• Dashboard

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

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.

   CREATE ROLE r_select;
   

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.

   GRANT r_select TO user1;
   

4. Directly grant user1 the INSERT privilege on table1.

   GRANT INSERT ON TABLE table1 TO user1;
   

See 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.

CREATE ROLE <roleName>;

CREATE ROLE payrollDept;

DROP ROLE

Remove a role.

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

DROP ROLE <roleName>;

DROP ROLE payrollDept;

GRANT

Grant role privileges to users and to other roles.

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

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

GRANT payrollDept TO dennis;

GRANT payrollDept, accountsPayableDept TO dennis, mike, hrDept;

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.

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

REVOKE payrollDept FROM dennis;

REVOKE payrollDept, accountsPayableDept FROM dennis, fred, hrDept;

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.

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>;

GRANT ALL ON TABLE employees TO payrollDept;

GRANT SELECT ON TABLE employees TO chris;

GRANT INSERT ON TABLE employees TO hrDept, accountsPayableDept;

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

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.

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

REVOKE ALL ON TABLE employees FROM nonemployee;

REVOKE SELECT ON TABLE directors FROM employee;

REVOKE INSERT ON TABLE directors FROM employee, laura;

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

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.

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

GRANT ALL ON VIEW employees TO payrollDept;

GRANT SELECT ON VIEW employees TO employee, venkat;

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

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.

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

REVOKE ALL ON VIEW employees FROM nonemployee;

REVOKE SELECT ON VIEW directors FROM employee;

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

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.

This clause requires superuser privileges.

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

GRANT ALL ON DATABASE companydb TO payrollDept, david;

GRANT ACCESS, SELECT ON DATABASE companydb TO employee;

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

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.

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

REVOKE ALL ON DATABASE employees FROM nonemployee;

REVOKE SELECT ON DATABASE directors FROM employee;

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

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.

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

GRANT ALL ON DASHBOARD 740 TO payrollDept;

GRANT VIEW ON DASHBOARD 730 TO hrDept, dennis;

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

See Also

• REVOKE ON DASHBOARD

Synopsis

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

Parameters

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

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 on database omnisci to marketingDeptEmployee1, marketingDeptEmployee2, marketingDeptManagerEmployee3;
grant access on database omnisci to salesDeptEmployee1, salesDeptEmployee2, salesDeptEmployee3, salesDeptEmployee4, salesDeptManagerEmployee5;

create role marketingDeptRole1;
create role marketingDeptRole2;

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

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

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

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