<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="events-privileges">
      </a>
      27.4.6 The Event Scheduler and MySQL Privileges
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045082022400">
  </a>
  <p>
   To enable or disable the execution of scheduled events, it is
      necessary to set the value of the global
   <a class="link" href="server-system-variables.html#sysvar_event_scheduler">
    <code class="literal">
     event_scheduler
    </code>
   </a>
   system variable.
      This requires privileges sufficient to set global system
      variables. See
   <a class="xref" href="system-variable-privileges.html" title="7.1.9.1 System Variable Privileges">
    Section 7.1.9.1, “System Variable Privileges”
   </a>
   .
  </p>
  <p>
   The
   <a class="link" href="privileges-provided.html#priv_event">
    <code class="literal">
     EVENT
    </code>
   </a>
   privilege governs the
      creation, modification, and deletion of events. This privilege can
      be bestowed using
   <a class="link" href="grant.html" title="15.7.1.6 GRANT Statement">
    <code class="literal">
     GRANT
    </code>
   </a>
   . For
      example, this
   <a class="link" href="grant.html" title="15.7.1.6 GRANT Statement">
    <code class="literal">
     GRANT
    </code>
   </a>
   statement
      confers the
   <a class="link" href="privileges-provided.html#priv_event">
    <code class="literal">
     EVENT
    </code>
   </a>
   privilege for the
      schema named
   <code class="literal">
    myschema
   </code>
   on the user
   <code class="literal">
    jon@ghidora
   </code>
   :
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa76633857"><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">EVENT</span> <span class="token keyword">ON</span> myschema<span class="token punctuation">.</span><span class="token operator">*</span> <span class="token keyword">TO</span> jon<span class="token variable">@ghidora</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   (We assume that this user account already exists, and that we wish
      for it to remain unchanged otherwise.)
  </p>
  <p>
   To grant this same user the
   <a class="link" href="privileges-provided.html#priv_event">
    <code class="literal">
     EVENT
    </code>
   </a>
   privilege on all schemas, use the following statement:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa73446477"><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">EVENT</span> <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> jon<span class="token variable">@ghidora</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   The
   <a class="link" href="privileges-provided.html#priv_event">
    <code class="literal">
     EVENT
    </code>
   </a>
   privilege has global or
      schema-level scope. Therefore, trying to grant it on a single
      table results in an error as shown:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa21357040"><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">GRANT</span> <span class="token keyword">EVENT</span> <span class="token keyword">ON</span> myschema<span class="token punctuation">.</span>mytable <span class="token keyword">TO</span> jon<span class="token variable">@ghidora</span><span class="token punctuation">;</span>
<span class="token output">ERROR 1144 (42000)<span class="token punctuation">:</span> Illegal GRANT/REVOKE command; please
consult the manual to see which privileges can be used</span></code></pre>
  </div>
  <p>
   It is important to understand that an event is executed with the
      privileges of its definer, and that it cannot perform any actions
      for which its definer does not have the requisite privileges. For
      example, suppose that
   <code class="literal">
    jon@ghidora
   </code>
   has the
   <a class="link" href="privileges-provided.html#priv_event">
    <code class="literal">
     EVENT
    </code>
   </a>
   privilege for
   <code class="literal">
    myschema
   </code>
   . Suppose also that this user has the
   <a class="link" href="privileges-provided.html#priv_select">
    <code class="literal">
     SELECT
    </code>
   </a>
   privilege for
   <code class="literal">
    myschema
   </code>
   , but no other privileges for this
      schema. It is possible for
   <code class="literal">
    jon@ghidora
   </code>
   to
      create a new event such as this one:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa49575269"><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">EVENT</span> e_store_ts
    <span class="token keyword">ON</span> <span class="token keyword">SCHEDULE</span>
      <span class="token keyword">EVERY</span> <span class="token number">10</span> <span class="token keyword">SECOND</span>
    <span class="token keyword">DO</span>
      <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> myschema<span class="token punctuation">.</span>mytable <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token function">UNIX_TIMESTAMP</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   The user waits for a minute or so, and then performs a
   <code class="literal">
    SELECT * FROM mytable;
   </code>
   query, expecting to see
      several new rows in the table. Instead, the table is empty. Since
      the user does not have the
   <a class="link" href="privileges-provided.html#priv_insert">
    <code class="literal">
     INSERT
    </code>
   </a>
   privilege for the table in question, the event has no effect.
  </p>
  <p>
   If you inspect the MySQL error log
      (
   <code class="filename">
    <em class="replaceable">
     <code>
      hostname
     </code>
    </em>
    .err
   </code>
   ),
      you can see that the event is executing, but the action it is
      attempting to perform fails:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-none"><div class="docs-select-all right" id="sa22181636"><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-none">2013-09-24T12:41:31.261992Z 25 [ERROR] Event Scheduler:
[jon@ghidora][cookbook.e_store_ts] INSERT command denied to user
'jon'@'ghidora' for table 'mytable'
2013-09-24T12:41:31.262022Z 25 [Note] Event Scheduler:
[jon@ghidora].[myschema.e_store_ts] event execution failed.
2013-09-24T12:41:41.271796Z 26 [ERROR] Event Scheduler:
[jon@ghidora][cookbook.e_store_ts] INSERT command denied to user
'jon'@'ghidora' for table 'mytable'
2013-09-24T12:41:41.272761Z 26 [Note] Event Scheduler:
[jon@ghidora].[myschema.e_store_ts] event execution failed.</code></pre>
  </div>
  <p>
   Since this user very likely does not have access to the error log,
      it is possible to verify whether the event's action statement is
      valid by executing it directly:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa14230531"><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">INSERT</span> <span class="token keyword">INTO</span> myschema<span class="token punctuation">.</span>mytable <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token function">UNIX_TIMESTAMP</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">ERROR 1142 (42000)<span class="token punctuation">:</span> INSERT command denied to user
'jon'@'ghidora' for table 'mytable'</span></code></pre>
  </div>
  <a class="indexterm" name="idm46045081987888">
  </a>
  <p>
   Inspection of the Information Schema
   <a class="link" href="information-schema-events-table.html" title="28.3.14 The INFORMATION_SCHEMA EVENTS Table">
    <code class="literal">
     EVENTS
    </code>
   </a>
   table shows that
   <code class="literal">
    e_store_ts
   </code>
   exists and is enabled, but its
   <code class="literal">
    LAST_EXECUTED
   </code>
   column is
   <code class="literal">
    NULL
   </code>
   :
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa56091578"><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> <span class="token operator">*</span> <span class="token keyword">FROM</span> INFORMATION_SCHEMA<span class="token punctuation">.</span><span class="token keyword">EVENTS</span>
     <span class="token operator">&gt;</span>     <span class="token keyword">WHERE</span> EVENT_NAME<span class="token operator">=</span><span class="token string">'e_store_ts'</span>
     <span class="token operator">&gt;</span>     <span class="token operator">AND</span> EVENT_SCHEMA<span class="token operator">=</span><span class="token string">'myschema'</span>\G
<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> 1. row <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>
   EVENT_CATALOG<span class="token punctuation">:</span> NULL
    EVENT_SCHEMA<span class="token punctuation">:</span> myschema
      EVENT_NAME<span class="token punctuation">:</span> e_store_ts
         DEFINER<span class="token punctuation">:</span> jon@ghidora
      EVENT_BODY<span class="token punctuation">:</span> SQL
EVENT_DEFINITION<span class="token punctuation">:</span> INSERT INTO myschema.mytable VALUES (UNIX_TIMESTAMP())
      EVENT_TYPE<span class="token punctuation">:</span> RECURRING
      EXECUTE_AT<span class="token punctuation">:</span> NULL
  INTERVAL_VALUE<span class="token punctuation">:</span> 5
  INTERVAL_FIELD<span class="token punctuation">:</span> SECOND
        SQL_MODE<span class="token punctuation">:</span> NULL
          STARTS<span class="token punctuation">:</span> 0000-00-00 00<span class="token punctuation">:</span>00<span class="token punctuation">:</span>00
            ENDS<span class="token punctuation">:</span> 0000-00-00 00<span class="token punctuation">:</span>00<span class="token punctuation">:</span>00
          STATUS<span class="token punctuation">:</span> ENABLED
   ON_COMPLETION<span class="token punctuation">:</span> NOT PRESERVE
         CREATED<span class="token punctuation">:</span> 2006-02-09 22<span class="token punctuation">:</span>36<span class="token punctuation">:</span>06
    LAST_ALTERED<span class="token punctuation">:</span> 2006-02-09 22<span class="token punctuation">:</span>36<span class="token punctuation">:</span>06
   LAST_EXECUTED<span class="token punctuation">:</span> NULL
   EVENT_COMMENT<span class="token punctuation">:</span>
</span><span class="token output">1 row in set (0.00 sec)</span></code></pre>
  </div>
  <p>
   To rescind the
   <a class="link" href="privileges-provided.html#priv_event">
    <code class="literal">
     EVENT
    </code>
   </a>
   privilege, use
      the
   <a class="link" href="revoke.html" title="15.7.1.8 REVOKE Statement">
    <code class="literal">
     REVOKE
    </code>
   </a>
   statement. In this
      example, the
   <a class="link" href="privileges-provided.html#priv_event">
    <code class="literal">
     EVENT
    </code>
   </a>
   privilege on the
      schema
   <code class="literal">
    myschema
   </code>
   is removed from the
   <code class="literal">
    jon@ghidora
   </code>
   user account:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa90353445"><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">REVOKE</span> <span class="token keyword">EVENT</span> <span class="token keyword">ON</span> myschema<span class="token punctuation">.</span><span class="token operator">*</span> <span class="token keyword">FROM</span> jon<span class="token variable">@ghidora</span><span class="token punctuation">;</span></code></pre>
  </div>
  <div class="important" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Important
   </div>
   <p>
    Revoking the
    <a class="link" href="privileges-provided.html#priv_event">
     <code class="literal">
      EVENT
     </code>
    </a>
    privilege from
        a user does not delete or disable any events that may have been
        created by that user.
   </p>
   <p>
    An event is not migrated or dropped as a result of renaming or
        dropping the user who created it.
   </p>
  </div>
  <p>
   Suppose that the user
   <code class="literal">
    jon@ghidora
   </code>
   has been
      granted the
   <a class="link" href="privileges-provided.html#priv_event">
    <code class="literal">
     EVENT
    </code>
   </a>
   and
   <a class="link" href="privileges-provided.html#priv_insert">
    <code class="literal">
     INSERT
    </code>
   </a>
   privileges on the
   <code class="literal">
    myschema
   </code>
   schema. This user then creates the
      following event:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa31259993"><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">EVENT</span> e_insert
    <span class="token keyword">ON</span> <span class="token keyword">SCHEDULE</span>
      <span class="token keyword">EVERY</span> <span class="token number">7</span> <span class="token keyword">SECOND</span>
    <span class="token keyword">DO</span>
      <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> myschema<span class="token punctuation">.</span>mytable<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   After this event has been created,
   <code class="literal">
    root
   </code>
   revokes
      the
   <a class="link" href="privileges-provided.html#priv_event">
    <code class="literal">
     EVENT
    </code>
   </a>
   privilege for
   <code class="literal">
    jon@ghidora
   </code>
   . However,
   <code class="literal">
    e_insert
   </code>
   continues to execute, inserting a new
      row into
   <code class="literal">
    mytable
   </code>
   each seven seconds. The same
      would be true if
   <code class="literal">
    root
   </code>
   had issued either of
      these statements:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <code class="literal">
       DROP USER jon@ghidora;
      </code>
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       RENAME USER jon@ghidora TO
          someotherguy@ghidora;
      </code>
     </p>
    </li>
   </ul>
  </div>
  <p>
   You can verify that this is true by examining the Information
      Schema
   <a class="link" href="information-schema-events-table.html" title="28.3.14 The INFORMATION_SCHEMA EVENTS Table">
    <code class="literal">
     EVENTS
    </code>
   </a>
   table before and after
      issuing a
   <a class="link" href="drop-user.html" title="15.7.1.5 DROP USER Statement">
    <code class="literal">
     DROP USER
    </code>
   </a>
   or
   <a class="link" href="rename-user.html" title="15.7.1.7 RENAME USER Statement">
    <code class="literal">
     RENAME USER
    </code>
   </a>
   statement.
  </p>
  <p>
   Event definitions are stored in the data dictionary. To drop an
      event created by another user account, you must be the MySQL
   <code class="literal">
    root
   </code>
   user or another user with the necessary
      privileges.
  </p>
  <p>
   Users'
   <a class="link" href="privileges-provided.html#priv_event">
    <code class="literal">
     EVENT
    </code>
   </a>
   privileges are stored
      in the
   <code class="literal">
    Event_priv
   </code>
   columns of the
   <code class="literal">
    mysql.user
   </code>
   and
   <code class="literal">
    mysql.db
   </code>
   tables. In both cases, this column holds one of the values
      '
   <code class="literal">
    Y
   </code>
   ' or '
   <code class="literal">
    N
   </code>
   '.
      '
   <code class="literal">
    N
   </code>
   ' is the default.
   <code class="literal">
    mysql.user.Event_priv
   </code>
   is set to
      '
   <code class="literal">
    Y
   </code>
   ' for a given user only if that user has the
      global
   <a class="link" href="privileges-provided.html#priv_event">
    <code class="literal">
     EVENT
    </code>
   </a>
   privilege (that is, if
      the privilege was bestowed using
   <code class="literal">
    GRANT EVENT ON
      *.*
   </code>
   ). For a schema-level
   <a class="link" href="privileges-provided.html#priv_event">
    <code class="literal">
     EVENT
    </code>
   </a>
   privilege,
   <a class="link" href="grant.html" title="15.7.1.6 GRANT Statement">
    <code class="literal">
     GRANT
    </code>
   </a>
   creates a row in
   <code class="literal">
    mysql.db
   </code>
   and sets that row's
   <code class="literal">
    Db
   </code>
   column to the name of the schema, the
   <code class="literal">
    User
   </code>
   column to the name of the user, and the
   <code class="literal">
    Event_priv
   </code>
   column to '
   <code class="literal">
    Y
   </code>
   '.
      There should never be any need to manipulate these tables
      directly, since the
   <a class="link" href="grant.html" title="15.7.1.6 GRANT Statement">
    <code class="literal">
     GRANT
      EVENT
    </code>
   </a>
   and
   <code class="literal">
    REVOKE EVENT
   </code>
   statements
      perform the required operations on them.
  </p>
  <a class="indexterm" name="idm46045081932128">
  </a>
  <p>
   Five status variables provide counts of event-related operations
      (but
   <span class="emphasis">
    <em>
     not
    </em>
   </span>
   of statements executed by events;
      see
   <a class="xref" href="stored-program-restrictions.html" title="27.8 Restrictions on Stored Programs">
    Section 27.8, “Restrictions on Stored Programs”
   </a>
   ). These are:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <code class="literal">
       Com_create_event
      </code>
      : The number of
      <a class="link" href="create-event.html" title="15.1.13 CREATE EVENT Statement">
       <code class="literal">
        CREATE EVENT
       </code>
      </a>
      statements
          executed since the last server restart.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       Com_alter_event
      </code>
      : The number of
      <a class="link" href="alter-event.html" title="15.1.3 ALTER EVENT Statement">
       <code class="literal">
        ALTER EVENT
       </code>
      </a>
      statements executed
          since the last server restart.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       Com_drop_event
      </code>
      : The number of
      <a class="link" href="drop-event.html" title="15.1.25 DROP EVENT Statement">
       <code class="literal">
        DROP EVENT
       </code>
      </a>
      statements executed
          since the last server restart.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       Com_show_create_event
      </code>
      : The number of
      <a class="link" href="show-create-event.html" title="15.7.7.8 SHOW CREATE EVENT Statement">
       <code class="literal">
        SHOW CREATE EVENT
       </code>
      </a>
      statements
          executed since the last server restart.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       Com_show_events
      </code>
      : The number of
      <a class="link" href="show-events.html" title="15.7.7.19 SHOW EVENTS Statement">
       <code class="literal">
        SHOW EVENTS
       </code>
      </a>
      statements executed
          since the last server restart.
     </p>
    </li>
   </ul>
  </div>
  <p>
   You can view current values for all of these at one time by
      running the statement
   <code class="literal">
    SHOW STATUS LIKE
      '%event%';
   </code>
   .
  </p>
 </div>
 <br/>
</div>
