Transcript Title Goes Here - Binus Repository

Matakuliah : T0413/Current Popular IT II
Tahun
: 2007
Security and Transaction Management
Pertemuan 8
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
2
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
3
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).
4
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.
5
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.
6
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;
7
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;
8
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.
9
Taking Privileges Away (cont’d)
• Syntax:
REVOKE [ GRANT OPTION FOR ]
{ ALL [PRIVILEGES] } | {privilege, …}
ON object
FROM PUBLIC | {grantee, …}
CASCADE | RESTRICT ;
10
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
11
Privileges on Other Kinds of Objects
• Temporary tables
– CREATE TABLE
• Domains
– CREATE DOMAIN
• Collations
– CREATE COLLATION
12
Transactions and Concurrency
• When does a change become permanent?
– COMMIT WORK;
– ROLLBACK WORK;
– SET AUTOCOMMIT ON;
– SET AUTOCOMMIT OFF:
13
Types of Concurrency Problems
• Standard terms for concurrency problems:
– Lost update
– Dirty read
– Non-repeatable read
– Phantom insert
14
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
15
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.
16
End of
Security and Transaction Management
Thank you
17