Privileges¶

To execute statements, a user needs to have the required privileges.

Table of contents

Introduction ¶

CrateDB has a superuser (crate) which has the privilege to do anything. The privileges of other users and roles have to be managed using the GRANT, DENY or REVOKE statements.

The privileges that can be granted, denied or revoked are:

DQL
DML
DDL
AL

Skip to Privilege types for details.

Privilege Classes ¶

The privileges can be granted on different classes:

CLUSTER
SCHEMA
TABLE and VIEW

Skip to Hierarchical inheritance of privileges for details.

A user with AL on level CLUSTER can grant privileges they have themselves to other users or roles as well.

Granting Data Query Language (DQL) privilege to a user or role, indicates that this user/role is allowed to execute SELECT, SHOW, REFRESH and COPY TO statements, as well as using the available user-defined functions, on the object for which the privilege applies.

`DML`¶

Granting Data Manipulation Language (DML) privilege to a user or role, indicates that this user/role is allowed to execute INSERT, COPY FROM, UPDATE and DELETE statements, on the object for which the privilege applies.

`DDL`¶

Granting Data Definition Language (DDL) privilege to a user or role, indicates that this user/role is allowed to execute the following statements on objects for which the privilege applies:

CREATE TABLE
DROP TABLE
CREATE VIEW
DROP VIEW
CREATE FUNCTION
DROP FUNCTION
CREATE REPOSITORY
DROP REPOSITORY
CREATE SNAPSHOT
DROP SNAPSHOT
RESTORE SNAPSHOT
ALTER TABLE

`AL`¶

Granting Administration Language (AL) privilege to a user or role, enables the user/role to execute the following statements:

CREATE USER/ROLE
DROP USER/ROLE
SET GLOBAL

All statements enabled via the AL privilege operate on a cluster level. So granting this on a schema or table level will have no effect.

Hierarchical inheritance of privileges ¶

Privileges can be managed on three different levels, namely: CLUSTER, SCHEMA, and TABLE/VIEW.

When a privilege is assigned on a certain level, the privilege will propagate down the hierarchy. Privileges defined on a lower level will always override those from a higher level:

  cluster
    ||
  schema
   /  \
table view

This statement will grant DQL privilege to user riley on all the tables and functions of the doc schema:

cr> GRANT DQL ON SCHEMA doc TO riley;
GRANT OK, 1 row affected (... sec)

This statement will deny DQL privilege to user riley on the doc schema table doc.accounting. However, riley will still have DQL privilege on all the other tables of the doc schema:

cr> DENY DQL ON TABLE doc.accounting TO riley;
DENY OK, 1 row affected (... sec)

Note

In CrateDB, schemas are just namespaces that are created and dropped implicitly. Therefore, when GRANT, DENY or REVOKE are invoked on a schema level, CrateDB takes the schema name provided without further validation.

Privileges can be managed on all schemas and tables of the cluster, except the information_schema.

Views are on the same hierarchy with tables, i.e. a privilege on a view is gained through a GRANT on either the view itself, the schema the view belongs to, or a cluster-wide privilege. Privileges on relations which are referenced in the view do not grant any privileges on the view itself. On the contrary, even if the user/role does not have any privileges on a view’s referenced relations but on the view itself, the user/role can still access the relations through the view. For example:

cr> CREATE VIEW first_customer as SELECT * from doc.accounting ORDER BY id LIMIT 1
CREATE OK, 1 row affected (... sec)

Previously we had issued a DENY for user riley on doc.accounting but we can still access it through the view because we have access to it through the doc schema:

cr> SELECT id from first_customer;
+----+
| id |
+----+
|  1 |
+----+
SELECT 1 row in set (... sec)

Behavior of `GRANT`, `DENY` and `REVOKE`¶

Note

You can only grant, deny, or revoke privileges for an existing user or role. You must first create a user/role and then configure privileges.

`GRANT`¶

To grant a privilege to an existing user or role on the whole cluster, we use the GRANT SQL statement, for example:

cr> GRANT DML TO wolfgang;
GRANT OK, 1 row affected (... sec)

DQL privilege can be granted on the sys schema to user wolfgang, like this:

cr> GRANT DQL ON SCHEMA sys TO wolfgang;
GRANT OK, 1 row affected (... sec)

The following statement will grant all privileges on table doc.books to user wolfgang:

cr> GRANT ALL PRIVILEGES ON TABLE doc.books TO wolfgang;
GRANT OK, 4 rows affected (... sec)

Using “ALL PRIVILEGES” is a shortcut to grant all the currently grantable privileges to a user or role.

Note

If no schema is specified in the table ident, the table will be looked up in the current schema.

If a user/role with the name specified in the SQL statement does not exist the statement returns an error:

cr> GRANT DQL TO layla;
RoleUnknownException[Role 'layla' does not exist]

To grant ALL PRIVILEGES to user will on the cluster, we can use the following syntax:

cr> GRANT ALL PRIVILEGES TO will;
GRANT OK, 4 rows affected (... sec)

Using ALL PRIVILEGES is a shortcut to grant all the currently grantable privileges to a user or role, namely DQL, DML and DDL.

Privileges can be granted to multiple users/roles in the same statement, like so:

cr> GRANT DDL ON TABLE doc.books TO wolfgang, will;
GRANT OK, 1 row affected (... sec)

`DENY`¶

To deny a privilege to an existing user or role on the whole cluster, use the DENY SQL statement, for example:

cr> DENY DDL TO will;
DENY OK, 1 row affected (... sec)

DQL privilege can be denied on the sys schema to user wolfgang like this:

cr> DENY DQL ON SCHEMA sys TO wolfgang;
DENY OK, 1 row affected (... sec)

The following statement will deny DQL privilege on table doc.books to user wolfgang:

cr> DENY DQL ON TABLE doc.books TO wolfgang;
DENY OK, 1 row affected (... sec)

DENY ALL or DENY ALL PRIVILEGES will deny all privileges to a user or role, on the cluster it can be used like this:

cr> DENY ALL TO will;
DENY OK, 3 rows affected (... sec)

`REVOKE`¶

To revoke a privilege that was previously granted or denied to a user or role use the REVOKE SQL statement, for example the DQL privilege that was previously denied to user wolfgang on the sys schema, can be revoked like this:

cr> REVOKE DQL ON SCHEMA sys FROM wolfgang;
REVOKE OK, 1 row affected (... sec)

The privileges that were granted and denied to user wolfgang on doc.books can be revoked like this:

cr> REVOKE ALL ON TABLE doc.books FROM wolfgang;
REVOKE OK, 4 rows affected (... sec)

The privileges that were granted to user will on the cluster can be revoked like this:

cr> REVOKE ALL FROM will;
REVOKE OK, 4 rows affected (... sec)

Note

The REVOKE statement can remove only privileges that have been granted or denied through the GRANT or DENY statements. If the privilege on a specific object was not explicitly granted, the REVOKE statement has no effect. The effect of the REVOKE statement will be reflected in the row count.

Note

When a privilege is revoked from a user or role, it can still be active for that user/role, if the user/role inherits it, from another role.

List privileges ¶

CrateDB exposes the privileges of users and roles of the database through the sys.privileges system table.

By querying the sys.privileges table you can get all information regarding the existing privileges. E.g.:

cr> SELECT * FROM sys.privileges order by grantee, class, ident;
+---------+----------+---------+----------------+-------+------+
| class   | grantee  | grantor | ident          | state | type |
+---------+----------+---------+----------------+-------+------+
| SCHEMA  | riley    | crate   | doc            | GRANT | DQL  |
| TABLE   | riley    | crate   | doc.accounting | DENY  | DQL  |
| TABLE   | will     | crate   | doc.books      | GRANT | DDL  |
| CLUSTER | wolfgang | crate   | NULL           | GRANT | DML  |
+---------+----------+---------+----------------+-------+------+
SELECT 4 rows in set (... sec)

Roles inheritance ¶

Introduction ¶

You can grant, or revoke roles for an existing user or role. This allows to group granted or denied privileges and inherit them to other users or roles.

You must first create usesr and roles and then grant roles to other roles or users. You can configure the privileges of each role before or after granting roles to other roles or users.

Note

Roles can be granted to other roles or users, but users (roles which can also login to the database) cannot be granted to other roles or users.

Note

Superuser crate cannot be granted to other users or roles, and roles cannot be granted to it.

Inheritance ¶

The inheritance can span multiple levels, so you can have role_a which is granted to role_b, which in turn is granted to role_c, and so on. Each role can be granted to multiple other roles and each role or user can be granted multiple other roles. Cycles cannot be created, for example:

cr> GRANT role_a TO role_b;
GRANT OK, 1 row affected (... sec)

cr> GRANT role_b TO role_c;
GRANT OK, 1 row affected (... sec)

cr> GRANT role_c TO role_a;
SQLParseException[Cannot grant role role_c to role_a, role_a is a parent role of role_c and a cycle will be created]

Privileges resolution ¶

When a user executes a statement, the privileges mechanism will check first if the user has been granted the required privileges, if not, it will check if the roles which this user has been granted have those privileges and if not, it will continue checking the roles granted to those parent roles of the user and so on. For example:

cr> GRANT role_a TO role_b;
GRANT OK, 1 row affected (... sec)

cr> GRANT role_b TO role_c;
GRANT OK, 1 row affected (... sec)

cr> GRANT DQL ON TABLE sys.users TO role_a;
GRANT OK, 1 row affected (... sec)

cr> GRANT role_c TO john;
GRANT OK, 1 row affected (... sec)

User john is able to query sys.users, as even though he lacks DQL privilege on the table, he is granted role_c which in turn is granted role_b which is granted role_a, and role has the DQL privilege on sys.users.

Keep in mind that DENY has precedence over GRANT. If a role has been both granted and denied a privilege (directly or through role inheritance), then DENY will take effect. For example, GRANT is inherited from a role and DENY directly set on the user:

cr> GRANT DQL ON TABLE sys.users TO role_a;
GRANT OK, 1 row affected (... sec)

cr> GRANT role_a TO john
GRANT OK, 1 row affected (... sec)

cr> DENY DQL ON TABLE sys.users TO john
DENY OK, 1 row affected (... sec)

User john cannot query sys.users.

Another example with DENY in effect, inherited from a role:

cr> GRANT DQL ON TABLE sys.users TO role_a;
GRANT OK, 1 row affected (... sec)

cr> DENY DQL ON TABLE sys.users TO role_b;
DENY OK, 1 row affected (... sec)

cr> GRANT role_a, role_b TO john;
GRANT OK, 2 rows affected (... sec)

User john cannot query sys.users.

`GRANT`¶

To grant an existing role to an existing user or role on the whole cluster, we use the GRANT SQL statement, for example:

cr> GRANT role_dql TO wolfgang;
GRANT OK, 1 row affected (... sec)

DML privilege can be granted on the sys schema to role role_dml, so, by inheritance, to user wolfgang as well, like this:

cr> GRANT DQL ON SCHEMA sys TO role_dql;
GRANT OK, 1 row affected (... sec)

The following statements will grant all privileges on table doc.books to role role_all_on_books, and by inheritance to user wolfgang as well:

cr> GRANT role_all_on_books TO wolfgang;
GRANT OK, 1 row affected (... sec)

cr> GRANT ALL PRIVILEGES ON TABLE doc.books TO role_all_on_books;
GRANT OK, 4 rows affected (... sec)

If a role with the name specified in the SQL statement does not exist the statement returns an error:

cr> GRANT DDL TO role_ddl;
RoleUnknownException[Role 'role_ddl' does not exist]

Multiple roles can be granted to multiple users/roles in the same statement, like so:

cr> GRANT role_dql, role_all_on_books TO layla, will;
GRANT OK, 4 rows affected (... sec)

Notice that 4 rows affected is returned, as in total there are 2 users, will and layla and each of them is granted two roles: role_dql and role_all_on_books.

`REVOKE`¶

To revoke a role that was previously granted to a user or role use the REVOKE SQL statement. For example role role_dql which was previously granted to users wolfgang,``layla`` and will, can be revoked like this:

cr> REVOKE role_dql FROM wolfgang, layla, will;
REVOKE OK, 3 rows affected (... sec)

If a privilege is revoked from a role which is granted to other roles or users, the privilege is automatically revoked also for those roles and users, for example if we revoke privileges on table doc.books from role_all_on_books:

cr> REVOKE ALL PRIVILEGES ON TABLE doc.books FROM role_all_on_books;
REVOKE OK, 4 rows affected (... sec)

user wolfgang, who is granted the role role_all_on_books, also looses those privileges.

If a user is granted the same privilege by inheriting two different roles, when revoking one of the roles, the user still keeps the privilege. For example if user john gets granted `role_dql and role_dml:

cr> GRANT DQL TO role_dql;
GRANT OK, 1 row affected (... sec)

cr> GRANT DQL, DML TO role_dml;
GRANT OK, 2 rows affected (... sec)

cr> GRANT role_dql, role_dml TO john;
GRANT OK, 2 rows affected (... sec)

and then we revoke role_dql from john:

cr> REVOKE role_dql FROM john;
REVOKE OK, 1 row affected (... sec)

john still has DQL privilege since it inherits it from role_dml which is still granted to him.

Privileges¶

Introduction ¶

Privilege Classes ¶

Privilege types ¶

`DQL`¶

`DML`¶

`DDL`¶

`AL`¶

Hierarchical inheritance of privileges ¶

Behavior of `GRANT`, `DENY` and `REVOKE`¶

`GRANT`¶

`DENY`¶

`REVOKE`¶

List privileges ¶

Roles inheritance ¶

Introduction ¶

Inheritance ¶

Privileges resolution ¶

`GRANT`¶

`REVOKE`¶