DDL - Roles and Privileges

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

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

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.
  • 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 MapD session is interrupted.

Database Object Privileges

There are four database object types:

  • Database
  • Table
  • View
  • Dashboard

Object Type Privilege
Database 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 dasboard 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.
Table SELECT, INSERT, TRUNCATE, UPDATE, DELETE - Allow these SQL statements on this table.
DROP - Drop this table.
View SELECT - Select from this view. NOTE: Users do not need privileges on objects referenced by this view.
DROP - Drop this view.
Dashboard 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.

Privilege Commands

SQL Description
CREATE ROLE Create role.
DROP ROLE Drop role.
GRANT Grant role to user.
REVOKE Revoke role from user.
GRANT ON TABLE Grant role privilege(s) on a database table to a role or user.
REVOKE ON TABLE Revoke role privilege(s) on database table from a role or user.
GRANT ON VIEW Grant role privilege(s) on a database view to a role or user.
REVOKE ON VIEW Revoke role privilege(s) on database view from a role or user.
GRANT ON DATABASE Grant role privilege(s) on database to a role or user.
REVOKE ON DATABASE Revoke role privilege(s) on database from a role or user.
GRANT ON DASHBOARD Grant role privilege(s) on dashboard to a role or user.
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.

  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.

Synopsis

CREATE ROLE <roleName>;

Parameters

<roleName>

Name of the role to create.

Example

Create a payroll department role called payrollDept.

CREATE ROLE payrollDept;

DROP ROLE

Remove a role.

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

Synopsis

DROP ROLE <roleName>;

Parameters

<roleName>

Name of the role to drop.

Example

Remove the payrollDept role.

DROP ROLE payrollDept;

GRANT

Grant role privileges to a user.

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

Synopsis

GRANT <roleName> TO <userName>;

Parameters

<roleName>

Name of role to grant to <userName>.

<userName>

Name of user.

Example

Assign payrollDept privileges to dennis.

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.

Synopsis

REVOKE <roleName> FROM <userName>;

Parameters

<roleName>

Name of the role.

<userName>

Name of the user.

Example

Remove payrollDept privileges from user dennis.

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.

Synopsis

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

Parameters

<privilege>

Parameter Value Descriptions
ALL Grant all possible access privileges on <tableName> to <entity>.
DELETE Grant DELETE privilege on <tableName> to <entity>.
DROP Grant DROP privilege on <tableName> to <entity>.
INSERT Grant INSERT privilege on <tableName> to <entity>.
SELECT Grant SELECT privilege on <tableName> to <entity>.
TRUNCATE Grant TRUNCATE privilege on <tableName> to <entity>.
UPDATE Grant UPDATE privilege on <tableName> to <entity>.

<tableName>

Name of the database table.

<entity>

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

Parameter Value Descriptions
role Name of role.
user Name of user.

Examples

Permit SELECT and INSERT privileges on the employees table for the administrator role.

GRANT ALL ON TABLE employees TO payrollDept;

Permit SELECT-only privilege on the employees table for the employee role.

GRANT SELECT ON TABLE employees TO employee;

Permit INSERT-only privilege on the employees table for the human resources department role.

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.

Synopsis

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

Parameters

<privilege>

Parameter Value Descriptions
ALL Remove all access privilege for <entity> on <tableName>.
DELETE Remove DELETE privilege for <entity> on <tableName>.
DROP Remove DROP privilege for <entity> on <tableName>.
INSERT Remove INSERT privilege for <entity> on <tableName>.
SELECT Remove SELECT privilege for <entity> on <tableName>.
TRUNCATE Remove TRUNCATE privilege for <entity> on <tableName>.
UPDATE Remove UPDATE privilege for <entity> on <tableName>.

<tableName>

Name of the database table.

<entity>

Name of entity 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 non-employee 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 the employee role.

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.

Synopsis

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

Parameters

<privilege>

Parameter Value Descriptions
ALL Grant all possible access privileges on <viewName> to <entity>.
DROP Grant DROP privilege on <viewName> to <entity>.
INSERT Grant INSERT privilege on <viewName> to <entity>.
SELECT Grant SELECT privilege on <viewName> to <entity>.

<viewName>

Name of the database view.

<entity>

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

Parameter Value Descriptions
role Name of role.
user Name of user.

Examples

Permit SELECT and INSERT privileges on the employees view for the administrator role.

GRANT ALL ON VIEW employees TO payrollDept;

Permit SELECT-only privilege on the employees view for the employee role.

GRANT SELECT ON VIEW employees TO employee;

Permit INSERT-only privilege on the employees view for the human resources department role.

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.

Synopsis

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

Parameters

<privilege>

Parameter Value Descriptions
ALL Remove all access privilege for <entity> on <viewName>.
DROP Remove DROP privilege for <entity> on <viewName>.
INSERT Remove INSERT privilege for <entity> on <viewName>.
SELECT Remove SELECT privilege for <entity> on <viewName>.

<viewName>

Name of the database view.

<entity>

Name of entity 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 view for the non-employee 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 operations on the directors view for the employee role.

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.

Synopsis

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

Parameters

<privilege>

Parameter Value Descriptions
ALL Grant all possible access privileges on <dbName> to <entity>.
CREATE TABLE Grant CREATE TABLE privilege on <dbName> to <entity>. Previously CREATE.
CREATE VIEW Grant CREATE VIEW privilege on <dbName> to <entity>.
CREATE DASHBOARD Grant CREATE DASHBOARD privilege on <dbName> to <entity>.
CREATE Grant CREATE privilege on <dbName> to <entity>.
DELETE Grant DELETE privilege on <dbName> to <entity>.
DELETE DASHBOARD Grant DELETE DASHBOARD privilege on <dbName> to <entity>.
DROP Grant DROP privilege on <dbName> to <entity>.
DROP VIEW Grant DROP VIEW privilege on <dbName> to <entity>.
EDIT DASHBOARD Grant EDIT DASHBOARD privilege on <dbName> to <entity>.
INSERT Grant INSERT privilege on <dbName> to <entity>.
SELECT Grant SELECT privilege on <dbName> to <entity>.
SELECT VIEW Grant SELECT VIEW privilege on <dbName> to <entity>.
TRUNCATE Grant TRUNCATE privilege on <dbName> to <entity>.
UPDATE Grant UPDATE privilege on <dbName> to <entity>.
VIEW DASHBOARD Grant VIEW DASHBOARD privilege on <dbName> to <entity>.

<dbName>

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

<entity>

Parameter Value Descriptions
role Name of role, which must exist.
user Name of user, which must exist. See Users and Databases.

Examples

Permit CREATE-only operations on the companydb database for the administrator role.

GRANT CREATE ON DATABASE companydb TO payrollDept;

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

GRANT SELECT ON DATABASE companydb TO employee;

Permit INSERT-only operations on the companydb database for the the human resources department role.

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.

Synopsis

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

Parameters

<privilege>

Parameter Value Descriptions
ALL Remove all possible access privileges on <dbName> to <entity>.
CREATE TABLE Remove CREATE TABLE privilege on <dbName> to <entity>. Previously CREATE.
CREATE VIEW Remove CREATE VIEW privilege on <dbName> to <entity>.
CREATE DASHBOARD Remove CREATE DASHBOARD privilege on <dbName> to <entity>.
CREATE Remove CREATE privilege on <dbName> for <entity>.
DELETE Remove DELETE privilege on <dbName> to <entity>.
DELETE DASHBOARD Remove DELETE DASHBOARD privilege on <dbName> to <entity>.
DROP Remove DROP privilege on <dbName> to <entity>.
DROP VIEW Remove DROP VIEW privilege on <dbName> to <entity>.
EDIT DASHBOARD Remove EDIT DASHBOARD privilege on <dbName> to <entity>.
INSERT Remove INSERT privilege on <dbName> to <entity>.
SELECT Remove SELECT privilege on <dbName> to <entity>.
SELECT VIEW Remove SELECT VIEW privilege on <dbName> to <entity>.
TRUNCATE Remove TRUNCATE privilege on <dbName> to <entity>.
UPDATE Remove UPDATE privilege on <dbName> to <entity>.
VIEW DASHBOARD Remove VIEW DASHBOARD privilege on <dbName> to <entity>.

<dbName>

Name of the database.

<entity>

Parameter Value Descriptions
role Name of role.
user Name of user.

Example

Prohibit SELECT and INSERT operations on the employees database for the non-employee role.

REVOKE ALL ON DATABASE employees FROM nonemployee;

Prohibit SELECT operations on the directors database for an employee.

REVOKE SELECT ON DATABASE directors FROM employee;

Prohibit INSERT operations on the directors database for an 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.

Synopsis

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

Parameters

<privilege>

Parameter Value Descriptions
ALL Grant all possible access privileges on <dashboardId> to <entity>.
CREATE Grant CREATE privilege to <entity>.
DELETE Grant DELETE privilege on <dashboardId> to <entity>.
EDIT Grant EDIT privilege on <dashboardId> to <entity>.
VIEW Grant VIEW privilege on <dashboardId> to <entity>.

<dashboardId>

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

<entity>

Parameter Value Descriptions
role Name of role, which must exist.
user Name of user, which must exist. See Users and Databases.

Examples

Permit DELETE privileges on the dashboard ID 740 for the administrator role.

GRANT DELETE ON DASHBOARD 740 TO payrollDept;

Permit VIEW-only privilege on dashboard 730 for user Dennis.

GRANT VIEW ON DASHBOARD 730 TO dennis;

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

GRANT EDIT ON DASHBOARD 740 TO hrdept;

See Also

REVOKE ON DASHBOARD

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

Synopsis

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

Parameters

<privilege>

Parameter Value Descriptions
ALL Revoke all possible access privileges on <dashboardId> for <entity>.
CREATE Revoke CREATE privilege for <entity>.
DELETE Revoke DELETE privilege on <dashboardId> for <entity>.
EDIT Revoke EDIT privilege on <dashboardId> for <entity>.
VIEW Revoke VIEW privilege on <dashboardId> for <entity>.

<dashboardId>

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

<entity>

Parameter Value Descriptions
role Name of role, which must exist.
user Name of user, which must exist. See Users and Databases.

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;

Example Roles and Privileges Session

Creating Tables

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

Creating User Accounts

Create Data Entry Department Employees

create user dataEntryDeptEmployee1 (password = 'ded1');

create user dataEntryDeptEmployee2 (password = 'ded2');

create user dataEntryDeptEmployee3 (password = 'ded3');

create user dataEntryDeptManagerEmployee4 (password = 'ded4');

Create Marketing Department Employees

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 Sales Department Employees

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 Information Systems Department Employees

create user informationSystemsDeptEmployee1 (password = 'is1');

create user informationSystemsManagerDeptEmployee2 (password = 'is2');

Granting Access Privileges to Data Entry Department Employees

Create Data Entry Department Role

create role dataEntryDeptRole1;

Grant Privilege to Data Entry Department Role

grant insert on database mapd to dataEntryDeptRole1;

Grant Data Entry Department Role to Data Entry Department Employees

grant dataEntryDeptRole1 to dataEntryDeptEmployee1;

grant dataEntryDeptRole1 to dataEntryDeptEmployee2;

grant dataEntryDeptRole1 to dataEntryDeptEmployee3;

Grant Additional Privilege to Data Entry Department Manager

grant select on database mapd to dataEntryDeptManagerEmployee4;

Granting Access Privileges to Marketing Department Employees

Create Marketing Department Roles

create role marketingDeptRole1;

create role marketingDeptRole2;

create role marketingDeptRole3;

Grant Privilege to Marketing Department Roles

grant select on table table1 to marketingDeptRole1;

grant select on table table2 to marketingDeptRole1;

grant select on table table3 to marketingDeptRole2;

Grant Marketing Department Roles to Marketing Department Employees

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 Sales Department Roles

create role salesDeptRole1;

create role salesDeptRole2;

create role salesDeptRole3;

Grant Privilege to Sales Department Roles

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 Sales Department Roles to Sales Department Employees

grant salesDeptRole1 to salesDeptEmployee1;

grant salesDeptRole2 to salesDeptEmployee2;

grant salesDeptRole2 to salesDeptEmployee3;

grant salesDeptRole3 to salesDeptEmployee4;

Grant All Roles to Sales Department Manager

grant salesDeptRole1 to salesDeptManagerEmployee5;

grant salesDeptRole2 to salesDeptManagerEmployee5;

grant salesDeptRole3 to salesDeptManagerEmployee5;

Granting Access Privileges to Information Systems Department Employees

Grant All Database Access Privileges Directly to Information System Department Employees

grant all on database mapd to informationSystemsDeptEmployee1;

grant all on database mapd to informationSystemsDeptManagerEmployee2;

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.

CommandDescriptionExample
\roles Reports all roles.
mapdql> \roles
DB3_REMOTE
DB3_ROLE
R1
R2
         
\role_list userName Reports all roles granted to the given user. The userName specified must exist.
mapdql> \role_list usr2
R2
R4
ROLE_S
R1
         
\privileges { roleName | userName } Reports all database objects privileges granted to the given role or user. The role or specified user must exist. Reports all database objects privileges granted to the given role or user. The <roleName> or <userName> specified must exist.
mapdql> \privileges role_s
mapd (database) privileges: insert create truncate
t1 (table) privileges: select insert
t2 (table) privileges: select
u1t1 (table) privileges: select truncate

mapdql> \privileges usr2
mapd (database) privileges:
t1 (table) privileges: select truncate
t2 (table) privileges: select insert
t3 (table) privileges: select insert truncate
         
\object_privileges objectType objectName Reports all privileges granted to the given object for all roles and users. If the specified objectName does not exist, no results are reported.
mapdql> \object_privileges database db3
Role/User: DB3_REMOTE
           DB Object: db3 (table)    privileges: select insert truncate
Role/User: DB3_ROLE
           DB Object: db3 (database) privileges: select insert create truncate
           DB Object: db3 (table)    privileges: select insert truncate
Role/User: R1
           DB Object: db3 (table)    privileges: select
Role/User: USR3
           DB Object: db3 (database) privileges: select insert create truncate
           DB Object: db3 (table)    privileges: insert