Create read-only database user by using "GRANT DQL"

Tutorials
,
1

Introduction

Data flows in analytical applications like pulling data into business intelligence or visualization tools typically only needs read-only access to database tables and views.

CrateDB’s privilege system provides the DQL privilege type to assign corresponding read-only permissions to a database user, see also users and roles management.

Usage

Create database user.

CREATE USER "read-only" WITH (password = '<YOUR_PASSWORD>');

Assign privilege type DQL, applying to all privilege classes.

GRANT DQL TO "read-only";

Privilege classes

CrateDB permissions are assigned to privilege classes. In this case, to assign read-only access to individual entities, use the SCHEMA, TABLE, or VIEW class identifiers.

GRANT DQL ON SCHEMA doc TO "read-only";
GRANT DQL ON TABLE doc.testdrive TO "read-only";
GRANT DQL ON VIEW doc.testdrive TO "read-only";

Verify

When authenticating using the database user, for example using crash, you can verify that DDL and DML operations are not permitted.

crash --hosts http://read-only:foo@localhost:4200/

cr> CREATE TABLE t01 (id INT);
MissingPrivilegeException[Missing 'DDL' privilege for user 'read-only']

Appendix

The reference documentation pages include essential details about CrateDB’s privilege system. In particular, we would like to highlight the sections about hierarchical inheritance of privileges and privilege details of database VIEWs.

1 Like