Rechercher dans le manuel MySQL
24.4.6 The Event Scheduler and MySQL Privileges
To enable or disable the execution of scheduled events, it is
necessary to set the value of the global
event_scheduler
system variable.
This requires privileges sufficient to set global system
variables. See Section 5.1.9.1, “System Variable Privileges”.
The EVENT
privilege governs the
creation, modification, and deletion of events. This privilege can
be bestowed using GRANT
. For
example, this GRANT
statement
confers the EVENT
privilege for the
schema named myschema
on the user
jon@ghidora
:
(We assume that this user account already exists, and that we wish for it to remain unchanged otherwise.)
To grant this same user the EVENT
privilege on all schemas, use the following statement:
The EVENT
privilege has global or
schema-level scope. Therefore, trying to grant it on a single
table results in an error as shown:
- consult the manual to see which privileges can be used
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 jon@ghidora
has the
EVENT
privilege for
myschema
. Suppose also that this user has the
SELECT
privilege for
myschema
, but no other privileges for this
schema. It is possible for jon@ghidora
to
create a new event such as this one:
The user waits for a minute or so, and then performs a
SELECT * FROM mytable;
query, expecting to see
several new rows in the table. Instead, the table is empty. Since
the user does not have the INSERT
privilege for the table in question, the event has no effect.
If you inspect the MySQL error log
(
),
you can see that the event is executing, but the action it is
attempting to perform fails:
hostname
.err
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.
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:
Inspection of the
INFORMATION_SCHEMA.EVENTS
table shows
that e_store_ts
exists and is enabled, but its
LAST_EXECUTED
column is
NULL
:
- *************************** 1. row ***************************
- EVENT_CATALOG: NULL
- EVENT_SCHEMA: myschema
- EVENT_NAME: e_store_ts
- DEFINER: jon@ghidora
- EVENT_BODY: SQL
- EVENT_TYPE: RECURRING
- EXECUTE_AT: NULL
- INTERVAL_VALUE: 5
- INTERVAL_FIELD: SECOND
- SQL_MODE: NULL
- STARTS: 0000-00-00 00:00:00
- ENDS: 0000-00-00 00:00:00
- STATUS: ENABLED
- ON_COMPLETION: NOT PRESERVE
- CREATED: 2006-02-09 22:36:06
- LAST_ALTERED: 2006-02-09 22:36:06
- LAST_EXECUTED: NULL
- EVENT_COMMENT:
To rescind the EVENT
privilege, use
the REVOKE
statement. In this
example, the EVENT
privilege on the
schema myschema
is removed from the
jon@ghidora
user account:
Revoking the EVENT
privilege from
a user does not delete or disable any events that may have been
created by that user.
An event is not migrated or dropped as a result of renaming or dropping the user who created it.
Suppose that the user jon@ghidora
has been
granted the EVENT
and
INSERT
privileges on the
myschema
schema. This user then creates the
following event:
After this event has been created, root
revokes
the EVENT
privilege for
jon@ghidora
. However,
e_insert
continues to execute, inserting a new
row into mytable
each seven seconds. The same
would be true if root
had issued either of
these statements:
DROP USER jon@ghidora;
RENAME USER jon@ghidora TO someotherguy@ghidora;
You can verify that this is true by examining the
mysql.event
table (discussed later in this
section) or the
INFORMATION_SCHEMA.EVENTS
table (see
Section 25.10, “The INFORMATION_SCHEMA EVENTS Table”) before and after issuing a
DROP USER
or
RENAME USER
statement.
Event definitions are stored in the mysql.event
table. To drop an event created by another user account, the MySQL
root
user (or another user with the necessary
privileges) can delete rows from this table. For example, to
remove the event e_insert
shown previously,
root
can use the following statement:
It is very important to match the event name and database schema
name when deleting rows from the mysql.event
table. This is because different events of the same name can exist
in different schemas.
Users' EVENT
privileges are stored
in the Event_priv
columns of the
mysql.user
and mysql.db
tables. In both cases, this column holds one of the values
'Y
' or 'N
'.
'N
' is the default.
mysql.user.Event_priv
is set to
'Y
' for a given user only if that user has the
global EVENT
privilege (that is, if
the privilege was bestowed using GRANT EVENT ON
*.*
). For a schema-level
EVENT
privilege,
GRANT
creates a row in
mysql.db
and sets that row's
Db
column to the name of the schema, the
User
column to the name of the user, and the
Event_priv
column to 'Y
'.
There should never be any need to manipulate these tables
directly, since the GRANT
EVENT
and REVOKE EVENT
statements
perform the required operations on them.
Five status variables provide counts of event-related operations (but not of statements executed by events; see Section C.1, “Restrictions on Stored Programs”). These are:
Com_create_event
: The number ofCREATE EVENT
statements executed since the last server restart.Com_alter_event
: The number ofALTER EVENT
statements executed since the last server restart.Com_drop_event
: The number ofDROP EVENT
statements executed since the last server restart.Com_show_create_event
: The number ofSHOW CREATE EVENT
statements executed since the last server restart.Com_show_events
: The number ofSHOW EVENTS
statements executed since the last server restart.
You can view current values for all of these at one time by
running the statement SHOW STATUS LIKE
'%event%';
.
Deutsche Übersetzung
Sie haben gebeten, diese Seite auf Deutsch zu besuchen. Momentan ist nur die Oberfläche übersetzt, aber noch nicht der gesamte Inhalt.Wenn Sie mir bei Übersetzungen helfen wollen, ist Ihr Beitrag willkommen. Alles, was Sie tun müssen, ist, sich auf der Website zu registrieren und mir eine Nachricht zu schicken, in der Sie gebeten werden, Sie der Gruppe der Übersetzer hinzuzufügen, die Ihnen die Möglichkeit gibt, die gewünschten Seiten zu übersetzen. Ein Link am Ende jeder übersetzten Seite zeigt an, dass Sie der Übersetzer sind und einen Link zu Ihrem Profil haben.
Vielen Dank im Voraus.
Dokument erstellt 26/06/2006, zuletzt geändert 26/10/2018
Quelle des gedruckten Dokuments:https://www.gaudry.be/de/mysql-rf-events-privileges.html
Die Infobro ist eine persönliche Seite, deren Inhalt in meiner alleinigen Verantwortung liegt. Der Text ist unter der CreativeCommons-Lizenz (BY-NC-SA) verfügbar. Weitere Informationen auf die Nutzungsbedingungen und dem Autor.
Referenzen
Diese Verweise und Links verweisen auf Dokumente, die während des Schreibens dieser Seite konsultiert wurden, oder die zusätzliche Informationen liefern können, aber die Autoren dieser Quellen können nicht für den Inhalt dieser Seite verantwortlich gemacht werden.
Der Autor Diese Website ist allein dafür verantwortlich, wie die verschiedenen Konzepte und Freiheiten, die mit den Nachschlagewerken gemacht werden, hier dargestellt werden. Denken Sie daran, dass Sie mehrere Quellinformationen austauschen müssen, um das Risiko von Fehlern zu reduzieren.