[MDEV-22311] implement SHOW CREATE ROLE Created: 2020-04-20  Updated: 2023-11-30

Status: Open
Project: MariaDB Server
Component/s: Authentication and Privilege System
Fix Version/s: None

Type: New Feature Priority: Major
Reporter: Geoff Montee (Inactive) Assignee: Ralf Gebhardt
Resolution: Unresolved Votes: 4
Labels: None

Issue Links:
Relates
relates to MDEV-22312 Bad error message for SET DEFAULT ROL... Closed
relates to MDEV-22313 SHOW GRANTS does not prints a user's ... Closed
relates to MDEV-23630 mysqldump to logically dump system ta... Closed

 Description   

SHOW CREATE ROLE doesn't work.

See here:

MariaDB [(none)]> CREATE ROLE 'admin_role';
 
Query OK, 0 rows affected (0.002 sec)
 
 
 
MariaDB [(none)]> CREATE ROLE 'user_role'  WITH ADMIN 'admin_role';
 
Query OK, 0 rows affected (0.004 sec)
 
 
 
MariaDB [(none)]> SHOW CREATE ROLE 'user_role';
 
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ROLE 'user_role'' at line 1

Since you can create a role with its own WITH ADMIN clause, this statement should be implemented, so that all role attributes can be easily printed.

 Comments   
Comment by Lon Hammonds [ 2020-10-07 ]

Something like this is needed as well as a way to determine default role so we can export users, grants AND roles for migration/import into other servers. As it stands now, the standard way to export users to .sql breaks if any roles exists because roles exist in the user database but are not treated the same.

I have documented my workaround below which uses "sed" to create the desired SQL syntax for importing on another server:

https://ubuntuforums.org/showthread.php?t=2451555

Here are copy/paste of the important bits:

Role Creation

mysql --skip-column-names --no-auto-rehash --silent --execute="SELECT User FROM mysql.user WHERE is_role = 'Y';" | sed 's/^/CREATE ROLE /;s/$/;/g;1s/^/## Create Roles ##\n/' > /tmp/role-create.sql

Role Grants

mysql --skip-column-names --no-auto-rehash --silent --execute="SELECT CONCAT('SHOW GRANTS FOR ''',User,''';') FROM mysql.user WHERE is_role = 'Y'" | mysql --skip-column-names --no-auto-rehash | sed 's/$/;/g;1s/^/## Grants for Roles ##\n/' > /tmp/role-grants.sql

User Creation

mysql --skip-column-names --no-auto-rehash --silent --execute="SELECT User,Host,Password FROM mysql.user WHERE is_role = 'N' AND User NOT IN ('mariadb.sys','root','mysql');" | sed 's/\t/`@`/;s/\t/` IDENTIFIED BY `/;s/^/CREATE USER `/;s/$/`;/;1s/^/## Create Users ##\n/' > /tmp/user-create.sql

User Grants

mysql --skip-column-names --no-auto-rehash --silent --execute="SELECT CONCAT('SHOW GRANTS FOR ''',User,'''@''',Host,''';') FROM mysql.user WHERE User <> '' AND is_role = 'N' AND user NOT IN ('mysql','mariadb.sys','root');" | mysql --skip-column-names --no-auto-rehash | sed 's/$/;/g;1s/^/## Grants for Users ##\n/' > /tmp/user-grants.sql

Role Defaults

mysql --skip-column-names --no-auto-rehash --silent --execute="SELECT default_role,User,Host FROM mysql.user WHERE is_role = 'N' AND User NOT IN ('mariadb.sys','root','mysql') AND default_role <> '';" | sed 's/\t/ FOR `/;s/\t/`@`/;s/^/SET DEFAULT ROLE /;1s/^/## Set Default Roles ##\n/;s/$/`;/' > /tmp/role-default.sql

I voted for this issue.
Thanks,
LHammonds

Comment by Sergei Golubchik [ 2020-11-04 ]

Roles should be dumped in a specific order.
See how MDEV-23630 does it: https://github.com/MariaDB/server/blob/10.2/client/mysqldump.c#L4403
I'm not sure SHOW CREATE ROLE will be of much help in this case.

Generated at Thu Feb 08 09:13:47 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.