Triggers security and authorization in sql l.jpg
This presentation is the property of its rightful owner.
Sponsored Links
1 / 14

Triggers, security and authorization in SQL PowerPoint PPT Presentation


  • 82 Views
  • Uploaded on
  • Presentation posted in: General

Triggers, security and authorization in SQL. Niki Sardjono CS 157A sect 2 Prof. S. M. Lee. Trigger. Statement that the system executes auto. As a side effect of a modification to the database. Requirements : When a trigger is executed.

Download Presentation

Triggers, security and authorization in SQL

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Triggers security and authorization in sql l.jpg

Triggers, security and authorization in SQL

Niki Sardjono

CS 157A sect 2

Prof. S. M. Lee


Trigger l.jpg

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.


Slide3 l.jpg

  • Database stores triggers just as regular data.

  • Why? So they are persistent and accessible to all database operations.


Need for trigger l.jpg

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 l.jpg

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.


Security violation l.jpg

Security Violation

  • Malicious access

    • Unauthorized reading of data.

    • Unauthorized modification of data.

    • Unauthorized destruction of data.

  • Database security -> protection from malicious access. Absolute protection is impossible however.

  • Security measures

    • Database Systems: users capable on accessing only portions of the database.


Slide7 l.jpg

  • Operating System

  • Network

  • Physical

  • Human


Authorization l.jpg

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.


Authorization and views and priviledge granting l.jpg

Authorization and Views and Priviledge Granting

  • View can hide data that the user does not need to see.

    • Simplify usage.

    • Security purpose.

  • User can pass on this authorization to other users (but can be revoked).

  • Represented by authorization graph.


Slide10 l.jpg

  • DBA U1 U4

    U2 U5

    U3

    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 l.jpg

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.


Authorization in sql l.jpg

Authorization in SQL

  • Privilege in SQL includes delete, insert, select, and update.

  • Also has reference -> permits user/role to declare foreign keys when creating relations.

  • How to do it?

  • Grant <privilege list> on <relation/view name> to <user/role list>

  • Example grant update(amount) on loan to U1,U2

  • All privileges -> shortcut in giving all access.


Slide13 l.jpg

  • Usage -> authorizes user to use specified domain.

  • Public -> all current and future users.

  • To create roles in SQL type create role <role name>

  • 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 <privilege list> on <relation/view name> from <user/role list> [restrict/cascade]


Slide14 l.jpg

  • Example revoke select on branch from U1

  • 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.


  • Login