Triggers, Security - Database Design - Lecture Slides, Slides of Database Management Systems (DBMS)

Download Triggers, Security - Database Design - Lecture Slides and more Slides Database Management Systems (DBMS) in PDF only on Docsity!

Triggers, security and

authorization in SQL

Trigger

• Statement that the system executes auto. As a side effect of a modification to the database.
• Requirements :
  • When a trigger is executed.
  • What action is performed.
• When phase is broken up into
  • Event – event that causes the trigger to checked.
  • Condition – that must be satisfied for trigger to proceed.
• Called as event-condition-action model.

Need for Trigger

• Alert humans or for starting tasks automatically when a certain conditions are met.
• Example: bank with overdraft and warehouse with items in stock.
• Trigger can’t usually perform updates outside the database.

Triggers in SQL

• Not standard before SQL:1999.
• Database system implemented in its own syntax  leading to incompatibilities.
• Triggering event can take many forms
  • Insert or delete instead of update
  • Update -> can specify which column whose update cause the trigger.
  • “Referencing old row as” used to save old value on updated row.
  • Can be triggered before event.

• Operating System
• Network
• Physical
• Human

Authorization

• Allow users to some of the functions of database
  • Read Autho.
  • Insert Autho.
  • Update Autho.
  • Index Autho.
  • Insert Autho.
  • etc
• Can be assigned to one or combinations of the above functions/authorization.

• DBA U1 U

U2 U

U

This graph means that U4 is given authorization only by U1, and U5 got the authorization from both U1 and U2. If authorization is revoked for U1, U4 will lose it too, but U5 will not.

Audit trail

• Audit Trail: a log of all changes (inserts/deletes/updates) to database with information such as who did the update and when the update is performed.
• Aid security.

• Usage -> authorizes user to use specified domain.
• Public -> all current and future users.
• To create roles in SQL type create role
• To grant another users/roles privilege, we append with grant option at the end of the grant statement.
• Example: grant select on brach to U1 with grant option
• To revoke, use revoke on <relation/view name> from <user/role list> [restrict/cascade]

• Example revoke select on branch from U
• This behaviour is called cascading of the revoke (because other users given privilege by that user is also revoked).
• Restrict option will give error if there are any cascading.