I have recently started migrating some business systems to MariaDB 10.1.13. One of the reasons was to start using the roles functionality to improve management of security and having some consistent viewpoints of it through the use of roles.
One of the things I found not so well pointed out in the document is the fact you need to be fairly fine grained with the grants. For example granting show databases will leave all tables invisible and inaccessible. You will need to grant select on the database to the role to be able to see the tables within that database.
CREATE ROLE DEVELOPER;
GRANT PROCESS, SHOW DATABASES ON *.* TO 'DEVELOPER';
GRANT ROLE DEVELOPER to `pmclarty`;
Log in as user pmclarty
Set ROLE DEVELOPER;
All I can see in MySQL WorkBench and other tools is a list of databases.
Add the following
GRANT ALL PRIVILEGES ON `db1`.`*` TO 'DEVELOPER';
Still no tables listed
GRANT SELECT, INSERT, UPDATE, DELETE, ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DROP, EVENT, EXECUTE, LOCK TABLES, REFERENCES, SHOW VIEW, TRIGGER, INDEX ON `db1`.`*` TO 'DEVELOPER';
Now we have a winner
All necessary access is there for the user to do all manner of things in this database.
Roles are very handy and you can grant different role sets to people for them to action different work and allow them to temporarily elevate privileges to carry out a task but not use them all the time and risk mistakes. No need to a user to have a read only account and a second account with higher privileges. Create two roles set the default role of the user to the read-only level of access and grant them a secondary role with the higher privileges to the user and then they can set the role for that when they need to carry out the extra functions. SWEET!!!
See ya round