Details

    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.

      Attachments

        Issue Links

          Activity

            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

            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

            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.

            serg 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 Unassigned
              GeoffMontee Geoff Montee (Inactive)
              Votes:
              4 Vote for this issue
              Watchers:
              8 Start 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.