Bug #89030 Add a CONNECT privilege to grant / revoke access to a database
Submitted: 22 Dec 2017 11:02
Reporter: Lukas Eder Email Updates:
Status: Open Impact on me:
None 
Category:MySQL Server Severity:S4 (Feature request)
Version:8.0.2 OS:Microsoft Windows (10)
Assigned to: CPU Architecture:Any
Tags: grant, privileges, revoke

[22 Dec 2017 11:02] Lukas Eder
Description:
Currently, it is not possible to grant the privilege of connecting to a database without getting access to at least one of the objects inside of that database. For instance when creating:

----------------------------------------------------------------------
CREATE USER 'NO_RIGHTS'@'%' IDENTIFIED BY 'NO_RIGHTS';
----------------------------------------------------------------------

This user doesn't have any privileges except the USAGE privilege, which grants access to the server, but not to any databases.

I would like to be able to do

----------------------------------------------------------------------
GRANT CONNECT ON test TO 'NO_RIGHTS'@'%';
----------------------------------------------------------------------

And then, the user should be able to connect, e.g. from JDBC using a jdbc:mysql://localhost/test connection URL.

Instead, the best possible workaround is to grant some dummy privilege as such:

----------------------------------------------------------------------
-- Create a dummy view that is never going to be used:
CREATE VIEW v_unused AS SELECT 1;

-- Now grant showing (not selecting) this view to the user:
GRANT SHOW VIEW ON test.v_unused TO 'NO_RIGHTS'@'%';
----------------------------------------------------------------------

Or, if it's not possible to create an object, perhaps grant the SHOW VIEW privilege to all views, or to a specific, unimportant view.

That's a bit clunky, I think. It would be better to be able to grant CONNECT (or some other name) to a database specifically.

How to repeat:
See description