Introduction to db security
This presentation is the property of its rightful owner.
Sponsored Links
1 / 59

Introduction to DB Security PowerPoint PPT Presentation


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

Introduction to DB Security. Confidentiality: Users should not be able to see things they are not supposed to. E.g., A student can’t see other students’ grades. Integrity: Users should not be able to modify things they are not supposed to. E.g., Only instructors can assign grades.

Download Presentation

Introduction to DB Security

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


Introduction to db security

Introduction to DB Security

Confidentiality:

Users should not be able to see things they are not supposed to.

E.g., A student can’t see other students’ grades.

Integrity:

Users should not be able to modify things they are not supposed to.

E.g., Only instructors can assign grades.

Availability:

Users should be able to see and modify things they are allowed to.

1


Access controls

Access Controls

Security Policy: specifies who is authorized to do what.

Security Mechanism: allows us to enforce a chosen security policy.

Two main mechanisms at the DBMS level:

Discretionary access control

Part of SQL standard : we will study.

Mandatory access control

Not part of SQL standard

We will study, but not in great detail

[EN] has more detail

2


Discretionary access control

Discretionary Access Control

Each user is given specific priveleges to access particular pieces of data.

  • Eg: Mike can look at (not modify) EMP

  • Eg: Alice can modify address in EMP

  • DBA: is a privileged user

    • Superuser, root

    • Can do things ordinary users can’t

    • Creates accounts: login names, passwords

  • 3


    Discretionary access control1

    Discretionary Access Control

    Owner: user who creates table or view, automatically gets all privileges on it

    Privileges can’t be revoked

    Owner can grant these privileges to others

    Only owner can do CREATE, ALTER and DROP

    DBMS: keeps track of privileges

    ensures only legitimate ops performed.

    If privileges not granted, not present

    default: no privilege

    4


    Grant

    GRANT

    GRANT{ALL PRIVILEGES, privilege list}

    ON tablename | viewname

    TO {PUBLIC, user names}

    [WITH GRANT OPTION]

    • WITH GRANT OPTION: if this is included, can pass some or all of the privileges (with or without grant option) to other users.

    • No WHERE statement in GRANT.

      • How to give access to some rows, some columns ?

    • done through views

    5


    Privileges

    Privileges

    SELECT: Can read all columns

    INSERT:Can insert tuples with values in any column.

    INSERT (col-name):Can insert tuples with non-null or non-default values in this column. Other columns: null or default.

    UPDATE:all columns

    UPDATE (col-name):

    DELETE:rows

    REFERENCES :can create a foreign key that refers to to all columns

    REFERENCES (col-name):to this column

    6


    Revoke

    REVOKE

    REVOKE [GRANT OPTION FOR]

    {ALL PRIVELEGES, privelege list}

    ON tablename | viewname

    FROM {PUBLIC, user names}

    CASCADE|RESTRICT

    • GRANT OPTION FOR: only lose right to propagate this privilege.

    • CASCADE: if they had propagated, others lose

    • RESTRICT: if they had propagated, and if others lose, REVOKE is rejected

    7


    Examples from en

    Examples From [EN]

    EMPLOYEE

    • Suppose that A1 creates the two base relations EMPLOYEE and DEPARTMENT; A1 is then owner of these two relations and hence all the relation privileges on each of them.

    DEPARTMENT

    8


    Grant example from en

    Grant Example From [EN]

    A1 wants to grant A2 the privilege to insert and delete tuples in both of these relations, but A1 does not want A2 to be able to propagate these privileges to additional accounts:

    GRANT INSERT, DELETE

    ON EMPLOYEE, DEPARTMENT

    TO A2;

    9


    Grant example from en1

    Grant Example From [EN]

    A1 wants to allow A3 to retrieve information from either of the two tables and also to be able to propagate the SELECT privilege to other accounts. A1 can issue the command:

    GRANT SELECT ON EMPLOYEE,DEPARTMENT

    TO A3 WITH GRANT OPTION;

    • A3 can grant the SELECT privilege on the EMPLOYEE relation to A4 by issuing:

      GRANT SELECT ON EMPLOYEE TO A4;

    • Notice that A4 can not propagate the SELECT privilege because GRANT OPTION was not given to A4.

    10


    Revoke example from en

    Revoke Example From [EN]

    A1 decides to revoke the SELECT privilege on the EMPLOYEE relation from A3; A1 can issue:

    REVOKE SELECT ON EMPLOYEE

    FROM A3 CASCADE;

    • DBMS will automatically revoke the SELECT privilege on EMPLOYEE from A4 also

      • because A3 granted that privilege to A4

      • A3 does not have the privilege any more

    • If RESTRICT : then REVOKE rejected

    11


    Grant example from en2

    Grant Example From [EN]

    A1 wants to give back to A3 a limited capability to SELECT from the EMPLOYEE relation and wants to allow A3 to be able to propagate the privilege.

    • The limitation is to retrieve only the NAME, BDATE, and ADDRESS attributes and only for the tuples with DNO=5.

    • Can’t do with WHERE because WHERE not allowed in GRANT statement.

      • How to do ?

    • Do with views.

    12


    Priveleges on views en eg

    Priveleges on Views [EN] Eg

    A1 creates the view:

    CREATE VIEW A3EMPLOYEE AS

    SELECT NAME, BDATE, ADDRESS

    FROM EMPLOYEE WHERE DNO = 5;

    • After the view is created, A1 can grant SELECT on the view A3EMPLOYEE to A3 as follows:

      GRANT SELECT ON A3EMPLOYEE TO A3

      WITH GRANT OPTION;

    13


    Creating views

    Creating Views

    What kind of privilege do you need to create a view. Eg:

    suppose A1 owns Emp

    A2 wants to execute

    CREATE VIEW TEMPVIEW ON Emp …

    What kind of privilege will A1 have to give A2 ?

    SELECT - why ?

    A2 has to read values from Emp

    14


    Update example from en

    Update Example From [EN]

    A1 wants to allow A4 to update only the SALARY attribute of EMPLOYEE. A1 can issue:

    GRANT UPDATE ON EMPLOYEE (SALARY)

    TO A4;

    15


    Authorization graphs

    Authorization graphs

    If user has privilege in multiple ways and loses the privilege in one way?

    Suppose B1 owns EMP :

    B1 : GRANT SELECT ON EMP TO B2

    WITH GRANT OPTION;

    B1 : GRANT SELECT ON EMP TO B3;

    B2 : GRANT SELECT ON EMP TO B3, B4;

    B1 : REVOKE SELECT ON EMP

    FROM B2 CASCADE;

    Will B3 or B4 still have SELECT privileges ?

    Authorization Graphs

    16


    Reference

    REFERENCE

    Why is REFERENCE privilege different from SELECT privilege ?

    Suppose B1 owns Employee table and gives B2 REFERENCE privilege

    B2 creates Junk table with ESSN foreign key (RESTRICT/NO ACTION) to SSN in Employee

    In Employee there is an employee with SSN 123

    In Junk there is a row with ESSN 123

    B1 tries to delete row with SSN 123. Result ?

    Can’t delete since would lead to FK violation

    B2 has some control over B1’s Employee table

    17


    Mandatory access control

    Mandatory Access Control

    System wide policy

    not decided by particular user.

    Users, documents have security classification.

    Typical security classes:

    Top secret (TS), secret (S), confidential (C), unclassified (U): TS ≥ S ≥ C ≥ U

    User with Secret classification can read Secret, Classified, Unclassified documents, but not Top Secret

    Not part of SQL standard

    18


    Discretionary access control dac and mandatory access control mac wikipedia

    Discretionary Access Control (DAC) and Mandatory Access Control (MAC)[Wikipedia]

    DAC allows users the ability to make decisions

    Eg : Bob: Alice allowed to UPDATE Employee table

    With MAC, security policy is centrally controlled

    users cannot override or modify this policy

    either accidentally or intentionally.

    Eg: user can’t grant access to table that would otherwise be restricted.

    MAC-enabled systems allow policy administrators to implement organization-wide security policies

    This allows security administrators to define a central policy that is guaranteed to be enforced for all users.

    19


    Dac trojan horse problem modified rg eg

    DAC Trojan horse problem : modified [RG] Eg

    • Justin has table J, doesn’t want Dick to see

    • Dick: create Horsie table,

      • give INSERT privileges on Horsie to Justin

      • Justin doesn’t know about this

    • Dick modifies code of application program used by Justin to access the table J

      • to additionally write some secret data to table Horsie.

    • When Justin accesses J, what will happen ?

    • Modified application program will write information into Horsie

      • Now Dick can see the secret info.

    20


    Mandatory access control rg

    Mandatory Access Control [RG]

    MAC security policies can stop the previous Trojan horse problem

    Trojan horse is a piece of software which appears to perform a certain action but in fact performs another

    MAC: based on system-wide policies that cannot be changed by individual users.

    Each DB object is assigned a security class.

    Each subject (user or user program) is assigned a clearance for a security class.

    Rules based on security classes and clearances govern who can read/write which objects.

    Not part of SQL standard

    21


    Bell lapadula model

    Bell-LaPadula model

    Objects (e.g., tables, views, tuples, columns, particular values)

    Subjects (e.g., users, user programs)

    Typical security classes:

    Top secret (TS), secret (S), confidential (C), unclassified (U): TS ≥ S ≥ C ≥ U

    Subjects and objects each have one of the security classifications

    Eg: user Bob has classification TS

    Eg: EMPLOYEE table has classification U

    Eg: 123’s salary has classification S

    22


    Mac restrictions

    MAC: Restrictions

    Tworestrictions enforced on data access based on subject/object classifications:

    • Simple security property : A subject S is not allowed read access to an object O unless class(S) ≥ class(O).

      • Eg: user Alice has classification C

      • Eg: 123’s salary has classification S

      • Alice will not be allowed to read 123’s salary

    • Can you guess what the other property will be?

    23


    Mac restrictions1

    MAC: Restrictions

    star property (or * property) : A subject S is not allowed to write an object O unless class(S) ≤ class(O).

    Eg: user Bob has classification TS

    Eg: 123’s salary has classification S

    Bob will not be allowed to write 123’s salary

    What’s the idea ?

    • Idea is to ensure that information can never flow from a higher to a lower security level

      • Intentionally or inadvertantly

      • o/w Bob could take TS info, write it into U table and U user would be able to read

    24


    Multilevel tables filtering rg eg

    Multilevel Tables: Filtering [RG] Eg

    bid

    bname

    color

    class

    101

    Salsa

    Red

    S

    102

    Pinto

    Brown

    C

    • [EN] has more complex Eg, we study simpler [RG] Egs

      • only tuples (rows) have classification; not attributes

    • What should user with S clearance see?

    • will see both rows. TS user will see same.

    • What should a C user see ?

    • will only see 2nd row. What should U user see ?

    • a user with U will see no rows i.e. empty table

    25


    Multilevel tables polyinstantiation rg eg

    Multilevel Tables: Polyinstantiation [RG] Eg

    bid

    bname

    color

    class

    101

    Salsa

    Red

    S

    102

    Pinto

    Brown

    C

    • C user is seeing only the 2nd row. Now suppose C user tries to insert <101, Pasta, Blue, C>

      • what should happen ?

    • If we don’t allow insertion ?

    • Disallowing insertion tells user that there is another object with key 101 that has a class > C

      • Not supposed to know this

    26


    Multilevel tables polyinstantiation rg eg1

    Multilevel Tables: Polyinstantiation [RG] Eg

    So we allow C to insert. Now diff. problem

    • S user is entitled to S info <101, Salsa, Red, S>

    • Don’t want to tell C user that there is S info

    • Polyinstantiation : users can see different things

      • C user sees <101, Pasta, Blue, C>

      • S user sees <101, Salsa, Red, S>

    • bid is key, but “101” repeated. How to fix ?

    • Treat classification field as part of key

      • (bid, classification) is key

    27


    Comparing discretionary access control dac and mandatory access control mac

    Comparing Discretionary Access Control (DAC) and Mandatory Access Control (MAC)

    DAC offers flexibility

    makes it suitable for a large variety of application domains.

    The main drawback of DAC model is vulnerability to malicious attacks

    such as Trojan horses embedded in application programs.

    MAC ensure a high degree of protection

    because prevents any illegal flow of information.

    But MAC has drawback of being rigid

    only applicable in limited environments.

    28


    Comparing discretionary access control dac and mandatory access control mac1

    Comparing Discretionary Access Control (DAC) and Mandatory Access Control (MAC)

    In many practical situations, DAC preferred

    better trade-off between security and applicability.

    Most commercial systems don’t support MAC

    When supported, typically combined with DAC mechanisms

    MAC rules applied in addition to DAC controls

    MAC used for specialized applications

    Eg: military

    DoD Trusted Computer System Evaluation Criteria (Orange book) includes DAC and MAC

    29


    Updates on views

    Updates on Views

    CREATE VIEW REDPARTS AS

    SELECT * FROM P WHERE COLOR = ‘RED’;

    • Suppose make a change toREDPARTS

      UPDATE REDPARTS

      SET WEIGHT = 30 WHERE P# = ‘P1’;

    • What effect will this have on the P table ?

    • Will change P1’s weight to 30

    • What about

      DELETE FROM REDPARTS WHERE P# = ‘P1’;

    • Will delete P1 from P


    Updates on views1

    Updates on Views

    • We create the following view:

      CREATE VIEW

      PARTQUANT (PNO, TOTALQT)

      AS SELECT P#, SUM (QTY)

      FROM SP GROUP BY P# ;

    • Suppose we execute

      UPDATE PARTQUANT

      SET TOTALQT = 339 WHERE P# = ‘P1’;

    • Will not work – why ?

    • TOTALQT is a derived attribute


    Updatable views

    Updatable Views

    • Many subtleties but broadly speaking, views which are derived from a single base table with a subset of rows and columns are updatable

    • With aggregate operators : not updatable

    • If primary key from base table is missing in view, not updatable

    • Some views which are derived from multiple tables are updatable

    • Views which we would regard as read-only can sometimes be modified by using INSTEAD OF triggers; will study later.


    Check option

    CHECK OPTION

    CREATE VIEW REDPARTS AS

    SELECT *

    FROM P

    WHERE COLOR = ‘Red’

    [WITH CHECK OPTION]

    • P1 will be in REDPARTS since it is red

    • Now we do the following:

      UPDATE REDPARTS

      SET COLOR = ‘Green’ WHERE P# = ‘P1’;

    • What will happen to P1 in REDPARTS ?


    Check option1

    CHECK OPTION

    • P1 is now GREEN so …

    • Does not satisfy the view condition

      SELECT *

      FROM REDPARTS

    • P1 will not show up

    • This is a strange situation

      • Didn’t delete row with P1, but P1 not showing up

    • should this be allowed ?

      • SQL gives you a choice of allowing or not allowing this to happen


    Check option2

    CHECK OPTION

    • If WITH CHECK OPTION specified then

      UPDATE REDPARTS

      SET COLOR = ‘Green’ WHERE P# = ‘P1’;

      • UPDATE will not be allowed

    • Can still modify base table directly

      • Just can’t do via modifying view

    • If CHECK OPTIONnot specified then allowed:

    • Similar situation with

      INSERT INTO REDPARTS(P#,COLOR)

      VALUES (‘P8’, ‘Blue’);


    View implementation through query modification

    View Implementation through Query Modification

    • Query modification: DBMS changes a query on a view to a query on underlying table.

      SELECT * FROM REDPARTS

      WHERE WEIGHT > 15;

      is implemented as

      SELECT * FROM P

      WHERE COLOR = ‘RED’ AND WEIGHT > 15;

    • Disadvantage ?


    View implementation through query modification1

    View Implementation through Query Modification

    • Suppose base table is not updated, but view being queried repeatedly

      • With query modification, how will repeated queries on the view be handled ?

    • If view is being queried repeatedly then view query has to be executed every time

      • Even if base table is not being updated

      • Worse if complex query

        • Eg : aggregate function with large table

    • Alternative ?


    View implementation through view materialization

    View Implementation through View materialization

    • View materialization : physically create and store the view as a temporary table.

    • To user: same as query modification. Upside?

    • Does not have to be calculated from scratch each time view queried. Downside?

    • Have to update view when table changed

    • Used in Oracle

      • Uses both query modification and view materialization

      • Optimizer decides which is likely to be better


    View maintenance

    View maintenance

    • View maintenance: need to change view to reflect changes to underlying base table

      CREATE VIEW REDPARTS AS SELECT * FROM P

      WHERE COLOR = ‘Red’;

    • When to do view maintenance ?

    • Immediate view maintenance:as soon as table changes, refresh the view. Downside?

    • Lazy view maintenance:refresh view only when there is a query on it. Downside?

    • Deferred view maintenance:refresh views after some fixed period of time. Eg: every 3 hours. Downside?


    Data warehousing systems

    Data Warehousing Systems

    Used for analysis, not transaction processing Since no transaction processing, consequence ?

    No updates. Impact of this ?

    Data is denormalized and stored together and materialized views are used

    Advantage of denormalized ?

    Data in fewer tables

    Fewer joins. Why do we normalize?

    Does that logic apply here ?

    No updates so no modification anomalies

    Advantage of materialized views?

    40


    Data warehousing systems1

    Data Warehousing Systems

    Don’t have to go back and recalculate views every time a view is referred to

    What is the problem with materialized views?

    Have to change on updates

    Does it apply here ?

    Can create lots of indexes (indexes on most columns)

    What is the problem with having lots of indexex?

    Cost of maintaining lots of indexes.

    Does this apply here ?

    No updates

    41


    Active databases and triggers

    Active Databases and Triggers

    • Motivation : suppose want to do the following:

      • When Date of birth entered/changed, automatically enter/change age of employee

      • If employee becomes a manager, automatically increase his salary by 10%

      • If employee is working on less than 3 projects, notify his supervisor

    • How to do this ?

      • Want some kind of procedure that should run automatically when some condition is specified


    Active databases and triggers1

    Active Databases and Triggers

    • Trigger: procedure that starts automatically if specified changes occur. Three parts:

    • Event: activates the trigger

      • INSERT, UPDATE, DELETE

      • Can also be DML like CREATE TABLE etc

    • Condition: optional true/false condition

      • Trigger should run only if condition true

    • Action: what happens if the trigger runs

      • One or more SQL statements that will be automatically executed


    Sql standard trigger format

    SQL standard Trigger Format

    CREATE TRIGGER triggername

    [BEFORE|AFTER|INSTEAD OF]

    [INSERT|DELETE|UPDATE[ATTRIBUTE]]

    [REFERENCING] clause

    [WHEN] condition

    action statements

    [FOR EACH ROW|FOR EACH STATEMENT]


    When is action executed

    When is Action Executed

    • AFTER

      • Executes after the event

    • BEFORE

      • Executes before the event

    • INSTEAD OF

      • Executes insteadof the event

        • Note that event does not execute in this case

          • E.g., to update views which otherwise we think of as not being directly updatable


    Statement level vs row level

    Statement-level vs Row-Level

    • SQL statement : INSERT/UPDATE/DELETE multiple rows. Does trigger get executed one time in total or once for each row ?

    • Statement-level triggers

      • FOR EACH STATEMENT : statement-level trigger

      • Execute once for the SQL statement

      • Default

    • Row-level triggers

      • FOR EACH ROW : a row-level trigger

      • When the EVENT happens (Eg: statement executed)

        • trigger executed separately for each affected row


    Condition action

    Condition, Action

    • Any true/false condition to control whether a trigger is activated on not

    • Absence of condition means that the trigger will always execute for the event

    • Otherwise, condition is evaluated. When?

    • Before the event for BEFORE trigger

    • After the event for AFTER trigger

    • Action specifies relevant modifications

      • A sequence of one or more SQL statements


    Trigger example

    Trigger example

    • Eg: If emp salary is reduced by more than 10%, reduce it by only 10% - be kind !

      CREATE TRIGGER UNHTRIGGER

      AFTER UPDATE OF SALARY ON EMP

      REFERENCING OLD AS O, NEW AS N

      WHEN (N. SALARY < 0.9* O. SALARY )

      UPDATE EMP

      SET SALARY = 0.9 * O.SALARY

      WHERE SSN = N.SSN

      FOR EACH ROW;


    Elmasri oracle trigger example

    Elmasri Oracle Trigger Example

    • No triggers in MS Access

    • Oracle Trigger syntax is similar to SQL standard

      • Details in Elmasri

    • Eg: If TotalSal attribute in Dept, want to keep it updated

      • Eg: When new employee added to a Dept, modify Total_sal of the Department to include the new employees salary

      • DNO attribute in Emp allowed to be null

    • This trigger will execute AFTER INSERT ON Employee table

    • It will do the following FOR EACH ROW

      • WHEN NEW.Dno is NOT NULL

      • The trigger will UPDATE the DEPARTMENT table

      • By Setting the new Total_sal to be the sum of

        • old Total_sal and NEW. Salary

        • WHERE the Dno matches the NEW.Dno;


    Elmasri oracle trigger example1

    Elmasri Oracle Trigger Example

    CREATE TRIGGER Total_sal1

    AFTER INSERT ON Employee

    FOR EACH ROW

    WHEN (NEW.Dno is NOT NULL)

    UPDATE DEPARTMENT

    SET Total_sal = Total_sal + NEW. Sal

    WHERE Dno = NEW.Dno;

    Can be CREATE or ALTER

    Can be FOR, AFTER, INSTEAD OF

    Can be INSERT, UPDATE, DELETE

    The condition

    The action

    • Why for each row ? Suppose we were adding 5 new employees, would want to do this for each employee

    • For what other conditions could Total Sal be changed?


    Elmasri oracle trigger example figure 24 2

    Elmasri Oracle Trigger ExampleFigure 24.2


    Another trigger example sks

    Another Trigger Example [SKS]

    • Suppose that instead of allowing negative account balances, the bank deals with overdrafts by

      • setting the account balance to zero

      • creating a loan in the amount of the overdraft

      • giving this loan a loan number identical to the account number of the overdrawn account

    • The condition for executing the trigger is an update to the account relation that results in a negative balance value.


    Trigger example in sql sks

    Trigger Example in SQL [SKS]

    create trigger overdraft-trigger after update on account referencing new row as nrow for each rowwhen nrow.balance < 0begin atomicinsert into borrower(select customer-name, account-number from depositor where nrow.account-number = depositor.account-number); insert into loan values(n.row.account-number, nrow.branch-name, – nrow.balance); update account set balance = 0where account.account-number = nrow.account-numberend


    Triggers in ms sqlserver syntax sks

    Triggers in MS-SQLServer Syntax [SKS]

    create trigger overdraft-trigger onaccountfor updateas if inserted.balance < 0begin insert into borrower(select customer-name,account-numberfrom depositor, inserted where inserted.account-number = depositor.account-number)insert into loan values (inserted.account-number, inserted.branch-name, – inserted.balance)update account set balance = 0 from account, inserted where account.account-number = inserted.account-numberend


    External world actions sks

    External World Actions [SKS]

    • We sometimes require external world actions to be triggered on a database update

      • E.g. re-ordering an item whose quantity in a warehouse has become small, or turning on an alarm light,

    • Triggers cannot be used to directly implement external-world actions, BUT

      • Triggers can be used to record actions-to-be-taken in a separate table

      • Have an external process that repeatedly scans the table, carries out external-world actions and deletes action from table

    • E.g. Suppose a warehouse has the following tables

      • inventory (item, level ): How much of each item is in the warehouse

      • minlevel (item, level ) : What is the minimum desired level of each item

      • reorder (item, amount ): What quantity should we re-order at a time

      • orders (item, amount ) : Orders to be placed (read by external process)


    External world actions sks1

    External World Actions [SKS]

    create trigger reorder-trigger after update of amount on inventory

    referencing old row as orow, new row as nrow

    for each row

    when nrow.level < = (select level

    from minlevel

    where minlevel.item = orow.item)

    and orow.level > (select level

    from minlevel

    where minlevel.item = orow.item)

    begin

    insert into orders

    (select item, amount

    from reorder

    where reorder.item = orow.item)

    end

    • Why orow.level > ?


    Active database triggers applications

    Active Database + Triggers applications

    • Notification when condition occurs

      • Eg: a lot of people ordering a particular book

        • Like the inventory action

    • Enforcing integrity constraints

      • Triggers can be more powerful than constraints

      • Eg: if total salary of a department has become more than 300k, reduce the manager’s salary

    • Maintenance of derived data

      • Eg: Total Sal of department

    • Read-only views modification : INSTEAD OF


    Triggers can be confusing

    Triggers can be confusing

    • One trigger can trigger another

    • Can have recursive triggers

      • Action will again cause trigger to come into play

    • If multiple triggers on same event, not specified in which order to execute

    • Can go into infinite loop. Eg: Elmasri Fig 24.4


    Triggers vs assertions

    Triggers vs Assertions

    • Assertions only check data, while triggers can check data but also do other stuff (Eg: raise salaries)

      • Assertions can be implemented via triggers, but not vice versa

    • Triggers are set off by specific events, while assertions are independent of specific events

      • Have to be checked in general

    • Commercial DBMSs generally support triggers, not assertions

      • Performance reasons


  • Login