MySQL Bugs: #108974: dumpInstance retains but comments out some grants

Bug #108974	dumpInstance retains but comments out some grants
Submitted:	2 Nov 2022 18:27	Modified:	7 Apr 2023 15:32
Reporter:	Duke Lee	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Shell Dump & Load	Severity:	S2 (Serious)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
If a mysql user has a grant on a specific view (e.g. GRANT SELECT ON db.my_view TO 'user'@'host') util.dumpInstance will dump out the user and grant information, but the grants for views will be commented out.

There's no error or warning output for this.

When a user then restores via util.loadDump, the restore will be missing the commented out grants. Since the grants are just comments, the load process silently fails this portion as well.

The only workaround is to scan through @.users.sql for the commented out grants and apply them manually.

How to repeat:
create database db;
use db;
create view db_view as select 1;
create user 'user'@'localhost' identified by 'user';
grant select on db.db_view to 'user'@'localhost';

mysqlsh -h localhost -u root -p -P 33060 -- util dumpInstance "blah" --compatibility=strip_tablespaces --excludeUsers='root'

In a separate instance:

mysqlsh -h localhost -u root -p -P 33060 -- util loadDump "blah" --loadUsers=true

show grants for 'user'@'localhost';
will be missing "select on db.db_view"

Suggested fix:
Don't comment out the view-related grants. I'm not sure if this has simply been overlooked during development? I would assume there shouldn't be any problems given that placeholder schemas/views are loaded in first.

Upon further testing, it appears that this affects more than just views. It can affect grants of the following patterns:

SELECT ON db.*
SELECT ON db.view
SELECT ON db.table
EXECUTE ON FUNCTION db.func
INSERT, CREATE, DROP ON db.table
INSERT, CREATE, DROP, ALTER ON db.table

At this point, I'm not sure why this is happening. The above is merely an observation from testing against my organization's actual data.

perl -n -e '/^\/\* (.+) \*\/;$/ && print $1 . ";\n"' @.users.sql

Command to print commented out grant statements

Hi Mr. Lee,

Thank you for your bug report.

Your commands work just fine, if MySQL Shell commands are excluded:

GRANT USAGE ON *.* TO `user`@`localhost`
GRANT SELECT ON `test`.`db_view` TO `user`@`localhost`

However, with those mysqlsh commands are executed, second GRANT is not displayed any more.

Verified as reported.

Posted by developer:
 
Hi,

I was able to reproduce this bug in regards of views, but not in case of other objects:

+----------------------------------------------------------------+
| Grants for user@localhost                                      |
+----------------------------------------------------------------+
| GRANT USAGE ON *.* TO `user`@`localhost`                       |
| GRANT SELECT ON `db2`.* TO `user`@`localhost`                  |
| GRANT INSERT, CREATE, DROP ON `db3`.`t1` TO `user`@`localhost` |
| GRANT SELECT ON `db3`.`t` TO `user`@`localhost`                |
| GRANT SELECT ON `db`.`db_view` TO `user`@`localhost`           |
| GRANT EXECUTE ON PROCEDURE `db`.`f` TO `user`@`localhost`      |
+----------------------------------------------------------------+

-- begin grants 'user'@'localhost'
GRANT USAGE ON *.* TO `user`@`localhost`;
GRANT SELECT ON `db2`.* TO `user`@`localhost`;
GRANT INSERT, CREATE, DROP ON `db3`.`t1` TO `user`@`localhost`;
GRANT SELECT ON `db3`.`t` TO `user`@`localhost`;
/* GRANT SELECT ON `db`.`db_view` TO `user`@`localhost` */;
GRANT EXECUTE ON PROCEDURE `db`.`f` TO `user`@`localhost`;
-- end grants 'user'@'localhost'

Could you share the reproduction steps for this case? Also, please include the exact shell version used.

Hi Pawel,

Luckily, I've figured out what's going on:

MySQL Shell 8.0.31

dumpInstance seems to be checking grants against their stated entities and commenting out lines whose entities don't exist. For example:

CREATE USER 'user'@'localhost';
GRANT SELECT ON fakedb.* TO 'user'@'localhost';

The above is valid even if fakedb doesn't exist but is commented out in the dump. You can also have a similar scenario against a nonexistent table:

CREATE USER 'user'@'localhost';
CREATE DATABASE realdb;
GRANT INSERT, CREATE, DROP, ALTER ON realdb.faketable TO 'user'@'localhost';

In the dump, the above will be commented out.

Regarding functions, I've found that it's due to case-sensitivity. You can create a function with a camel-cased name like this:

CREATE USER 'user'@'localhost';
CREATE DATABASE blah;
USE blah;
DELIMITER @@
CREATE FUNCTION camelCasedFunction() RETURNS int
BEGIN
  RETURN 1;
END
@@
DELIMITER ;
GRANT EXECUTE ON FUNCTION blah.camelCasedFunction TO 'user'@'localhost';

If you run SHOW GRANTS FOR 'user'@'localhost'; you'll find that the output shows the grant with a lower-cased function name even though the function's actual name is camel-cased. Subsequently, the dumpInstance's @.users.sql file will have the grants commented out and also in lower-case.

It appears that dumpInstance is doing a case-sensitive match between the grant and the function's actual name, detecting that the function "doesn't exist," and then commenting it out.

The same applies to procedures (e.g. GRANT EXECUTE ON PROCEDURE ...)

I think the proper solution is to have dumpInstance not do any of this matching/detecting and simply not comment out any of the grants.

There are valid situations where you may want to apply grants on nonexistent entities such as grants against an intermediary load table where you want the user to _only_ be able to create, insert, and drop that table name. If the dump process is commenting things out, it becomes hard to tell which grants are valid vs which grants are those that were forgotten and needed to be cleaned up, so it's much safer to not comment out the grants. It might be helpful to keep the detection functionality and limit its use to logging warnings to STDOUT.

Posted by developer:
 
Hi Duke,

Thanks for spending your time and investigating this further.

The intention of the code which is commenting out grants is to exclude grants for the objects which were excluded from the dump using the filtering options. The non-existing objects are affected as well, because dumper does not distinguish between excluded objects and non-existing ones. We will rework this to allow for valid GRANTS on non-existing objects.

Thank you, Pawel .....

Posted by developer:
 
Added the following note to the MySQL Shell 8.0.33 release notes:
	
The dumpInstance() utility incorrectly commented out GRANT statements referring to existing views, routines,
and non-existing schemas and objects instead of only commenting out GRANT statements which referred to objects
not included in the dump.
As of this release, GRANT statement filtering has been removed for both dump and load utilities. Dump utilities
now detect invalid grants and print a warning if they are detected.
A new compatibility option is introduced, strip_invalid_grants, which removes invalid GRANT statements from the dump.

Thank you, Mr. Gilmore .......

Thanks Edward for introducing this `strip_invalid_grants` option. I just can't figure out of how to enable it. Is it a global server option or just a `mysqlsh` option?

Tried with this:

```
mysqlsh-js> shell.options.setPersist('strip_invalid_grants', 'true')
Options.setPersist: Unrecognized option: strip_invalid_grants. (ArgumentError)
```

And when trying to put it into MySQL server's `my.cnf`...

```
[mysqld]
strip_invalid_grants = 1
```

then I'll get:

[ERROR] [MY-000067] [Server] unknown variable 'strip_invalid_grants=1'.

Using Percona MySQL 8.0.33-25 (2023-06-15). Any advice appreciated!

Hi Mr. Iezzi,

That option is a part of MySQL Shell. 

You should read our documentation here:

https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-dump-instance-schema.ht...

this is a forum for bug reports with repeatable test cases, not a forum for asking questions.

Thank you, MySQL Verification Team. And sorry about the noise.

Not a problem.