Distributed Database System

Data And Access Control 1/2

• Involves:
• View management
• Security control
• Integrity control
• Objective :
• Ensure that authorized users perform correct operations on the
database, contributing to the maintenance of the database
integrity.
Data And Access Control 2/2
• In RDBMS, data and access control can be achieved in a uniform way
• views, security constraints, and integrity constraints can be defined as rules
that the system automatically enforces
• The basic tasks of data and access control in DDBMS are similar to the
ones in centralized database systems
• In addition to the centralized case each task must consider the
• fragmentation of relations
• distribution of fragments across multiples sites
Database Schemas (1)
• Databases are often represented through the three-schema
architecture or ANSI-SPARC architecture.
• The goal of this architecture is to separate the user application from
the physical database.
• The three levels are −
• Internal Level having Internal Schema
• Conceptual Level having Conceptual Schema
• External or View Level having External Schemas or Views
Database Schemas (2)
• Internal Level having Internal Schema
• It describes the physical structure, details of internal storage and access paths
for the database.
• Conceptual Level having Conceptual Schema
• It describes the structure of the whole database while hiding the details of
physical storage of data.
• This illustrates the entities, attributes with their data types and constraints,
user operations and relationships.
• External or View Level having External Schemas or Views
• It describes the portion of a database relevant to a particular user or a group
of users while hiding the rest of database.
• VIEWS are not stored in DBMS but generated on demand
View
• Virtual table created using Create View command
• Views are virtual relations that are defined as the result of a query on base
relations (real relations)
• Views are very useful for ensuring data security in a simple way
• By selecting a subset of the database, views hide some data
• Users cannot see the hidden data
• It is not that the View is precomputed and stored on the disk instead, a
View is computed each time it is used or accessed.
• Whenever a view is used the query expression in Create View command is
executed at that particular moment.
• Hence, you always get the updated data in a View.
View Management in Centralized Database 1/6
• A view is a relation that is derived from a base relation as a result of a
relational query
• Example: The view of system analysts derived from relation EMP

CREATE VIEW SYSAN(ENO,ENAME) AS SELECT ENO,ENAME FROM EMP WHERE

TITLE=“Syst. Anal.”
View Management in Centralized Database 2/6
• Queries expressed on views are translated into queries
expressed on base relations
• Example: “Find the names of all the system analysts with
their project number and responsibility”
SELECT ENAME, PNO, RESP
FROM SYSAN, ASG
WHERE SYSN.ENO = ASG.ENO
View Management in Centralized Database 3/6
SELECT ENAME, PNO, RESP
FROM SYSAN, ASG
WHERE SYSN.ENO = ASG.ENO
is translated into
SELECT ENAME,PNO,RESP
FROM EMP, ASG
WHERE EMP.ENO = ASG.ENO
AND TITLE = “Syst. Anal.”

• Automatic query modification is required, i.e. ANDing query qualification

with view qualification
View Management in Centralized Database 4/6
• All views can be queried as base relations, but not all view can be updated
as such
• Updates through views can be handled automatically only if they can be propagated
correctly to the base relations
• We classify views as:
• updatable or
• non-updatable
• Current systems are very restrictive about supporting updates through
views
• Views can be updated only if they are derived from a single relation by selection and
projection
• However, it is theoretically possible to automatically support updates of a larger class
of views, e.g., joins
View Management in Centralized Database 5/6
• Updatable view: The updates to the view can be propagated to the
base relations without ambiguity.
CREATE VIEW SYSAN(ENO, ENAME) AS
SELECT ENO, ENAME
FROM EMP
WHERE TITLE = 'Syst. Anal.'
• e.g, insertion of tuple (201,’Smith’) can be mapped into the insertion of a new
employee (201, ’Smith’, ’Syst. Anal.’)
• If attributes other than TITLE were hidden by the view, they would be
assigned the value null
View Management in Centralized Database 6/6
• Non-updatable view: The updates to the view cannot be propagated
to the base relations without ambiguity.
CREATE VIEW EG(ENAME, RESP) AS
SELECT DISTINCT ENAME, RESP
FROM EMP, ASG
WHERE EMP.ENO = ASG.ENO
• e.g, deletion of (’Smith’, ’Analyst’) is ambiguous, since the deletion of ’Smith’
in EMP and the deletion of ’Analyst’ in ASG are both meaningful.
Updatable Views in Oracle
• In Oracle a view is not updatable if the defining query expression
contains any of the following constructs:
• A set operator
• A DISTINCT operator
• An aggregate or analytic function
• A GROUP BY clause
• Recursive views
• Expressions is used in the view definition
• A subquery in a SELECT list
• Joins except if tables are key preserved (see below) a key-preserved table has
its key columns preserved through a SQL join) and one base table tuple
appears at most once in the view.
View Management in Distributed Database 1/4
• Definition of views in DDBMS is similar as in centralized DBMS
• However, a view in a DDBMS may be derived from fragmented relations
stored at different sites
• Views definitions might be centralized at one site, partially
replicated, fully replicated
• In any case the information associating a view name to its definition
site should be replicated.
• The mapping of query expressed on view into query expressed on
base relations can also be done in same way as in centralized system
(through query modification – later chapter)
View Management in Distributed Database 2/4
• Views derived from distributed relations may be costly to evaluate as
it is likely that many users access the same view which must be
recomputed for each user
• Materialized view is the physical copy of the original base tables
(snapshot or picture of the original base tables)
• Unlike view, materialized view are precomputed and stored on a disk
like an object and not updated each time they are used.
• Need to be updated manually or with the help of triggers
View Management in Distributed Database 3/4
View Management in Distributed Database 4/4
• Materialized views in data warehouses typically involve aggregate (such as
SUM and COUNT) and grouping (GROUP BY) operators because they
provide compact database summaries.
• Today, all major database products support materialized views.
• Example:
• The following view over relation PROJ(PNO,PNAME,BUDGET,LOC) gives, for
each location, the number of projects and the total budget.
CREATE VIEW PL(LOC, NBPROJ, TBUDGET)
AS SELECT LOC, COUNT( * ),SUM(BUDGET)
FROM PROJ
GROUP BY LOC
Data Security
• Data security protects data against unauthorized access and has two
aspects:
• Data protection
• Authorization (Access) control
• Data protection is required to prevent unauthorized users from
understanding the physical content of data.
• Well established standards exist
• Data encryption standard
• Public-key encryption schemes
• Authorization control must guarantee that only authorized users perform
operations they are allowed to perform on the database.
Authorization Control 1/4
• Three actors are involved in authorization
• users, who trigger the execution of application programs
• operations, which are embedded in applications programs
• database objects, on which the operations are performed
• Authorization control consists of checking whether a given triple
(user, operation type, object) can be allowed to proceed.
• Introduction of a user in a system is typically done by a pair (username and
password)
• In relational system objects can be defined by their type (view, relation, tuple,
attribute) as well as their content using selection predicates
• In an SQL based relational DBMS an operation is a high-level statement such
as SELECT, INSERT, UPDATE or DELETE
Authorization Control 2/4
• GRANT and REVOKE statements are used to authorize triplets (user,
operation, data object)
• GRANT <operations> ON <object> TO <users>
• REVOKE <operations> ON <object> TO <users>
• Examples:
• GRANT CREATE ANY INDEX TO Robert;
• REVOKE ALL PRIVILEGES FROM Robert;
• GRANT SELECT ON emp TO Tom;
• Typically, the creator of objects gets all permissions
• Might have the permission to GRANT permissions
• This requires a recursive revoke process
Authorization Control 3/4
• Oracle offers about 200 privileges:
Authorization Control 4/4
• Privileges are stored in the directory/catalogue, conceptually as a
matrix

• Roles can be used to group privileges.

Distributed Authorization Control 1/2
• Additional problems of authorization control in a distributed
environment stem from the fact that objects and subjects are
distributed:
• remote user authentication
• management of access rules
• handling of views and of user groups
• Remote user authentication is necessary since any site of a DDBMS
may accept programs initiated and authorized at remote sites
Distributed Authorization Control 2/2
Two solutions are possible:
• (username, password) is replicated at all sites and are communicated
between the sites whenever a site modifies user information
• beneficial if the users move from a site to a site
• (username, password) is not replicated; all sites of the DDBMS identify and
authenticate themselves similarly as users do
• Inter-site communication is protected by the use of the site password;
• (username, password) is authorized by application at the start of the session;
• no remote user authentication is required for accessing remote relations once the
start site has been authenticated
• beneficial if users are static (i.e., a user accesses DDB from the same site always)
Integrity Constraints 1/2
• Another important and difficult problem for a database system is how
to guarantee database consistency
• A database is consistent if it satisfies a set of constraints, called
integrity constraints
• Integrity control ensures database consistency by:
• rejecting update transactions that lead to inconsistent database states
• or by activating specific actions on the database state which compensate for
the effects of the update instructions.
Integrity Constraints 2/2
Two main types of integrity constraints can be distinguished:
• Structural constraints:
• basic semantic properties inherent to a data model
• e.g., unique key constraint in relational model, or one-to-many associations between
objects in the object-oriented model.
• Behavioral constraints:
• regulate application behavior.
• They can express associations between objects, such as inclusion dependency in the
relational model, or describe object properties and structures
• An integrity control system has 2 components:
• Integrity constraint specification
• Integrity constraint enforcement
Integrity Constraint Specification 1/5
• In RDBMS, integrity constraints are defined as assertions, i.e.,
expression in tuple relational calculus
• 3 types of integrity constraints/assertions are distinguished:
• predefined
• precompiled
• general constraints
Integrity Constraint Specification 2/5
• In the following examples we use the following relations:
EMP(ENO, ENAME, TITLE)
PROJ(PNO, PNAME, BUDGET)
ASG(ENO, PNO, RESP, DUR)
Integrity Constraint Specification 3/5
• Predefined constraints are based on simple keywords and specify the more
common constraints of the relational model
• Not-null attribute:
• e.g., Employee number in EMP cannot be null
ENO NOT NULL IN EMP
• Unique key:
• e.g., ENO is the unique key in EMP
ENO UNIQUE IN EMP
• Foreign key:
• e.g., PNO in ASG is a foreign key matching the primary key PNO in PROJ
PNO IN ASG REFERENCES PNO IN PROJ
• Functional dependency:
• e.g., employee number functionally determines the employee name
ENO IN EMP DETERMINES ENAME
Integrity Constraint Specification 4/5
• Precompiled constraints express preconditions that must be satisfied by all
tuples in a relation for a given update type
• General form:
CHECK ON <relation> [WHEN <Update Type>] <qualification>
• Domain constraint, e.g., constrain the budget:
CHECK ON PROJ ( BUDGET > 500000 AND BUDGET <= 1000000 )
• Domain constraint on deletion, e.g., only tuples with budget 0 can be deleted:
CHECK ON PROJ WHEN DELETE ( BUDGET = 0 )
• Transition constraint, e.g., a budget can only increase:
CHECK ON PROJ ( NEW.BUDGET > OLD.BUDGET AND NEW.PNO = OLD.PNO )
• OLD and NEW are implicitly defined variables to identify the tuples that are
subject to update
Integrity Constraint Specification 5/5
• General constraints may involve more than one relation
• General form:
CHECK ON <variable>:<relation> (<qualification>)
• Functional dependency:
CHECK ON e1:EMP, e2:EMP
( e1.ENAME = e2.ENAME IF e1.ENO = e2.ENO )
• Constraint with aggregate function:
e.g., The total duration for all employees in the CAD project is less than 100
CHECK ON g:ASG, j:PROJ
( SUM(g.DUR WHERE g.PNO = j.PNO) < 100 IF j.PNAME = 'CAD/CAM' )
Integrity Constraints in SQL (1/2)
• unique, not null, primary key, foreign key, check
CREATE TABLE customer (
SSN INT(10),
CustName CHAR(20) UNIQUE,
CustStreet CHAR(30),
CustCity CHAR(30) NOT NULL,
CustCntry CHAR(3),
PRIMARY KEY (SSN),
FOREIGN KEY (CustCntry) REFERENCES country,
CHECK (CustCntry IN ('US','CA'))
)
Integrity Constraints in SQL (2/2)
• General assertion have not been implemented until now:
CREATE ASSERTION sum_constraint CHECK (
NOT EXISTS (
SELECT *
FROM branch
WHERE ( SELECT SUM(amount)
FROM loan
WHERE loan.BName = branch.BName )
>=
( SELECT SUM(amount)
FROM account
WHERE loan.BName = branch.BName )))
Integrity Constraints Enforcement 1/2
• Enforcing integrity constraints consists of rejecting update programs
that violate some integrity constraints
• Two methods to enforce integrity constraints:
• Detection of inconsistencies
• Prevention of inconsistencies
Integrity Constraints Enforcement 2/2
• Example: Consider a query for increasing the budget of CAD/CAM projects
by 10%:
UPDATE PROJ
SET BUDGET = BUDGET * 1.1
WHERE PNAME = 'CAD/CAM'
and the domain constraint
CHECK ON PROJ (BUDGET > 50K AND BUDGET < 100K)
• The query modification algorithm transforms the query into:
UPDATE PROJ
SET BUDGET = BUDGET * 1.1
WHERE PNAME = 'CAD/CAM'
AND NEW.BUDGET > 50K
AND NEW.BUDGET < 100K