<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="system-variable-privileges">
      </a>
      7.1.9.1 System Variable Privileges
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045266647968">
  </a>
  <a class="indexterm" name="idm46045266646480">
  </a>
  <a class="indexterm" name="idm46045266645408">
  </a>
  <a class="indexterm" name="idm46045266644336">
  </a>
  <a class="indexterm" name="idm46045266643248">
  </a>
  <a class="indexterm" name="idm46045266642176">
  </a>
  <a class="indexterm" name="idm46045266641104">
  </a>
  <a class="indexterm" name="idm46045266640016">
  </a>
  <p>
   A system variable can have a global value that affects server
        operation as a whole, a session value that affects only the
        current session, or both:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      For dynamic system variables, the
      <a class="link" href="set-variable.html" title="15.7.6.1 SET Syntax for Variable Assignment">
       <code class="literal">
        SET
       </code>
      </a>
      statement can be used to change their global or session
            runtime value (or both), to affect operation of the current
            server instance. (For information about dynamic variables,
            see
      <a class="xref" href="dynamic-system-variables.html" title="7.1.9.2 Dynamic System Variables">
       Section 7.1.9.2, “Dynamic System Variables”
      </a>
      .)
     </p>
    </li>
    <li class="listitem">
     <p>
      For certain global system variables,
      <a class="link" href="set-variable.html" title="15.7.6.1 SET Syntax for Variable Assignment">
       <code class="literal">
        SET
       </code>
      </a>
      can be used to persist their value to the
      <code class="filename">
       mysqld-auto.cnf
      </code>
      file in the data
            directory, to affect server operation for subsequent
            startups. (For information about persisting system variables
            and the
      <code class="filename">
       mysqld-auto.cnf
      </code>
      file, see
      <a class="xref" href="persisted-system-variables.html" title="7.1.9.3 Persisted System Variables">
       Section 7.1.9.3, “Persisted System Variables”
      </a>
      .)
     </p>
    </li>
    <li class="listitem">
     <p>
      For persisted global system variables,
      <a class="link" href="reset-persist.html" title="15.7.8.7 RESET PERSIST Statement">
       <code class="literal">
        RESET PERSIST
       </code>
      </a>
      can be used to
            remove their value from
      <code class="filename">
       mysqld-auto.cnf
      </code>
      , to affect server
            operation for subsequent startups.
     </p>
    </li>
   </ul>
  </div>
  <p>
   This section describes the privileges required for operations
        that assign values to system variables at runtime. This includes
        operations that affect runtime values, and operations that
        persist values.
  </p>
  <p>
   To set a global system variable, use a
   <a class="link" href="set-variable.html" title="15.7.6.1 SET Syntax for Variable Assignment">
    <code class="literal">
     SET
    </code>
   </a>
   statement with the appropriate keyword. These privileges apply:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      To set a global system variable runtime value, use the
      <a class="link" href="set-variable.html" title="15.7.6.1 SET Syntax for Variable Assignment">
       <code class="literal">
        SET
            GLOBAL
       </code>
      </a>
      statement, which requires the
      <a class="link" href="privileges-provided.html#priv_system-variables-admin">
       <code class="literal">
        SYSTEM_VARIABLES_ADMIN
       </code>
      </a>
      privilege (or the deprecated
      <a class="link" href="privileges-provided.html#priv_super">
       <code class="literal">
        SUPER
       </code>
      </a>
      privilege).
     </p>
    </li>
    <li class="listitem">
     <p>
      To persist a global system variable to the
      <code class="filename">
       mysqld-auto.cnf
      </code>
      file (and set the
            runtime value), use the
      <a class="link" href="set-variable.html" title="15.7.6.1 SET Syntax for Variable Assignment">
       <code class="literal">
        SET
            PERSIST
       </code>
      </a>
      statement, which requires the
      <a class="link" href="privileges-provided.html#priv_system-variables-admin">
       <code class="literal">
        SYSTEM_VARIABLES_ADMIN
       </code>
      </a>
      or
      <a class="link" href="privileges-provided.html#priv_super">
       <code class="literal">
        SUPER
       </code>
      </a>
      privilege.
     </p>
    </li>
    <li class="listitem">
     <p>
      To persist a global system variable to the
      <code class="filename">
       mysqld-auto.cnf
      </code>
      file (without setting
            the runtime value), use the
      <a class="link" href="set-variable.html" title="15.7.6.1 SET Syntax for Variable Assignment">
       <code class="literal">
        SET
            PERSIST_ONLY
       </code>
      </a>
      statement, which requires the
      <a class="link" href="privileges-provided.html#priv_system-variables-admin">
       <code class="literal">
        SYSTEM_VARIABLES_ADMIN
       </code>
      </a>
      and
      <a class="link" href="privileges-provided.html#priv_persist-ro-variables-admin">
       <code class="literal">
        PERSIST_RO_VARIABLES_ADMIN
       </code>
      </a>
      privileges.
      <a class="link" href="set-variable.html" title="15.7.6.1 SET Syntax for Variable Assignment">
       <code class="literal">
        SET
            PERSIST_ONLY
       </code>
      </a>
      can be used for both dynamic and
            read-only system variables, but is particularly useful for
            persisting read-only variables, for which
      <a class="link" href="set-variable.html" title="15.7.6.1 SET Syntax for Variable Assignment">
       <code class="literal">
        SET
            PERSIST
       </code>
      </a>
      cannot be used.
     </p>
    </li>
    <li class="listitem">
     <p>
      Some global system variables are persist-restricted (see
      <a class="xref" href="nonpersistible-system-variables.html" title="7.1.9.4 Nonpersistible and Persist-Restricted System Variables">
       Section 7.1.9.4, “Nonpersistible and Persist-Restricted System Variables”
      </a>
      ). To
            persist these variables, use the
      <a class="link" href="set-variable.html" title="15.7.6.1 SET Syntax for Variable Assignment">
       <code class="literal">
        SET
            PERSIST_ONLY
       </code>
      </a>
      statement, which requires the
            privileges described previously. In addition, you must
            connect to the server using an encrypted connection and
            supply an SSL certificate with the Subject value specified
            by the
      <a class="link" href="server-system-variables.html#sysvar_persist_only_admin_x509_subject">
       <code class="literal">
        persist_only_admin_x509_subject
       </code>
      </a>
      system variable.
     </p>
    </li>
   </ul>
  </div>
  <p>
   To remove a persisted global system variable from the
   <code class="filename">
    mysqld-auto.cnf
   </code>
   file, use the
   <a class="link" href="reset-persist.html" title="15.7.8.7 RESET PERSIST Statement">
    <code class="literal">
     RESET PERSIST
    </code>
   </a>
   statement. These
        privileges apply:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      For dynamic system variables,
      <a class="link" href="reset-persist.html" title="15.7.8.7 RESET PERSIST Statement">
       <code class="literal">
        RESET
            PERSIST
       </code>
      </a>
      requires the
      <a class="link" href="privileges-provided.html#priv_system-variables-admin">
       <code class="literal">
        SYSTEM_VARIABLES_ADMIN
       </code>
      </a>
      or
      <a class="link" href="privileges-provided.html#priv_super">
       <code class="literal">
        SUPER
       </code>
      </a>
      privilege.
     </p>
    </li>
    <li class="listitem">
     <p>
      For read-only system variables,
      <a class="link" href="reset-persist.html" title="15.7.8.7 RESET PERSIST Statement">
       <code class="literal">
        RESET
            PERSIST
       </code>
      </a>
      requires the
      <a class="link" href="privileges-provided.html#priv_system-variables-admin">
       <code class="literal">
        SYSTEM_VARIABLES_ADMIN
       </code>
      </a>
      and
      <a class="link" href="privileges-provided.html#priv_persist-ro-variables-admin">
       <code class="literal">
        PERSIST_RO_VARIABLES_ADMIN
       </code>
      </a>
      privileges.
     </p>
    </li>
    <li class="listitem">
     <p>
      For persist-restricted variables,
      <a class="link" href="reset-persist.html" title="15.7.8.7 RESET PERSIST Statement">
       <code class="literal">
        RESET
            PERSIST
       </code>
      </a>
      does not require an encrypted connection
            to the server made using a particular SSL certificate.
     </p>
    </li>
   </ul>
  </div>
  <p>
   If a global system variable has any exceptions to the preceding
        privilege requirements, the variable description indicates those
        exceptions. Examples include
   <a class="link" href="server-system-variables.html#sysvar_default_table_encryption">
    <code class="literal">
     default_table_encryption
    </code>
   </a>
   and
   <a class="link" href="server-system-variables.html#sysvar_mandatory_roles">
    <code class="literal">
     mandatory_roles
    </code>
   </a>
   , which require
        additional privileges. These additional privileges apply to
        operations that set the global runtime value, but not operations
        that persist the value.
  </p>
  <p>
   To set a session system variable runtime value, use the
   <a class="link" href="set-variable.html" title="15.7.6.1 SET Syntax for Variable Assignment">
    <code class="literal">
     SET
        SESSION
    </code>
   </a>
   statement. In contrast to setting global
        runtime values, setting session runtime values normally requires
        no special privileges and can be done by any user to affect the
        current session. For some system variables, setting the session
        value may have effects outside the current session and thus is a
        restricted operation that can be done only by users who have a
        special privilege:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      The privilege required is
      <a class="link" href="privileges-provided.html#priv_session-variables-admin">
       <code class="literal">
        SESSION_VARIABLES_ADMIN
       </code>
      </a>
      .
     </p>
     <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
      <div class="admon-title">
       Note
      </div>
      <p>
       Any user who has
       <a class="link" href="privileges-provided.html#priv_system-variables-admin">
        <code class="literal">
         SYSTEM_VARIABLES_ADMIN
        </code>
       </a>
       or
       <a class="link" href="privileges-provided.html#priv_super">
        <code class="literal">
         SUPER
        </code>
       </a>
       effectively has
       <a class="link" href="privileges-provided.html#priv_session-variables-admin">
        <code class="literal">
         SESSION_VARIABLES_ADMIN
        </code>
       </a>
       by
              implication and need not be granted
       <a class="link" href="privileges-provided.html#priv_session-variables-admin">
        <code class="literal">
         SESSION_VARIABLES_ADMIN
        </code>
       </a>
       explicitly.
      </p>
     </div>
    </li>
   </ul>
  </div>
  <p>
   If a session system variable is restricted, the variable
        description indicates that restriction. Examples include
   <a class="link" href="replication-options-binary-log.html#sysvar_binlog_format">
    <code class="literal">
     binlog_format
    </code>
   </a>
   and
   <a class="link" href="replication-options-binary-log.html#sysvar_sql_log_bin">
    <code class="literal">
     sql_log_bin
    </code>
   </a>
   . Setting the
        session value of these variables affects binary logging for the
        current session, but may also have wider implications for the
        integrity of server replication and backups.
  </p>
  <p>
   <a class="link" href="privileges-provided.html#priv_session-variables-admin">
    <code class="literal">
     SESSION_VARIABLES_ADMIN
    </code>
   </a>
   enables
        administrators to minimize the privilege footprint of users who
        may previously have been granted
   <a class="link" href="privileges-provided.html#priv_system-variables-admin">
    <code class="literal">
     SYSTEM_VARIABLES_ADMIN
    </code>
   </a>
   or
   <a class="link" href="privileges-provided.html#priv_super">
    <code class="literal">
     SUPER
    </code>
   </a>
   for the purpose of enabling
        them to modify restricted session system variables. Suppose that
        an administrator has created the following role to confer the
        ability to set restricted session system variables:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa56712652"><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">ROLE</span> set_session_sysvars<span class="token punctuation">;</span>
<span class="token keyword">GRANT</span> SYSTEM_VARIABLES_ADMIN <span class="token keyword">ON</span> <span class="token operator">*</span><span class="token punctuation">.</span><span class="token operator">*</span> <span class="token keyword">TO</span> set_session_sysvars<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Any user granted the
   <code class="literal">
    set_session_sysvars
   </code>
   role
        (and who has that role active) is able to set restricted session
        system variables. However, that user is also able to set global
        system variables, which may be undesirable.
  </p>
  <p>
   By modifying the role to have
   <a class="link" href="privileges-provided.html#priv_session-variables-admin">
    <code class="literal">
     SESSION_VARIABLES_ADMIN
    </code>
   </a>
   instead
        of
   <a class="link" href="privileges-provided.html#priv_system-variables-admin">
    <code class="literal">
     SYSTEM_VARIABLES_ADMIN
    </code>
   </a>
   , the
        role privileges can be reduced to the ability to set restricted
        session system variables and nothing else. To modify the role,
        use these statements:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa76425217"><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> SESSION_VARIABLES_ADMIN <span class="token keyword">ON</span> <span class="token operator">*</span><span class="token punctuation">.</span><span class="token operator">*</span> <span class="token keyword">TO</span> set_session_sysvars<span class="token punctuation">;</span>
<span class="token keyword">REVOKE</span> SYSTEM_VARIABLES_ADMIN <span class="token keyword">ON</span> <span class="token operator">*</span><span class="token punctuation">.</span><span class="token operator">*</span> <span class="token keyword">FROM</span> set_session_sysvars<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Modifying the role has an immediate effect: Any account granted
        the
   <code class="literal">
    set_session_sysvars
   </code>
   role no longer has
   <a class="link" href="privileges-provided.html#priv_system-variables-admin">
    <code class="literal">
     SYSTEM_VARIABLES_ADMIN
    </code>
   </a>
   and is not
        able to set global system variables without being granted that
        ability explicitly. A similar
   <a class="link" href="grant.html" title="15.7.1.6 GRANT Statement">
    <code class="literal">
     GRANT
    </code>
   </a>
   /
   <a class="link" href="revoke.html" title="15.7.1.8 REVOKE Statement">
    <code class="literal">
     REVOKE
    </code>
   </a>
   sequence can be applied to any account that was granted
   <a class="link" href="privileges-provided.html#priv_system-variables-admin">
    <code class="literal">
     SYSTEM_VARIABLES_ADMIN
    </code>
   </a>
   directly
        rather than by means of a role.
  </p>
 </div>
 <br/>
</div>
