Executed as root, statements like:
create role WB_CLIENT;
|
|
grant execute on procedure badges.p_hubspotter_lookup to WB_CLIENT;
|
grant execute on procedure badges.p_wristband_lookup to WB_CLIENT;
|
grant execute on procedure badges.p_login to WB_CLIENT;
|
grant execute on procedure badges.p_hubspotter_list to WB_CLIENT;
|
grant execute on procedure badges.p_token_list to WB_CLIENT;
|
|
...
|
|
grant WB_CLIENT to 'wristband_client'@'10.%';
|
Log in as "wristband_client:"
mysql --user=wristband_client '--password=[password]' --host=[host]
|
Welcome to the MariaDB monitor. Commands end with ; or \g.
|
Your MariaDB connection id is 164
|
Server version: 10.0.12-MariaDB MariaDB Server
|
|
Copyright (c) 2000, 2014, Oracle, Monty Program Ab and others.
|
|
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
|
|
MariaDB [(none)]> use badges;
|
Database changed
|
MariaDB [badges]> show grants;
|
+--------------------------------------------------------------------------------------------------------------------------------------+
|
| Grants for wristband_client@10.% |
|
+--------------------------------------------------------------------------------------------------------------------------------------+
|
| GRANT WB_CLIENT TO 'wristband_client'@'10.%' |
|
| GRANT CREATE TEMPORARY TABLES ON *.* TO 'wristband_client'@'10.%' IDENTIFIED BY PASSWORD '...' |
|
| GRANT SELECT ON `mysql`.`proc` TO 'wristband_client'@'10.%' |
|
+--------------------------------------------------------------------------------------------------------------------------------------+
|
3 rows in set (0.02 sec)
|
|
MariaDB [badges]> set role WB_CLIENT;
|
ERROR 1959 (OP000): Invalid role specification `WB_CLIENT`.
|
MariaDB [badges]> select current_user();
|
+-----------------------+
|
| current_user() |
|
+-----------------------+
|
| wristband_client@10.% |
|
+-----------------------+
|
1 row in set (0.03 sec)
|
|
MariaDB [badges]> SELECT * FROM INFORMATION_SCHEMA.APPLICABLE_ROLES;
|
+-----------------------+-----------+--------------+
|
| GRANTEE | ROLE_NAME | IS_GRANTABLE |
|
+-----------------------+-----------+--------------+
|
| wristband_client@10.% | WB_CLIENT | NO |
|
+-----------------------+-----------+--------------+
|
1 row in set (0.03 sec)
|
I have only ever been able to get SET ROLE to work if the account is defined as 'wristband_client'@'localhost'. Seen on two separate instances of MariaDB 10. This came from a freshly-installed instance (one week ago).
Executed as root, statements like:
create role WB_CLIENT;
grant execute on procedure badges.p_hubspotter_lookup to WB_CLIENT;
grant execute on procedure badges.p_wristband_lookup to WB_CLIENT;
grant execute on procedure badges.p_login to WB_CLIENT;
grant execute on procedure badges.p_hubspotter_list to WB_CLIENT;
grant execute on procedure badges.p_token_list to WB_CLIENT;
...
grant WB_CLIENT to 'wristband_client'@'10.%';
Log in as "wristband_client:"
mysql --user=wristband_client '--password=[password]' --host=[host]
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 164
Server version: 10.0.12-MariaDB MariaDB Server
Copyright (c) 2000, 2014, Oracle, Monty Program Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> use badges;
Database changed
MariaDB [badges]> show grants;
+--------------------------------------------------------------------------------------------------------------------------------------+
| Grants for wristband_client@10.% |
+--------------------------------------------------------------------------------------------------------------------------------------+
| GRANT WB_CLIENT TO 'wristband_client'@'10.%' |
| GRANT CREATE TEMPORARY TABLES ON *.* TO 'wristband_client'@'10.%' IDENTIFIED BY PASSWORD '...' |
| GRANT SELECT ON `mysql`.`proc` TO 'wristband_client'@'10.%' |
+--------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.02 sec)
MariaDB [badges]> set role WB_CLIENT;
ERROR 1959 (OP000): Invalid role specification `WB_CLIENT`.
MariaDB [badges]> select current_user();
+-----------------------+
| current_user() |
+-----------------------+
| wristband_client@10.% |
+-----------------------+
1 row in set (0.03 sec)
MariaDB [badges]> SELECT * FROM INFORMATION_SCHEMA.APPLICABLE_ROLES;
+-----------------------+-----------+--------------+
| GRANTEE | ROLE_NAME | IS_GRANTABLE |
+-----------------------+-----------+--------------+
| wristband_client@10.% | WB_CLIENT | NO |
+-----------------------+-----------+--------------+
1 row in set (0.03 sec)
I have only ever been able to get SET ROLE to work if the account is defined as 'wristband_client'@'localhost'. Seen on two separate instances of MariaDB 10. This came from a freshly-installed instance (one week ago).