MySQL存储程序权限控制

MySQL存储程序权限控制

MySQL5.0引入了一些管理存储程序的权限:
[code]
CREATE ROUTINE: 允许用户创建新的存储程序
ALTER ROUTINE: 允许用户修改security mode/SQL mode/comment
EXECUTE: 允许用户只需存储程序
[/code]

给用户赋予权限:
[code]
GRANT CREATE ROUTINE ON mydatabase.* TO sp_creator;
GRANT ALTER ROUTINE ON mydatabase.* TO sp_creator;
GRANT EXECUTE ON mydatabase.* TO sp_creator;
GRANT EXECUTE ON PROCEDURE mydatabase.test1 TO sp_creator;
[/code]

存储程序的执行安全模式:
[code]
SQL SECURITY DEFINER: 默认模式，存储程序的定义者才能执行
SQL SECURITY INVOKER: 调用者都可以执行，但是权限细粒度的控制转交到存储程序里的SQL语句的执行权限
[/code]

存储程序里封装访问权限的例子:
[code]
CREATE PROCEDURE sp_employee_list(in_department_id DECIMAL(8,0)
SQL SECURITY DEFINER READS SQL DATA
BEGIN
DECLARE l_user_name VARCHAR(30);
DECLARE l_not_found INT DEFAULT 0;
DECLARE l_department_name VARCHAR(30);
DECLARE l_manager_id INT;

DECLARE user_csr CURSOR FOR
SELECT d.department_name, e.manager_id
FROM departments d JOIN employees e USING(department_id)
WHERE db_user=l_user_name;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET l_not_foun=1;

/* Strip out the host from the user name */
SELECT SUBSTR(USER(),1,INSTR(USER(), '@')-1)
INTO l_user_name;

OPEN user_csr;
FETCH user_csr INTO l_department_name, l_manager_id;
CLOSE user_csr;

IF l_department_name='PAYROLL' OR l_manager_id IN (0, 1) THEN
SELECT surname, firstname, salary
FROM employees
WHERE department_id=in_department_id
ORDER BY employee_id;
ELSE
/* Not authorized to see salary */
SELECT surname, firstname, 'XXXXXXXX' AS salary
FROM employees
WHERE department_id=in_department_id
ORDER BY employee_id;
END IF;

END;
[/code]