<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h2 class="title">
      <a name="stored-objects-security">
      </a>
      27.6 Stored Object Access Control
     </h2>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045081663328">
  </a>
  <a class="indexterm" name="idm46045081661872">
  </a>
  <a class="indexterm" name="idm46045081660784">
  </a>
  <a class="indexterm" name="idm46045081659296">
  </a>
  <a class="indexterm" name="idm46045081657808">
  </a>
  <a class="indexterm" name="idm46045081656320">
  </a>
  <a class="indexterm" name="idm46045081655248">
  </a>
  <a class="indexterm" name="idm46045081653760">
  </a>
  <p>
   Stored programs (procedures, functions, triggers, and events) and
      views are defined prior to use and, when referenced, execute
      within a security context that determines their privileges. The
      privileges applicable to execution of a stored object are
      controlled by its
   <code class="literal">
    DEFINER
   </code>
   attribute and
   <code class="literal">
    SQL SECURITY
   </code>
   characteristic.
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <a class="xref" href="stored-objects-security.html#stored-objects-security-definer" title="The DEFINER Attribute">
       The DEFINER Attribute
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="stored-objects-security.html#stored-objects-security-sql-security" title="The SQL SECURITY Characteristic">
       The SQL SECURITY Characteristic
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="stored-objects-security.html#stored-objects-security-examples" title="Examples">
       Examples
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="stored-objects-security.html#stored-objects-security-orphan-objects" title="Orphan Stored Objects">
       Orphan Stored Objects
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="stored-objects-security.html#stored-objects-security-guidelines" title="Risk-Minimization Guidelines">
       Risk-Minimization Guidelines
      </a>
     </p>
    </li>
   </ul>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h3 class="title">
       <a name="stored-objects-security-definer">
       </a>
       The DEFINER Attribute
      </h3>
     </div>
    </div>
   </div>
   <p>
    A stored object definition can include a
    <code class="literal">
     DEFINER
    </code>
    attribute that names a MySQL account.
        If a definition omits the
    <code class="literal">
     DEFINER
    </code>
    attribute,
        the default object definer is the user who creates it.
   </p>
   <p>
    The following rules determine which accounts you can specify as
        the
    <code class="literal">
     DEFINER
    </code>
    attribute for a stored object:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       If you have the
       <a class="link" href="privileges-provided.html#priv_set-any-definer">
        <code class="literal">
         SET_ANY_DEFINER
        </code>
       </a>
       privilege,
            you can specify any account as the
       <code class="literal">
        DEFINER
       </code>
       attribute. If the account does
            not exist, a warning is generated. Additionally, to set a
            stored object
       <code class="literal">
        DEFINER
       </code>
       attribute to an
            account that has the
       <a class="link" href="privileges-provided.html#priv_system-user">
        <code class="literal">
         SYSTEM_USER
        </code>
       </a>
       privilege, you
            must have the
       <a class="link" href="privileges-provided.html#priv_system-user">
        <code class="literal">
         SYSTEM_USER
        </code>
       </a>
       privilege.
      </p>
     </li>
     <li class="listitem">
      <p>
       Otherwise, the only permitted account is your own, specified
            either literally or as
       <a class="link" href="information-functions.html#function_current-user">
        <code class="literal">
         CURRENT_USER
        </code>
       </a>
       or
       <a class="link" href="information-functions.html#function_current-user">
        <code class="literal">
         CURRENT_USER()
        </code>
       </a>
       . You cannot
            set the definer to any other account.
      </p>
     </li>
    </ul>
   </div>
   <p>
    Creating a stored object with a nonexistent
    <code class="literal">
     DEFINER
    </code>
    account creates an orphan object,
        which may have negative consequences; see
    <a class="xref" href="stored-objects-security.html#stored-objects-security-orphan-objects" title="Orphan Stored Objects">
     Orphan Stored Objects
    </a>
    .
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h3 class="title">
       <a name="stored-objects-security-sql-security">
       </a>
       The SQL SECURITY Characteristic
      </h3>
     </div>
    </div>
   </div>
   <p>
    For stored routines (procedures and functions) and views, the
        object definition can include an
    <code class="literal">
     SQL SECURITY
    </code>
    characteristic with a value of
    <code class="literal">
     DEFINER
    </code>
    or
    <code class="literal">
     INVOKER
    </code>
    to specify whether the object
        executes in definer or invoker context. If the definition omits
        the
    <code class="literal">
     SQL SECURITY
    </code>
    characteristic, the default
        is definer context.
   </p>
   <p>
    Triggers and events have no
    <code class="literal">
     SQL SECURITY
    </code>
    characteristic and always execute in definer context. The server
        invokes these objects automatically as necessary, so there is no
        invoking user.
   </p>
   <p>
    Definer and invoker security contexts differ as follows:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       A stored object that executes in definer security context
            executes with the privileges of the account named by its
       <code class="literal">
        DEFINER
       </code>
       attribute. These privileges may
            be entirely different from those of the invoking user. The
            invoker must have appropriate privileges to reference the
            object (for example,
       <a class="link" href="privileges-provided.html#priv_execute">
        <code class="literal">
         EXECUTE
        </code>
       </a>
       to call a stored procedure or
       <a class="link" href="privileges-provided.html#priv_select">
        <code class="literal">
         SELECT
        </code>
       </a>
       to select from a
            view), but during object execution, the invoker's privileges
            are ignored and only the
       <code class="literal">
        DEFINER
       </code>
       account
            privileges matter. If the
       <code class="literal">
        DEFINER
       </code>
       account
            has few privileges, the object is correspondingly limited in
            the operations it can perform. If the
       <code class="literal">
        DEFINER
       </code>
       account is highly privileged
            (such as an administrative account), the object can perform
            powerful operations
       <span class="emphasis">
        <em>
         no matter who invokes
            it.
        </em>
       </span>
      </p>
     </li>
     <li class="listitem">
      <p>
       A stored routine or view that executes in invoker security
            context can perform only operations for which the invoker
            has privileges. The
       <code class="literal">
        DEFINER
       </code>
       attribute has
            no effect on object execution.
      </p>
     </li>
    </ul>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h3 class="title">
       <a name="stored-objects-security-examples">
       </a>
       Examples
      </h3>
     </div>
    </div>
   </div>
   <p>
    Consider the following stored procedure, which is declared with
    <code class="literal">
     SQL SECURITY DEFINER
    </code>
    to execute in definer
        security context:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa217678"><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">DEFINER</span> <span class="token operator">=</span> <span class="token string">'admin'</span>@<span class="token string">'localhost'</span> <span class="token keyword">PROCEDURE</span> p1<span class="token punctuation">(</span><span class="token punctuation">)</span>
<span class="token keyword">SQL</span> <span class="token keyword">SECURITY</span> <span class="token keyword">DEFINER</span>
<span class="token keyword">BEGIN</span>
  <span class="token keyword">UPDATE</span> t1 <span class="token keyword">SET</span> counter <span class="token operator">=</span> counter <span class="token operator">+</span> <span class="token number">1</span><span class="token punctuation">;</span>
<span class="token keyword">END</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    Any user who has the
    <a class="link" href="privileges-provided.html#priv_execute">
     <code class="literal">
      EXECUTE
     </code>
    </a>
    privilege for
    <code class="literal">
     p1
    </code>
    can invoke it with a
    <a class="link" href="call.html" title="15.2.1 CALL Statement">
     <code class="literal">
      CALL
     </code>
    </a>
    statement. However, when
    <code class="literal">
     p1
    </code>
    executes, it does so in definer security
        context and thus executes with the privileges of
    <code class="literal">
     'admin'@'localhost'
    </code>
    , the account named as its
    <code class="literal">
     DEFINER
    </code>
    attribute. This account must have the
    <a class="link" href="privileges-provided.html#priv_execute">
     <code class="literal">
      EXECUTE
     </code>
    </a>
    privilege for
    <code class="literal">
     p1
    </code>
    as well as the
    <a class="link" href="privileges-provided.html#priv_update">
     <code class="literal">
      UPDATE
     </code>
    </a>
    privilege for the table
    <code class="literal">
     t1
    </code>
    referenced within the object body.
        Otherwise, the procedure fails.
   </p>
   <p>
    Now consider this stored procedure, which is identical to
    <code class="literal">
     p1
    </code>
    except that its
    <code class="literal">
     SQL
        SECURITY
    </code>
    characteristic is
    <code class="literal">
     INVOKER
    </code>
    :
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa75937940"><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">DEFINER</span> <span class="token operator">=</span> <span class="token string">'admin'</span>@<span class="token string">'localhost'</span> <span class="token keyword">PROCEDURE</span> p2<span class="token punctuation">(</span><span class="token punctuation">)</span>
<span class="token keyword">SQL</span> <span class="token keyword">SECURITY</span> <span class="token keyword">INVOKER</span>
<span class="token keyword">BEGIN</span>
  <span class="token keyword">UPDATE</span> t1 <span class="token keyword">SET</span> counter <span class="token operator">=</span> counter <span class="token operator">+</span> <span class="token number">1</span><span class="token punctuation">;</span>
<span class="token keyword">END</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    Unlike
    <code class="literal">
     p1
    </code>
    ,
    <code class="literal">
     p2
    </code>
    executes in
        invoker security context and thus with the privileges of the
        invoking user regardless of the
    <code class="literal">
     DEFINER
    </code>
    attribute value.
    <code class="literal">
     p2
    </code>
    fails if the invoker
        lacks the
    <a class="link" href="privileges-provided.html#priv_execute">
     <code class="literal">
      EXECUTE
     </code>
    </a>
    privilege for
    <code class="literal">
     p2
    </code>
    or the
    <a class="link" href="privileges-provided.html#priv_update">
     <code class="literal">
      UPDATE
     </code>
    </a>
    privilege for the table
    <code class="literal">
     t1
    </code>
    .
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h3 class="title">
       <a name="stored-objects-security-orphan-objects">
       </a>
       Orphan Stored Objects
      </h3>
     </div>
    </div>
   </div>
   <a class="indexterm" name="idm46045081587280">
   </a>
   <a class="indexterm" name="idm46045081585792">
   </a>
   <a class="indexterm" name="idm46045081584720">
   </a>
   <a class="indexterm" name="idm46045081583264">
   </a>
   <p>
    An orphan stored object is one for which its
    <code class="literal">
     DEFINER
    </code>
    attribute names a nonexistent
        account:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       An orphan stored object can be created by specifying a
            nonexistent
       <code class="literal">
        DEFINER
       </code>
       account at
            object-creation time.
      </p>
     </li>
     <li class="listitem">
      <p>
       An existing stored object can become orphaned through
            execution of a
       <a class="link" href="drop-user.html" title="15.7.1.5 DROP USER Statement">
        <code class="literal">
         DROP USER
        </code>
       </a>
       statement that drops the object
       <code class="literal">
        DEFINER
       </code>
       account, or a
       <a class="link" href="rename-user.html" title="15.7.1.7 RENAME USER Statement">
        <code class="literal">
         RENAME USER
        </code>
       </a>
       statement that renames the object
       <code class="literal">
        DEFINER
       </code>
       account.
      </p>
     </li>
    </ul>
   </div>
   <p>
    An orphan stored object may be problematic in these ways:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       Because the
       <code class="literal">
        DEFINER
       </code>
       account does not
            exist, the object may not work as expected if it executes in
            definer security context:
      </p>
      <div class="itemizedlist">
       <ul class="itemizedlist" style="list-style-type: circle; ">
        <li class="listitem">
         <p>
          For a stored routine, an error occurs at routine
                execution time if the
          <code class="literal">
           SQL SECURITY
          </code>
          value is
          <code class="literal">
           DEFINER
          </code>
          but the definer
                account does not exist.
         </p>
        </li>
        <li class="listitem">
         <p>
          For a trigger, it is not a good idea for trigger
                activation to occur until the account actually does
                exist. Otherwise, the behavior with respect to privilege
                checking is undefined.
         </p>
        </li>
        <li class="listitem">
         <p>
          For an event, an error occurs at event execution time if
                the account does not exist.
         </p>
        </li>
        <li class="listitem">
         <p>
          For a view, an error occurs when the view is referenced
                if the
          <code class="literal">
           SQL SECURITY
          </code>
          value is
          <code class="literal">
           DEFINER
          </code>
          but the definer account does
                not exist.
         </p>
        </li>
       </ul>
      </div>
     </li>
     <li class="listitem">
      <p>
       The object may present a security risk if the nonexistent
       <code class="literal">
        DEFINER
       </code>
       account is subsequently
            re-created for a purpose unrelated to the object. In this
            case, the account
       <span class="quote">
        “
        <span class="quote">
         adopts
        </span>
        ”
       </span>
       the object and, with
            the appropriate privileges, is able to execute it even if
            that is not intended.
      </p>
     </li>
    </ul>
   </div>
   <p>
    The server imposes the following account-management security
        checks designed to prevent operations that (perhaps
        inadvertently) cause stored objects to become orphaned or that
        cause adoption of stored objects that are currently orphaned:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       <a class="link" href="drop-user.html" title="15.7.1.5 DROP USER Statement">
        <code class="literal">
         DROP USER
        </code>
       </a>
       fails with an error
            if any account to be dropped is named as the
       <code class="literal">
        DEFINER
       </code>
       attribute for any stored object.
            (That is, the statement fails if dropping an account would
            cause a stored object to become orphaned.)
      </p>
     </li>
     <li class="listitem">
      <p>
       <a class="link" href="rename-user.html" title="15.7.1.7 RENAME USER Statement">
        <code class="literal">
         RENAME USER
        </code>
       </a>
       fails with an
            error if any account to be renamed is named as the
       <code class="literal">
        DEFINER
       </code>
       attribute for any stored object.
            (That is, the statement fails if renaming an account would
            cause a stored object to become orphaned.)
      </p>
     </li>
     <li class="listitem">
      <p>
       <a class="link" href="create-user.html" title="15.7.1.3 CREATE USER Statement">
        <code class="literal">
         CREATE USER
        </code>
       </a>
       fails with an
            error if any account to be created is named as the
       <code class="literal">
        DEFINER
       </code>
       attribute for any stored object.
            (That is, the statement fails if creating an account would
            cause the account to adopt a currently orphaned stored
            object.)
      </p>
     </li>
    </ul>
   </div>
   <p>
    In certain situations, it may be necessary to deliberately
        execute those account-management statements even when they would
        otherwise fail. To make this possible, if a user has the
    <a class="link" href="privileges-provided.html#priv_allow-nonexistent-definer">
     <code class="literal">
      ALLOW_NONEXISTENT_DEFINER
     </code>
    </a>
    privilege, that privilege overrides the orphan object security
        checks and the statements succeed with a warning rather than
        failing with an error.
   </p>
   <p>
    To obtain information about the accounts used as stored object
        definers in a MySQL installation, query the
    <code class="literal">
     INFORMATION_SCHEMA
    </code>
    .
   </p>
   <p>
    This query identifies which
    <code class="literal">
     INFORMATION_SCHEMA
    </code>
    tables describe objects
        that have a
    <code class="literal">
     DEFINER
    </code>
    attribute:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa57706897"><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> TABLE_SCHEMA<span class="token punctuation">,</span> <span class="token keyword">TABLE_NAME</span> <span class="token keyword">FROM</span> INFORMATION_SCHEMA<span class="token punctuation">.</span><span class="token keyword">COLUMNS</span>
       <span class="token keyword">WHERE</span> <span class="token keyword">COLUMN_NAME</span> <span class="token operator">=</span> <span class="token string">'DEFINER'</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 class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> TABLE_SCHEMA       <span class="token punctuation">|</span> TABLE_NAME <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 class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> information_schema <span class="token punctuation">|</span> EVENTS     <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> information_schema <span class="token punctuation">|</span> ROUTINES   <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> information_schema <span class="token punctuation">|</span> TRIGGERS   <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> information_schema <span class="token punctuation">|</span> VIEWS      <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 class="token punctuation">+</span></span></code></pre>
   </div>
   <p>
    The result tells you which tables to query to discover which
        stored object
    <code class="literal">
     DEFINER
    </code>
    values exist and which
        objects have a particular
    <code class="literal">
     DEFINER
    </code>
    value:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       To identify which
       <code class="literal">
        DEFINER
       </code>
       values exist in
            each table, use these queries:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa73446548"><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 keyword">DISTINCT</span> <span class="token keyword">DEFINER</span> <span class="token keyword">FROM</span> INFORMATION_SCHEMA<span class="token punctuation">.</span><span class="token keyword">EVENTS</span><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token keyword">DISTINCT</span> <span class="token keyword">DEFINER</span> <span class="token keyword">FROM</span> INFORMATION_SCHEMA<span class="token punctuation">.</span>ROUTINES<span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token keyword">DISTINCT</span> <span class="token keyword">DEFINER</span> <span class="token keyword">FROM</span> INFORMATION_SCHEMA<span class="token punctuation">.</span><span class="token keyword">TRIGGERS</span><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token keyword">DISTINCT</span> <span class="token keyword">DEFINER</span> <span class="token keyword">FROM</span> INFORMATION_SCHEMA<span class="token punctuation">.</span>VIEWS<span class="token punctuation">;</span></code></pre>
      </div>
      <p>
       The query results are significant for any account displayed
            as follows:
      </p>
      <div class="itemizedlist">
       <ul class="itemizedlist" style="list-style-type: circle; ">
        <li class="listitem">
         <p>
          If the account exists, dropping or renaming it causes
                stored objects to become orphaned. If you plan to drop
                or rename the account, consider first dropping its
                associated stored objects or redefining them to have a
                different definer.
         </p>
        </li>
        <li class="listitem">
         <p>
          If the account does not exist, creating it causes it to
                adopt currently orphaned stored objects. If you plan to
                create the account, consider whether the orphaned
                objects should be associated with it. If not, redefine
                them to have a different definer.
         </p>
        </li>
       </ul>
      </div>
      <p>
       To redefine an object with a different definer, you can use
       <a class="link" href="alter-event.html" title="15.1.3 ALTER EVENT Statement">
        <code class="literal">
         ALTER EVENT
        </code>
       </a>
       or
       <a class="link" href="alter-view.html" title="15.1.11 ALTER VIEW Statement">
        <code class="literal">
         ALTER VIEW
        </code>
       </a>
       to directly modify
            the
       <code class="literal">
        DEFINER
       </code>
       account of events and views.
            For stored procedures and functions and for triggers, you
            must drop the object and re-create it to assign a different
       <code class="literal">
        DEFINER
       </code>
       account
      </p>
     </li>
     <li class="listitem">
      <p>
       To identify which objects have a given
       <code class="literal">
        DEFINER
       </code>
       account, use these queries,
            substituting the account of interest for
       <code class="literal">
        <em class="replaceable">
         <code>
          user_name
         </code>
        </em>
        @
        <em class="replaceable">
         <code>
          host_name
         </code>
        </em>
       </code>
       :
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa53359433"><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> EVENT_SCHEMA<span class="token punctuation">,</span> EVENT_NAME <span class="token keyword">FROM</span> INFORMATION_SCHEMA<span class="token punctuation">.</span><span class="token keyword">EVENTS</span>
<span class="token keyword">WHERE</span> <span class="token keyword">DEFINER</span> <span class="token operator">=</span> <span class="token string">'<em class="replaceable">user_name</em>@<em class="replaceable">host_name</em>'</span><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> ROUTINE_SCHEMA<span class="token punctuation">,</span> ROUTINE_NAME<span class="token punctuation">,</span> ROUTINE_TYPE
<span class="token keyword">FROM</span> INFORMATION_SCHEMA<span class="token punctuation">.</span>ROUTINES
<span class="token keyword">WHERE</span> <span class="token keyword">DEFINER</span> <span class="token operator">=</span> <span class="token string">'<em class="replaceable">user_name</em>@<em class="replaceable">host_name</em>'</span><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> TRIGGER_SCHEMA<span class="token punctuation">,</span> TRIGGER_NAME <span class="token keyword">FROM</span> INFORMATION_SCHEMA<span class="token punctuation">.</span><span class="token keyword">TRIGGERS</span>
<span class="token keyword">WHERE</span> <span class="token keyword">DEFINER</span> <span class="token operator">=</span> <span class="token string">'<em class="replaceable">user_name</em>@<em class="replaceable">host_name</em>'</span><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> TABLE_SCHEMA<span class="token punctuation">,</span> <span class="token keyword">TABLE_NAME</span> <span class="token keyword">FROM</span> INFORMATION_SCHEMA<span class="token punctuation">.</span>VIEWS
<span class="token keyword">WHERE</span> <span class="token keyword">DEFINER</span> <span class="token operator">=</span> <span class="token string">'<em class="replaceable">user_name</em>@<em class="replaceable">host_name</em>'</span><span class="token punctuation">;</span></code></pre>
      </div>
      <p>
       For the
       <a class="link" href="information-schema-routines-table.html" title="28.3.30 The INFORMATION_SCHEMA ROUTINES Table">
        <code class="literal">
         ROUTINES
        </code>
       </a>
       table, the
            query includes the
       <code class="literal">
        ROUTINE_TYPE
       </code>
       column so
            that output rows distinguish whether the
       <code class="literal">
        DEFINER
       </code>
       is for a stored procedure or
            stored function.
      </p>
      <p>
       If the account you are searching for does not exist, any
            objects displayed by those queries are orphan objects.
      </p>
     </li>
    </ul>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h3 class="title">
       <a name="stored-objects-security-guidelines">
       </a>
       Risk-Minimization Guidelines
      </h3>
     </div>
    </div>
   </div>
   <p>
    To minimize the risk potential for stored object creation and
        use, follow these guidelines:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       Do not create orphan stored objects; that is, objects for
            which the
       <code class="literal">
        DEFINER
       </code>
       attribute names a
            nonexistent account. Do not cause stored objects to become
            orphaned by dropping or renaming an account named by the
       <code class="literal">
        DEFINER
       </code>
       attribute of any existing object.
      </p>
     </li>
     <li class="listitem">
      <p>
       For a stored routine or view, use
       <code class="literal">
        SQL SECURITY
            INVOKER
       </code>
       in the object definition when possible so
            that it can be used only by users with permissions
            appropriate for the operations performed by the object.
      </p>
     </li>
     <li class="listitem">
      <p>
       If you create definer-context stored objects while using an
            account that has the
       <a class="link" href="privileges-provided.html#priv_set-any-definer">
        <code class="literal">
         SET_ANY_DEFINER
        </code>
       </a>
       privilege,
            specify an explicit
       <code class="literal">
        DEFINER
       </code>
       attribute
            that names an account possessing only the privileges
            required for the operations performed by the object. Specify
            a highly privileged
       <code class="literal">
        DEFINER
       </code>
       account only
            when absolutely necessary.
      </p>
     </li>
     <li class="listitem">
      <p>
       Administrators can prevent users from creating stored
            objects that specify highly privileged
       <code class="literal">
        DEFINER
       </code>
       accounts by not granting them the
       <a class="link" href="privileges-provided.html#priv_set-any-definer">
        <code class="literal">
         SET_ANY_DEFINER
        </code>
       </a>
       privilege.
      </p>
     </li>
     <li class="listitem">
      <p>
       Definer-context objects should be written keeping in mind
            that they may be able to access data for which the invoking
            user has no privileges. In some cases, you can prevent
            references to these objects by not granting unauthorized
            users particular privileges:
      </p>
      <div class="itemizedlist">
       <ul class="itemizedlist" style="list-style-type: circle; ">
        <li class="listitem">
         <p>
          A stored routine cannot be referenced by a user who does
                not have the
          <a class="link" href="privileges-provided.html#priv_execute">
           <code class="literal">
            EXECUTE
           </code>
          </a>
          privilege for it.
         </p>
        </li>
        <li class="listitem">
         <p>
          A view cannot be referenced by a user who does not have
                the appropriate privilege for it
                (
          <a class="link" href="privileges-provided.html#priv_select">
           <code class="literal">
            SELECT
           </code>
          </a>
          to select from
                it,
          <a class="link" href="privileges-provided.html#priv_insert">
           <code class="literal">
            INSERT
           </code>
          </a>
          to insert into
                it, and so forth).
         </p>
        </li>
       </ul>
      </div>
      <p>
       However, no such control exists for triggers and events
            because they always execute in definer context. The server
            invokes these objects automatically as necessary, and users
            do not reference them directly:
      </p>
      <div class="itemizedlist">
       <ul class="itemizedlist" style="list-style-type: circle; ">
        <li class="listitem">
         <p>
          A trigger is activated by access to the table with which
                it is associated, even ordinary table accesses by users
                with no special privileges.
         </p>
        </li>
        <li class="listitem">
         <p>
          An event is executed by the server on a scheduled basis.
         </p>
        </li>
       </ul>
      </div>
      <p>
       In both cases, if the
       <code class="literal">
        DEFINER
       </code>
       account is
            highly privileged, the object may be able to perform
            sensitive or dangerous operations. This remains true if the
            privileges needed to create the object are revoked from the
            account of the user who created it. Administrators should be
            especially careful about granting users object-creation
            privileges.
      </p>
     </li>
     <li class="listitem">
      <p>
       By default, when a routine with the
       <code class="literal">
        SQL SECURITY
            DEFINER
       </code>
       characteristic is executed, MySQL Server
            does not set any active roles for the MySQL account named in
            the
       <code class="literal">
        DEFINER
       </code>
       clause, only the default
            roles. The exception is if the
       <a class="link" href="server-system-variables.html#sysvar_activate_all_roles_on_login">
        <code class="literal">
         activate_all_roles_on_login
        </code>
       </a>
       system variable is enabled, in which case MySQL Server sets
            all roles granted to the
       <code class="literal">
        DEFINER
       </code>
       user,
            including mandatory roles. Any privileges granted through
            roles are therefore not checked by default when the
       <a class="link" href="create-procedure.html" title="15.1.17 CREATE PROCEDURE and CREATE FUNCTION Statements">
        <code class="literal">
         CREATE PROCEDURE
        </code>
       </a>
       or
       <a class="link" href="create-function.html" title="15.1.14 CREATE FUNCTION Statement">
        <code class="literal">
         CREATE
            FUNCTION
        </code>
       </a>
       statement is issued. For stored programs,
            if execution should occur with roles different from the
            default, the program body can execute
       <a class="link" href="set-role.html" title="15.7.1.11 SET ROLE Statement">
        <code class="literal">
         SET ROLE
        </code>
       </a>
       to activate the
            required roles. This must be done with caution since the
            privileges assigned to roles can be changed.
      </p>
     </li>
    </ul>
   </div>
  </div>
 </div>
 <br/>
</div>
