Transcript Lecture - Department of Computing

Term 2, 2004, Lecture 6, Views and Security
Marian Ursu, Department of Computing, Goldsmiths College
3
Views and Security
1
Term 2, 2004, Lecture 6, Views and Security
Marian Ursu, Department of Computing, Goldsmiths College
Outline
 views generalities
 updating through views
 security
2
Term 2, 2004, Lecture 6, Views and Security
Marian Ursu, Department of Computing, Goldsmiths College
1
3
Term 2, 2004, Lecture 6, Views and Security
Marian Ursu, Department of Computing, Goldsmiths College
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
4
Term 2, 2004, Lecture 6, Views and Security
Marian Ursu, Department of Computing, Goldsmiths College
View - a window to the database
the database - a set of base tables
5
Term 2, 2004, Lecture 6, Views and Security
Marian Ursu, Department of Computing, Goldsmiths College
View
 named relational expression
 virtual relation
 substitution process
6
Term 2, 2004, Lecture 6, Views and Security
Marian Ursu, Department of Computing, Goldsmiths College
View in SQL
 named SELECT statement
 some restrictions apply (not any SELECT statement can be
declared as a view)
CREATE VIEW <view> [ <column names> ]
AS <SELECT statement>
[WITH CHECK OPTION] ;
DROP VIEW <view> <option> ;
<option> ::= RESTRICT | CASCADE
7
Term 2, 2004, Lecture 6, Views and Security
Marian Ursu, Department of Computing, Goldsmiths College
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 ;
8
Term 2, 2004, Lecture 6, Views and Security
Marian Ursu, Department of Computing, Goldsmiths College
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, Department
WHERE Budget > 1500 AND
Employee.Dept_id = Department.Dept_id
9
Term 2, 2004, Lecture 6, Views and Security
Marian Ursu, Department of Computing, Goldsmiths College
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
10
Term 2, 2004, Lecture 6, Views and Security
Marian Ursu, Department of Computing, Goldsmiths College
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
11
Term 2, 2004, Lecture 6, Views and Security
Marian Ursu, Department of Computing, Goldsmiths College
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
12
Term 2, 2004, Lecture 6, Views and Security
Marian Ursu, Department of Computing, Goldsmiths College
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
13
Term 2, 2004, Lecture 6, Views and Security
Marian Ursu, Department of Computing, Goldsmiths College
Disadvantages
 update restriction
• will see in more detail next lecture
 structure restriction
 performance
14
Term 2, 2004, Lecture 6, Views and Security
Marian Ursu, Department of Computing, Goldsmiths College
2
15
Term 2, 2004, Lecture 6, Views and Security
Marian Ursu, Department of Computing, Goldsmiths College
Data manipulation operations on views
 retrieval operations
 in theory
 in practice (SQL92)
 update operations
 in theory - basics
 in practice (SQL92)
16
Term 2, 2004, Lecture 6, Views and Security
Marian Ursu, Department of Computing, Goldsmiths College
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
17
Term 2, 2004, Lecture 6, Views and Security
Marian Ursu, Department of Computing, Goldsmiths College
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 ;
18
Term 2, 2004, Lecture 6, Views and Security
Marian Ursu, Department of Computing, Goldsmiths College
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
19
Term 2, 2004, Lecture 6, Views and Security
Marian Ursu, Department of Computing, Goldsmiths College
Retrieving from “Course Lists”
-- all students that take “Database Systems”
SELECT Student FROM Course-lists
WHERE Course = ‘Database Systems’ ;
--Marian’s tutees and the courses they take
SELECT Student, Course FROM Course-lists
WHERE Tutor = ‘Marian’ ;
--etc.
20
Term 2, 2004, Lecture 6, Views and Security
Marian Ursu, Department of Computing, Goldsmiths College
Insert into “Course List”
 insert the fact that “Bob Marley”, who’s 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?
21
Term 2, 2004, Lecture 6, Views and Security
Marian Ursu, Department of Computing, Goldsmiths College
Deleting a student from a “Course Lists”
22
Term 2, 2004, Lecture 6, Views and Security
Marian Ursu, Department of Computing, Goldsmiths College
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):
23
Term 2, 2004, Lecture 6, Views and Security
Marian Ursu, Department of Computing, Goldsmiths College
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)
24
Term 2, 2004, Lecture 6, Views and Security
Marian Ursu, Department of Computing, Goldsmiths College
3
25
Term 2, 2004, Lecture 6, Views and Security
Marian Ursu, Department of Computing, Goldsmiths College
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?
26
Term 2, 2004, Lecture 6, Views and Security
Marian Ursu, Department of Computing, Goldsmiths College
SQL’s GRANT and REVOKE
GRANT
<list of privileges>
ON
<data object>
TO
<list of userIDs> | PUBLIC
[ WITH GRANT OPTION ]
REVOKE [ GRANT OPTION FOR] <list of privileges>
ON
<data object>
FROM <list of userIDs> <option>
27
Term 2, 2004, Lecture 6, Views and Security
Marian Ursu, Department of Computing, Goldsmiths College
Clarifications
 privileges
• USAGE (for domains), SELECT, INSERT (column
specific), UPDATE (column specific), DELETE,
REFERENCES (for integrity constraint definitions)
 <data object>
• DOMAIN <domain>
• [ TABLE ] <table> (a base table or a view)
 <option>
• RESTRICT | CASCADE
28
Term 2, 2004, Lecture 6, Views and Security
Marian Ursu, Department of Computing, Goldsmiths College
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
29
Term 2, 2004, Lecture 6, Views and Security
Marian Ursu, Department of Computing, Goldsmiths College
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
30
Term 2, 2004, Lecture 6, Views and Security
Marian Ursu, Department of Computing, Goldsmiths College
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
31
Term 2, 2004, Lecture 6, Views and Security
Marian Ursu, Department of Computing, Goldsmiths College
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()
32
Term 2, 2004, Lecture 6, Views and Security
Marian Ursu, Department of Computing, Goldsmiths College
Example #4
GRANT INSERT
ON
Transactions
WHERE Day() NOT IN (‘Saturday’, ‘Sunday’) AND
Time() > ’ 9:00’ AND Time() < ‘17:00’
TO
Till;
--Till is a group of users
33
Term 2, 2004, Lecture 6, Views and Security
Marian Ursu, Department of Computing, Goldsmiths College
Other issues
 logical “OR” between security rules
 anything not explicitly allowed is implicitly prohibited
34
Term 2, 2004, Lecture 6, Views and Security
Marian Ursu, Department of Computing, Goldsmiths College
–
35
Term 2, 2004, Lecture 6, Views and Security
Marian Ursu, Department of Computing, Goldsmiths College
Conclusions
 views generalities
 updating through views
 security
36