I am investigating user privileges in a MySQL database.
CREATE USER 'reader' IDENTIFIED BY 'reader';
As per MySQL documentation, the user gets created with the least privilege--SELECT.
How is it possible that they are able to use a CREATE DATABASE/SCHEMA statement? I use a MySQL instance in the official Docker image, and I did not find any mention of OS differences.
If it's just documentation obsoletion, how do I create a user that can have just read access to the database objects, mainly on the information_schema or sys schemas?
The least privileges is USAGE, not SELECT. Using MYSQL_{USER,PASSWORD,DATABASE} will create a user with all privs on the database.
Information schema is readable by anyone, however the database objects visible are only those the user has access too.
Access to the sys schema requires a GRANT SELECT ON sys.* TO user. As the sys schema is a view on the performance_schema, GRANT SELECT ON performance_schema.* TO user is also required.