1 / 5

Security: there are many aspects, one of which is to limit access

Security: there are many aspects, one of which is to limit access on a need-to-know basis. A view is a virtual table that is materialized on demand. For example, a “ stocker ” need only be able to see Book.Isbn and Book.CopiesInSock. CREATE VIEW Stocker (Isbn, Copies)

cybil
Download Presentation

Security: there are many aspects, one of which is to limit access

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Security: there are many aspects, one of which is to limit access on a need-to-know basis. A view is a virtual table that is materialized on demand. For example, a “stocker” need only be able to see Book.Isbn and Book.CopiesInSock. CREATE VIEW Stocker (Isbn, Copies) AS SELECT B.Isbn, B.CopiesInStock FROM Book Stocker (Isbn, Copies) …… CopiesInStock Book Isbn ……. 123 25

  2. We can use a view in a query just as we can use a base table. SELECT S.Copies FROM Stocker S WHERE S.Isbn = I If I = 123, then this query would return (25) How is the query evaluated? Do we always/generally have to materialize the view? No. Minimally, substitute (behind the scenes) the view definition in the query (and optimize/evaluate) that modified query. SELECT S.Copies FROM (SELECT B.Isbn, B.CopiesInStock FROM Book B) AS S WHERE S.Isbn = I I is a parameter. Note the Query nested in the FROM clause

  3. An update (including Insert and Delete) on a view invokes an update on the base table. For example, if I=123 and N=100, then UPDATE Stocker S SET S.Copies = S.Copies + N WHERE S.Isbn = I would result in …… CopiesInStock Book Isbn ……. 123 125 Since Isbn is key of Book, only one row would be updated, but in general an update to a view can result in 0 or more rows being updated in a base table (top of next page).

  4. DELETE FROM Stocker S WHERE S.Copies < 5 will delete all rows from Book in which the CopiesInStock < 5 (undoubtedly, we would NOT want a Stocker to be able to do this, which motivates discussion of privilege granting later). INSERT INTO STOCKER (Isbn, Copies) VALUES (567, 200) will insert a row into Book, where Isbn = 567, CopiesInStock = 200 and ALL other attributes are NULL. However, if such an insert will violate a constraint of Book (e.g., PublisherName is NOT NULL), the the insert is rejected. A View can be defined over multiple base tables, as in the following:

  5. Define a view that is required by a person (or process) responsible to “shipping” books that are ready to be shipped. That is, the books have been paid for [PaymentClearanceDate is not null], the books have not been shipped yet [ShipDate is null], and there are enough copies in stock of the book to satisfy the order

More Related