Database Security

1
Database Security
2
DB Security subsystem

• Authentication - ensures that a user is who he or
  she claims to be.
• Authorization - allows the user access to various
  resources based on the user's identity (a.k.a.
  permission, access right, privilege)
• Privacy - DBMS should provide confidentiality

3
Database Security

• Different aspects of database security
• data encryption - encoding, transmission,
  decoding
• allow retrieval of statistical information
• protect individual information (could be deduced
  by smart queries)
• Access control for a whole DBMS
• account numbers and passwords
• Access control for portions of a database
• DB security and authorization subsystems secure
  portions of a DB against unauthorized access (3
  approaches)
• Discretionary Access Control (DAC)
• Mandatory Access Control (MAC)
• Role Base Access Control (RBAC)

4
Discretionary Access Control

• Based on granting and revoking privileges
• 2 levels for assigning privileges
• account level (subject)
• independent of the relations
• create schema, create table, create view
• relation level (object)
• on a particular base relation or view

5
DBA

• DBA is responsible for the overall security of
  the DB system. In particular
• Account creation - access to the whole DBMS
• Privilege granting - DAC
• Privilege revocation - DAC
• Security level assignment - MAC

6
Access (authorization) matrix model

• row - subject
• column - object
• M(i,j) - read, write, update
• for example M(a,B) read means that subject a
  hold a read privilege on object B
• Owner of the relation (typically the creator) is
  assigned the owner account for that relation and
  is given all privileges on that relation

7
Grant SQL statement

• Grant privileges on table view to user
  public role
• Where privileges are
• Select, alter, delete, update, index, references,
  insert, all
• Can specify list of (columns) after privileges
  only for insert, update
• Grant select, delete on Employee, Department
• to Smith

8
To access tables granted permission

• User granted access to table must qualify name of
  that table with owner
• Select
• from jones.Employee
• where dno 4

9
Grant/Revoke

• Revoking privileges
• Revoke privilege on table view from user
  public role
• Revoke delete on Department from Smith

10
Roles

• Rather than grant privileges to individual users,
  can grant them to groups using roles
• Create role role_name identified by pw
• Grant privilege on table to role_name
• Grant role_name to user

11
Example of grant/revoke

• Example U1 issues
• Create table Employee(SSN, Fname, Lname, Salary)
• Propagating/Revoking privileges - horizontal and
  vertical
• Use WITH GRANT OPTION
• U1 can issue the following statements
• Grant select on Employee to A2
• Grant select on Employee to A3 with grant option
  
• Revoke select on Employee from A3

12
Using views for Security

• What if you only want a use to see some of the
  rows from a table?
• Or some of the columns?
• Can use views

13
Relational Views

• A view - a virtual table that is derived from
  other tables (vs. base table)
• A view can be used to simplify frequent queries
• e.g. a join condition
• A view does not necessarily exist in physical
  form
•  There is no limit on querying a view
• (limits on an update to a view)
•  Views are useful for security and authorization
  mechanisms

14
Create view

• CREATE VIEW view_name (col1 , col2)
• AS SELECT col1 , col2
• FROM (table1 view1) , table2 view2
• WHERE search_condition

15
Create View

• View attribute names are inherited from other
  tables
• If aggregate functions are the result of
  arithmetic operations, they must be renamed
• Views can be defined using other views

16
Multiple table views

• To create a view from multiple tables
• Create View Works_on1
• As Select fname, lname, pname, hours
• From Employee, Project, Works_on
• Where ssn essn and pno pnumber

17
Create View

• Create a view to list for each department
  dname, number of employees and total salary paid
  out
• Create View Dept_Info (dept_name, no_of_emps,
  total_sal)
• As Select dname, count(),
  sum(salary)
• From Department, Employee
• Where dnumber dno
• Group By dname

18
Views

• Queries on View - same as queries on base tables
• Retrieve the last and first names of all
  employees who work on ProjectX
• Select lname, fname
• From Works_on1
• Where pname ProjectX 

19
Maintain views

• 2 strategies to maintain views
• View is stored as a temporary table for future
  queries called view materialization
• view is not realized at the time of the view
  definition but when specify the query
• Another strategy is to modify the view query into
  a query on the underlying base table called query
  modification
• DBMS keeps views up to date how?

20
Views

• A view is removed by using the DROP VIEW command.
• Drop View Works_on1
• Drop View Dept_info 

21
Updating views

•  If specify an update to a view, it updates the
  corresponding tables.
• Create View Emp
• As Select fname, lname, ssn, dno
• From Employee
• Update Emp
• Set dno 1
• Where lname English

22
Updating views

• It may not make sense to update some views why?
• Update Dept_info
• Set total_sal 100000
• Where dname Research
• Cannot always guarantee that a view can be updated

23
Views

• When would it not make sense?
• General Rule (true for ORACLE)
• View with one defining table is updatable if
  the view attributes contain a primary key
• Views with more than one table using joins is
  not updatable
• View with aggregate functions are not updatable

24
Using Views for Security

• Create view EMP as select Fname, Lname from
  Employee where salary
• Grant select on EMP to A4

25
Mandatory Access Control

• Users have security clearances, data has
  security classifications
• Need to restrict the access according to security
  level of user and data
• Useful for environments with hierarchical
  propagation of information
• Each subject and object are classified into one
  of the security classifications
• Security classes example
• TS(Top Secret), S (Secret), C(Classified), U
  (Unclassified)
• TS S C U

26
Bell-LaPadulla properties

• Restrictions on data access
• simple property No READ UP
• star () property No WRITE DOWN (write at own
  level)
• necessary but not sufficient conditions
• May still have problems covert channel
• Indirect means by which info at higher levels
  passed to lower levels

27
MLS

• multilevel relation (MLS) schema
• classification attribute C
• tuple classification TC
• Lowest user level that can see tuple
• R(A1, C1, A2, C2, ...An, Cn, TC) Jajodia-Sandhu
  

28
MLS Relation Example

• Vessel(pk) Objective Destination TC
• Micra U Shipping U Moon U U
• Vision U Spying U Saturn U U
• Avenger C Spying C Mars C C
• Logos S Shipping S Venus S S

29
MLS

• Level U sees first 2 tuples
• Level C sees first 3 tuples
• Level S sees all tuples

30
MLS Insert

• What if a U user wants to insert a tuple with
  vessel Avenger?
• If insert another Avenger, will have 2 Avengers
  problems?
• If reject the insert what will happen?

31
MLS Relation

• Vessel Objective Destination TC
• Micra U Shipping U Moon U U
• Vision U Spying U Saturn U U
• Avenger U Shipping U Mars U U
• Avenger C Spying C Mars C C
• Logos S Shipping S Venus S S

32
Issues - problems

• What if a U user wants to insert a tuple with
  vessel Avenger?
• If insert another Avenger, what about the primary
  key? Will have 2 Avengers
• Violates uniquess constraint
• If reject the insert what will happen?
• Covert channel

33
MLS Update

• What if the S level wants to update one of the
  tuples at the U level?
• U cannot see the update
• Replicate the tuple polyinstantiation
• What if U then updates objective?

34
MLS Relation

• Vessel Objective Destination TC
• Micra U Shipping U Moon U U
• Vision U Spying U Saturn U U
• Avenger U Shipping U Moon U U
• Avenger C Spying C Mars C C
• Logos S Shipping S Venus S S
• Vision U Spying U Venus S S

35
Issues

• Entity integrity
• What is the primary key?
• Updates
• Insert, delete update

36
MLS Model

• apparent key
• user defined primary key
• PK Classification
• polyinstantiation
• more than one tuple with the same apparent key
  value but different attribute values for users at
  different classification levels
• - entity (tuple) polyinstantiation
• - attribute (element) polyinstantiation

37
Covert Chanel

• Indirect downward flow of information
• must be avoided since it allows downward flow of
  information
• Can occur of reject update
• Can be used maliciously (higher level user can
  signal lower level user)

38
Jajodia Sandhu MLS Model

• Entity integrity rule
• all attributes that are members of the apparent
  key must not be null and must have the same
  security classification within each individual
  tuple
• Null integrity
• Nulls are classified at the level of the key
• One tuple does not subsume another (null values
  subsumed by non-null values)
• Inter-Instance Integrity
• User can only see portion of relation for which
  is cleared (use filters)
• Data not cleared is set to null
• Eliminate subsumed tuples

39
Jajodia Sandhu MLS Model

• S-user view
• Vessel Objective Destination TC
• Enterprise U Exploration U Talos U
  U
• Voyager U Spying S Mars S S
• U-user view
• Vessel Objective Destination TC
• Enterprise U Exploration U Talos U
  U
• Voyager U Null U Null U U

40
Jajodia Sandhu MLS Model

• Polyinstantiation Integrity
• AK, CAK, Ci - Ai
• Implies Primary key in MLS is
• AK U CAK U CR
• AK are data in PK, CAK is class of PK data, CR is
  data not in AK

41
Examples of Polyinstantiation Integrity

• Legal instance
• Vessel Objective Destination TC
• Enterprise U Exploration U Talos U
  U
• Voyager U Exploration U Talos U U
• Voyager U Spying S Mars S S
• Illegal instance
• Vessel Objective Destination TC
• Voyager U Exploration S Talos S S
• Voyager U Spying S Mars S S

42
Jajodia Sandhu MLS Model

• Suppose S-user updates Enterprise destination to
  Rigel
• Vessel Objective Destination TC
• Enterprise U Exploration U null U
  U
• Enterprise U Exploration U Rigel S
  S
• What is view to U-user? S-user?

43
Insert

• Suppose S wants to insert (Enterprise, Spying,
  Rigel) into
• Vessel Objective Destination TC
• Enterprise U Exploration U Talos U
  U
• Can this insert be rejected?
• What if reversed?

44
Update

• U view
• Vessel Objective Destination TC
• Enterprise U Exploration U null U
  U
• S view
• Vessel Objective Destination TC
• Enterprise U Exploration U Rigel S
  S
• U user wants to update, set Destination Talos
  where Starship Enterprise

45
Update

• U view
• Vessel Objective Destination TC
• Enterprise U Exploration U Talos U
  U
• S View
• Vessel Objective Destination TC
• Enterprise U Exploration U Talos U
  U
• Enterprise U Exploration U Rigel S
  S
• Suppose S users want to update, set
  objectivespying where starship Enterprise
  and destination Rigel

46
Update

• Vessel Objective Destination TC
• Enterprise U Exploration U Talos U
  U
• Enterprise U Spying S Rigel S S
• What if S user set objectivespying where
  starshipEnterprise
• Vessel Objective Destination TC
• Enterprise U Exploration U Talos U
  U
• Enterprise U Spying S Talos U S
• Enterprise U Spying S Rigel S S

47
Winslett Smith Belief Model

• Tuples at users level believe info
• See info at lower levels
• R(K, KC, A1, A2, ... An, TC)  Smith-Winslett
• Every tuple has a base tuple level at which
  first inserted
• Relational operations still messy

48
MLR Model Sandhu Chen

• Try to eliminate semantic ambiguity
• Borrowing to indicate belief in lower level
  tuples
• Does it mean T or F?
• Cannot indicate disbelief

49
Extensions to MLS model

• Belief consistent model (Jukic-Vrbsky)
• Can easily see what others believe at lower
  levels
• Can assert if one level believes lower level
  belief is false
• Reduces tuple propagation
• Can even have a cover story for a PK
• Useful for e-Business and Customer Relationship
  Modeling (CRM).

50
BC-MLS relational model

• Expanded set of labels - belief-consistent labels
  Example
• Levels with - preceeding it indicate false
• Levels that follow with indicate true
• Can label an attribute as True, False or not
  verified on a classification level
• Introduce Belief Property - e.g. if believe a
  particular value is True, then a different value
  for that same attribute must be False
• Include asserting beliefs, such as verifyTrue or
  verifyFalse

51
BC-MLS relational model

• Redefine Integrity Properties  Entity,
  Polyinstantiation, Base Tuple, Foreign Key,
  Referential Integrity Properties
• Define belief-consistent relational algebra
  operations and update/delete operations
• Non-key related cover stories
• E-commerce

52
Roles

• Role-based access control (RBAC)
• Sandhu, R., Coyne, Feinstein, Youman Role-Based
  Access Control Models http//csrc.nist.gov/rbac/
  sandhu96.pdf
• Semantic construct
• System administrator creates roles according to
  job functions
• Role
• Specific task competency, duty assignments
• Embody authority and responsibility
• Grant permissions to users to these roles
• Users permissions, Permissions roles

53
Motivation

• Many organizations
• Base access control in role of individual users
• Want to centrally control and maintain access
  rights
• Access control needs are unique
• commercially available products lack flexibility

54
Motivation

• Roles define individuals and extent of resource
  access
• Combination of users and permissions can change
• E.g. user membership in roles
• Permissions associated with roles stable
• Administration of roles rather than permissions
• Role permission predefined
• Easier to add/remove users membership than create
  new roles/permissions
• Roles part of SQL3
• Supported by many software products
• Roles used in Windows NT, XP (system admin)

55
RBAC basics

• Access control in RBAC exists in
• Role-permission
• User-role
• Role-role relationships
• RBAC supports principles
• Least privilege
• Separation of duties- mutually exclusive roles
• Data abstraction- abstract permissions (not just
  R/W)
• Limitations
• RBAC cannot enforce way principles applied
  system admin could configure to violate

56
Groups vs. roles

• Groups a collection of users, but not a
  collection of permissions
• Roles a collection of users and permissions
• Example Unix group membership defined in
• /etc/passwd and /etc/group
• Permission bits set for files and directories
• To determine permissions for a group requires
  traversing entire file system tree
• Unix groups can implement roles

57
DAC, MAC vs. RBAC

• DAC vs. MAC emerged from defense security
  research
• RBAC independent of access control
• RBAC can be used to implement DAC, MAC

58
RBAC models

• Authors proposed four conceptual models
• RBAC0
• Minimum for RBAC
• RBAC1
• RBAC0 plus role hierarchies
• RBAC2
• RBAC0 plus constraints
• RBAC3
• RBAC1 plus RBAC2

59
RBAC models
60
RBAC models
61
RBAC definitions

• Permission
• Can be access to entire subnetwork or record in
  table
• Example user assignment, permission assignment
  tables can be Many-many
• User/roles, roles/permissions
• Role provides greater control between users and
  permissions

62
Sessions

• Users establish sessions during which activate
  a subset of roles
• Each session associated with a single user
• User can have multiple sessions open
• User determines which roles activated in any
  session

63
Role hierarchies

• More powerful roles at top, less powerful on
  bottom
• Inherits up
• Inheritance transitive
• Multiple inheritance
• Partial order - reflexive, transitive,
  antisymmetric
• Can create private roles not inherited

64
(No Transcript)
65
Constraints

• Mutually exclusive roles
• User at most 1 role in ME set
• Combinations of roles and permissions can be
  prohibited
• Cardinality
• Maximum number of members in a role
• Minimum cardinality difficult to implement
• Prerequisite role
• User assigned to role B, only if assigned to A
• Permission p assigned to role only if role has
  permission q

66
In Oracle

• Rather than grant privileges to individual users,
  can grant them to groups using roles
• Create role role_name identified by pw
• Grant privilege on table to role_name
• Grant role_name to user