Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.1.44, 10.2.31, 10.3.22, 10.4.12, 10.5.2
-
None
Description
Let's say that we create a role and a user account:
MariaDB [(none)]> CREATE ROLE 'test_role'; |
Query OK, 0 rows affected (0.004 sec) |
|
|
MariaDB [(none)]> CREATE USER 'test_user'@'%'; |
Query OK, 0 rows affected (0.004 sec) |
And then let's say that we set this role to be the default role for the user account:
MariaDB [(none)]> GRANT 'test_role' TO 'test_user'@'%'; |
Query OK, 0 rows affected (0.004 sec) |
|
|
MariaDB [(none)]> SET DEFAULT ROLE 'test_role' FOR 'test_user'@'%'; |
Query OK, 0 rows affected (0.004 sec) |
Neither SHOW CREATE USER now SHOW GRANTS prints this default role for the user account:
MariaDB [(none)]> SHOW CREATE USER 'test_user'@'%'; |
+-----------------------------+ |
| CREATE USER for test_user@% | |
+-----------------------------+ |
| CREATE USER 'test_user'@'%' | |
+-----------------------------+ |
1 row in set (0.000 sec) |
|
|
MariaDB [(none)]> SHOW GRANTS FOR 'test_user'@'%'; |
+---------------------------------------+ |
| Grants for test_user@% | |
+---------------------------------------+ |
| GRANT test_role TO 'test_user'@'%' | |
| GRANT USAGE ON *.* TO 'test_user'@'%' | |
+---------------------------------------+ |
2 rows in set (0.000 sec) |
In my opinion, it should at least be printed by SHOW GRANTS, since it is somewhat related to one of the grants that is printed.
Attachments
Issue Links
- relates to
-
MDEV-23630 mysqldump to logically dump system tables
-
- Closed
-
-
-
- Closed
-
-
MDEV-22311 implement SHOW CREATE ROLE
-
- Open
-
-
-
- Closed
-
Activity
Hi anel,
Why GRANT test_role TO 'test_user'@'%' is not good enough?
Because that is not all of the information about the grant. It is missing information. That shows that the role was granted to the user. It does *not* show that the role is the default role for the user. Do you understand the distinction? In my opinion, both pieces of this information should be shown.
What do you expect to be showed?
All of the information--both the GRANT, and the SET DEFAULT ROLE. i.e.:
SHOW GRANTS FOR 'test_user'@'%'; |
+---------------------------------------+ |
| Grants for test_user@% | |
+---------------------------------------+ |
| GRANT test_role TO 'test_user'@'%' | |
| SET DEFAULT ROLE 'test_role' FOR 'test_user'@'%' | |
| GRANT USAGE ON *.* TO 'test_user'@'%' | |
Does that make sense to you?
Also SHOW CREATE USER is added in `10.2`, so update in MDEV version or example is needed.
10.1 still has both roles and SHOW GRANTS, so this bug still applies to 10.1, other than the SHOW CREATE USER component. I think this information is more appropriate for SHOW GRANTS anyway.
I am not sure I agree. SHOW GRANTS is supposed to show grants, not whether a role is set by default or not. SHOW CREATE USER looks more appropriate, but it always used to return just one statement, it feels like a big step to change that.
On the other hand, if one wants to recreate the user by replaying SHOW CREATE USER and SHOW GRANTS, a default role has to be set after grants, so it cannot be in SHOW CREATE USER.
A default role is seen in INFORMATION_SCHEMA.APPLICABLE_ROLES, is it not good enough?
Hi serg,
I am not sure I agree. SHOW GRANTS is supposed to show grants, not whether a role is set by default or not.
I agree that it is not a perfect fit for SHOW GRANTS, because you do not use GRANT to set the default role. However, in my opinion, SET DEFAULT ROLE x FOR y is so closely related to GRANT x TO y that it would be logical to add it to the output of SHOW GRANTS.
SHOW CREATE USER looks more appropriate, but it always used to return just one statement, it feels like a big step to change that.
On the other hand, if one wants to recreate the user by replaying SHOW CREATE USER and SHOW GRANTS, a default role has to be set after grants, so it cannot be in SHOW CREATE USER.
Yeah, I agree that it would be problematic to add SET DEFAULT ROLE to the output of SHOW CREATE USER.
A default role is seen in INFORMATION_SCHEMA.APPLICABLE_ROLES, is it not good enough?
It is "good enough" in the sense that the information is available somewhere. However, it is not ideal for the use case that I am analyzing. Let me provide some background.
I am currently working on "data loading" and "migration" procedures that rely on mysqldump. mysqldump backs up user accounts and roles as a series of INSERT statements into the privilege tables. This is not ideal in our environment for various reasons (limited privileges, possibility of accidentally dropping internal user accounts, etc.).
Therefore, mysqldump has to be executed with --all-databases --ignore-database=mysql, and user accounts and roles have to be backed up separately. There are some limitations in MariaDB that make this a bit harder than it needs to be, in my opinion. Here is what I currently need to do:
1.) To create user accounts:
WITH mariadb_users AS ( |
SELECT User, Host, default_role |
FROM mysql.user |
WHERE is_role='N' |
AND User NOT LIKE 'internal_%' |
AND Super_priv != 'Y' |
)
|
SELECT CONCAT( |
'SHOW CREATE USER ''', |
mariadb_users.User, |
'''@''', |
mariadb_users.Host,
|
''';' |
)
|
FROM mariadb_users |
This is fine, but not entirely ideal, because:
- Semicolons need to be added to each line of the output.
- The output needs to be re-executed.
2.) To create roles:
WITH mariadb_roles AS ( |
SELECT User |
FROM mysql.user |
WHERE is_role='Y' |
)
|
SELECT CONCAT( |
'SELECT ''CREATE ROLE ''''', |
mariadb_roles.User, |
''''''';' |
)
|
FROM mariadb_roles |
This would be a bit cleaner if we had SHOW CREATE ROLE. I reported that as MDEV-22311.
I only wrapped it in the inner SELECT, so that the whole script output can be re-executed, to match the limitations of creating user accounts (#1).
3.) To grant user privileges:
WITH mariadb_users AS ( |
SELECT User, Host, default_role |
FROM mysql.user |
WHERE is_role='N' |
AND User NOT LIKE 'internal_%' |
AND Super_priv != 'Y' |
)
|
SELECT CONCAT( |
'SHOW GRANTS FOR ''', |
mariadb_users.User, |
'''@''', |
mariadb_users.Host,
|
''';' |
)
|
This has the same draw-backs as creating user accounts (#1):
- Semicolons need to be added to each line of the output.
- The output needs to be re-executed.
3.) To grant role privileges:
WITH mariadb_roles AS ( |
SELECT User |
FROM mysql.user |
WHERE is_role='Y' |
)
|
SELECT CONCAT( |
'SHOW GRANTS FOR ''', |
mariadb_roles.User, |
''';' |
)
|
This has the same draw-backs as creating user accounts (#1):
- Semicolons need to be added to each line of the output.
- The output needs to be re-executed.
4.) To set default roles:
WITH mariadb_users AS ( |
SELECT User, Host, default_role |
FROM mysql.user |
WHERE is_role='N' |
AND User NOT LIKE 'internal_%' |
AND Super_priv != 'Y' |
)
|
SELECT CONCAT ( |
'SELECT ''SET DEFAULT ROLE ''''', |
mariadb_users.default_role,
|
''''' FOR ''''', |
mariadb_users.User, |
'''''@''''', |
mariadb_users.Host,
|
''''''';' |
)
|
FROM mariadb_users |
WHERE mariadb_users.default_role != ''; |
In my opinion, this step just seems a bit unnecessary, since it could be wrapped into granting user privileges (#3), so that's why I submitted this Jira.
As far as I understand, we're solving a task of a "dump tool" that needs to dump users and roles as a set of SQL statements.
Consider a user, who creates a role and then sets this role as a default for herself.
The role must exist before it can be set as a default role.
A user must exist before the role is created, because user name will be specified in the WITH ADMIN clause.
To solve it you basically have to to use three SQL statements like
CREATE USER foo@bar; |
CREATE ROLE foo_role WITH ADMIN foo@bar; |
SET DEFAULT ROLE foo_role FOR foo@bar; |
MDEV-23630 work in progress:
https://github.com/MariaDB/server/compare/10.2...grooverdan:10.2-MDEV-23630-mysqldump-native-sql?expand=1 (dump_all_users)
|
client/mysqldump --system=all --insert-ignore -u root -S /tmp//build-mariadb-server-10.2.sock |
CREATE USER IF NOT EXISTS 'root'@'localhost';
|
CREATE USER IF NOT EXISTS 'root'@'linux.fritz.box';
|
CREATE USER IF NOT EXISTS 'root'@'127.0.0.1';
|
CREATE USER IF NOT EXISTS 'root'@'::1';
|
CREATE USER IF NOT EXISTS ''@'localhost';
|
CREATE USER IF NOT EXISTS ''@'linux.fritz.box';
|
CREATE USER IF NOT EXISTS 'dan'@'localhost' IDENTIFIED VIA unix_socket;
|
CREATE USER IF NOT EXISTS 'bob'@'%' IDENTIFIED BY PASSWORD '*61584B76F6ECE8FB9A328E7CF198094B2FAC55C7';
|
CREATE USER IF NOT EXISTS 'jan'@'%';
|
/*M!100005 CREATE ROLE IF NOT EXISTS 'journalist' WITH ADMIN 'root'@'localhost' */;
|
/*M!100005 CREATE ROLE IF NOT EXISTS 'developer' WITH ADMIN 'dan'@'localhost' */;
|
/*M!100005 CREATE ROLE IF NOT EXISTS 'coffee_boy' WITH ADMIN 'journalist' */;
|
GRANT journalist TO 'root'@'localhost' WITH ADMIN OPTION;
|
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
|
GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION;
|
/*M!100005 SET DEFAULT ROLE NONE FOR 'root'@'localhost' */;
|
GRANT ALL PRIVILEGES ON *.* TO 'root'@'linux.fritz.box' WITH GRANT OPTION;
|
GRANT PROXY ON ''@'%' TO 'root'@'linux.fritz.box' WITH GRANT OPTION;
|
/*M!100005 SET DEFAULT ROLE NONE FOR 'root'@'linux.fritz.box' */;
|
GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' WITH GRANT OPTION;
|
/*M!100005 SET DEFAULT ROLE NONE FOR 'root'@'127.0.0.1' */;
|
GRANT ALL PRIVILEGES ON *.* TO 'root'@'::1' WITH GRANT OPTION;
|
/*M!100005 SET DEFAULT ROLE NONE FOR 'root'@'::1' */;
|
GRANT USAGE ON *.* TO ''@'localhost';
|
/*M!100005 SET DEFAULT ROLE NONE FOR ''@'localhost' */;
|
GRANT USAGE ON *.* TO ''@'linux.fritz.box';
|
/*M!100005 SET DEFAULT ROLE NONE FOR ''@'linux.fritz.box' */;
|
GRANT developer TO 'dan'@'localhost' WITH ADMIN OPTION;
|
GRANT ALL PRIVILEGES ON *.* TO 'dan'@'localhost' IDENTIFIED VIA unix_socket WITH GRANT OPTION;
|
/*M!100005 SET DEFAULT ROLE NONE FOR 'dan'@'localhost' */;
|
GRANT journalist TO 'bob'@'%';
|
GRANT USAGE ON *.* TO 'bob'@'%' IDENTIFIED BY PASSWORD '*61584B76F6ECE8FB9A328E7CF198094B2FAC55C7';
|
/*M!100005 SET DEFAULT ROLE NONE FOR 'bob'@'%' */;
|
GRANT coffee_boy TO 'jan'@'%';
|
GRANT USAGE ON *.* TO 'jan'@'%';
|
/*M!100005 SET DEFAULT ROLE 'coffee_boy' FOR 'jan'@'%' */;
|
GRANT coffee_boy TO 'journalist' WITH ADMIN OPTION;
|
GRANT USAGE ON *.* TO 'journalist';
|
GRANT ALL PRIVILEGES ON `stories`.* TO 'journalist';
|
GRANT USAGE ON *.* TO 'coffee_boy';
|
GRANT USAGE ON *.* TO 'developer';
|
GRANT USAGE ON *.* TO 'coffee_boy';
|
I agree that there needs to be built-in way to export to .sql files in such a way they can be imported / migrated on another server. As of now, if I add a role to the database, the backups of users breaks and even if fixed to not break, it lacks all the necessary information to re-create on the other server. I have a workaround for re-creating roles and their privileges but nothing yet on default roles. And default roles are extremely important because if all the permissions are defined in a role, a user has no privileges unless the default role is set.
I have a workaround using "sed" and documented it here: 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
Hi serg,
again buildbot queue is full of tasks, so I'm sending the patch here to review:
https://github.com/an3l/server/commit/581857bd999781b45ddef1ff9b88215fa3112165
Thanks.
Hi serg,
what I have seen that DEFALT_ROLE doesn't get set after all grants for root.
Here is an example:
SET DEFAULT ROLE test_role; #order doesn't matter
|
+SET ROLE test_role;
|
SHOW GRANTS;
|
Grants for root@localhost
|
GRANT test_role TO 'root'@'localhost' WITH ADMIN OPTION
|
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION
|
GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION
|
SET DEFAULT ROLE test_role FOR 'root'@'localhost' #this line should be last line?
|
+GRANT USAGE ON *.* TO 'test_role'
|
I guess I need to change this?
If the check for default_role is validated after rolename check:
if (rolename)
|
@@ -8801,6 +8796,10 @@ bool mysql_show_grants(THD *thd, LEX_USER *lex_user)
|
}
|
}
|
|
+ /* Show default role to acl_user */
|
+ if (show_default_role(thd, acl_user, buff, sizeof(buff)))
|
+ goto end;
|
+
|
Grants for test_user@%
|
GRANT test_role TO 'test_user'@'%'
|
GRANT USAGE ON *.* TO 'test_user'@'%'
|
+SET ROLE test_role;
|
SET DEFAULT ROLE test_role;
|
SHOW GRANTS;
|
Grants for root@localhost
|
GRANT test_role TO 'root'@'localhost' WITH ADMIN OPTION
|
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION
|
GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION
|
+GRANT USAGE ON *.* TO 'test_role'
|
SET DEFAULT ROLE test_role FOR 'root'@'localhost' #now it is the last line
|
SET DEFAULT ROLE NONE;
|
SHOW GRANTS;
|
So this is done on the new 10.2 commit (again local repo):
https://github.com/an3l/server/commit/2e0bac4688a73cd6df406727ce7f6c62a57b7293
Thanks for few review iterations.
anel I rebased MDEV-23630 (no server changes) on 2e0bac4688a73cd6df406727ce7f6c62a57b7293
and managed to trigger this crash::
Thread pointer: 0x7f2e6c000c48
|
Attempting backtrace. You can use the following information to find out
|
where mysqld died. If you see no messages after this, something went
|
terribly wrong...
|
stack_bottom = 0x7f2ebc416dd8 thread_stack 0x49000
|
mysys/stacktrace.c:173(my_print_stacktrace)[0x5606a381e8f9]
|
sql/signal_handler.cc:209(handle_fatal_signal)[0x5606a33d71ab]
|
sigaction.c:0(__restore_rt)[0x7f2ec2352a90]
|
sql/sql_acl.cc:8889(show_role_grants(THD*, char const*, ACL_USER_BASE*, char*, unsigned long) [clone .constprop.0])[0x5606a31ec73e]
|
sql/sql_acl.cc:8535(print_grants_for_role(THD*, ACL_ROLE*))[0x5606a31ed696]
|
sql/sql_acl.cc:8630(show_grants_callback(ACL_USER_BASE*, void*))[0x5606a31ed7a2]
|
sql/sql_acl.cc:5744(traverse_role_graph_impl(ACL_USER_BASE*, void*, long, int (*)(ACL_USER_BASE*, void*), int (*)(ACL_USER_BASE*, ACL_ROLE*, void*)))[0x5606a31e802d]
|
sql/sql_acl.cc:8833(mysql_show_grants(THD*, st_lex_user*))[0x5606a31fb212]
|
sql/sql_parse.cc:5250(mysql_execute_command(THD*))[0x5606a324b3ab]
|
sql/sql_parse.cc:7734(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x5606a325074f]
|
sql/sql_parse.cc:1829(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x5606a3252b6a]
|
sql/sql_parse.cc:1383(do_command(THD*))[0x5606a3253bde]
|
sql/sql_connect.cc:1336(do_handle_one_connection(CONNECT*))[0x5606a331c604]
|
sql/sql_connect.cc:1243(handle_one_connection)[0x5606a331c792]
|
perfschema/pfs.cc:1872(pfs_spawn_thread)[0x5606a37e35d3]
|
nptl/pthread_create.c:478(start_thread)[0x7f2ec2347432]
|
x86_64/clone.S:97(??)[0x7f2ec19b3913]
|
|
|
Trying to get some variables.
|
Some pointers may be invalid and cause the dump to abort.
|
Query (0x7f2e6c00f290): SHOW GRANTS FOR 'role_1'
|
|
Please check the SHOW GRANTS on a ROLE case.
role_1 definition- https://github.com/grooverdan/mariadb-server/pull/2/files#diff-21df662c1ccaf5443b23e4b7ca89121f6d3b0ff013e5a2b512f4cb3f1ef7f2a5R27
serg, anel, how do you feel about versioned output here for the SET DEFAULT ROLE (what I'm doing in MDEV-23630):
/*M!100005 SET DEFAULT ROLE %s FOR %s */
|
/*!80001 ALTER USER %s DEFAULT ROLE %s */
|
What do you expect to be showed?
Why GRANT test_role TO 'test_user'@'%' is not good enough?
How should be different from SHOW GRANTS for 'test_role; ?
Also SHOW CREATE USER is added in `10.2`, so update in MDEV version or example is needed.