Access Control & Views 控制访问和视图

The Importance of Views & Privileges • In large organisations, DBMSs are used by a range of staff: — directors, managers, analysts, engineers, personnel, secretarial, etc. • Consequently, access to data in different tables may need to be controlled to: — provide access to authorised users — restrict access to unauthorised users — enforce business rules or government regulations • Views & privileges can help implement access control...需要控制对不同表数据的访问需要 为授权用户提供访问权限 限制未授权用户访问权限 执行业务规则和政府法规 因此视图和权限便于控制访问

SQL’s Access Control Model • Access Control in SQL is similar to multi-user operating systems (e.g., Unix, Windows, ...) • A user supplies an Authorisation Id and password to the DBMS • The DBMS opens a session for the user • The DBMS runs SQL statements on behalf of the user • The user becomes the owner of any objects they create • By default, only the owner may access their objects • The owner may grant and revoke access privileges to other usersSQL的控制访问模型 用户向DBMS提供授权ID和密码 DBMS为用户打开绘画 DBMS代表用户执行SQL语句 用户成为创建的任何对象的所有者 在默认情况下只有所有者可以访问其对象 所有者可以授权和撤销其他用户访问权限

Granting Privileges授权

• where (typically): — ObjectName is a table — PrivilegeList may be a combination of: ◦ SELECT, INSERT, UPDATE, DELETE (can specify column names) ◦ REFERENCES (column names referenced by integrity constraints) ◦ USAGE (use of domain definitions) ObjectName是一个表 PrivilegeList是以下内容组合 ◦ SELECT、INSERT、UPDATE、DELETE（可指定列名） ◦ REFERENCES（通过完整性约束引用列名） ◦ USAGE（使用域定义）

What are Views? • A view is a virtual table, constructed from base tables • Only the definition of a view is stored permanently • A view is realised dynamically when it is first referenced • Views are manipulated like other DBMS objects:视图是一个虚拟表由基本表构成 只有视图的定义是永久存储的 首次引用视图时，视图动态实现 视图操作域DBMS对象一样

Creating Views – Horizontal Views创建水平视图Creating Views – Vertical Views创建垂直视图General Syntax for Creating Views

• The SubSelect clause is called the defining query • To create a view, a user must have SELECT privilege on the base tables • Once created, views often behave like ordinary base tables... • Views can be used in SELECT or JOIN clauses • Views can be updated (with some restrictions)SubSelect子句-定义查询 需要基础表的SELECT权限 视图和基础表一样运行 视图在SELECT或JOIN子句中使用 视图受一定限制更新

Final Example Grouped & Joined Views • Views can be used to help simplify complex queries • Example: create a view showing the number of properties managed by each member of staff and the branches they work at:视图可以简化复杂查询

• Can now query StaffProperties as if its a base table

How Are Views Implemented? • Most DBMSs implement views using view resolution: — SQL re-writes the view references back to the underlying base tables • The alternative is view materialisation: — SQL populates a temporary table when the view is first referenced — However, keeping the temporary table up-to-date can be difficult... • View materialisation is an active area of DB research通过视图解析实现视图 SQL可以将视图引用重写会底层基础表 还可以视图实体化--当视图被首次引用，SQL填充临时表但是临时表很难进行更新

Restrictions on Views • With views, some queries are not permitted: — Queries that resolve to nested aggregates — Queries that give aggregates in a WHERE clause • Views can be updated provided: — There are no aggregates in the columns to be updated — There are no GROUP BY or HAVING clauses — The view contains only one source table with no nested SELECTs对视图的限制 不允许使用一些查询——解析嵌套聚合查询或在WHERE子句中给出聚合的查询 视图可以更新但需要更新的列中没有聚合 GROUP BY或HAVING子句 只包含一个源表不嵌套SELECT

How to Access Other Users’ Objects访问其他用户的对象

Updating Views An Important Subtlety • SQL allows a view to be updated provided the changed rows in the base tables still satisfy all of the conditions of the defining query’s WHERE clause • For example:SQL在基础表更改后仍满足定义查询后WHERE子句所有条件

• This would fail because the modified row ( BranchNo = 'B005' ) would no longer be selected by the view definition ( WHERE BranchNo = 'B003' ) — View updates may not allow rows to migrate into or out of the view — Can exploit this behaviour to help enforce DB integrity — Put domain/business constraints into the view definition & only update views视图定义不选择已经修改后的行 视图更新可能不允许行迁出迁入视图 可以利用这个性质保证数据课完整性 在数据定义中加入域业务约束并只更新视图

Summary of Views • Advantages: — Views help provide granularity of access control — Views can help reduce complexity and improve access control — Views can help maintain DB integrity (e.g., by doing updates via views) • Disadvantages: — There are some restrictions on their use — Resolution method can cause a performance penalty — Materialisation method can cause consistency problems视图优点 有助于访问控制权限 降低复杂度并改进访问控制 维护数据库完整性 缺点 视图使用由限制 解析方法可能会导致性能下降 物化方法可能影响一致性

SQL – Overall Summary • SQL is a powerful relational DB query language • SQL is declarative, not procedural (e.g., no variables) • SQL is showing its age ... (e.g., quirky syntax, bolted-on features) • ANSI SQL resolves some inconsistencies between DBMS vendors • But despite being over 25 years old... • SQL remains THE ‘world-standard’ for DBMS