Skip to main content

Row and column level security

Row and column level security is an alpha feature that should be considered preview and subject to change or removal at any time. Alpha features are provided "as is," and are not subject to Imply SLAs.

You can use the SQL Views feature of Imply's distribution of Apache Druid to limit user access to rows or columns within a datasource. The Imply View Manager lets you define views that expose a subset of data for access to a type of user. You can assign permissions for specific views to user roles to enable or disable user access. The examples in this topic use the basic metadata authenticator and authorizer. For information on other security options, see Basic Security.

SQL Views are a good security strategy when you want to restrict user access to a limited subset of the data within a datasource based upon a security role without duplicating the datasource. If you don't need to restrict access by rows or columns, this security model adds complexity that may exceed your requirements. If you already use row and column level security in Pivot data cubes, Druid level row and column security is an alternative. In this version, views will not be visible in Pivot.

Consider the following example from the wikipedia dataset. Imagine that you want users with the "english" role to see only the data from the "#en.wikipedia" channel, but you only want them to see the time, added, channel, and page columns and no others. You can create an "english" view using the SQL Views API that limits results only your required columns and the rows that have "#en.wikipedia". Then you can assign the users to the role.

The instructions in this topic assume that you have access to load extensions for Druid and that you have administrator privileges. To load wikipedia data set for the examples, see the Quickstart.

Known issues​

  • Row and column level security uses SQL views in Druid. See View Manager for restrictions when creating views.
  • Views are not yet visible in Pivot.
  • You cannot update a view. Instead, delete and recreate it.
  • You have to manually define permissions for administrator authorization. See Add the permissions for SQL Views to roles.
  • You cannot create views with parameter variables that accept user data from the query context to control data access.

Load the View Manager extension​

To use SQL Views enable the Imply View Manager as follows:

  1. Edit the common.runtime.properties to add the imply-view-manager to the list of loaded extensions. For example:
druid.extensions.loadList=["druid-hdfs-storage", "druid-kafka-indexing-service", "druid-datasketches", "druid-basic-security", "imply-view-manager"]

The example here also loads druid-basic-security used for authentication and authorization in the examples. For more information, see Basic Security.

  1. Specify imply for the view manager type:
# Specify the view manager
druid.sql.viewmanager.type=imply
  1. From the Druid Console, open the Status panel to verify that the Extension "imply-view-manager" is loaded.

For additional configuration options, see View Manager extension.

Add permissions for SQL Views to roles​

SQL views depend on the authorizer to validate permissions. In turn, the authorizer depends on the authenticator to validate a user's identity. Therefore, to use the security features with SQL views, you must enable authentication and authorization for Druid. See Basic Security. With authentication and authorization enabled, you can use the roles API to add READ and WRITE privileges for all VIEW resource types to the "admin" role.

POST to the Coordinator Security roles API to update the existing "admin" role to include READ and write privileges for views as follows:

curl --location --request POST 'http://localhost:8081/druid-ext/basic-security/authorization/db/MyBasicMetadataAuthorizer/roles/admin/permissions' \
--header 'Content-Type: application/json' \
--data-raw '[
{
  "resource": {
    "name": ".*",
    "type": "DATASOURCE"
  },
  "action": "READ"
},
{
  "resource": {
    "name": ".*",
    "type": "DATASOURCE"
  },
  "action": "WRITE"
},
{
  "resource": {
    "name": ".*",
    "type": "CONFIG"
  },
  "action": "READ"
},
{
  "resource": {
    "name": ".*",
    "type": "CONFIG"
  },
  "action": "WRITE"
},
{
  "resource": {
    "name": ".*",
    "type": "STATE"
  },
  "action": "READ"
},
{
  "resource": {
    "name": ".*",
    "type": "STATE"
  },
  "action": "WRITE"
},
{
  "resource": {
    "name": ".*",
    "type": "VIEW"
  },
  "action": "READ"
},
{
  "resource": {
    "name": ".*",
    "type": "VIEW"
  },
  "action": "WRITE"
}
]'

A successful update returns HTTP Status Code 200. To verify, you can run a GET:

curl --location --request GET 'http://localhost:8081/druid-ext/basic-security/authorization/db/MyBasicMetadataAuthorizer/roles/admin/permissions'

Create a view​

Call the SQL Views API to create a SQL view with the row and column restrictions. For example, create a view that only displays rows that have a value of "#en.wikipedia" and only contains the time, added, channel, and page columns:

curl --location --request POST 'http://localhost:8081/druid-ext/view-manager/v1/views/english' \
--header 'Content-Type: application/json' \
--header 'Accept: application/json' \
--data-raw '{
  "viewSql": "SELECT __time, added, channel, page FROM druid.wikipedia WHERE channel = '\''#en.wikipedia'\''"
}'

When Druid creates the View, it returns a 201 Created response. Navigate to the views. namespace on the Query* tab to see the view. For instance:

Druid dashboard Query tab showing the view namespace with the example view

For more detail, including limitations for creating views, see View Manager and Create a view.

Query a view​

You can query a view like you would a datasource, but you need to specify the "view" namespace. For example:

select * from "view"."english"

Add permissions for a view to a role​

Druid roles control access to views. You can use the Coordinator Security API to assign permissions for a view the same way you would a datasource. For example, to grant query access to a view, authorize the role with the READ action for the view. See Managing view access for more information.

For example, the following steps create a read only role and assign the READ permission the "english" view:

  1. Create the role:
curl --location --request POST 'http://localhost:8081/druid-ext/basic-security/authorization/db/MyBasicMetadataAuthorizer/roles/read_only'
  1. Assign read permissions to the new role:
curl --location --request POST 'http://localhost:8081/druid-ext/basic-security/authorization/db/MyBasicMetadataAuthorizer/roles/read_only/permissions' \
--header 'Content-Type: application/json' \
--data-raw '[
{
  "resource": {
    "name": "english",
    "type": "VIEW"
  },
  "action": "READ"
}
]'

Any user with the "read_only" role will only have access to the "english" view, not the underlying datasource nor any other views nor resources. For information about how to assign users to a role, see Coordinator Security API.

Learn more​

See the following topics for more information: