1. What privileges are
needed for a user to execute the following SQL statement over tables
tables Worker(ID,name) and Works(ID,company)?
Delete From Worker
Where ID In (Select ID From Works Group By ID Having Count(*) >
3)
2. What privileges are
needed for a user to execute the following SQL statement over tables
Employee(ID,salary,rank,deptID) and Department(ID,category)?
Update Employee E1
Set salary = (Select Avg(salary) From Employee E2 Where E1.rank =
E2.rank)
Where deptID In (Select ID from Department Where category = 'Sales')
3. Suppose you are the
owner of table Employee(ID,salary,dept). You want to authorize user
Amy to see (but not modify) employee information for those employees
who earn less than $80,000 and work in a department with fewer than
10 people. Specify a SQL statement or sequence of statements that
achieves this goal.
4. Consider tables
Worker(ID,name) and Works(ID,company), where ID is a key for each
table. Consider the following pair of SQL statements. Assume Amy is
a valid user, and the statements are issued by a single user who is
the owner of both tables Worker and Works.
Create View NoJob As
Select Distinct ID From Worker, Works Where
Worker.ID = Works.ID
Grant Delete On NoJob to Amy With Grant Option
Why is this pair of statements disallowed by the SQL standard? Can
you write an equivalent pair of statements that conforms to the
standard?
5. Consider a table
T(A,B,C) with owner Amy, and the following sequence of statements
related to privileges on T. Each statement is prefaced with the user
issuing it.
Amy: Grant Select, Delete On T To Bob With Grant Option
Amy: Grant Select, Delete On T To Carol With Grant Option
Bob: Grant Select(A,B), Delete on T to David With Grant Option
Carol: Grant Select(A,C) On T To David With Grant Option
David: Grant Select(A), Delete on T to Eve
Amy: Revoke Select, Delete on T From Bob Cascade
What privileges on table T does Eve have after this sequence of
statements?
6. Consider a table
T(A,B,C) with owner Amy, and the following sequence of statements
related to privileges on T. Each statement is numbered and prefaced
with the user issuing it.
1 - Amy: Grant Select On T To Bob With Grant Option
2 - Bob: Grant Select On T To Carol With Grant Option
3 - Carol: Grant Select(A,C) On T To David With Grant Option
4 - Carol: Grant Select(A,B) On T To Eve With Grant Option
5 - Amy: Grant Select On T To Eve
6 - Amy: Grant Select(C) On T To Frank
7 - David: Grant Select(A,C) On T To Frank With Grant Option
8 - Eve: Grant Select(A,C) On T To Frank
9 - David: Grant Select(A) On T To Gary
10 - Eve: Grant Select(A) On T To Gary
11 - Amy: Revoke Select On T From Eve Restrict
12 - Carol: Revoke Select(A,C) On T From David Restrict
13 - David: Revoke Select(A) On T From Eve
14 - Bob: Revoke Select On T From Carol Cascade
15 - Amy: Revoke Select On T From Bob Restrict
(a) Which of the Grant statments, if any, would be disallowed?
(b) Which of the Revoke statements, if any, would be disallowed?
(c) After the statements complete execution (excluding any disallowed ones), what privileges does user Frank have on table T?
1.
Worker - Delete, Select(ID)
Works - Select(ID)
2.
Employee - Update(salary), Select(salary,rank,deptID)
Department - Select
3.
create view V as
select * from Employee E1
where salary < 80,0000
and 10 > (select count(*) from Employee E2
where E2.dept = E1.dept)
grant select on V to Amy
4.
NoJob is not an updatable view so delete privileges are
disallowed.
The following statements are equivalent, but NoJob2 is an
updatable
view.
Create View NoJob2 As
Select ID From Worker
Where ID In (Select ID From Works)