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
If you are running Ubuntu, you can execute
sqlite3 ./mapdto 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 ;