introduction to db security
Download
Skip this Video
Download Presentation
Introduction to DB Security

Loading in 2 Seconds...

play fullscreen
1 / 59

Introduction to DB Security - PowerPoint PPT Presentation


  • 54 Views
  • Uploaded on

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.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' Introduction to DB Security' - rolf


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?
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 atomic insert 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 = 0 where 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
ad