Megan Taylor

front-end dev, volunteacher, news & data junkie, bibliophile, Flyers fan, sci-fi geek and kitteh servant

Intro to Databases: Database Authorization

Make sure users see only the data they’re supposed to see
Guard the database against modifications by malicious users

Users have privileges, can only operate on data for which they are authorized
Select on R or Select (A1,…,An) on R
Insert on R or Insert (A1,…,An) on R
Update on R or Update (A1,…,An) on R
Delete on R

Examples

Update Apply
Set dec = ‘Y’
Where sID In (Select sID From Student Where GPA > 3.9)
Apply: update(dec), select (sID)
Student: select (sID, GPA)

Delete From Student
Where sID Not In (Select sID From Apply)
Student: Delete, Select(sID)
Apply: Select (sID)

Select student info for Stanford applicants only
Create View SS As
Select * From Student
Where sID In (Select sID From Apply Where cName = ‘Stanford’)
Select(ss)

Delete Berkley applicants only
Create View BA As
Select * From Apply
Where cName = ‘Berkley’
BA: Delete

Obtaining Privileges
Relation creator is owner
Owner has all privileges and may grant privileges
Grant privs on R To users [With Grant Option]

Revoking Privileges
Revoke privs On R From users [Cascade | Restrict] Cascade revokes privs for user and all users that user has granted privileges to

December 2, 2011 | Comments Off on Intro to Databases: Database Authorization | Categories: Posts | Permalink

Comments are closed.

%d bloggers like this: