What privileges would be needed to issue such a GRANT?

It is how we match user to privileges (so: no new privileges). But probably new special user which will represent PUBLIC (need to find something impossible to create, on the other hand we shoud prohobit user "public" so maybe use it with impossible host name)

PUBLIC is a special role-like name. No valid role can have a name PUBLIC or NONE. See is_invalid_role_name()

So basically this can be implemented as a new entry in the mysql.global_priv table, a role with the name "PUBLIC". You'll know that no user could create it explicitly, you'll have to create it automatically on the first GRANT TO PUBLIC

Wouldn't it then make sense to always have that role in the mysql.global_priv_table? It just wouldn't have any privileges until some are explicitly granted. Since you can't drop the role it seems weird that it only appears on the first GRANT and then can never be removed.

I'd also had it permanently (updated during upgrade).

I am not sure also if it will bother some clients which do not like something new in privileges (but it will not be present in mysql.user so probably it is OK )

I mean, one can always do DELETE and remove it manually. So if it does not exist, it should not be an error, but it should be automatically and transparently added.

But DROP ROLE PUBLIC should be an error, because it is not a role. And any list of roles should not include it either, I_S.APPLICABLE_ROLES should not (I think, but better to check the standard), etc.

bb-10.10-MDEV-5215-2 has problem with:

main.sp_notembedded                      w12 [ fail ]

        Test ended at 2022-06-17 15:55:09

CURRENT_TEST: main.sp_notembedded

--- /home/sanja/maria/git/10.10/mysql-test/main/sp_notembedded.result	2022-06-15 15:00:29.896745134 +0200

+++ /home/sanja/maria/git/10.10/mysql-test/main/sp_notembedded.reject	2022-06-17 15:55:09.222884812 +0200

@@ -345,6 +345,7 @@

 Grants for foo1@localhost

 GRANT USAGE ON *.* TO `foo1`@`localhost` IDENTIFIED BY PASSWORD '-F3A2A51A9B0F2BE2468926B4132313728C250DBF'

 GRANT CREATE ROUTINE ON `test`.* TO `foo1`@`localhost`

+GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE `test`.`spfoo` TO `foo1`@`localhost`

 connection default;

 disconnect foo;

 drop procedure spfoo;

sporadically repeatable in test suite run, can not repeat as single test. Repeating chain of calls of tests did not helped to repeat it.

bb-10.11-MDEV-5215

commit 5154e224a978c3e5e459eb2fcb51a9e1ea9c1692 (HEAD -> bb-10.11-MDEV-5215, origin/bb-10.11-MDEV-5215)

Author: Oleksandr Byelkin <sanja@mariadb.com>

Date:   Mon Dec 13 16:15:21 2021 +0100

    MDEV-5215 Granted to PUBLIC

commit bcfc424d859bd91c1514256d65f183a66b495cc5

Author: Oleksandr Byelkin <sanja@mariadb.com>

Date:   Mon Jun 13 14:37:59 2022 +0200

    MDEV-5215 prerequisite: remove test and test_* database hacks in the test suite

commit 1c3182a99e5c4442532c58b9801efae50cac9e00

Author: Oleksandr Byelkin <sanja@mariadb.com>

Date:   Mon Jun 13 09:44:40 2022 +0200

    MDEV-5215 prerequisite of prerequisite: if DB is not mentioned in connect ignore errors of switching to it

Is a PUBLIC role meant to be sufficient so support anonymous users? I tried mariadb-install-db --skip-test-db, then:

From MDEV-20947:

MariaDB [mysql]> grant all on test.* TO PUBLIC;

Query OK, 0 rows affected (0.001 sec)

MariaDB [mysql]> GRANT ALL PRIVILEGES ON `test\_%`.* TO PUBLIC;

Query OK, 0 rows affected (0.001 sec)

MariaDB [mysql]> create database test;

Query OK, 1 row affected (0.001 sec)

And non-existent user's still couldn't access:

$ mc -u bbo

ERROR 1698 (28000): Access denied for user 'bbo'@'localhost'

$ mc -u bbo test

ERROR 1698 (28000): Access denied for user 'bbo'@'localhost'

No, PUBLIC role isn't sufficient. It can allow everyone to access test db, but it won't allow just anyone to connect. If you want an anonymous user, you need to it with CREATE USER ''@'%'

abc294fab44 on bb-10.11-MDEV-5215 okay to push

serg, we are very confused by the code changes linked to this JIRA (cc tingynia):

• In the commit MDEV-5215 prerequisite: remove test and test_* database hacks in the test suite, you removed the grants from the test database, requiring each individual test to GRANT ALL ON test.* TO mysqltest@localhost.
• In the subsequent commit Add magic database access to test and test_% and removing the script, you restored the grants in the script… but at the same time you stopped calling the script* from mariadb-test-run.pl: The latter change seemingly renders the former one ineffectual

What is/was the goal of these changes?

In the next release, do you actually intend to require that each individual test files does GRANT ALL ON test.*, or not?

Correct, tests have to include GRANT ALL ON test.* as needed. For the majority of the tests it's not needed, as they're run under the root user anyway. Tests that create new users might need to grant all needed privileges explicitly to these new users. There were actually few bugs in tests because newly created users were automatically be able to do something.

The second commit restores access to test and test_% (using the new PUBLIC feature) — the intention here is to restore the historical behavior for normal usage, in bintar and rpm installations of MariaDB, but not restore it for the test suite.

Tests that create new users might need to grant all needed privileges explicitly to these new users. There were actually few bugs in tests because newly created users were automatically be able to do something.

Ah, thank you very much for clarifying, serg. Now this totally makes sense! If we need to test the privilege environment of new users, then magically/automatically having certain privilege will get in the way of realistic testing of those privileges.

Okay, so we can expect that GRANT ALL ON test.* will be needed for non-root users going forward. Thanks!