Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions

  




 

 

21.4. 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 variable. This requires the SUPER privilege.

MySQL 5.1.6 introduces a privilege governing the creation, modification, and deletion of events, the EVENT privilege. This privilege can be bestowed using GRANT. For example, this GRANT statement confers the EVENT privilege for the schema named myschema on the user [email protected]:

GRANT EVENT ON myschema.* TO [email protected];

(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 would require the following statement:

GRANT EVENT ON *.* TO [email protected];

The EVENT privilege has schema-level scope. Therefore, trying to grant it on a single table results in an error as shown:

mysql> GRANT EVENT ON myschema.mytable TO [email protected];
ERROR 1144 (42000): Illegal GRANT/REVOKE command; please
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 [email protected] 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 [email protected] to create a new event such as this one:

CREATE EVENT e_store_ts
    ON SCHEDULE EVERY 10 SECOND
    DO INSERT INTO myschema.mytable VALUES (UNIX_TIMESTAMP());

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, he finds that the table is empty. Since he does not have the INSERT privilege for the table in question, the event has no effect.

If you inspect the MySQL error log (hostname.err), you can see that the event is executing, but the action it is attempting to perform fails, as indicated by RetCode=0:

060209 22:39:44 [Note]     EVEX EXECUTING event newdb.e [EXPR:10]
060209 22:39:44 [Note]     EVEX EXECUTED event newdb.e  [EXPR:10]. RetCode=0
060209 22:39:54 [Note]     EVEX EXECUTING event newdb.e [EXPR:10]
060209 22:39:54 [Note]     EVEX EXECUTED event newdb.e  [EXPR:10]. RetCode=0
060209 22:40:04 [Note]     EVEX EXECUTING event newdb.e [EXPR:10]
060209 22:40:04 [Note]     EVEX EXECUTED event newdb.e  [EXPR:10]. RetCode=0

Since this user very likely does not have access to the error log, he can verify whether the event's action statement is valid by running it himself:

mysql> INSERT INTO myschema.mytable VALUES (UNIX_TIMESTAMP());
ERROR 1142 (42000): INSERT command denied to user 
'jon'@'ghidora' for table 'mytable'

Inspection of the INFORMATION_SCHEMA.EVENTS table shows that e_store_ts exists and is enabled, but its LAST_EXECUTED column is NULL:

mysql> SELECT * FROM INFORMATION_SCHEMA.EVENTS
     >     WHERE EVENT_NAME='e_store_ts'
     >     AND EVENT_SCHEMA='myschema'\G
*************************** 1. row ***************************
 EVENT_CATALOG: NULL
  EVENT_SCHEMA: myschema
    EVENT_NAME: e_store_ts
       DEFINER: [email protected]
    EVENT_BODY: INSERT INTO myschema.mytable VALUES (UNIX_TIMESTAMP())
    EVENT_TYPE: RECURRING
    EXECUTE_AT: NULL
INTERVAL_VALUE: 5
INTERVAL_FIELD: INTERVAL_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:
1 row in set (0.00 sec)

To rescind the EVENT privilege, use the REVOKE statement. In this example, the EVENT privilege on the schema myschema is removed from the [email protected] user account:

REVOKE EVENT ON myschema.* FROM [email protected];

Important: Revoking the EVENT privilege from a user account does not delete or disable any events that may have been created by that account.

For example, suppose that that user [email protected] has been granted the EVENT and INSERT privileges on the myschema schema. This user then creates the following event:

CREATE EVENT e_insert
    ON SCHEDULE EVERY 7 SECOND
    SO INSERT INTO myschema.mytable;

After this event has been created, root revokes the EVENT privilege for [email protected]. However, e_insert continues to execute, inserting a new row into mytable each seven seconds.

Event definitions are stored in the mysql.event table, which was added in MySQL 5.1.6. 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:

DELETE FROM mysql.event
    WHERE db = 'myschema' 
      AND definer = '[email protected]' 
      AND name = 'e_insert';

It is very important to match the event name, schema name, and user account when deleting rows from the mysql.event table. This is because:

  • The same user can create different events of the same name in different schemas.

  • Different users can create different events having the same name in the same schema.

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 statement perform the required operations on them.

MySQL 5.1.6 introduces five status variables providing counts of event-related operations (but not of statements executed by events — see Section 21.5, “Event Scheduler Limitations and Restrictions”). These are:

  • Com_create_event: The number of CREATE EVENT statements executed since the last server restart.

  • Com_alter_event: The number of ALTER EVENT statements executed since the last server restart.

  • Com_drop_event: The number of DROP EVENT statements executed since the last server restart.

  • Com_show_create_event: The number of SHOW CREATE EVENT statements executed since the last server restart.

  • Com_show_events: The number of SHOW 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%';.


 
 
  Published under the terms of the GNU General Public License Design by Interspire