Views and Security

1
Views and Security
3
2
Outline

• views generalities
• updating through views
• security

3
1
4
General Definition

• a view is a perspective of the database
• different users may need to see the database
  differently this is achieved through the view
  mechanism
• a view is part of the external level
• remember the three level architecture internal,
  conceptual and external

5
View - a window to the database
the database - a set of base tables
6
View

• named relational expression
• virtual relation
• substitution process

7
View in SQL

• named SELECT statement
• some restrictions apply (not any SELECT statement
  can be declared as a view)
• CREATE VIEW
• AS
• WITH CHECK OPTION
• DROP VIEW
• RESTRICT CASCADE

8
SQL views - examples

• consider
• Employee ( Emp_Id, Name, Address, Salary, Dept_id
  )
• Department (Dept_id, Manager, Budget, Office )
• CREATE VIEW Emp AS
• SELECT Emp_Id, Name, Address, Dept_id
• FROM Employee
• CREATE VIEW GoodEmp AS
• SELECT Emp_id, Name, Address, Salary, Dept_id
• FROM Employee
• WHERE Salary 45

9
SQL views - examples

• consider
• Employee ( Emp_Id, Name, Address, Salary, Dept_id
  )
• Department (Dept_id, Manager, Budget, Office )
• CREATE VIEW SafeEmployees AS
• SELECT Name, Employee.Dept_id FROM
  Employee, DepartmentWHERE Budget 1500 AND
  Employee.Dept_id
  Department.Dept_id

10
SQL views - examples

• consider
• Employee ( Emp_Id, Name, Address, Salary, Dept_id
  )
• Department (Dept_id, Manager, Budget, Office )
• CREATE VIEW TotSalPerDept AS
• SELECT Dept_id, SUM(Salary) AS TotSal
• FROM Employee
• GROUP BY Dept_id

11
SQL Views - use

• views are used as if they were base relations
• from the point of view of the user, a view is the
  same as a base relation
• however, certain restrictions exist
• in a view, a column that is based on an aggregate
  function cannot be subject to an aggregate
  function or to a WHERE clause (e.g. TotSal
  before)
• a grouped (contains GROUP BY) view may never be
  joined with a base table or another view

12
With Check Option

• only for updateable views
• migrating rows
• a row of a view, after being updated, may not
  satisfy the condition of the view anymore,
  therefore it will migrate out of the view
• WITH CHECK OPTION avoids such situations the
  update is not permitted if the row will no longer
  satisfy the condition of the defining query

13
Advantages

• automatic/improved security
• reduced complexity
• through macro facility
• customisation
• the same data can be seen differently by users
• through macro facility
• data integrity
• WITH CHECK OPTION

14
Disadvantages

• update restriction
• will see in more detail next lecture
• structure restriction
• performance

15
2
16
Data manipulation operations on views

• retrieval operations
• in theory
• in practice (SQL92)
• update operations
• in theory - basics
• in practice (SQL92)

17
Application

• suppose
• Student ( S_id, Name, Address, Programme, Tutor,
  )
• Tutor ( T_id, Name, Address, )
• Course ( C_id, Name, Length, )
• C_Reg ( S_id, C_id, ... )
• suppose all tutors need access to all
  course-lists, but are not allowed to see other
  information about students this can be expressed
  as a view

18
View Course Lists

• CREATE VIEW Course-lists (Student, Tutor, Course)
  AS
• SELECT (Student.Name, Tutor.Name,
  Course.Name)
• FROM Student, Tutor, Course, C_Reg
• WHERE Tutor T_id AND Course.C_id
  C_Reg.C_id
• AND Student.S_id C_Reg.S_id

19
Data operations on Course Lists

• retrieve
• delete
• update
• insert
• ANY OPERATION ON A VIEW HAS TO BE PROPAGATED TO
  THE BASE RELATIONS on which the view is defined

20
Retrieving from Course Lists

• -- all students that take Database Systems
• SELECT Student FROM Course-lists
• WHERE Course Database Systems
• --Marians tutees and the courses they take
• SELECT Student, Course FROM Course-lists
• WHERE Tutor Marian
• --etc.

21
Insert into Course List

• insert the fact that Bob Marley, whos personal
  tutor is Marian Ursu, has registered for
  Database Systems
• INSERT INTO Course-lists
• VALUES (Bob Marley, Marian Ursu, Database
  Systems)
• how should this work? why?

22
Deleting a student from a Course Lists
23
Principle

• a view should look like and behave exactly like a
  base relation
• satisfied, in theory, in case of retrieval
  operations
• in practice, even in case of retrieval
  operations, it is sometimes violated e.g. in
  SQL92 (recall restrictions see p. 11)

24
SQL rules for updating views

• the FROM clause of the SELECT statement of the
  defining query contains exactly one table
  reference (the view is defined on exactly one
  table), i.e. the view definition does not contain
  JOIN, UNION, INTERSECT or EXCEPT
• every element in the SELECT statement of the
  defining query is a column name (rather than a
  calculated field, aggregate function, )
• the SELECT clause defining the view does not
  contain the word DISTINCT
• the defining query does not include a GROUP BY
  clause
• etc (refer to the text book)

25
3
26
The problem of data security

• aspects
• origins of security rules
• social - legal, ethical, political, strategic,
  ...
• operational problems
• are the computers safe?
• does the operating system have a security system
  (passwords, storage protection keys ...)?
• ...
• does the DBMS have a concept of data ownership?

27
SQLs GRANT and REVOKE

• GRANT
• ON
• TO PUBLIC
• WITH GRANT OPTION
• REVOKE GRANT OPTION FOR
• ON
• FROM

28
Clarifications

• privileges
• USAGE (for domains), SELECT, INSERT (column
  specific), UPDATE (column specific), DELETE,
  REFERENCES (for integrity constraint definitions)
• DOMAIN
• TABLE (a base table or a view)
• RESTRICT CASCADE

29
Example 1

• CREATE VIEW View1 AS
• SELECT S_id, S_name, Status, City
• FROM Suppliers WHERE City Paris
• GRANT SELECT, INSERT,
• UPDATE ( S_name, Status ), DELETE
• ON View1
• TO Mark, Spencer

30
Example 2

• CREATE VIEW View2 AS
• SELECT S_id, S_name, Status, City FROM S
• WHERE EXISTS
• ( SELECT FROM SP
• WHERE EXISTS
• (SELECT FROM P
• WHERE S.S_id SP.S_id AND
• P.P_id SP.P_id AND P.City Rome ))
• GRANT SELECT ON View2 TO John

31
Example 3

• CREATE VIEW View3 AS
• SELECT P_id, ( SELECT SUM (Contracts.Qty)
• FROM Contracts
• WHERE Contracts.P_id Parts.P_id )
• AS Quantity
• FROM Parts
• GRANT SELECT ON View3 TO Bill

32
Context dependent/independent rules

• context-independent rules
• the previous examples
• context-dependent rules
• the rule and/or the view definition will contain
  context dependent functions
• date(), day(), time(), terminal()

33
Example 4

• GRANT INSERT
• ON Transactions
• WHERE Day() NOT IN (Saturday, Sunday) AND
• Time() 900 AND Time()
• TO Till
• --Till is a group of users

34
Other issues

• logical OR between security rules
• anything not explicitly allowed is implicitly
  prohibited

35

36
Conclusions

• views generalities
• updating through views
• security