User roles in MariaDB
Is this going to be an upcoming feature for Heidi? I would really like to start using them, I did a test, adding a role using;
CREATE ROLE "test_role";
And then when going into the User Manager to try and assign permissions, I click on test_role, and am greeted with an SQL Error (1141)
"There is no such grant defined for user test_role on host %"
"Starting the server without --skip-name-resolve may solve this issue"
So, firstly, HeidiSQL should not list roles in the users list, should it? So, the query to get all users should be changed from
SELECT `user`, `host`, `password` FROM `mysql`.`user`;
SELECT `user`, `host`, `password` FROM `mysql`.`user` WHERE `is_role`='N';
... if that "is_role" column exists.
But that's just a bugfix. How should HeidiSQL support roles in general? I guess there is need for additional options in the user manager, or?
- by setting mysql.user.default_role via SET DEFAULT ROLE (-> auto SET ROLE on connect), or
- generally (using mysql.roles_mapping) via GRANT.
Roles, even if assigned to a user, do not become active until SET ROLE is used (except for default role).
All these need privileges, docs are somewhat unclear which ones. Also there is one (or several, inherited) "role owner"s to be considered.
Better than MySQL, but not really uncomplicated.
Bumping this thread since roles started to become useful in MariaDB 10.1.1. You no longer have to execute a SET ROLE to gain the privileges granted by your role since now it's possible to assign a user a default role: https://mariadb.com/kb/en/mariadb/set-default-role/
Currently Heidi doesn't recognize privileges set via roles. That is, if a user has direct privileges set for database A and his default role additionally grants access to database B, Heidi would only display database A in the left hand navigation pane. As expected it is possible (however less convenient) to execute queries against database B in the Query tab.
While it would be handy as well to be able to maintain and assign roles in the User Manager dialog, it would actually be most useful to have the privileges granted via roles reflected in what a user sees in Heidi when connecting, that is include the available DBs in the navigation pane.
I'd assume this requires a SELECT CURRENT_ROLE() when usually getting a user's privileges for a new session and adding the privileges resulting out of SHOW GRANTS FOR $role. This would have to be refreshed on each SET ROLE.
Hmmm, I would have to have a think about the best way to do it, maybe still use the existing User Manager, so permissions and such can still be assigned to the role, but it would also need the ability to assign users to the roles, either individually using each users properties, and maybe if you have the role selected, you can choose from a list of users to add ... just some quick thoughts, I will have more of a think on it though
I totally agree with you nqrith
Please login to leave a reply, or register at first.