usertype USER ROLE USER User userid eduroot assistant

支持访问权限管理的数据结构 user_type USER ROLE USER User user_id edu_root assistant teacher student A B C password 123456 1 2 333 creator_id null edu_root A B Privilege granter_id Object_id privilege_type grantee_ id grant_tag edu_root enroll INSERT student RELAY edu_root enroll SELECT student RELAY edu_root assistant ROLE A A student ROLE B

权限管理三原则： 1）某个对象，其创建者拥有对其访问的全部权限； 2）一个用户可将其拥有的权限授予给其它用户； 3）授权者可收回其授予出去的权限，收回具有连带性 User user_type USER ROLE USER user_id edu_root student A B password 123456 1 2 creator_id NULL edu_root A Privilege granter_id Object_id privilege_type grantee_ id grant_tag sys_root enroll ALL PRIVILEGES edu_root RELAY edu_root enroll INSERT student RELAY edu_root assistant SELECT PUBLIC NULL

创建用户和角色 CREATE USER jinming. Yang IDENTIFIED BY ‘ 123’； CREATE ROLE teacher； DROP USER jinming. Yang ； DROP ROLE teacher；

授权 GRANT SELECT ON dept TO PUBLIC; GRANT SELECT, UPDATE(salary) ON Emp TO Manager, Director; GRANT ALL PRIVILEGES ON Proj TO Director WITH GRANT OPTION;

数据操作的权限要求分析 下列数据操作要求有什么样的权限？ UPDATE Emp SET salary=salary*1. 1 WHERE eno IN ( SELECT eno FROM Works. On WHERE hours > 30); DELETE FROM dept WHERE dno NOT IN (SELECT dno FROM Works. On); INSERT INTO Works. On (eno, pno) VALUES ('E 5', 'P 5');

收回权限 REVOKE SELECT ON dept FROM PUBLIC; REVOKE ALL PRIVILEGES ON Emp FROM Joe;

通过视图来增强安全——将SELECT权限 细化到列 增强安全(Security) , 例如隐藏员 表中的birthdate和 salary: CREATE VIEW Emp. View AS SELECT eno, ename, title, supereno, dno FROM emp；

通过视图来增强安全——将权限细化到行 CREATE VIEW Account. Emp AS SELECT eno, name, title, superno, dno FROM emp WHERE dno = '410'; 授权： GRANT SELECT ON Account. Emp TO Account. Staff;

用触发器实现安全审计 CREATE TRIGGER cheating. Employee AFTER UPDATE OF salary ON Emp REFERENCING OLD ROW AS old NEW ROW AS new FOR EACH ROW WHEN (@new. salary > @old. salary*1. 1) INSERT INTO audit. Emp. Salary VALUES (get_user_id( ), get_connect_ip( ), timestamp(now()), @new. eno, @new. salary, @old. salary);

权限管理的简化 GRANT SELECT ON student TO stu_X; GRANT SELECT ON course TO stu_X; GRANT SELECT ON enroll TO stu_X; GRANT INSERT ON enroll TO stu_X; GRANT DELETE ON enroll TO stu_X; 学生用户 10, 000; 那么授权次数 50, 000。 l 用户角色（ROLE）概念的提出； l 能简化权限管理;

访问权限管理的技巧 l 不要给某个用户授权，而只给角色授权。然后再将角色授予给某 个用户； GRANT INSERT(s_no, c_no, semester ) ON enroll TO student; GRANT UPDATE(class_no, t_no) ON enroll TO administrator; GRANT UPDATE(grade) ON enroll TO teacher; GRANT SELECT ON enroll TO student, administrator, teacher; GRANT DELETE ON enroll TO administrator, student; l 授权时，不要以用户身份授权，而应以角色身份授权 SET CURRENT_ROLE AS assistant; GRANT student TO B GRANTED BY CURRENT_ROLE;

权限管理三原则： 1）某个对象，其创建者拥有对其访问的全部权限； 2）一个用户可将其拥有的权限授予给其它用户； 3）授权者可收回其授予出去的权限，收回具有连带性 User user_type USER ROLE USER user_id edu_root student A B password 123456 1 2 creator_id NULL edu_root A Privilege granter_id Object_id privilege_type grantee_ id grant_tag sys_root enroll ALL PRIVILEGES edu_root RELAY edu_root enroll INSERT student RELAY edu_root assistant SELECT PUBLIC NULL

利用系统漏洞进行攻击——通过数据来引 发程序语义畸变 用户名 ' OR '1' ='1 SQL注入攻击 HTML注入攻击 密码 登录 程序为：string sql. State= “SELECT password FROM user WHERE user_id ='” + user_name + “'; ” SELECT password FROM user WHERE user_id ='' OR'1' ='1';

SQL注入攻击 修改用户信息 用户名 邮箱 ' OR '1' ='1 909485030@qq. com 确定 程序为：string sql. State= “UPDATE user SET mail='”+ mailbox + “' WHERE user_id ='” + user_name + “'; ” UPDATE user SET mail='909485030@qq. com' WHERE user_id ='' OR'1' ='1';

HTML注入攻击—— XSS (Cross site Script ) 用户评论： </li></ol><script> alert(“XSS attack”); </script> <ol><li > 确定 HTML文档 <ol><li> </li></ol> <li>@comment </li> <script> alert(“XSS attack”); </script> </ol> <ol><li ></li> </ol>