Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 37.4Mb
PDF (A4) - 37.4Mb
PDF (RPM) - 36.8Mb
HTML Download (TGZ) - 10.2Mb
HTML Download (Zip) - 10.2Mb
HTML Download (RPM) - 8.8Mb
Man Pages (TGZ) - 210.2Kb
Man Pages (Zip) - 318.3Kb
Info (Gzip) - 3.4Mb
Info (Zip) - 3.4Mb
Excerpts from this Manual

MySQL 5.7 Reference Manual  /  ...  /  The No-Login Authentication Plugin

7.5.1.7 The No-Login Authentication Plugin

The mysql_no_login server-side authentication plugin prevents all client connections to any account that uses it. Use cases for such a plugin includes accounts that must be able to execute stored programs and views with elevated privileges without exposing those privileges to ordinary users, and proxy accounts that should never permit direct login.

The following table shows the plugin and library file names. The file name suffix might differ on your system. The file location is the directory named by the plugin_dir system variable. For installation information, see Section 7.3.8, “Pluggable Authentication”.

Table 7.14 MySQL No Login Authentication Plugin

Server-side plugin namemysql_no_login
Client-side plugin nameNone
Library file namemysql_no_login.so

An account that authenticates using mysql_no_login may be used as the DEFINER for stored program and view objects. If such an object definition also includes SQL SECURITY DEFINER, it executes with that account's privileges. DBAs can use this behavior to provide access to confidential or sensitive data that is exposed only through well-controlled interfaces.

The following example provides a simple illustration of these principles. It defines an account that does not permit client connections, and associates with it a view that exposes only certain columns of the mysql.user table:

CREATE DATABASE nologindb;
CREATE USER 'nologin'@'localhost' IDENTIFIED WITH mysql_no_login;
GRANT ALL ON nologindb.* TO 'nologin'@'localhost';
GRANT SELECT ON mysql.user TO 'nologin'@'localhost';
CREATE DEFINER = 'nologin'@'localhost' SQL SECURITY DEFINER
VIEW nologindb.myview AS SELECT User, Host FROM mysql.user;

To provide protected access to the view to ordinary users, do this:

GRANT SELECT ON nologindb.myview TO 'ordinaryuser'@'localhost';

Now the ordinary user can use the view to access the limited information it presents:

SELECT * FROM nologindb.myview;

Attempts by the user to access columns other than those exposed by the view result in an error.

Note

Because the nologin account cannot be used directly, the operations required to set up objects that it uses must be performed by root or similar account with the privileges required to create the objects and set DEFINER values.

An account that authenticates using mysql_no_login may be used as a base user for proxy accounts:

CREATE USER 'proxy_base'@'localhost' IDENTIFIED WITH mysql_no_login;
... grant to 'proxy_base'@'localhost' any privileges it requires ...
GRANT PROXY ON 'proxy_base'@'localhost' TO 'real_user'@'localhost';

This enables clients to access MySQL through the proxy account but not to bypass the proxy mechanism by connecting directly as the proxy user.

For general information about pluggable authentication in MySQL, see Section 7.3.8, “Pluggable Authentication”.


User Comments
Sign Up Login You must be logged in to post a comment.