MySQL存储程序权限控制

MySQL存储程序权限控制

MySQL5.0引入了一些管理存储程序的权限:

CREATE ROUTINE: 允许用户创建新的存储程序
ALTER ROUTINE: 允许用户修改security mode/SQL mode/comment
EXECUTE: 允许用户只需存储程序

给用户赋予权限:

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;

存储程序的执行安全模式:

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

存储程序里封装访问权限的例子:

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;