Lecture 17

Announcements

• Homework #5 will be out later today, due thursday after exam#2

  • You can solve it now or later the exam (though time is short). It is a great learning exercise

  • You can submit it now or wait to see how you did in the exam if you are not sure if it is correct

  • I will provide some sample output, but I cannot guarantee if you will match it 100%. Let me know if you do.

• Lecture exercise #16 is out tomorrow at noon (the latest), due on sunday midnight.

Today’s lecture

• Triggers

• Access control

We will revisit the procedural paradigms shortly next week

Triggers

• Become active on some action (insert/delete/update) on a table

CREATE TRIGGER BEFORE/AFTER UPDATE/INSERT/DELETE on

FOR EACH ROW WHEN BEGIN END ;

• Example: inserting a tuple

ACCESS CONTROL

• Database server (dbclass.cs.rpi.edu)

  • Schema/namespace (public)

  • Users/roles

• Roles is a set of users

• A user may have many roles

CREATE ROLE dbclass ;
GRANT dbclass to adalis ;
GRANT dbclass to rattar ;

CREATE ROLE dbta ;
GRANT dbta to rattar ;
– REVOKE dbta from rattar ;

• Give access rights to individual roles or users

GRANT CONNECT ON baking to dbclass ;
GRANT SELECT ON bakers to dbclass ;
GRANT INSERT ON bakers to dbta ;