Security and Transaction Management Pertemuan 8

1. Security and Transaction ManagementPertemuan 8 Matakuliah : T0413/Current Popular IT II Tahun : 2007

2. AGENDA:•Users and Privileges•Object Privileges•Taking Privileges Away•Using Views to Filter Table Privileges•Privileges on Other Kinds of Objects •Transactions and Concurrency•Types of Concurrency Problems•Using Pessimistic Locking•Using Optimistic Locking Book: Mastering SQL by Martin Gruber Sybex (2000) Chapter : 16-17

3. Users and Privileges • A database user called an authorization identifier (Authorization_ID) • Similar to OS users. A database user has a name that is associated with certain a set up privilleges, a set of objects and DB sessions • One to one correspondence between Database and OS users

4. Users and Privileges (cont’d) • Types of Privileges • System Privileges: Control general access to the database and involve such things as the right to connect, the right to create tables and other objects, and the right to administer the database. • Object Privileges: Specify to a particular database object (a particular view, etc).

5. Object Privileges • Here are the standard operations that apply to privileges on tables and views: • ALTER a user with this privilege can perform ALTER TABLE statement on the table. • SELECT a user with this privilege can perform queries on the table. • INSERT a user with this privilege can perform the INSERT statement on the table. • UPDATE a user with this privilege can perform the UPDATE statement on the table. You may limit this privilege to specified columns of the table.

6. Object Privileges (cont’d) • DELETE a user with this privilege can perform the DELETE statement on the table. • REFERENCES a user with this privilege can define a foreign key that uses one or more columns of the table as a parent key. You may limit this privilege to specified columns. This privileges does not apply to views. • INDEX a user with this privilege can create an index on the table (will be discussed later). • DROP a user with this privilege can drop the table.

7. Granting Privileges • Using GRANT • GRANT SELECT ON Customers TO Adrian; • Restricting Table Privileges to Certain Columns • GRANT UPDATE(city, comm) ON Salespeople TO Diane; • Using the ALL and PUBLIC Arguments • ALL : to give the grantee all of the privileges on the table • PUBLIC : when grant privileges to public, all users receive them automatically. • Examples: • GRANT ALL PRIVILEGES ON Customers TO Stephen; • GRANT ALL ON Customers TO Stephen; • GRANT SELECT ON Orders TO PUBLIC;

8. Granting Privileges (cont’d) • Granting with the GRANT OPTION • Sometimes a creator of a table wants other users to be able to grant privileges on that table. • To support this, we can use WITH GRANT OPTION • Example: Diane wanted Adrian to have right to grant SELECT privilege on the Customers table to other users. GRANT SELECT ON Customers TO Adrian WITH GRANT OPTION; • Then, Adrian would have the right to give SELECT privilege to third parties GRANT SELECT ON Diane.Customers TO Stephen;

9. Taking Privileges Away • After given privileges to users, you can also taking it away from them. • Using REVOKE • REVOKE INSERT ON Orders FROM Adrian; • REVOKE INSERT, DELETE ON Customers FROM Adrian, Stephen; • General principles of REVOKE: • You can revoke only a privilege you have granted • When you revoke a privilege that you have granted with the GRANT OPTION, all users who received the privilege as a consequence of that GRANT OPTION lose it as well. • Certain objects can depend on certain privileges for their existence. • You can also REVOKE the GRANT OPTION on a privilege without revoking the privilege itself.

10. Taking Privileges Away (cont’d) • Syntax: REVOKE [ GRANT OPTION FOR ] { ALL [PRIVILEGES] } | {privilege, …} ON object FROM PUBLIC | {grantee, …} CASCADE | RESTRICT ;

11. Using Views to Filter Table Privileges • Limiting the SELECT Privilege to Certain Columns • CREATE VIEW Clairesview AS SELECT snum, sname FROM Salespeople; • GRANT SELECT ON Clairesview TO Claire; • Limiting Privileges to Certain Rows • Granting Access Only to Derived Data

12. Privileges on Other Kinds of Objects • Temporary tables • CREATE TABLE • Domains • CREATE DOMAIN • Collations • CREATE COLLATION

13. Transactions and Concurrency • When does a change become permanent? • COMMIT WORK; • ROLLBACK WORK; • SET AUTOCOMMIT ON; • SET AUTOCOMMIT OFF:

14. Types of Concurrency Problems • Standard terms for concurrency problems: • Lost update • Dirty read • Non-repeatable read • Phantom insert

15. Using Pessimistic Locking • Locks that prevent some kinds of data access by simultaneous transactions. • Isolation Levels: • READ UNCOMMITED • READ COMMITED • REPEATABLE READ • SERIALIZABLE • Share Locks • Exclusive Locks

16. Using Optimistic Locking • Locks that keep track of when clashes occur and roll back transactions as necessary. • The mechanism optimistic locking uses is the timestamp. • DBMS makes a record whenever a transaction touches a piece of data. • If transaction makes a violation, DBMS rolls it back.

17. End of Security and Transaction Management Thank you