database authorization annotated
DESCRIPTION
DATABASE authorization annotated For more tutorials and presentations or reports visit : http://www.mytrickslab.com/TRANSCRIPT
![Page 1: DATABASE authorization annotated](https://reader036.vdocuments.us/reader036/viewer/2022062521/568c4e0f1a28ab4916a66b71/html5/thumbnails/1.jpg)
Jennifer Widom
Authorization
![Page 2: DATABASE authorization annotated](https://reader036.vdocuments.us/reader036/viewer/2022062521/568c4e0f1a28ab4916a66b71/html5/thumbnails/2.jpg)
Jennifer Widom
AuthorizationDatabase Authorization
Make sure users see only the data they’re supposed to see Guard the database against modifications by malicious users
![Page 3: DATABASE authorization annotated](https://reader036.vdocuments.us/reader036/viewer/2022062521/568c4e0f1a28ab4916a66b71/html5/thumbnails/3.jpg)
Jennifer Widom
AuthorizationDatabase Authorization
Users have privileges; can only operate on datafor 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
![Page 4: DATABASE authorization annotated](https://reader036.vdocuments.us/reader036/viewer/2022062521/568c4e0f1a28ab4916a66b71/html5/thumbnails/4.jpg)
Jennifer Widom
cName state enr sID sName GPA HS sID cName major decCollege Student Apply
AuthorizationUpdate ApplySet dec = ‘Y’Where sID In (Select sID From Student Where GPA > 3.9)
![Page 5: DATABASE authorization annotated](https://reader036.vdocuments.us/reader036/viewer/2022062521/568c4e0f1a28ab4916a66b71/html5/thumbnails/5.jpg)
Jennifer Widom
cName state enr sID sName GPA HS sID cName major decCollege Student Apply
AuthorizationDelete From StudentWhere sID Not In (Select sID From Apply)
![Page 6: DATABASE authorization annotated](https://reader036.vdocuments.us/reader036/viewer/2022062521/568c4e0f1a28ab4916a66b71/html5/thumbnails/6.jpg)
Jennifer Widom
cName state enr sID sName GPA HS sID cName major decCollege Student Apply
AuthorizationSelect student info for Stanford applicants only
Create View SS As Select * From Student Where sID In (Select sID From Apply Where cName =‘Stanford’)
![Page 7: DATABASE authorization annotated](https://reader036.vdocuments.us/reader036/viewer/2022062521/568c4e0f1a28ab4916a66b71/html5/thumbnails/7.jpg)
Jennifer Widom
cName state enr sID sName GPA HS sID cName major decCollege Student Apply
AuthorizationDelete Berkeley applications only
Create View BA As Select * From Apply Where cName =‘Berkeley’
![Page 8: DATABASE authorization annotated](https://reader036.vdocuments.us/reader036/viewer/2022062521/568c4e0f1a28ab4916a66b71/html5/thumbnails/8.jpg)
Jennifer Widom
AuthorizationObtaining Privileges
Relation creator is owner Owner has all privileges and may grant privileges
Grant privs On R To users [ With Grant Option ]
![Page 9: DATABASE authorization annotated](https://reader036.vdocuments.us/reader036/viewer/2022062521/568c4e0f1a28ab4916a66b71/html5/thumbnails/9.jpg)
Jennifer Widom
AuthorizationRevoking Privileges
Revoke privs On R From users [ Cascade | Restrict ]
![Page 10: DATABASE authorization annotated](https://reader036.vdocuments.us/reader036/viewer/2022062521/568c4e0f1a28ab4916a66b71/html5/thumbnails/10.jpg)
Jennifer Widom
AuthorizationRevoking Privileges
Cascade: Also revoke privileges granted from privileges being revoked (transitively), unless also granted from another source
Revoke privs On R From users [ Cascade | Restrict ]
![Page 11: DATABASE authorization annotated](https://reader036.vdocuments.us/reader036/viewer/2022062521/568c4e0f1a28ab4916a66b71/html5/thumbnails/11.jpg)
Jennifer Widom
AuthorizationRevoking Privileges
Restrict: Disallow if Cascade would revoke any other privileges
Revoke privs On R From users [ Cascade | Restrict ]
![Page 12: DATABASE authorization annotated](https://reader036.vdocuments.us/reader036/viewer/2022062521/568c4e0f1a28ab4916a66b71/html5/thumbnails/12.jpg)
Jennifer Widom
DBMSMore software
Even more software
Data
AuthorizationWhere Privileges Reside
Database applicationdeveloper
End user
![Page 13: DATABASE authorization annotated](https://reader036.vdocuments.us/reader036/viewer/2022062521/568c4e0f1a28ab4916a66b71/html5/thumbnails/13.jpg)
Jennifer Widom
AuthorizationDatabase 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 Grant and Revoke statements Beyond simple table-level privileges: use views