DDL - Roles and Privileges

Users and Privileges

When you create a database, the mapd superuser is created by default. The mapd 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.

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 a user.

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

GRANT <roleName> TO <userName>;

GRANT payrollDept TO dennis;

REVOKE

Remove role privilege from user. This removes database object access privileges granted with the role.

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

REVOKE <roleName> FROM <userName>;

REVOKE payrollDept FROM dennis;

GRANT ON TABLE

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

This clause requires superuser privilege or <tableName> must have been created the user invoking this command. The specified <tableName> and user or role <entity> must exist.

GRANT <privilege> ON TABLE <tableName> TO <entity>;

GRANT ALL ON TABLE employees TO payrollDept;

GRANT SELECT ON TABLE employees TO employee;

GRANT INSERT ON TABLE employees TO hrdept;

REVOKE ON TABLE

Remove the privilege(s) a role or user has on the specified table. You can either individually remove INSERT, SELECT, DELETE, UPDATE, or TRUNCATE privilege, 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 user or role <entity> must exist.

REVOKE <privilege> ON TABLE <tableName> FROM <entity>;

REVOKE ALL ON TABLE employees FROM nonemployee;

REVOKE SELECT ON TABLE directors FROM employee;

REVOKE INSERT ON TABLE directors FROM employee;

GRANT ON VIEW

Define the privilege(s) a role or user has on the specified view. You can individually specify the SELECT or INSERT privilege, 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 user or role <entity> must exist.

GRANT <privilege> ON VIEW <viewName> TO <entity>;

GRANT ALL ON VIEW employees TO payrollDept;

GRANT SELECT ON VIEW employees TO employee;

GRANT INSERT ON VIEW employees TO hrdept;

REVOKE ON VIEW

Remove the privilege(s) a role or user has on the specified view. You can either individually remove INSERT 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 user or role <entity> must exist.

REVOKE <privilege> ON VIEW <viewName> FROM <entity>;

REVOKE ALL ON VIEW employees FROM nonemployee;

REVOKE SELECT ON VIEW directors FROM employee;

REVOKE INSERT ON VIEW directors FROM employee;

GRANT ON DATABASE

Define the valid privilege(s) a role or user has on the specified database. You can specify privileges individually or specify all privileges.

This clause requires superuser privileges.

GRANT <privilege> ON DATABASE <dbName> TO <entity>;

GRANT CREATE ON DATABASE companydb TO payrollDept;

GRANT SELECT ON DATABASE companydb TO employee;

GRANT INSERT ON DATABASE companydb TO hrdept;

REVOKE ON DATABASE

Remove the operation(s) 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 role or user <entity> must exist.

REVOKE <privilege> ON DATABASE <dbName> FROM <entity>;

REVOKE ALL ON DATABASE employees FROM nonemployee;

REVOKE SELECT ON DATABASE directors FROM employee;

REVOKE INSERT ON DATABASE directors FROM employee;

GRANT ON DASHBOARD

Define the valid privilege(s) a role or user has for working with dashboards. You can specify privileges individually or specify all possible privileges.

This clause requires superuser privileges.

GRANT <privilege> [ON DASHBOARD <dashboardId>] TO <entity>;

GRANT DELETE ON DASHBOARD 740 TO payrollDept;

GRANT VIEW ON DASHBOARD 730 TO dennis;

GRANT EDIT ON DASHBOARD 740 TO hrdept;

See Also

• REVOKE ON DASHBOARD

Synopsis

REVOKE <privilege> [ON DASHBOARD <dashboardId>] TO <entity>;

Parameters

Examples

Remove DELETE privileges on dashboard 740 for the administrator role.

REVOKE DELETE ON DASHBOARD 740 TO payrollDept;

Revoke VIEW privilege on dashboard 730 for user Dennis.

REVOKE VIEW ON DASHBOARD 730 TO dennis;

Revoke editing of dashboard 740 for the human resources department role.

REVOKE EDIT ON DASHBOARD 740 TO hrdept;

See Also

• GRANT ON DASHBOARD

Creating Tables

This session assumes tables table1 through table4 are created as needed.

Creating User Accounts

create user dataEntryDeptEmployee1 (password = 'ded1');

create user dataEntryDeptEmployee2 (password = 'ded2');

create user dataEntryDeptEmployee3 (password = 'ded3');

create user dataEntryDeptManagerEmployee4 (password = 'ded4');

create user marketingDeptEmployee1 (password = 'md1');

create user marketingDeptEmployee2 (password = 'md2');

create user marketingDeptEmployee3 (password = 'md3');

create user marketingDeptEmployee4 (password = 'md4');

create user marketingDeptEmployee5 (password = 'md5');

create user marketingDeptManagerEmployee6 (password = 'md6');

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

create user informationSystemsDeptEmployee1 (password = 'is1');

create user informationSystemsManagerDeptEmployee2 (password = 'is2');

Granting Access Privileges to Data Entry Department Employees

create role dataEntryDeptRole1;

grant insert on database mapd to dataEntryDeptRole1;

grant dataEntryDeptRole1 to dataEntryDeptEmployee1;

grant dataEntryDeptRole1 to dataEntryDeptEmployee2;

grant dataEntryDeptRole1 to dataEntryDeptEmployee3;

grant select on database mapd to dataEntryDeptManagerEmployee4;

Granting Access Privileges to Marketing Department Employees

create role marketingDeptRole1;

create role marketingDeptRole2;

create role marketingDeptRole3;

grant select on table table1 to marketingDeptRole1;

grant select on table table2 to marketingDeptRole1;

grant select on table table3 to marketingDeptRole2;

grant marketingDeptRole1 to marketingDeptEmployee1;

grant marketingDeptRole1 to marketingDeptEmployee2;

grant marketingDeptRole1 to marketingDeptEmployee3;

grant marketingDeptRole2 to marketingDeptEmployee4;

grant marketingDeptRole2 to marketingDeptEmployee5;

grant marketingDeptRole1 to marketingDeptManagerEmployee6;

grant marketingDeptRole2 to marketingDeptManagerEmployee6;

Granting Access Privileges to Sales Department Employees

create role salesDeptRole1;

create role salesDeptRole2;

create role salesDeptRole3;

grant select on table table1 to salesDeptRole1;

grant select on table table3 to salesDeptRole1;

grant select on table table3 to salesDeptRole2;

grant select on table table4 to salesDeptRole3;

grant salesDeptRole1 to salesDeptEmployee1;

grant salesDeptRole2 to salesDeptEmployee2;

grant salesDeptRole2 to salesDeptEmployee3;

grant salesDeptRole3 to salesDeptEmployee4;

grant salesDeptRole1 to salesDeptManagerEmployee5;

grant salesDeptRole2 to salesDeptManagerEmployee5;

grant salesDeptRole3 to salesDeptManagerEmployee5;

Granting Access Privileges to Information Systems Department Employees

grant all on database mapd to informationSystemsDeptEmployee1;

grant all on database mapd to informationSystemsDeptManagerEmployee2;