2008-05-28

MySQL存储程序权限控制

关键字: 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;
评论
发表评论

您还没有登录，请登录后发表评论

hideto
搜索本博客
我的相册
A6bdc31c-c66e-468e-961e-9cc721e82adc-thumb
screenshot
共 1 张
存档
最新评论