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 touse 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.
Attachments
Issue Links
relates to
MDEV-22312Bad error message for SET DEFAULT ROLE when user account is not granted the role
Closed
MDEV-22313SHOW GRANTS does not prints a user's default role
Closed
MDEV-23630mysqldump to logically dump system tables
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:
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
Lon Hammonds
added a comment - - edited 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
Sergei Golubchik
added a comment - 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.
People
Unassigned
Geoff Montee (Inactive)
Votes:
4Vote for this issue
Watchers:
8Start watching this issue
Dates
Created:
Updated:
Git Integration
Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.
{"report":{"fcp":814.3000001907349,"ttfb":221.09999990463257,"pageVisibility":"visible","entityId":85784,"key":"jira.project.issue.view-issue","isInitial":true,"threshold":1000,"elementTimings":{},"userDeviceMemory":8,"userDeviceProcessors":64,"apdex":1,"journeyId":"57019f62-18d1-4e52-abea-674b45814c24","navigationType":0,"readyForUser":886.0999999046326,"redirectCount":0,"resourceLoadedEnd":592.7000002861023,"resourceLoadedStart":232.80000019073486,"resourceTiming":[{"duration":50.299999713897705,"initiatorType":"link","name":"https://jira.mariadb.org/s/2c21342762a6a02add1c328bed317ffd-CDN/lu2bu7/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/css/_super/batch.css","startTime":232.80000019073486,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":232.80000019073486,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":283.09999990463257,"responseStart":0,"secureConnectionStart":0},{"duration":50.700000286102295,"initiatorType":"link","name":"https://jira.mariadb.org/s/7ebd35e77e471bc30ff0eba799ebc151-CDN/lu2bu7/820016/12ta74/8679b4946efa1a0bb029a3a22206fb5d/_/download/contextbatch/css/jira.browse.project,project.issue.navigator,jira.view.issue,jira.general,jira.global,atl.general,-_super/batch.css?agile_global_admin_condition=true&jag=true&jira.create.linked.issue=true&slack-enabled=true","startTime":233.09999990463257,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":233.09999990463257,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":283.80000019073486,"responseStart":0,"secureConnectionStart":0},{"duration":163.40000009536743,"initiatorType":"script","name":"https://jira.mariadb.org/s/fbf975c0cce4b1abf04784eeae9ba1f4-CDN/lu2bu7/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/js/_super/batch.js?locale=en","startTime":233.09999990463257,"connectEnd":233.09999990463257,"connectStart":233.09999990463257,"domainLookupEnd":233.09999990463257,"domainLookupStart":233.09999990463257,"fetchStart":233.09999990463257,"redirectEnd":0,"redirectStart":0,"requestStart":287,"responseEnd":396.5,"responseStart":297.80000019073486,"secureConnectionStart":233.09999990463257},{"duration":309.3999996185303,"initiatorType":"script","name":"https://jira.mariadb.org/s/099b33461394b8015fc36c0a4b96e19f-CDN/lu2bu7/820016/12ta74/8679b4946efa1a0bb029a3a22206fb5d/_/download/contextbatch/js/jira.browse.project,project.issue.navigator,jira.view.issue,jira.general,jira.global,atl.general,-_super/batch.js?agile_global_admin_condition=true&jag=true&jira.create.linked.issue=true&locale=en&slack-enabled=true","startTime":233.2000002861023,"connectEnd":233.2000002861023,"connectStart":233.2000002861023,"domainLookupEnd":233.2000002861023,"domainLookupStart":233.2000002861023,"fetchStart":233.2000002861023,"redirectEnd":0,"redirectStart":0,"requestStart":287.09999990463257,"responseEnd":542.5999999046326,"responseStart":306.30000019073486,"secureConnectionStart":233.2000002861023},{"duration":70.40000009536743,"initiatorType":"script","name":"https://jira.mariadb.org/s/94c15bff32baef80f4096a08aceae8bc-CDN/lu2bu7/820016/12ta74/c92c0caa9a024ae85b0ebdbed7fb4bd7/_/download/contextbatch/js/atl.global,-_super/batch.js?locale=en","startTime":233.30000019073486,"connectEnd":233.30000019073486,"connectStart":233.30000019073486,"domainLookupEnd":233.30000019073486,"domainLookupStart":233.30000019073486,"fetchStart":233.30000019073486,"redirectEnd":0,"redirectStart":0,"requestStart":287.59999990463257,"responseEnd":303.7000002861023,"responseStart":300.7000002861023,"secureConnectionStart":233.30000019073486},{"duration":90.90000009536743,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2bu7/820016/12ta74/1.0/_/download/batch/jira.webresources:calendar-en/jira.webresources:calendar-en.js","startTime":233.5,"connectEnd":233.5,"connectStart":233.5,"domainLookupEnd":233.5,"domainLookupStart":233.5,"fetchStart":233.5,"redirectEnd":0,"redirectStart":0,"requestStart":288.90000009536743,"responseEnd":324.40000009536743,"responseStart":322.09999990463257,"secureConnectionStart":233.5},{"duration":91.10000038146973,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2bu7/820016/12ta74/1.0/_/download/batch/jira.webresources:calendar-localisation-moment/jira.webresources:calendar-localisation-moment.js","startTime":233.59999990463257,"connectEnd":233.59999990463257,"connectStart":233.59999990463257,"domainLookupEnd":233.59999990463257,"domainLookupStart":233.59999990463257,"fetchStart":233.59999990463257,"redirectEnd":0,"redirectStart":0,"requestStart":290,"responseEnd":324.7000002861023,"responseStart":322.90000009536743,"secureConnectionStart":233.59999990463257},{"duration":55.5,"initiatorType":"link","name":"https://jira.mariadb.org/s/b04b06a02d1959df322d9cded3aeecc1-CDN/lu2bu7/820016/12ta74/a2ff6aa845ffc9a1d22fe23d9ee791fc/_/download/contextbatch/css/jira.global.look-and-feel,-_super/batch.css","startTime":233.7000002861023,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":233.7000002861023,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":289.2000002861023,"responseStart":0,"secureConnectionStart":0},{"duration":117.7999997138977,"initiatorType":"script","name":"https://jira.mariadb.org/rest/api/1.0/shortcuts/820016/47140b6e0a9bc2e4913da06536125810/shortcuts.js?context=issuenavigation&context=issueaction","startTime":233.80000019073486,"connectEnd":233.80000019073486,"connectStart":233.80000019073486,"domainLookupEnd":233.80000019073486,"domainLookupStart":233.80000019073486,"fetchStart":233.80000019073486,"redirectEnd":0,"redirectStart":0,"requestStart":292.30000019073486,"responseEnd":351.59999990463257,"responseStart":346,"secureConnectionStart":233.80000019073486},{"duration":56.69999980926514,"initiatorType":"link","name":"https://jira.mariadb.org/s/3ac36323ba5e4eb0af2aa7ac7211b4bb-CDN/lu2bu7/820016/12ta74/d176f0986478cc64f24226b3d20c140d/_/download/contextbatch/css/com.atlassian.jira.projects.sidebar.init,-_super,-project.issue.navigator,-jira.view.issue/batch.css?jira.create.linked.issue=true","startTime":233.90000009536743,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":233.90000009536743,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":290.59999990463257,"responseStart":0,"secureConnectionStart":0},{"duration":114.59999990463257,"initiatorType":"script","name":"https://jira.mariadb.org/s/3339d87fa2538a859872f2df449bf8d0-CDN/lu2bu7/820016/12ta74/d176f0986478cc64f24226b3d20c140d/_/download/contextbatch/js/com.atlassian.jira.projects.sidebar.init,-_super,-project.issue.navigator,-jira.view.issue/batch.js?jira.create.linked.issue=true&locale=en","startTime":234,"connectEnd":234,"connectStart":234,"domainLookupEnd":234,"domainLookupStart":234,"fetchStart":234,"redirectEnd":0,"redirectStart":0,"requestStart":293.40000009536743,"responseEnd":348.59999990463257,"responseStart":345.09999990463257,"secureConnectionStart":234},{"duration":357.80000019073486,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2bu7/820016/12ta74/1.0/_/download/batch/jira.webresources:bigpipe-js/jira.webresources:bigpipe-js.js","startTime":234.90000009536743,"connectEnd":234.90000009536743,"connectStart":234.90000009536743,"domainLookupEnd":234.90000009536743,"domainLookupStart":234.90000009536743,"fetchStart":234.90000009536743,"redirectEnd":0,"redirectStart":0,"requestStart":579.7000002861023,"responseEnd":592.7000002861023,"responseStart":591.8000001907349,"secureConnectionStart":234.90000009536743},{"duration":350.2999997138977,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2bu7/820016/12ta74/1.0/_/download/batch/jira.webresources:bigpipe-init/jira.webresources:bigpipe-init.js","startTime":241.30000019073486,"connectEnd":241.30000019073486,"connectStart":241.30000019073486,"domainLookupEnd":241.30000019073486,"domainLookupStart":241.30000019073486,"fetchStart":241.30000019073486,"redirectEnd":0,"redirectStart":0,"requestStart":580,"responseEnd":591.5999999046326,"responseStart":590.5999999046326,"secureConnectionStart":241.30000019073486},{"duration":224.60000038146973,"initiatorType":"xmlhttprequest","name":"https://jira.mariadb.org/rest/webResources/1.0/resources","startTime":526.0999999046326,"connectEnd":526.0999999046326,"connectStart":526.0999999046326,"domainLookupEnd":526.0999999046326,"domainLookupStart":526.0999999046326,"fetchStart":526.0999999046326,"redirectEnd":0,"redirectStart":0,"requestStart":718.0999999046326,"responseEnd":750.7000002861023,"responseStart":749.7000002861023,"secureConnectionStart":526.0999999046326},{"duration":229.40000009536743,"initiatorType":"xmlhttprequest","name":"https://jira.mariadb.org/rest/webResources/1.0/resources","startTime":771.3000001907349,"connectEnd":771.3000001907349,"connectStart":771.3000001907349,"domainLookupEnd":771.3000001907349,"domainLookupStart":771.3000001907349,"fetchStart":771.3000001907349,"redirectEnd":0,"redirectStart":0,"requestStart":968.5999999046326,"responseEnd":1000.7000002861023,"responseStart":999.5999999046326,"secureConnectionStart":771.3000001907349},{"duration":247.69999980926514,"initiatorType":"script","name":"https://www.google-analytics.com/analytics.js","startTime":807.4000000953674,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":807.4000000953674,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":1055.0999999046326,"responseStart":0,"secureConnectionStart":0}],"fetchStart":0,"domainLookupStart":0,"domainLookupEnd":0,"connectStart":0,"connectEnd":0,"requestStart":80,"responseStart":221,"responseEnd":241,"domLoading":225,"domInteractive":992,"domContentLoadedEventStart":993,"domContentLoadedEventEnd":1039,"domComplete":1356,"loadEventStart":1356,"loadEventEnd":1357,"userAgent":"Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com)","marks":[{"name":"bigPipe.sidebar-id.start","time":933.5999999046326},{"name":"bigPipe.sidebar-id.end","time":934.3000001907349},{"name":"bigPipe.activity-panel-pipe-id.start","time":934.4000000953674},{"name":"bigPipe.activity-panel-pipe-id.end","time":935.9000000953674},{"name":"activityTabFullyLoaded","time":1060.5}],"measures":[],"correlationId":"e9a156d1423e60","effectiveType":"4g","downlink":9.7,"rtt":0,"serverDuration":84,"dbReadsTimeInMs":14,"dbConnsTimeInMs":23,"applicationHash":"9d11dbea5f4be3d4cc21f03a88dd11d8c8687422","experiments":[]}}
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
Role Grants
User Creation
User Grants
Role Defaults
I voted for this issue.
Thanks,
LHammonds