User roles in MariaDB

MariaDB has supported user roles since 10.0.5 (https://mariadb.com/kb/en/mariadb/roles-overview/)

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"

Ok, did not know nothing about these roles thingy yet.

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`;

to

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?

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

apparently, roles is a mariadb-only feature. a role is a pseudo-user defined to be used as a user privileges template; it is stored in the mysql.user table, distinguished from real users by the (mariadb-specific) is_Role='Y' field. you can assign arbitrary privileges to roles, just as to normal users; and then you can assign these privileges en bloc to a user by
- 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.

Is support for roles a feature you could imagine to add?

If this is not the right place to submit feature requests please let me know.

Sounds difficult if that's a MariaDB only feature. If at least MySQL + MariaDB would support that...

It's a feature of MySQL 8. FYI

MariaDB 10.2.x and MySQL 8.x are supporting roles now. Is this feature going to be supported in a future release of HeidiSQL? Would be very nice!

There should be a specific option for role management in HeidiSQL.

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