<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="no-login-pluggable-authentication">
      </a>
      8.4.1.9 No-Login Pluggable Authentication
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045244253744">
  </a>
  <a class="indexterm" name="idm46045244252656">
  </a>
  <p>
   The
   <code class="literal">
    mysql_no_login
   </code>
   server-side authentication
        plugin prevents all client connections to any account that uses
        it. Use cases for this plugin include:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Accounts that must be able to execute stored programs and
            views with elevated privileges without exposing those
            privileges to ordinary users.
     </p>
    </li>
    <li class="listitem">
     <p>
      Proxied accounts that should never permit direct login but
            are intended to be accessed only through proxy accounts.
     </p>
    </li>
   </ul>
  </div>
  <p>
   The following table shows the plugin and library file names. The
        file name suffix might differ on your system. The file must be
        located in the directory named by the
   <a class="link" href="server-system-variables.html#sysvar_plugin_dir">
    <code class="literal">
     plugin_dir
    </code>
   </a>
   system variable.
  </p>
  <div class="table">
   <a name="idm46045244245760">
   </a>
   <p class="title">
    <b>
     Table 8.24 Plugin and Library Names for No-Login Authentication
    </b>
   </p>
   <div class="table-contents">
    <table summary="Names for the plugins and library file used for no-login password authentication.">
     <colgroup>
      <col style="width: 30%"/>
      <col style="width: 70%"/>
     </colgroup>
     <thead>
      <tr>
       <th>
        Plugin or File
       </th>
       <th>
        Plugin or File Name
       </th>
      </tr>
     </thead>
     <tbody>
      <tr>
       <td>
        Server-side plugin
       </td>
       <td>
        <code class="literal">
         mysql_no_login
        </code>
       </td>
      </tr>
      <tr>
       <td>
        Client-side plugin
       </td>
       <td>
        None
       </td>
      </tr>
      <tr>
       <td>
        Library file
       </td>
       <td>
        <code class="filename">
         mysql_no_login.so
        </code>
       </td>
      </tr>
     </tbody>
    </table>
   </div>
  </div>
  <br class="table-break"/>
  <p>
   The following sections provide installation and usage
        information specific to no-login pluggable authentication:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <a class="xref" href="no-login-pluggable-authentication.html#no-login-pluggable-authentication-installation" title="Installing No-Login Pluggable Authentication">
       Installing No-Login Pluggable Authentication
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="no-login-pluggable-authentication.html#no-login-pluggable-authentication-uninstallation" title="Uninstalling No-Login Pluggable Authentication">
       Uninstalling No-Login Pluggable Authentication
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="no-login-pluggable-authentication.html#no-login-pluggable-authentication-usage" title="Using No-Login Pluggable Authentication">
       Using No-Login Pluggable Authentication
      </a>
     </p>
    </li>
   </ul>
  </div>
  <p>
   For general information about pluggable authentication in MySQL,
        see
   <a class="xref" href="pluggable-authentication.html" title="8.2.17 Pluggable Authentication">
    Section 8.2.17, “Pluggable Authentication”
   </a>
   . For proxy user
        information, see
   <a class="xref" href="proxy-users.html" title="8.2.19 Proxy Users">
    Section 8.2.19, “Proxy Users”
   </a>
   .
  </p>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="no-login-pluggable-authentication-installation">
       </a>
       Installing No-Login Pluggable Authentication
      </h5>
     </div>
    </div>
   </div>
   <p>
    This section describes how to install the no-login
          authentication plugin. For general information about
          installing plugins, see
    <a class="xref" href="plugin-loading.html" title="7.6.1 Installing and Uninstalling Plugins">
     Section 7.6.1, “Installing and Uninstalling Plugins”
    </a>
    .
   </p>
   <p>
    To be usable by the server, the plugin library file must be
          located in the MySQL plugin directory (the directory named by
          the
    <a class="link" href="server-system-variables.html#sysvar_plugin_dir">
     <code class="literal">
      plugin_dir
     </code>
    </a>
    system
          variable). If necessary, configure the plugin directory
          location by setting the value of
    <a class="link" href="server-system-variables.html#sysvar_plugin_dir">
     <code class="literal">
      plugin_dir
     </code>
    </a>
    at server startup.
   </p>
   <p>
    The plugin library file base name is
    <code class="literal">
     mysql_no_login
    </code>
    . The file name suffix
          differs per platform (for example,
    <code class="filename">
     .so
    </code>
    for Unix and Unix-like systems,
    <code class="filename">
     .dll
    </code>
    for
          Windows).
   </p>
   <p>
    To load the plugin at server startup, use the
    <a class="link" href="server-options.html#option_mysqld_plugin-load-add">
     <code class="option">
      --plugin-load-add
     </code>
    </a>
    option to
          name the library file that contains it. With this
          plugin-loading method, the option must be given each time the
          server starts. For example, put these lines in the server
    <code class="filename">
     my.cnf
    </code>
    file, adjusting the
    <code class="filename">
     .so
    </code>
    suffix for your platform as
          necessary:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-ini"><div class="docs-select-all right" id="sa65575307"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-ini"><span class="token selector">[mysqld]</span>
<span class="token constant">plugin-load-add</span><span class="token attr-value"><span class="token punctuation">=</span>mysql_no_login.so</span></code></pre>
   </div>
   <p>
    After modifying
    <code class="filename">
     my.cnf
    </code>
    , restart the
          server to cause the new settings to take effect.
   </p>
   <p>
    Alternatively, to load the plugin at runtime, use this
          statement, adjusting the
    <code class="filename">
     .so
    </code>
    suffix for
          your platform as necessary:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa39076875"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">INSTALL</span> <span class="token keyword">PLUGIN</span> mysql_no_login <span class="token keyword">SONAME</span> <span class="token string">'mysql_no_login.so'</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    <a class="link" href="install-plugin.html" title="15.7.4.4 INSTALL PLUGIN Statement">
     <code class="literal">
      INSTALL PLUGIN
     </code>
    </a>
    loads the plugin
          immediately, and also registers it in the
    <code class="literal">
     mysql.plugins
    </code>
    system table to cause the
          server to load it for each subsequent normal startup without
          the need for
    <a class="link" href="server-options.html#option_mysqld_plugin-load-add">
     <code class="option">
      --plugin-load-add
     </code>
    </a>
    .
   </p>
   <p>
    To verify plugin installation, examine the Information Schema
    <a class="link" href="information-schema-plugins-table.html" title="28.3.22 The INFORMATION_SCHEMA PLUGINS Table">
     <code class="literal">
      PLUGINS
     </code>
    </a>
    table or use the
    <a class="link" href="show-plugins.html" title="15.7.7.27 SHOW PLUGINS Statement">
     <code class="literal">
      SHOW PLUGINS
     </code>
    </a>
    statement (see
    <a class="xref" href="obtaining-plugin-information.html" title="7.6.2 Obtaining Server Plugin Information">
     Section 7.6.2, “Obtaining Server Plugin Information”
    </a>
    ). For example:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa8512579"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> PLUGIN_NAME<span class="token punctuation">,</span> PLUGIN_STATUS
       <span class="token keyword">FROM</span> INFORMATION_SCHEMA<span class="token punctuation">.</span><span class="token keyword">PLUGINS</span>
       <span class="token keyword">WHERE</span> PLUGIN_NAME <span class="token operator">LIKE</span> <span class="token string">'%login%'</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> PLUGIN_NAME    <span class="token punctuation">|</span> PLUGIN_STATUS <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> mysql_no_login <span class="token punctuation">|</span> ACTIVE        <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span></code></pre>
   </div>
   <p>
    If the plugin fails to initialize, check the server error log
          for diagnostic messages.
   </p>
   <p>
    To associate MySQL accounts with the no-login plugin, see
    <a class="xref" href="no-login-pluggable-authentication.html#no-login-pluggable-authentication-usage" title="Using No-Login Pluggable Authentication">
     Using No-Login Pluggable Authentication
    </a>
    .
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="no-login-pluggable-authentication-uninstallation">
       </a>
       Uninstalling No-Login Pluggable Authentication
      </h5>
     </div>
    </div>
   </div>
   <p>
    The method used to uninstall the no-login authentication
          plugin depends on how you installed it:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       If you installed the plugin at server startup using a
       <a class="link" href="server-options.html#option_mysqld_plugin-load-add">
        <code class="option">
         --plugin-load-add
        </code>
       </a>
       option,
              restart the server without the option.
      </p>
     </li>
     <li class="listitem">
      <p>
       If you installed the plugin at runtime using an
       <a class="link" href="install-plugin.html" title="15.7.4.4 INSTALL PLUGIN Statement">
        <code class="literal">
         INSTALL PLUGIN
        </code>
       </a>
       statement,
              it remains installed across server restarts. To uninstall
              it, use
       <a class="link" href="uninstall-plugin.html" title="15.7.4.6 UNINSTALL PLUGIN Statement">
        <code class="literal">
         UNINSTALL PLUGIN
        </code>
       </a>
       :
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa55891216"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">UNINSTALL</span> <span class="token keyword">PLUGIN</span> mysql_no_login<span class="token punctuation">;</span></code></pre>
      </div>
     </li>
    </ul>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="no-login-pluggable-authentication-usage">
       </a>
       Using No-Login Pluggable Authentication
      </h5>
     </div>
    </div>
   </div>
   <p>
    This section describes how to use the no-login authentication
          plugin to prevent accounts from being used for connecting from
          MySQL client programs to the server. It is assumed that the
          server is running with the no-login plugin enabled, as
          described in
    <a class="xref" href="no-login-pluggable-authentication.html#no-login-pluggable-authentication-installation" title="Installing No-Login Pluggable Authentication">
     Installing No-Login Pluggable Authentication
    </a>
    .
   </p>
   <p>
    To refer to the no-login authentication plugin in the
    <code class="literal">
     IDENTIFIED WITH
    </code>
    clause of a
    <a class="link" href="create-user.html" title="15.7.1.3 CREATE USER Statement">
     <code class="literal">
      CREATE USER
     </code>
    </a>
    statement, use the
          name
    <code class="literal">
     mysql_no_login
    </code>
    .
   </p>
   <p>
    An account that authenticates using
    <code class="literal">
     mysql_no_login
    </code>
    may be used as the
    <code class="literal">
     DEFINER
    </code>
    for stored program and view
          objects. If such an object definition also includes
    <code class="literal">
     SQL SECURITY DEFINER
    </code>
    , 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.
   </p>
   <p>
    The following example illustrates 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
    <code class="literal">
     mysql.user
    </code>
    system table:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa30489229"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">DATABASE</span> nologindb<span class="token punctuation">;</span>
<span class="token keyword">CREATE</span> <span class="token keyword">USER</span> <span class="token string">'nologin'</span>@<span class="token string">'localhost'</span>
  <span class="token keyword">IDENTIFIED</span> <span class="token keyword">WITH</span> mysql_no_login<span class="token punctuation">;</span>
<span class="token keyword">GRANT</span> <span class="token keyword">ALL</span> <span class="token keyword">ON</span> nologindb<span class="token punctuation">.</span><span class="token operator">*</span>
  <span class="token keyword">TO</span> <span class="token string">'nologin'</span>@<span class="token string">'localhost'</span><span class="token punctuation">;</span>
<span class="token keyword">GRANT</span> <span class="token keyword">SELECT</span> <span class="token keyword">ON</span> mysql<span class="token punctuation">.</span><span class="token keyword">user</span>
  <span class="token keyword">TO</span> <span class="token string">'nologin'</span>@<span class="token string">'localhost'</span><span class="token punctuation">;</span>
<span class="token keyword">CREATE</span> <span class="token keyword">DEFINER</span> <span class="token operator">=</span> <span class="token string">'nologin'</span>@<span class="token string">'localhost'</span>
  <span class="token keyword">SQL</span> <span class="token keyword">SECURITY</span> <span class="token keyword">DEFINER</span>
  <span class="token keyword">VIEW</span> nologindb<span class="token punctuation">.</span>myview
  <span class="token keyword">AS</span> <span class="token keyword">SELECT</span> <span class="token keyword">User</span><span class="token punctuation">,</span> <span class="token keyword">Host</span> <span class="token keyword">FROM</span> mysql<span class="token punctuation">.</span><span class="token keyword">user</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    To provide protected access to the view to an ordinary user,
          do this:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa74076063"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">GRANT</span> <span class="token keyword">SELECT</span> <span class="token keyword">ON</span> nologindb<span class="token punctuation">.</span>myview
  <span class="token keyword">TO</span> <span class="token string">'ordinaryuser'</span>@<span class="token string">'localhost'</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    Now the ordinary user can use the view to access the limited
          information it presents:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa16613530"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> nologindb<span class="token punctuation">.</span>myview<span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    Attempts by the user to access columns other than those
          exposed by the view result in an error, as do attempts to
          select from the view by users not granted access to it.
   </p>
   <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
    <div class="admon-title">
     Note
    </div>
    <p>
     Because the
     <code class="literal">
      nologin
     </code>
     account cannot be
            used directly, the operations required to set up objects
            that it uses must be performed by
     <code class="literal">
      root
     </code>
     or
            similar account that has the privileges required to create
            the objects and set
     <code class="literal">
      DEFINER
     </code>
     values.
    </p>
   </div>
   <p>
    The
    <code class="literal">
     mysql_no_login
    </code>
    plugin is also useful in
          proxying scenarios. (For a discussion of concepts involved in
          proxying, see
    <a class="xref" href="proxy-users.html" title="8.2.19 Proxy Users">
     Section 8.2.19, “Proxy Users”
    </a>
    .) An account that
          authenticates using
    <code class="literal">
     mysql_no_login
    </code>
    may be
          used as a proxied user for proxy accounts:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa49768817"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token comment" spellcheck="true">-- create proxied account</span>
<span class="token keyword">CREATE</span> <span class="token keyword">USER</span> <span class="token string">'proxied_user'</span>@<span class="token string">'localhost'</span>
  <span class="token keyword">IDENTIFIED</span> <span class="token keyword">WITH</span> mysql_no_login<span class="token punctuation">;</span>
<span class="token comment" spellcheck="true">-- grant privileges to proxied account</span>
<span class="token keyword">GRANT</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
  <span class="token keyword">ON</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
  <span class="token keyword">TO</span> <span class="token string">'proxied_user'</span>@<span class="token string">'localhost'</span><span class="token punctuation">;</span>
<span class="token comment" spellcheck="true">-- permit proxy_user to be a proxy account for proxied account</span>
<span class="token keyword">GRANT</span> <span class="token keyword">PROXY</span>
  <span class="token keyword">ON</span> <span class="token string">'proxied_user'</span>@<span class="token string">'localhost'</span>
  <span class="token keyword">TO</span> <span class="token string">'proxy_user'</span>@<span class="token string">'localhost'</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    This enables clients to access MySQL through the proxy account
          (
    <code class="literal">
     proxy_user
    </code>
    ) but not to bypass the proxy
          mechanism by connecting directly as the proxied user
          (
    <code class="literal">
     proxied_user
    </code>
    ). A client who connects using
          the
    <code class="literal">
     proxy_user
    </code>
    account has the privileges
          of the
    <code class="literal">
     proxied_user
    </code>
    account, but
    <code class="literal">
     proxied_user
    </code>
    itself cannot be used to
          connect.
   </p>
   <p>
    For alternative methods of protecting proxied accounts against
          direct use, see
    <a class="xref" href="proxy-users.html#preventing-proxied-account-direct-login" title="Preventing Direct Login to Proxied Accounts">
     Preventing Direct Login to Proxied Accounts
    </a>
    .
   </p>
  </div>
 </div>
 <br/>
</div>
