In this article we’ll explore how to define policies based on roles, for Snowflake repos. In Snowflake, roles define privileges on objects. These roles are then assigned to users, which can only perform actions allowed by the roles they are granted.
Cyral policy rules define how an identity is allowed to interact with data, and can be defined for many types of identities, however, for Snowflake repos we currently only support specifying users and roles as identities. To learn more about Cyral policies, please refer to the Cyral policy guide.
Snowflake roles
Snowflake has a set of system-defined roles which are created by default and can be granted to users or used to create custom roles. For a complete list of these roles, please refer to the Snowflake role documentation.
When you log into the Snowflake UI, you can see the current role in use in the top right corner of the screen, under your user name (marked in red in the image below). You can also change the current role by clicking on its name in the worksheet bar (marked blue in the image below), and selecting a new role from a list of available ones for the current user.
You may wish to create a custom role to use with policies. In this case, use Snowflake's CREATE ROLE command. In the following example, we create a child role for the system-defined role ACCOUNTADMIN and grant it to a user.
# Create role. CREATE ROLE MYROLE; # Create a hierarchy between the new role and ACCOUNTADMIN. GRANT ROLE ACCOUNTADMIN TO ROLE MYROLE; # Grant the new role to a user. GRANT ROLE MYROLE TO USER HSOUSA;
Role based policy evaluation
Now that we have explored Snowflake roles, we’ll see how to use them with Cyral policies. As mentioned, we can use these roles as identities in policies. Let’s take a look at a simple policy example:
data:
- CC
- CCF
rules:
- identities:
dbRoles:
- myrole
reads:
- data:
- CCF
rows: 5
severity: high
- data:
- CC
rows: 1
severity: highThis policy governs the access to two data labels: CC and CCF. It has a single rule, which is set up to apply to a single identity, a database role called myrole. Note that unquoted role names in Snowflake are case-insensitive. We can verify this policy in practice by executing some queries that access the data it governs.
Example 1: An allowed query
This query example will not trigger a policy violation, since role myrole is allowed to read both data labels, but only 1 row for CC. (Not shown here is the Cyral data map that associates the label CC with the Snowflake table, finance.cards.)
SELECT * FROM finance.cards LIMIT 1;
It generates the following activity logs:
{
"activityId": "43:1",
"activityTime": "2021-05-21 15:21:50.930495349 +0000 UTC",
"activityTimeNanos": 1621610510930495349,
"activityTypes": [
"query"
],
"identity": {
"endUser": "hsousa",
"repoUser": "hsousa",
"dbRole": "myrole"
},
"repo": {
"id": "1pDwCs7ZgjIfZz3EEtDSKiO6GLL",
"name": "jonsnow",
"type": "snowflake",
"host": "account.snowflakecomputing.com",
"port": 443
},
"client": {
"connectionId": "43",
"connectionTime": "2021-05-21 15:18:54.336317523 +0000 UTC",
"connectionTimeNanos": 1621610334336317523,
"host": "186.206.254.122",
"port": 28945
},
"sidecar": {
"id": "1sqifFM1BuUUYGnCamk1eG4ycNw",
"name": "snowflake-tests",
"autoScalingGroupInstance": "i-055e61b2682c6950a"
},
"request": {
"statement": "SELECT * FROM finance.cards LIMIT ${cyral_redact_1}",
"statementType": "SELECT",
"isSensitive": true,
"datasetsAccessed": [
{
"dataset": "cyral.finance.cards",
"accessType": "read"
}
],
"fieldsAccessed": [
{
"field": "cyral.finance.cards.card_number",
"label": "CC",
"accessType": "read"
},
{
"field": "cyral.finance.cards.credit_limit",
"label": "CC",
"accessType": "read"
},
{
"field": "cyral.finance.cards.card_family",
"label": "CCF",
"accessType": "read"
}
]
},
"response": {
"message": "OK",
"isError": false,
"records": 1,
"bytes": 56,
"executionTime": "1.50672167s",
"executionTimeNanos": 1506721670
},
"policyViolated": false
}Example 2: A query that violates the rule
But, if we increase the query limit to 2, we’ll see a violation, since CC can only be read 1 row at a time by a user whose role is myrole.
SELECT * FROM finance.cards LIMIT 2;
This generates an activity log showing the violation:
{
"activityId": "36:2",
"activityTime": "2021-05-21 15:22:19.235939632 +0000 UTC",
"activityTimeNanos": 1621610539235939632,
"activityTypes": [
"query"
],
"identity": {
"endUser": "hsousa",
"repoUser": "hsousa",
"dbRole": "myrole"
},
"repo": {
"id": "1pDwCs7ZgjIfZz3EEtDSKiO6GLL",
"name": "jonsnow",
"type": "snowflake",
"host": "snowflakeaccount.snowflakecomputing.com",
"port": 443
},
"client": {
"connectionId": "36",
"connectionTime": "2021-05-21 15:18:05.219888371 +0000 UTC",
"connectionTimeNanos": 1621610285219888371,
"host": "186.206.254.122",
"port": 28922
},
"sidecar": {
"id": "1sqifFM1BuUUYGnCamk1eG4ycNw",
"name": "snowflake-tests",
"autoScalingGroupInstance": "i-055e61b2682c6950a"
},
"request": {
"statement": "SELECT * FROM finance.cards LIMIT ${cyral_redact_1}",
"statementType": "SELECT",
"isSensitive": true,
"datasetsAccessed": [
{
"dataset": "cyral.finance.cards",
"accessType": "read"
}
],
"fieldsAccessed": [
{
"field": "cyral.finance.cards.card_number",
"label": "CC",
"accessType": "read"
},
{
"field": "cyral.finance.cards.credit_limit",
"label": "CC",
"accessType": "read"
},
{
"field": "cyral.finance.cards.card_family",
"label": "CCF",
"accessType": "read"
}
]
},
"response": {
"message": "OK",
"isError": false,
"records": 2,
"bytes": 111,
"executionTime": "751.372283ms",
"executionTimeNanos": 751372283
},
"policyViolated": true,
"policyViolations": [
{
"label": "CC",
"policyName": "hugoSnowPolicy",
"policyId": "1sqpGYgbdEXS776MV0nhcgxIuE4",
"accessType": "read",
"selectedIdentity": "dbRole:myrole",
"reasons": [
"2 rows accessed exceeding limit of 1"
],
"severity": "high"
},
{
"label": "CC",
"policyName": "hugoSnowPolicy",
"policyId": "1sqpGYgbdEXS776MV0nhcgxIuE4",
"accessType": "read",
"selectedIdentity": "dbRole:myrole",
"reasons": [
"2 rows accessed exceeding limit of 1"
],
"severity": "high"
}
]
}