1 / 20

Transactions & Access Control

Transactions & Access Control. Week 4, Day 1 (based on Ch 6 of Connolly and Begg). Transactions and Access Control - Overview. Today we will consider making SQL work: Some further comments on Views Transactions (are briefly introduced) Access Control. Further on Views - quickly.

cole
Download Presentation

Transactions & Access Control

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. Transactions & Access Control Week 4, Day 1 (based on Ch 6 of Connolly and Begg) CMPT 355 Sept-Decr 2010 - w4d1

  2. Transactions and Access Control - Overview Today we will consider making SQL work: • Some further comments on Views • Transactions (are briefly introduced) • Access Control CMPT 355 Sept-Decr 2010 - w4d1

  3. Further on Views - quickly • Restrictions on Views (p.181) • Summary data may not be used in a WHERE clause of a SELECT, • since it exists nowhere. • A view with summary data cannot be joined with a base table or view, • since they are different types of things. CMPT 355 Sept-Decr 2010 - w4d1

  4. Further on Views - quickly • View Updatability (p.181-182) • All updates to base tables are immediately reflected in all views that encompass them. • The only views that can be used for updating • are views of individual records from a single base table. • “The viewed table is updatable if and only if the <query expression> is updatable.”1 1 ISO/IEC 9075-2 Database Languages – SQL - Foundation CMPT 355 Sept-Decr 2010 - w4d1

  5. Further on Views - quickly • WITH CHECK OPTION (p.183-184) • Is only applicable to updatable views. • Prohibits rows from migrating out of a view. • Two versions: • WITH LOCAL CHECK OPTION • Any INSERTs or UPDATEs to data in this view or other views derived from this view must not cause the row to disappear from this view unless it also disappears from the table or view from which it is derived. • WITH CASCADED CHECK OPTION (the default) • Any INSERTs or UPDATEs to data in this view or other views derived from this view must not cause the row to disappear from this view. CMPT 355 Sept-Decr 2010 - w4d1

  6. Further on Views - quickly • Advantages of Views (p. 184-186) • Data independence • Improved security • Reduced complexity • Customization CMPT 355 Sept-Decr 2010 - w4d1

  7. Further on Views - quickly • Disadvantages of Views (p. 184-186) • Update restriction • on complex views • still need single table views for updating • Lack of updatability of VIEW definitions • Need to drop and recreate • Performance • Views are virtual and need to be created dynamically via queries unless “materialized” (see 6.4.8 p 186-187) CMPT 355 Sept-Decr 2010 - w4d1

  8. A NOTE about style • For purposes of this class • views should be used for: • ALL updates • (INSERTs, UPDATEs, DELETEs). • User queries • Commonly used queries CMPT 355 Sept-Decr 2010 - w4d1

  9. A NOTE about style Use views for ALL updates • (INSERTs, UPDATEs, DELETEs). • Updates should be applied to updatable views that are appropriate to the user of the view • to maintain data independence between applications and the underlying data structure • (updatable views are based on rows in a single table) • and these views should specify • WITH LOCAL CHECK OPTION to ensure that all rows remain updatable • NOTE: • attributes not defined in a view will be INSERTed as NULL unless a DEFAULT has been specified in the base table definition CMPT 355 Sept-Decr 2010 - w4d1

  10. A NOTE about style • Use views for user queries • to limit access to data on a need to know basis • to remove the need to do a join at the application level • to isolate users from changes to the actual database structure • Use views for commonly used queries • to allow users to easily get the results they need CMPT 355 Sept-Decr 2010 - w4d1

  11. Transactions • “A transaction is a logical unit of work • consisting of one or more SQL statements • that is guaranteed to be atomic with respect to recovery.” (p. 187) • A transaction is “an action, or series of actions, • carried out by a single user or application program, • which reads or updates the contents of the database.” (Ch. 20, p. 573) • “A transaction is a logical unit of work on the database. • It may be an entire program, a part of a program, • or a single command (for example, the SQL command INSERT of UPDATE), • and it may involve any number of operations on the database.” (Ch. 20, p. 573) CMPT 355 Sept-Decr 2010 - w4d1

  12. Transactions • Transactions allow us to identify • a bunch of database updates • that need to be done together or not at all • and that may all need to be done before anyone can access any of the effected data. • E.g. a sales slip with 5 line items on it • E.g. an airline reservation consisting of multiple flights • E.g. the daily sales of a store which are being entered as a batch • E.g. applying a 10% raise to all staff CMPT 355 Sept-Decr 2010 - w4d1

  13. Transactions • A transaction starts • when we access the database • automatically when we start using the system, and • after having executed a COMMIT or ROLLBACK • The effect of a transaction in a multi-user system can be specified by • SET TRANSACTION (p.188) • NOTE: this only sets the current transaction, not future ones • (we’ll discuss this later as part of Chapter 20) CMPT 355 Sept-Decr 2010 - w4d1

  14. Transactions • Transactions allow us to decide whether or not to make our changes permanent. • COMMIT makes database changes permanent • Before the COMMIT statement is executed, changes are tentative and may or may not be visible to other transactions. • For now, you should COMMIT your changes before logging off. • ROLLBACK backs out any changes since the last COMMIT CMPT 355 Sept-Decr 2010 - w4d1

  15. Access Control • Remember our database goals of: • Data integrity • Ensuring the correctness, protection, and security of the data • Data sharability • Ensuring the ability to share data between applications and between users on a need-to-know basis • Data availability • Ensuring the ability to access the data when and where it is needed • We need to control access • to the database • and to parts of the database • in order to be able to meet these goals. CMPT 355 Sept-Decr 2010 - w4d1

  16. Access Control • The Database Administrator (DBA) • has full access privileges to the entire database. • The person who creates a table • owns the table • has full access privileges to the entire table. • The person who creates a view • owns the view • has only those access privileges given to • the table(s) / view(s) upon which the created view is based. CMPT 355 Sept-Decr 2010 - w4d1

  17. Access Control • Access can be granted on: • TABLEs • (and VIEWs which are a type of table) • VIEWs can be used to control record level access • some TABLE access privileges (SELECT, INSERT, UPDATE, REFERENCE) can also control access to specific attributes • DOMAINs • the use of DOMAIN definitions created by a different user • requires that they provide you with USAGE access • various other database objects (that we don’t need to worry about yet) • COLLATION, CHARACTER SET, TRANSLATION, user defined TYPE, and SEQUENCE also require USAGE access CMPT 355 Sept-Decr 2010 - w4d1

  18. Access Control There’s access & then there’s access • For a TABLE: • SELECT allows a user to retrieve data • INSERT allows a user to insert new records • UPDATE allows a user to update data values • DELETE allows a user to delete records • REFERENCE allows a user to create constraints that use attributes from the table • USAGE applies to COLLATION, CHARACTER SET, TRANSLATION, user defined TYPE, and SEQUENCE objects • ALL PRIVILEGES includes all of the above • SELECT, INSERT, UPDATE, REFERENCE can each specify certain attributes CMPT 355 Sept-Decr 2010 - w4d1

  19. Access Control The general format to grant a privilege is (p.190): • GRANT {privilegeList / ALL PRIVILEGES} • ON objectName • TO {userlist / PUBLIC} • [WITH GRANT OPTION] Where: • privilegeList names one or more privileges • objectName is the name of a TABLE or VIEW • userList names one or more users known to the DBMS • (users must be registered before they can be granted privileges) • WITH GRANT OPTION allows users to grant to others any privileges that they have been granted in this command CMPT 355 Sept-Decr 2010 - w4d1

  20. Access Control The general format to revoke a privilegeis (p.192): • REVOKE [WITH GRANT OPTION] • {privilegeList / ALL PRIVILEGES} • ON objectName • FROM {userlist / PUBLIC} • [RESTRICT / CASCADE] Where: • WITH GRANT OPTION limits this command to granting only • privilegeList names one or more privileges • objectName is the name of a TABLE or VIEW • userList names one or more users known to the DBMS • RESTRICT (default) rejects the operation if this user owns any objects (TABLEs, VIEWs, DOMAINs, CONSTRAINTs, ASSERTIONs) that are dependent on this privilege • CASCADE drops any objects that are owned by this user that are dependent on this privilege CMPT 355 Sept-Decr 2010 - w4d1

More Related