Examen 3. Presentacion 6 Flashcards
Semantic Data Control Involves
View management
Security control
Integrity control
Semantic Data Control Objective
Ensure that authorized users perform correct operations on the
database, contributing to the maintenance of the database
integrity.
View – virtual relation
generated from base relation(s) by a query
not stored as base relations
CREATE VIEW SYSAN(ENO,ENAME)
AS SELECT ENO,ENAME
FROM EMP
WHERE TITLE= “Syst. Anal.”
View – virtual relation Example
ELECT ENAME, PNO, RESP
FROM SYSAN, ASG
WHERE SYSAN.ENO = ASG.ENO
Views can be manipulated as base relations
SELECT ENAME, PNO, RESP
FROM SYSAN, ASG
WHERE SYSAN.ENO = ASG.ENO
Queries expressed on views
SELECT ENAME,PNO,RESP
FROM EMP, ASG
WHERE EMP.ENO = ASG.ENO
AND TITLE = “Syst. Anal.”
Queries expressed on base relations
CREATE VIEW ESAME
AS SELECT *
FROM EMP E1, EMP E2
WHERE E1.TITLE = E2.TITLE
AND E1.ENO = USER
To restrict access
CREATE VIEW SYSAN(ENO,ENAME)
AS SELECT ENO,ENAME
FROM EMP
WHERE TITLE=”Syst. Anal.”
Updatable
CREATE VIEW EG(ENAME,RESP)
AS SELECT ENAME,RESP
FROM EMP, ASG
WHERE EMP.ENO=ASG.ENO
Non-updatable
View Management in Distributed DBMS
Views might be derived from fragments.
View definition storage should be treated as database
storage
Query modification results in a distributed query
View evaluations might be costly if base relations are
distributed
Materialized View
Stored as a database relation, possibly with indices
Deferred mode
DDBMS: No need to access remote, base relations
Data warehouse: to speed up OLAP
Materialized View updating
Resembles data replication but there are differences
View expressions typically more complex
Replication configurations more general
When to Refresh a View Modes
Immediate mode, Deferred mode
Deffered Mode times
Lazily: just before evaluating a query on the view
Periodically: every hour, every day, etc
Forcedly: after a number of predefined updates
Deferred mode
Through separate refresh transactions
Triggered at different times with different trade-offs
Immediate mode
As part of the updating transaction, e.g. through 2PC
View always consistent with base data and fast queries
But increased transaction time to update base data
How to Refresh a View
Full computing from base data
Incremental computing by applying only the changes to
the view
Data Security
Data protection
Access control
Data protection
Prevents the physical content of data to be understood by unauthorized users
Uses encryption/decryption techniques
Access control
Only authorized users perform operations they are allowed to on database objects
Types of access control
Discretionary access control (DAC)
Multilevel access control (MAC)
DAC’sMain actors
Subjects (users, groups of users) who execute operations
Operations (in queries or application programs)
Objects, on which operations are performed
Problem with DAC
A malicious user can access unauthorized data through
an authorized user
Multilevel Access Control
Different security levels
Access controlled by 2 rules
A relation can be classified at different levels
A classified relation is thus multilevel
Different security levels
Top Secret > Secret > Confidential > Unclassified
No read up
subject S is allowed to read an object of level L only if level(S) ≥ L
Protect data from unauthorized disclosure, e.g. a subject with secret
clearance cannot read top secret data
No write down
subject S is allowed to write an object of level L only if level(S) ≤ L
Protect data from unauthorized change, e.g. a subject with top secret
clearance can only write top secret data but not secret data (which could
then contain top secret data)
A relation can be classified at different levels
Relation: all tuples have the same clearance
Tuple: every tuple has a clearance
Attribute: every attribute has a clearance
Additional problems in a distributed environment
Remote user authentication
Management of DAC rules
Covert channels in MAC
Semantic Integrity Control
Maintain database consistency by enforcing a set of
constraints defined on the database.
Structural constraints
Basic semantic properties inherent to a data model e.g., unique key constraint in relational model
Behavioral constraints
Regulate application behavior, e.g., dependencies in the
relational model
Two components
Integrity constraint specification
Integrity constraint enforcement
Predefined constraints
Not-null attribute, Unique key, Foreign key, Functional dependency
Precompiled constraints Definition
Express preconditions that must be satisfied by all tuples in a relation for a given update type
Precompiled constraints
Domain constraint, Domain constraint on deletion, Transition constraint
General constraints Definition
Constraints that must always be true. Formulae of tuple
relational calculus where all variables are quantified
General constraints
Functional dependency, Constraint with aggregate function,
