Cassandra permissions are easy to manage and very similar to a relational database: you can grant alter, authorize, create, drop modify or select to any user to any table.
Also, to verify a permission, you just have to query the permission table.
SELECT * FROM SYSTEM_AUTH.PERMISSIONS WHERE USERNAME = 'user1';
username | resource | permissions
----------+--------------------+--------------------------------------------------------------
user1 | data/demo1 | {'ALTER', 'AUTHORIZE', 'CREATE', 'DROP', 'MODIFY', 'SELECT'}
user1 | data/demo2/table25 | {'MODIFY'}
user1 | data/demo2/table29 | {'ALTER'}
user1 | data/demo2/table44 | {'ALTER', 'SELECT'}
user1 | data/demo2/table49 | {'SELECT'}
user1 | data/demo2/table7 | {'AUTHORIZE'}
user1 | data/demo3/table10 | {'MODIFY'}
user1 | data/demo3/table15 | {'ALTER'}
user1 | data/demo4/table13 | {'SELECT'}
user1 | data/demo4/table35 | {'AUTHORIZE'}
user1 | data/demo4/table38 | {'SELECT'}
user1 | data/demo5/table17 | {'DROP'}
user1 | data/demo6/table16 | {'ALTER'}
user1 | data/demo6/table30 | {'SELECT'}
user1 | data/demo6/table31 | {'AUTHORIZE', 'MODIFY'}
user1 | data/demo6/table37 | {'SELECT'}
(16 rows)
But, visually, it may not be the best choice.
For instance, the user1 has access to which table?
It is clearly obviously for the administrator that user1 has access to the keyspace demo1 and all its tables. But it may not be so obviously for everyone.
Those kind of analysis will be even more needed when using role-based permissions, which will add more complexity to monitor the permissions.
One advantage of using the ODBC driver is the possibility to access relative small tables and convert the raw data into visual forms.
As you can see below, using Tableau to access the small permissions tables gives you much more visual information and make it much easier to monitoring the permissions.
Example #1 - What are the user1 permissions?
Example #2 - Who has access to keyspace demo2 and its tables?
The query to achieve that resultset is not so trivial but isn't too complex:
SELECT
"permissions"."object" as "object_permissions",
"users"."name",
"permissions"."resource",
"users"."super",
"permissions"."grant",
"permissions"."type",
CASE WHEN "tables"."keyspace_name" ||'.'|| "columnfamily_name" IS NULL
THEN "permissions"."object"
ELSE "tables"."keyspace_name" ||'.'|| "columnfamily_name"
END
AS "object"
FROM
"system_auth"."users" "users"
LEFT JOIN
(
SELECT
(
CASE WHEN ("permissions"."resource" <> '') THEN
{fn REPLACE(
CASE WHEN ({fn LENGTH("permissions"."resource")} - 5) >= 0 THEN
{fn RIGHT("permissions"."resource",({fn LENGTH("permissions"."resource")} - 5))}
ELSE NULL
END,'/','.')
}
ELSE NULL
END
) AS "object",
"permissions"."resource" AS "resource",
"permissions"."username" AS "username",
"permissions"."permissions_value" as "grant",
'tables' as "type"
FROM
"system_auth"."permissions_vt_permissions" "permissions"
UNION ALL
SELECT
(
CASE WHEN ("permissions"."resource" <> '') THEN
{fn REPLACE(
CASE WHEN ({fn LENGTH("permissions"."resource")} - 5) >= 0 THEN
{fn RIGHT("permissions"."resource",({fn LENGTH("permissions"."resource")} - 5))}
ELSE NULL
END,'/','.')
}
ELSE NULL
END
) AS "object",
"permissions"."resource" AS "resource",
"permissions"."username" AS "username",
"permissions"."permissions_value" as "grant",
'keyspace' as "type"
FROM
"system_auth"."permissions_vt_permissions" "permissions"
WHERE
{fn LOCATE('/',
{fn RIGHT("permissions"."resource",({fn LENGTH("permissions"."resource")} - 5))}
)} = 0
) "permissions"
ON ("users"."name" = "permissions"."username")
LEFT JOIN "system"."schema_columnfamilies" "tables"
ON
(
(
"tables"."keyspace_name" ||'.'|| "columnfamily_name" = "permissions"."object"
OR
"tables"."keyspace_name" = "permissions"."object"
)
and "permissions"."type" = 'tables'
)
WHERE
"users"."super" = 0
UNION ALL
SELECT
'super user' as "object_permissions",
"super_users"."name",
'super user' as "resource",
"super_users"."super",
"super_user_grants"."grant",
"super_users"."type",
"super_users"."object"
from
(
(
SELECT
'super user' as "object_permissions",
"users"."name",
'super user' "resource",
"users"."super",
'All' as "grant",
"type",
"object"
FROM
"system_auth"."users" "users"
LEFT JOIN
(
select
distinct "keyspace_name" as "object",
'keyspace' as "type"
from
"system"."schema_columnfamilies"
) "k"
ON (1=1)
where
"users"."super" = 1
UNION ALL
SELECT
'super user' as "object_permissions",
"users"."name",
'super user' "resource",
"users"."super",
'All' as "grant",
"type",
"object"
FROM
"system_auth"."users" "users"
LEFT JOIN
(
select
distinct "keyspace_name" || '.' || "columnfamily_name" AS "object",
'tables' as "type"
from
"system"."schema_columnfamilies"
) "k"
ON (1=1)
where
"users"."super" = 1
) "super_users"
INNER JOIN
(
SELECT 'ALTER' as "grant"
UNION ALL
SELECT 'AUTHORIZE'
UNION ALL
SELECT 'CREATE'
UNION ALL
SELECT 'DROP'
UNION ALL
SELECT 'MODIFY'
UNION ALL
SELECT 'SELECT'
) "super_user_grants"
on (1=1)
)
The idea of the query is:
1) expose all the access of the super users;
2) replicate the keyspace access to its tables.
After the query is done, all you need is to create your Tableau visualization:
