How we migrate to role-based permission


#1

With the new MapD 4.1.1 rolled out, we decided to migrate database access permissions from user-based to role-based. This will help make future permission management a lot simpler. We also ran into an issue where users were showing up on share dashboard list simply by having ACCESS permission on the database it seems.

To do that, we need to first find out which databases should each role has access to so that we can grant those access accordingly to the roles. Secondly, which databases each users have access to that we need to revoke.

If you are not familiar, you can see mapd administrative data in their SQLite file, located in your data folder ./data/mapd_catalogs/mapd.
If you are running Ubuntu, you can execute sqlite3 ./mapd to access the file.

Here are the queries used to find out the users’ and roles’ permissions.

// role to database access for granting
select mapd_roles.roleName, mapd_databases.name as dbName from mapd_roles
inner join mapd_users on mapd_users.name = mapd_roles.userName
inner join mapd_privileges on mapd_users.userid = mapd_privileges.userid
inner join mapd_databases on mapd_databases.dbid = mapd_privileges.dbid
where mapd_roles.roleName like '%_role'
;

// user to database for revoking
select objectName, roleName from mapd_object_permissions
where roleType = 1 and objectPermissionsType = 1
;

Once you have the list, the easiest way to do this is to pre-generate all the queries in advance, and put those into a file for batch execution.

I did mine through Google Sheets, simple copy-and-paste.

You can run this inside ./bin folder where the mapdql executable lies, but it can be done anywhere by simply change the reference to the mapdql command or at it to your $PATH.

#!/usr/bin/env bash
cat <<QUERY | ./mapdql -p $MAPD_PASSWORD

GRANT ACCESS ON DATABASE <db> TO <db_role> ;
REVOKE ACCESS ON DATABASE <db> FROM <user> ;
REVOKE VIEW SQL EDITOR ON DATABASE <db> FROM <user> ;

QUERY

After the script finishes, you can confirm that the user no longer has permissions to the databases, by checking that object permissions are set to 0 for those users, in the mapd SQLite file.

select * from mapd_object_permissions
where roleType = 1 and objectPermissionsType = 1
;