Join us on
Star us on
Get Started
Slack
GitHub
Get Started
v2.5 (latest) v2.2 (stable) v2.1 (earlier version) v2.0 (earlier version) v1.3 (earlier version)
  • GET STARTED
    • Quick start
      • 1. Install YugabyteDB
      • 2. Create a local cluster
      • 3. Explore YSQL
      • 4. Build an application
        • Java
        • NodeJS
        • Go
        • Python
        • Ruby
        • C#
        • PHP
        • C++
        • C
    • Introduction
    • Explore core
      • 1. Linear scalability
      • 2. Fault tolerance
      • 3. Global distribution
      • 4. Auto sharding
      • 5. Tunable reads
      • 6. Observability
  • USER GUIDES
    • Develop
      • Learn app development
        • 1. SQL vs NoSQL
        • 2. Data modeling
        • 3. Data types
        • 4. ACID transactions
        • 5. Aggregations
        • 6. Batch operations
        • 7. Date and time
        • 8. Strings and text
      • Ecosystem integrations
        • Apache Kafka
        • Apache Spark
        • JanusGraph
        • KairosDB
        • Presto
        • Metabase
      • Real-world examples
        • E-Commerce App
        • IoT Fleet Management
        • Retail Analytics
      • Explore sample applications
    • Deploy
      • Checklist
      • Manual deployment
        • 1. System configuration
        • 2. Install software
        • 3. Start YB-Masters
        • 4. Start YB-TServers
        • 5. Verify deployment
      • Kubernetes
        • Helm Chart
        • Helm configuration
        • Local SSD
      • Docker
      • Public clouds
        • Amazon Web Services
        • Google Cloud Platform
        • Microsoft Azure
      • Pivotal Cloud Foundry
      • Yugabyte Platform
        • 1. Prepare cloud environment
        • 2. Install Admin Console
        • 3. Configure Admin Console
        • 4. Configure Cloud Providers
    • Benchmark
      • Performance
      • YCSB
      • Large datasets
    • Secure
      • Security checklist
      • Authentication
      • Authorization
        • 1. RBAC Model
        • 2. Create Roles
        • 3. Grant permissions
      • TLS encryption
        • 1. Prepare nodes
        • 2. Server-server encryption
        • 3. Client-server encryption
        • 4. Connect to cluster
      • Encryption at Rest
    • Manage
      • Backup and restore
        • Backing up data
        • Restoring data
      • Data migration
        • Bulk import
        • Bulk export
      • Change cluster config
      • Upgrade deployment
      • Diagnostics reporting
      • Yugabyte Platform
        • Create universe - Multi-zone
        • Create universe - Multi-region
        • Edit universe
        • Edit config flags
        • Health checking and alerts
        • Create and edit instance tags
        • Node status and actions
        • Read replicas
        • Back up and restore
        • Upgrade universe
        • Delete universe
    • Troubleshoot
      • Troubleshooting overview
      • Cluster level issues
        • YCQL connection issues
        • YEDIS connection Issues
      • Node level issues
        • Check processes
        • Inspect logs
        • System statistics
      • Yugabyte Platform
        • Troubleshoot universes
  • REFERENCE
    • APIs
      • YSQL
        • Statements
          • ABORT
          • ALTER DATABASE
          • ALTER DOMAIN
          • ALTER TABLE
          • BEGIN
          • COMMENT
          • COMMIT
          • COPY
          • CREATE DATABASE
          • CREATE DOMAIN
          • CREATE INDEX
          • CREATE SCHEMA
          • CREATE SEQUENCE
          • CREATE TABLE
          • CREATE TABLE AS
          • CREATE TYPE
          • CREATE USER
          • CREATE VIEW
          • DEALLOCATE
          • DELETE
          • DROP DATABASE
          • DROP DOMAIN
          • DROP SEQUENCE
          • DROP TABLE
          • DROP TYPE
          • END
          • EXECUTE
          • EXPLAIN
          • GRANT
          • INSERT
          • LOCK
          • PREPARE
          • RESET
          • REVOKE
          • ROLLBACK
          • SELECT
          • SET
          • SET CONSTRAINTS
          • SET TRANSACTION
          • SHOW
          • SHOW TRANSACTION
          • TRUNCATE
          • UPDATE
        • Data types
          • Binary
          • Boolean
          • Character
          • Date-time
          • Json
          • Money
          • Numeric
          • Serial
          • UUID
        • Expressions
          • currval()
          • lastval()
          • nextval()
        • Keywords
        • Reserved Names
      • YCQL
        • Quick Start YCQL
        • ALTER KEYSPACE
        • ALTER ROLE
        • ALTER TABLE
        • CREATE INDEX
        • CREATE KEYSPACE
        • CREATE ROLE
        • CREATE TABLE
        • CREATE TYPE
        • DROP INDEX
        • DROP KEYSPACE
        • DROP ROLE
        • DROP TABLE
        • DROP TYPE
        • GRANT PERMISSION
        • GRANT ROLE
        • REVOKE PERMISSION
        • REVOKE ROLE
        • USE
        • INSERT
        • SELECT
        • UPDATE
        • DELETE
        • TRANSACTION
        • TRUNCATE
        • Simple Value
        • Subscript
        • Function Call
        • Operator Call
        • BLOB
        • BOOLEAN
        • MAP, SET, LIST
        • FROZEN
        • INET
        • Integer & Counter
        • Non-Integer
        • TEXT
        • Date & Time Types
        • UUID & TIMEUUID
        • JSONB
        • Date and time functions
    • CLIs
      • yb-ctl
      • yb-docker-ctl
      • yb-master
      • yb-tserver
      • ysqlsh
      • cqlsh
    • Sample data
      • Chinook
      • Northwind
      • PgExercises
      • SportsDB
    • Tools
      • TablePlus
  • RELEASES
    • Release history
      • v1.3.1
      • v1.3.0
      • v1.2.12
      • v1.2.11
      • v1.2.10
      • v1.2.9
      • v1.2.8
      • v1.2.6
      • v1.2.5
      • v1.2.4
  • CONCEPTS
    • Architecture
      • Design goals
      • Layered architecture
      • Basic concepts
        • Universe
        • YB-TServer
        • YB-Master
        • Acknowledgements
      • Query layer
        • Overview
      • DocDB store
        • Sharding
        • Replication
        • Persistence
        • Performance
      • DocDB transactions
        • Isolation Levels
        • Single row transactions
        • Distributed transactions
        • Transactional IO path
  • FAQ
    • Comparisons
      • CockroachDB
      • Google Cloud Spanner
      • MongoDB
      • FoundationDB
      • Amazon DynamoDB
      • Azure Cosmos DB
      • Apache Cassandra
      • Redis in-memory store
      • Apache HBase
    • Other FAQs
      • Product
      • Architecture
      • Yugabyte Platform
      • API compatibility
  • CONTRIBUTOR GUIDES
    • Get involved
  • Misc
    • YEDIS
      • Quick start
      • Develop
        • Client drivers
          • C
          • C++
          • C#
          • Go
          • Java
          • NodeJS
          • Python
      • API reference
        • APPEND
        • AUTH
        • CONFIG
        • CREATEDB
        • DELETEDB
        • LISTDB
        • SELECT
        • DEL
        • ECHO
        • EXISTS
        • EXPIRE
        • EXPIREAT
        • FLUSHALL
        • FLUSHDB
        • GET
        • GETRANGE
        • GETSET
        • HDEL
        • HEXISTS
        • HGET
        • HGETALL
        • HINCRBY
        • HKEYS
        • HLEN
        • HMGET
        • HMSET
        • HSET
        • HSTRLEN
        • HVALS
        • INCR
        • INCRBY
        • KEYS
        • MONITOR
        • PEXPIRE
        • PEXPIREAT
        • PTTL
        • ROLE
        • SADD
        • SCARD
        • RENAME
        • SET
        • SETEX
        • PSETEX
        • SETRANGE
        • SISMEMBER
        • SMEMBERS
        • SREM
        • STRLEN
        • ZRANGE
        • TSADD
        • TSCARD
        • TSGET
        • TSLASTN
        • TSRANGEBYTIME
        • TSREM
        • TSREVRANGEBYTIME
        • TTL
        • ZADD
        • ZCARD
        • ZRANGEBYSCORE
        • ZREM
        • ZREVRANGE
        • ZSCORE
        • PUBSUB
        • PUBLISH
        • SUBSCRIBE
        • UNSUBSCRIBE
        • PSUBSCRIBE
        • PUNSUBSCRIBE
> Secure > Authorization >

3. Grant permissions

Attention

This page documents an earlier version. Go to the latest (v2.3) version.
  • 1. Create role hierarchy
  • 2. List permissions for roles
  • 3. Grant permissions to roles
    • Grant read access
    • Grant data modify access
    • Grant alter table access
    • Grant all privileges
  • 4. Revoke permissions from roles

In this tutorial, we shall run through a scenario. Assume a company has an engineering organization, with three sub-teams - developers, qa and DB admins. We are going to create a role for each of these entities.

Here is what we want to achieve from a role-based access control (RBAC) perspective.

  • All members of engineering should be able to read data from any keyspace and table.
  • Both developers and qa should be able to modify data in existing tables in the keyspace dev_keyspace.
  • QA should be able to alter the integration_tests table in the keyspace dev_keyspace.
  • DB admins should be able to perform all operations on any keyspace.

1. Create role hierarchy

Connect to the cluster using a superuser role. Read more about enabling authentication and connecting using a superuser role in YugabyteDB clusters for YCQL. For this article, we are using the default cassandra user and connect to the cluster using cqlsh as follows:

$ cqlsh -u cassandra -p cassandra

Create a keyspace eng_keyspace.

cassandra@cqlsh> CREATE KEYSPACE IF NOT EXISTS dev_keyspace;

Create the dev_keyspace.integration_tests table:

CREATE TABLE dev_keyspace.integration_tests (
  id UUID PRIMARY KEY,
  time TIMESTAMP,
  result BOOLEAN,
  details JSONB
);

Next, create roles engineering, developer, qa and db_admin.

cassandra@cqlsh> CREATE ROLE IF NOT EXISTS engineering;
                 CREATE ROLE IF NOT EXISTS developer;
                 CREATE ROLE IF NOT EXISTS qa;
                 CREATE ROLE IF NOT EXISTS db_admin;

Grant the engineering role to developer, qa and db_admin roles since they are all a part of the engineering organization.

cassandra@cqlsh> GRANT engineering TO developer;
                 GRANT engineering TO qa;
                 GRANT engineering TO db_admin;

List all the roles.

cassandra@cqlsh> SELECT role, can_login, is_superuser, member_of FROM system_auth.roles;

You should see the following output:

 role        | can_login | is_superuser | member_of
-------------+-----------+--------------+-----------------
          qa |     False |        False | ['engineering']
   developer |     False |        False | ['engineering']
 engineering |     False |        False |                []
    db_admin |     False |        False | ['engineering']
   cassandra |      True |         True |                []

(5 rows)

2. List permissions for roles

You can list all permissions granted to the various roles with the following command:

cassandra@cqlsh> SELECT * FROM system_auth.role_permissions;

You should see something like the following output.

 role      | resource          | permissions
-----------+-------------------+--------------------------------------------------------------
 cassandra | roles/engineering |                               ['ALTER', 'AUTHORIZE', 'DROP']
 cassandra |   roles/developer |                               ['ALTER', 'AUTHORIZE', 'DROP']
 cassandra |          roles/qa |                               ['ALTER', 'AUTHORIZE', 'DROP']
 cassandra | data/dev_keyspace | ['ALTER', 'AUTHORIZE', 'CREATE', 'DROP', 'MODIFY', 'SELECT']
 cassandra |    roles/db_admin |                               ['ALTER', 'AUTHORIZE', 'DROP']

(5 rows)

The above shows the various permissions the cassandra role has. Since cassandra is a superuser, it has all permissions on all keyspaces, including ALTER, AUTHORIZE and DROP on the roles we created (engineering, developer, qa and db_admin).

Note

For the sake of brevity, we will drop the cassandra role related entries in the remainder of this article.

3. Grant permissions to roles

In this section, we will grant permissions to achieve the following as mentioned in the beginning of this tutorial:

  • All members of engineering should be able to read data from any keyspace and table.
  • Both developers and qa should be able to modify data in existing tables in the keyspace dev_keyspace.
  • Developers should be able to create, alter and drop tables in the keyspace dev_keyspace.
  • DB admins should be able to perform all operations on any keyspace.

Grant read access

All members of engineering should be able to read data from any keyspace and table. Use the GRANT SELECT command to grant SELECT (or read) access on ALL KEYSPACES to the engineering role. This can be done as follows:

cassandra@cqlsh> GRANT SELECT ON ALL KEYSPACES TO engineering;

We can now verify that the engineering role has SELECT permission as follows:

cassandra@cqlsh> SELECT * FROM system_auth.role_permissions;

The output should look similar to below, where we see that the engineering role has SELECT permission on the data resource.

 role        | resource          | permissions
-------------+-------------------+--------------------------------------------------------------
 engineering |              data |                                                   ['SELECT']
 ...

Note

The resource data represents all keyspaces and tables.

Granting the role engineering to any other role will cause all those roles to inherit the SELECT permissions. Thus, developer, qa and db_admin will all inherit the SELECT permission.

Grant data modify access

Both developers and qa should be able to modify data existing tables in the keyspace dev_keyspace. They should be able to execute statements such as INSERT, UPDATE, DELETE or TRUNCATE in order to modify data on existing tables. This can be done as follows:

cassandra@cqlsh> GRANT MODIFY ON KEYSPACE dev_keyspace TO developer;
                 GRANT MODIFY ON KEYSPACE dev_keyspace TO qa;

We can now verify that the developer and qa roles have the appropriate MODIFY permission by running the following command.

cassandra@cqlsh> SELECT * FROM system_auth.role_permissions;

We should see that the developer and qa roles have MODIFY permissions on the keyspace data/dev_keyspace.

 role        | resource          | permissions
-------------+-------------------+--------------------------------------------------------------
          qa | data/dev_keyspace |                                                   ['MODIFY']
   developer | data/dev_keyspace |                                                   ['MODIFY']
 engineering |              data |                                                   ['SELECT']
 ...

Note

In the resource hierarchy, data represents all keyspaces and data/dev_keyspace represents one keyspace in it.

Grant alter table access

QA should be able to alter the table integration_tests in the keyspace dev_keyspace. This can be done as follows.

cassandra@cqlsh> GRANT ALTER ON TABLE dev_keyspace.integration_tests TO qa;

Once again, run the following command to verify the permissions.

cassandra@cqlsh> SELECT * FROM system_auth.role_permissions;

We should see a new row added, which grants the ALTER permission on the resource data/dev_keyspace/integration_tests to the role qa.

 role        | resource                            | permissions
-------------+-------------------------------------+--------------------------------------------------------------
          qa |                   data/dev_keyspace |                                                   ['MODIFY']
          qa | data/dev_keyspace/integration_tests |                                                    ['ALTER']
   developer |                   data/dev_keyspace |                                                   ['MODIFY']
 engineering |                                data |                                                   ['SELECT']

Note

The resource data/dev_keyspace/integration_tests denotes the hierarchy:

All Keyspaces (data) > keyspace (dev_keyspace) > table (integration_tests)

Grant all privileges

DB admins should be able to perform all operations on any keyspace. There are two ways to achieve this:

  1. The DB admins can be granted the superuser privilege. Read more about granting the superuser privilege to roles. Note that doing this will give the DB admin all the permissions over all the roles as well.

  2. Grant ALL privileges to the db_admin role. This can be achieved as follows.

cassandra@cqlsh> GRANT ALL ON ALL KEYSPACES TO db_admin;

Run the following command to verify the permissions:

cassandra@cqlsh> SELECT * FROM system_auth.role_permissions;

We should see the following, which grants the all permissions on the resource data to the role db_admin.

 role        | resource                            | permissions
-------------+-------------------------------------+--------------------------------------------------------------
          qa |                   data/dev_keyspace |                                                   ['MODIFY']
          qa | data/dev_keyspace/integration_tests |                                                    ['ALTER']
   developer |                   data/dev_keyspace |                                                   ['MODIFY']
 engineering |                                data |                                                   ['SELECT']
    db_admin |                                data | ['ALTER', 'AUTHORIZE', 'CREATE', 'DROP', 'MODIFY', 'SELECT']
    ...

4. Revoke permissions from roles

Let us say we want to revoke the AUTHORIZE permission from the DB admins so that they can no longer change permissions for other roles. This can be done as follows.

cassandra@cqlsh> REVOKE AUTHORIZE ON ALL KEYSPACES FROM db_admin;

Run the following command to verify the permissions.

cassandra@cqlsh> SELECT * FROM system_auth.role_permissions;

We should see the following output.

 role        | resource                            | permissions
-------------+-------------------------------------+--------------------------------------------------------------
          qa |                   data/dev_keyspace |                                                   ['MODIFY']
          qa | data/dev_keyspace/integration_tests |                                                    ['ALTER']
   developer |                   data/dev_keyspace |                                                   ['MODIFY']
 engineering |                                data |                                                   ['SELECT']
    db_admin |                                data |              ['ALTER', 'CREATE', 'DROP', 'MODIFY', 'SELECT']
    ...

The AUTHORIZE permission is no longer granted to the db_admin role.

  • 1. Create role hierarchy
  • 2. List permissions for roles
  • 3. Grant permissions to roles
    • Grant read access
    • Grant data modify access
    • Grant alter table access
    • Grant all privileges
  • 4. Revoke permissions from roles
Ask our community
  • Slack
  • Github
  • Forum
  • StackOverflow
Yugabyte
Contact Us
Copyright © 2017-2020 Yugabyte, Inc. All rights reserved.