Permissions Required for Stored Programs

MySQL 5.0 introduced a few new privileges to manage stored programs. These privileges are:

 

CREATE ROUTINE

Allows a user to create new stored programs.

 

ALTER ROUTINE

Allows a user to alter the security mode, SQL mode, or comment for an existing stored program.

 

EXECUTE

Allows a user to execute a stored procedure or function.

With these distinct privileges available, we can very granularly decide what we want to allow individual developers to be able to do (as in "Sam can run program X, but not make any changes to it.").

18.1.1. Granting Privileges to Create a Stored Program

To give a user permission to create a stored procedure, function, or trigger, grant the CREATE ROUTINE privilege to that user using the GRANT statement. We can do this for a specific database or for all databases on the server. For example, the following GRANT statement gives the user sp_creator permission to create stored programs within the database mydatabase:

 GRANT CREATE ROUTINE ON mydatabase.* TO sp_creator;

 

18.1.2. Granting Privileges to Modify a Stored Program

The ALTER ROUTINE privilege gives a user permission to change the security mode, SQL mode, or comment for a stored procedure or function. However, this privilege does not allow us to change the actual program code of a procedure. To change the program code, we must DROP and then CREATE a new program. In the following example, we change the security mode, sql_mode setting, and comment for a procedure:

 ALTER PROCEDURE simple_stored_proc
 SQL SECURITY INVOKER
 READS SQL DATA
 COMMENT 'A simple stored procedure';

 

18.1.3. Granting Privileges to Execute a Stored Program

The EXECUTE privilege gives a user permission to execute a stored procedure or function. (For triggers, see Chapter 11.) EXECUTE privileges should be granted selectively, especially if the program is created with the "definer rights" security setting (see the section "The SQL SECURITY Clause" later in this chapter). The syntax for this form of the GRANT statement is:

 GRANT EXECUTE [ON {PROCEDURE|FUNCTION}] database.program_name TO user

You can omit the ON PROCEDURE or ON FUNCTION clause if you are performing a wildcard grant, as in the following example:

 GRANT EXECUTE ON mydatabase.* TO sp_creator;

If you are granting access to a specific program, you must specify ON PROCEDURE or ON FUNCTION explicitly; it is possible for a stored procedure and a stored function to have the same name, and it is unacceptable to issue an ambiguous security command. To grant the EXECUTE privilege on the procedure mydatabase.test1, issue the following statement:

 GRANT EXECUTE ON PROCEDURE mydatabase.test1 TO sp_creator;


Part I: Stored Programming Fundamentals

Introduction to MySQL Stored Programs

MySQL Stored Programming Tutorial

Language Fundamentals

Blocks, Conditional Statements, and Iterative Programming

Using SQL in Stored Programming

Error Handling

Part II: Stored Program Construction

Creating and Maintaining Stored Programs

Transaction Management

MySQL Built-in Functions

Stored Functions

Triggers

Part III: Using MySQL Stored Programs in Applications

Using MySQL Stored Programs in Applications

Using MySQL Stored Programs with PHP

Using MySQL Stored Programs with Java

Using MySQL Stored Programs with Perl

Using MySQL Stored Programs with Python

Using MySQL Stored Programs with .NET

Part IV: Optimizing Stored Programs

Stored Program Security

Tuning Stored Programs and Their SQL

Basic SQL Tuning

Advanced SQL Tuning

Optimizing Stored Program Code

Best Practices in MySQL Stored Program Development

show all menu



MySQL Stored Procedure Programming
MySQL Stored Procedure Programming
ISBN: 0596100892
EAN: 2147483647
Year: 2004
Pages: 208
Similar book on Amazon

Flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net