Views and Security views and security 1 Database
Views and Security views and security 1
Database Design Methodology l l l l Create and check ER model Map ER model to tables Translate logical database design for target DBMS Choose file organizations and indexes Design user views Design security mechanisms Consider the introduction of controlled redundancy Monitor and tune the operational system views and security 2
Views l l A view is just a relation, but we store a definition, rather than a set of tuples. Views are created using the CREATE VIEW command can be dropped using the DROP VIEW command. l A view of Staff that excludes salary: CREATE VIEW Staff 1_View AS SELECT staff. No, name, position FROM Staff WHERE branch. No = ‘B 001’; l Microsoft Access 2002 does not support the CREATE VIEW statement. Instead, use QBE to create a stored query. views and security 3
Views and Security l Views can be used to present necessary information (or a summary), while hiding details in underlying relation(s). l l Given Staff 1_View, but not Staff, we can find staff's name and position, but not the sensitive salary data. The user views were identified during Requirements Collection and Analysis stage. views and security 4
Materialized Views l A view whose tuples are stored in the database is said to be materialized. l l Provides fast access, like a (very high-level) cache. Need to maintain the view as the underlying tables change. Ideally, we want incremental view maintenance algorithms. Close relationship to data warehousing, OLAP (On. Line Analytical Processing), (asynchronously) maintaining distributed databases, checking integrity constraints, and evaluating rules and triggers. views and security 5
Issues in View Materialization l l l What views should we materialize, and what indexes should we build on the precomputed results? Given a query and a set of materialized views, can we use the materialized views to answer the query? How frequently should we refresh materialized views to make them consistent with the underlying tables? (And how can we do this incrementally? ) views and security 6
View Maintenance l Two steps: l l l Propagate: Compute changes to view when data changes. Refresh: Apply changes to the materialized view table. Maintenance policy: Controls when we do refresh. l l Immediate: As part of the transaction that modifies the underlying data tables. (+ Materialized view is always consistent; - updates are slowed) Deferred: Some time later, in a separate transaction. (View becomes inconsistent; + can scale to maintain many views without slowing updates) views and security 7
Deferred Maintenance l Three flavors: l l l Lazy: Delay refresh until next query on view; then refresh before answering the query. Periodic (Snapshot): Refresh periodically. Queries possibly answered using outdated version of view tuples. Widely used, especially for asynchronous replication in distributed databases, and for warehouse applications. Event-based: E. g. , Refresh after a fixed number of updates to underlying data tables. views and security 8
Issues in View Maintenance (1/3) l l expensive_parts(pno) : - parts(pno, cost), cost > 1000 What information is available? (Base relations, materialized view, ICs). Suppose parts(p 5, 5000) is inserted: l l l Only materialized view available: Add p 5 if it isn’t there. Parts table is available: If there isn’t already a parts tuple p 5 with cost >1000, add p 5 to view. l May not be available if the view is in a data warehouse. If we know pno is key for parts: Can infer that p 5 is not already in view, must insert it. views and security 9
Issues in View Maintenance (2/3) l l expensive_parts(pno) : - parts(pno, cost), cost > 1000 What changes are propagated? (Inserts, deletes, updates). Suppose parts(p 1, 3000) is deleted: l l l Only materialized view available: If p 1 is in view, no way to tell whether to delete it. (Why? ) l If count(#derivations) is maintained for each view tuple, can tell whether to delete p 1 (decrement count and delete if = 0). Parts table is available: If there is no other tuple p 1 with cost >1000 in parts, delete p 1 from view. If we know pno is key for parts: Can infer that p 1 is currently in view, and must be deleted. views and security 10
Issues in View Maintenance (3/3) l l l View definition language? (Conjunctive queries, SQL subset, duplicates, aggregates, recursion) Supp_parts(pno) : - suppliers(sno, pno), parts(pno, cost) Suppose parts(p 5, 5000) is inserted: l Can’t tell whether to insert p 5 into view if we’re only given the materialized views and security 11
Incremental Maintenance Alg: One Rule, Inserts l l l View(X, Y) : - Rel 1(X, Z), Rel 2(Z, Y) Step 0: For each tuple in the materialized view, store a “derivation count”. Step 1: Rewrite this rule using Seminaive rewriting, set “delta_old” relations for Rel 1 and Rel 2 to be the inserted tuples. Step 2: Compute the “delta_new” relations for the view relation. l Important: Don’t remove duplicates! For each new tuple, maintain a “derivation count”. Step 3: Refresh the stored view by doing “multiset union” of the new and old view tuples. (I. e. , update the derivation counts of existing tuples, and add the new tuples that weren’t in the view earlier. ) views and security 12
Incremental Maintenance Alg: One Rule, Deletes l l l View(X, Y) : - Rel 1(X, Z), Rel 2(Z, Y) Steps 0 - 2: As for inserts. Step 3: Refresh the stored view by doing “multiset difference” of the new and old view tuples. l To update the derivation counts of existing tuples, we must now subtract the derivation counts of the new tuples from the counts of existing tuples. views and security 13
Incremental Maintenance Alg: General l The “counting” algorithm can be generalized to views defined by multiple rules. In fact, it can be generalized to SQL queries with duplicate semantics, negation, and aggregation. l Try and do this. The extension is straightforward. views and security 14
To Restrict View Updates Clubs Students cname jyear mname sid Sailing 1996 Dave 50000 Dave Hiking 1997 Smith Rowing 1998 Smith Active. Students name login age gpa dave@cs 19 3. 3 53666 Jones jones@cs 18 3. 4 53688 Smith smith@ee 18 3. 2 53650 Smith smith@math 19 3. 8 CREATE VIEW Active. Students (name, login, club, since) AS SELECT S. sname, S. login, C. cname, C, jyear FROM Students S, Clubs C WHERE S. sname = C. mname AND S. gpa > 3 name login club since Dave dave@cs Sailing 1996 Smith smith@ee Hiking 1997 Smith smith@ee Rowing 1998 Smith smith@math Hiking 1997 l Smith smith@math Rowing 1998 l l l views and security l How to delete <Smith, smith@ee, Hiking, 1997> from Active. Students? Either deleting <53688, Smith, smith@ee, 18, 3. 2> from Students, Or deleting <Hiking, 1997, Smith> From Clubs. Neither is satisfactory. Disallow such updates. 15
Updates on Views l A field of a view can be updated if: l l l It is obtained from exactly one of the underlying tables. And the primary key of that table is included in the fields of the view. The updatability property may still not enable us to decide into which table to insert a new tuple. views and security 16
Introduction to DB Security l Secrecy: Users should not be able to see things they are not supposed to. l l Integrity: Users should not be able to modify things they are not supposed to. l l E. g. , A student can’t see other students’ grades. E. g. , Only instructors can assign grades. Availability: Users should be able to see and modify things they are allowed to. views and security 17
Access Controls l DB admin is responsible for overall security. l l Designs security policy, maintains an audit trail, or history of users’ accesses to DB. A security policy specifies who is authorized to do what. A security mechanism allows us to enforce a chosen security policy. Two main mechanisms at the DBMS level: l l Discretionary access control l Based on notion of privileges. Mandatory access control l Based on notion of security classes. views and security 18
Discretionary Access Control l l Based on the concept of access rights or privileges for objects (tables and views), and mechanisms for giving users privileges (and revoking privileges). Creator of a table or a view automatically gets all privileges on it. l DMBS keeps track of who subsequently gains and loses privileges, and ensures that only requests from users who have the necessary privileges (at the time the request is issued) are allowed. views and security 19
GRANT Command l l GRANT privileges ON object TO users [WITH GRANT OPTION] The following privileges can be specified: l SELECT: Can read all columns (including those added later via ALTER TABLE command). l INSERT(col-name): Can insert tuples with non-null or nondefault values in this column. l l l INSERT means same right with respect to all columns. DELETE: Can delete tuples. REFERENCES (col-name): Can define foreign keys (in other tables) that refer to this column. If a user has a privilege with the GRANT OPTION, can pass privilege on to other users (with or without passing on the GRANT OPTION). Only owner can execute CREATE, ALTER, and DROP. views and security 20
GRANT and REVOKE of Privileges l l l GRANT INSERT, SELECT ON Sailors TO Horatio l Horatio can query Sailors or insert tuples into it. GRANT DELETE ON Sailors TO Yuppy WITH GRANT OPTION l Yuppy can delete tuples, and also authorize others to do so. GRANT UPDATE (rating) ON Sailors TO Dustin l Dustin can update (only) the rating field of Sailors tuples. GRANT SELECT ON Active. Sailors TO Guppy, Yuppy l This does NOT allow the ‘uppies to query Sailors directly! REVOKE: When a privilege is revoked from X, it is also revoked from all users who got it solely from X. views and security 21
GRANT/REVOKE on Views l l If the creator of a view loses the SELECT privilege on an underlying table, the view is dropped. If the creator of a view loses a privilege held with the grant option on an underlying table, (s)he loses the privilege on the view as well; so do users who were granted that privilege on the view. Creator of view has a privilege on the view if (s)he has the privilege on all underlying tables. Together with GRANT/REVOKE commands, views are a very powerful access control tool. views and security 22
Role-Based Authorization l l In SQL-92, privileges are actually assigned to authorization ids, which can denote a single user or a group of users. In SQL: 1999 (and in many current systems), privileges are assigned to roles. l l l Roles can then be granted to users and to other roles. Reflects how real organizations work. Illustrates how standards often catch up with “de facto” standards embodied in popular systems. views and security 23
Security to the Level of a Field l l l Can create a view that only returns one field of one tuple. (How? ) Then grant access to that view accordingly. Allows for arbitrary granularity of control, but: l l Clumsy to specify, though this can be hidden under a good UI Performance is unacceptable if we need to define fieldgranularity access frequently. (Too many view creations and look-ups. ) views and security 24
Internet-Oriented Security l l l Key Issues: User authentication and trust. l When DB must be accessed from a secure location, passwordbased schemes are usually adequate. For access over an external network, trust is hard to achieve. l If someone with Sam’s credit card wants to buy from you, how can you be sure it is not someone who stole his card? l How can Sam be sure that the screen for entering his credit card information is indeed yours, and not some rogue site spoofing you (to steal such information)? How can he be sure that sensitive information is not “sniffed” while it is being sent over the network to you? Encryption is a technique used to address these issues. views and security 25
Mandatory Access Control l Based on system-wide policies that cannot be changed by individual users. l l Each DB object is assigned a security class. Each subject (user or user program) is assigned a clearance for a security class. Rules based on security classes and clearances govern who can read/write which objects. Most commercial systems do not support mandatory access control. Versions of some DBMSs do support it; used for specialized (e. g. , military) applications. views and security 26
Why Mandatory Control? l Discretionary control has some flaws, e. g. , the Trojan horse problem: l l Dick creates Horsie and gives INSERT privileges to Justin (who doesn’t know about this). Dick modifies the code of an application program used by Justin to additionally write some secret data to table Horsie. Now, Justin can see the secret info. The modification of the code is beyond the DBMSs control, but it can try and prevent the use of the database as a channel for secret information. views and security 27
Bell-La. Padula Model l Objects (e. g. , tables, views, tuples) Subjects (e. g. , users, user programs) Security classes: l l Top secret (TS), secret (S), confidential (C), unclassified (U): TS > S> C > U Each object and subject is assigned a class. l l Subject S can read object O only if class(S) >= class(O) (Simple Security Property) Subject S can write object O only if class(S) <= class(O) (*-Property) views and security 28
Intuition l l Idea is to ensure that information can never flow from a higher to a lower security level. E. g. , If Dick has security class C, Justin has class S, and the secret table has class S: l l Dick’s table, Horsie, has Dick’s clearance, C. Justin’s application has his clearance, S. So, the program cannot write into table Horsie. The mandatory access control rules are applied in addition to any discretionary controls that are in effect. views and security 29
Multilevel Relations bid 101 102 l l bname Salsa Pinto color Red Brown class S C Users with S and TS clearance will see both rows; a user with C will only see the 2 nd row; a user with U will see no rows. If user with C tries to insert <101, Pasta, Blue, C>: l l l Allowing insertion violates key constraint Disallowing insertion tells user that there is another object with key 101 that has a class > C. Problem resolved by treating class field as part of key. views and security 30
Statistical DB Security l l Statistical DB: Contains information about individuals, but allows only aggregate queries (e. g. , average age, rather than Joe’s age). New problem: It may be possible to infer some secret information. l l E. g. , If I know Joe is the oldest sailor, I can ask “How many sailors are older than X? ” for different values of X until I get the answer 1; this allows me to infer Joe’s age. Idea: Insist that each query must involve at least N rows, for some N. Will this work? (No!) views and security 31
Why Minimum N is Not Enough l l By asking “How many sailors older than X? ” until the system rejects the query, can identify a set of N sailors, including Joe, that are older than X; let X=55 at this point. Next, ask “What is the sum of ages of sailors older than X? ” Let result be S 1. Next, ask “What is sum of ages of sailors other than Joe who are older than X, plus my age? ” Let result be S 2. S 1 -S 2 is Joe’s age! views and security 32
- Slides: 32