security and transaction management pertemuan 8
DESCRIPTION
Security and Transaction Management Pertemuan 8. Matakuliah: T0413/Current Popular IT II Tahun: 2007. AGENDA: • Users and Privileges • Object Privileges • Taking Privileges Away • Using Views to Filter Table Privileges • Privileges on Other Kinds of Objects. - PowerPoint PPT PresentationTRANSCRIPT
Security and Transaction ManagementPertemuan 8
Matakuliah : T0413/Current Popular IT IITahun : 2007
2
AGENDA: • Users and Privileges • Object Privileges • Taking Privileges Away • Using Views to Filter Table Privileges • Privileges on Other Kinds of Objects
Book:Mastering SQL by Martin GruberSybex (2000)Chapter : 16-17
• Transactions and Concurrency • Types of Concurrency Problems • Using Pessimistic Locking • Using Optimistic Locking
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.
– UPDATEa 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.
– REFERENCESa 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.
– INDEXa user with this privilege can create an index on the table (will be discussed later).
– DROPa 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 partiesGRANT 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 objectFROM PUBLIC | {grantee, …}
CASCADE | RESTRICT ;
11
Using Views to Filter Table Privileges• Limiting the SELECT Privilege to Certain Columns
– CREATE VIEW ClairesviewAS 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 ofSecurity and Transaction Management
Thank you