数据库系统authorization.ppt

Chapter 10 Advanced Topics in Relational Database 第10章 关系数据库高级议题 10.1 Security and User Authorization in SQL A file system identifies certain privileges on the objects (files) it manages. Typically read, write, execute. A file system identifies certain participants to whom privileges may be granted. Typically the owner, a group, all users. SQL identifies a more detailed set of privileges on objects (relations) than the typical file system. 10.1 Security and User Authorization in SQL Nine privileges in all, some of which can be restricted to one column of one relation. SELECT = right to query the relation. INSERT = right to insert tuples. May apply to only one attribute. DELETE = right to delete tuples. UPDATE = right to update tuples. May apply to only one attribute. REFERENCES = right to refer to the relation in an integrity constraint. USAGE = right to use some element in one’s own declaration. TRIGGER = right to define triggers on the relation. EXECUTE = right to execute a piece of code. UNDER = right to create subtypes of a given type. 10.1 Security and User Authorization in SQL For the statement below: INSERT INTO Customer(custid) SELECT custid FROM Salesorder WHERE NOT EXISTS (SELECT * FROM Customer WHERE custid=Salesorder.custid); We require privileges SELECT on Customer and Salesorder, and INSERT on Customer or Customer.custid. 10.1 Security and User Authorization in SQL The objects on which privileges exist include stored tables and views. Other privileges are the right to create objects of a type, e.g., triggers. Views form an important tool for access control. 10.1 Security and User Authorization in SQL We might not want to give the SELECT privilege on Emps(name, addr, salary). But it is safer to give SELECT on: CREATE VIEW SafeEmps AS SELECT name, addr FROM Emps; Queries on SafeEmps do not require SELECT on Emps, just on SafeEmps. 10.1 Security and User Authorization in SQL DB