Transcript slides

Theory, Practice & Methodology
of Relational Database
Design and Programming
Copyright © Ellis Cohen 2002-2008
Roles &
Constraints
These slides are licensed under a Creative Commons
Attribution-NonCommercial-ShareAlike 2.5 License.
For more information on how you may use them,
please see http://www.openlineconsult.com/db
1
Overview of Lecture
Role Models and
Application-Based Access Control
Multiple Role Models
Transition Constraints & Post-Conditions
Access Constraints & Pre-Conditions
Characterizing Access Constraints
System Constraints
Behavior Constraints Associated with
Potential State Constraint Violations
© Ellis Cohen 2001-2008
2
Role Models and
Application-Based
Access Control
© Ellis Cohen 2001-2008
3
Super-Roles and Sub-Roles
User Role Diagram
Job Candidate
Known User
Known User is a superrole of Job Candidate
Job Candidate is a
sub-role of User
Means two things (BOTH are important!)
• A Job Candidate is a kind of Known User (but not
every Known User is a Job Candidate)
• Every operation that a Known User is allowed to
perform can also be performed by a Job Candidate
© Ellis Cohen 2001-2008
4
Disjoint Subroles
User Role Diagram
Means that the roles of Job Candidate and
Company Representative do not overlap.
A logged-in user CANNOT play both roles
at the same time!
Company
Representative
Known User
Known User is a super-role
of Company Representative
and Job Candidate
Job Candidate
User Role Diagrams show the roles used in an application
and the relationships among them.
They are a simplification of UML Use Case Diagrams in
which just the actors are shown
© Ellis Cohen 2001-2008
5
Complete Disjoint Subroles
User Role Diagram
Company
Representative
Known User
Every known user is either
a company representative
or a job candidate
Job Candidate
© Ellis Cohen 2001-2008
6
Incomplete Disjoint Subroles
User Role Diagram
Company
Representative
Known User
No known user may be both a
Company Representative and
a Job Candidate
Job Candidate
BUT, there are known users
who are neither
© Ellis Cohen 2001-2008
7
Assignable vs Abstract Roles
Known User is an abstract role. The
application never explicitly assigns this
role to a user, It is just a convenient
place to assign common operations
Company
Representative
Known User
Company Representative & Job Candidate
are assignable roles.
Job Candidate
That is, the application may explicitly assign
a user one of these roles
© Ellis Cohen 2001-2008
8
Static Single Role Model
In a Static Single Role Model
• A logged-in user has a single role
• A user would need to log out and log
back in (as a different user) to change
their role
• If user authentication is implemented
using the database, some table will
likely associate a user and their role
• A session variable, say :currole, can be
used to keep track of the session user's
role
• Operations can implement role-based
pre-conditions by checking the value of
:currole
© Ellis Cohen 2001-2008
9
Role-Based Checking
Suppose only a company representative can get a
list of all active companies.
Here's possible pseudo-code for the corresponding
user query GetActiveCompanies
-- R stands for Company Representative
IF :currole != 'R'
THEN raise an error
SELECT compnam, industry FROM Companies
WHERE status = 'ACTIVE'
ORDER BY compnam
© Ellis Cohen 2001-2008
10
Dynamic Single Role Model
In a Dynamic Single Role Model
• A logged-in user has a single role at a time
• There may be operations which change the user's
role, explicitly, or as a side effect of a change in
state
• If user authentication is implemented using the
database, some table will likely associate a user
and the multiple roles they can play (e.g. as a
UserRoles table, as a rolemask associated with
each user, etc.), and possibly their default initial
role
• The session's current role is still generally
determined by a session variable, say :currole
• Operations can still implement role-based preconditions by checking the value of :currole
© Ellis Cohen 2001-2008
11
Multiple Role Inheritance
Staff
Director
Known
User
Account
User
Broker
A Broker is both Staff and an Account User (in
order to access his client's accounts)
Brokers can execute any operation that either
a Staff or Account User can execute
An Active Client can perform account-specific
operations that a broker can't
© Ellis Cohen 2001-2008
Active
Client
12
Multiple Role Models
© Ellis Cohen 2001-2008
13
Overlapping Roles
Company
Representative
Known
User
Job Candidate
If a user can play more than one assignable role at a
time, then the roles must overlap
In this case, a KnownUsers may be both a Company
Representative and a Job Candidate at the same time and
invoke the operations associated with both roles
© Ellis Cohen 2001-2008
14
Implementing Multiple Role Models
In a Multiple Role Model
• A logged-in user may play multiple roles
• If user authentication is implemented using the
database, some table will likely associate a user
and the multiple roles they can play (e.g. as a
UserRoles table, as a rolemask associated with
each user, etc.)
• The session's current roles (which may be a
subset of the roles the user could play) are
typically implemented by
– A session variable, say :curroles, implemented as a
bitmask, a list, or a temporary table
– Functions, say GetRoles or CheckRole, which
computes/checks it based on the DB state
• Operations can implement role-based preconditions by checking the value of :curroles or
calling GetRoles or CheckRole
© Ellis Cohen 2001-2008
15
Multiple Role Models
A logged-in user may be assigned more
than one role at a time, and can execute
operations associated with any of those
roles (or their super-roles).
Static
A logged-in user is assigned multiple roles when
they log in, and must log out & back in (as a
different user) to be assigned a different set of
roles
Dynamic
There may be operations which change the set of
roles assigned to the current user
Enabled
A logged-in user has a fixed set of roles, but can
explicitly indicate which of those roles are
enabled (or active)
© Ellis Cohen 2001-2008
16
Role Constraints
When roles overlap, there may be
additional constraints about
which combinations of roles a
user can play at the same time.
This must be specified as explicit
role constraints, and
implemented by adding more
information to the tables used for
user authentication
© Ellis Cohen 2001-2008
17
Role Constraint Example
Role A
Role B
Known
User
Role C
Role Constraint
A user can
simultaneously take on
either roles A, B and C,
or roles B and D
or roles A and D
© Ellis Cohen 2001-2008
Role D
18
Single Role Conversion
A multiple role model can be
converted to a single role
model by
– defining a new role for each
allowable combination of roles
– using multiple inheritance to
define these combination roles
© Ellis Cohen 2001-2008
19
Defining Combination Roles
Role A
A/D
Role B
A/B/C
Known
User
Role C
B/D
Role D
© Ellis Cohen 2001-2008
20
Transition
Constraints
& Post-Conditions
© Ellis Cohen 2001-2008
21
Transition Constraint
Characterizes the results to be
produced by specified
operations or changes
+ Transition Constraint:
When a department loses its manager,
the oldest employee in the department
becomes acting department manager
© Ellis Cohen 2001-2008
22
Generalizing Post-Conditions
A Transition Constraint includes
– post-condition
– generalized post-condition
Two ways to generalize post-conditions
• Operation-Dependent
condition for a group of operations, rather than
just a specific one
• Operation-Independent
condition which doesn’t refer to operations at all,
but is just based on database/user/environment
(we'll focus on these)
© Ellis Cohen 2001-2008
23
Generalizing Post-Conditions
Operation:
Remove Me As A Candidate
– removes the current user as a candidate
Post-Condition:
– The job entries of the current user
are also removed
Operation-Independent
Transition Constraint
Whenever a candidate is removed, all the
candidate's job entries are removed as well
Note: this is a lifetime dependency constraint, which can be
implemented using on delete cascade
© Ellis Cohen 2001-2008
24
Transition Constraint Problem
Operation:
Deactivate Company
– deactivates my company
by setting its status to INACTIVE
Post-Conditions:
– Removes all the company's job postings
What's an operation-independent transition
constraint corresponding to this post-condition?
© Ellis Cohen 2001-2008
25
Post-Condition Generalization
Operation:
Deactivate Company
– deactivates my company
by setting its status to INACTIVE
Post-Conditions:
– Removes all the company's job postings
Operation-Independent
Transition Constraint
Whenever a company is deactivated, all the
company's job postings are removed
Note: this cannot be implemented using on delete cascade
© Ellis Cohen 2001-2008
26
Interesting Transition Constraints
Interesting Transition Constraints have one
or more of the following characteristics
• Generalize interesting post-conditions
• Not invariant (i.e. always true); executing
operations makes them true
• Characterize how changes to one entity classes or
relationship changes others in a way that is not
entirely expected or obvious
• Ensure that redundant data is kept consistent (e.g.
derivation)
© Ellis Cohen 2001-2008
27
Access
Constraints
& Pre-Conditions
© Ellis Cohen 2001-2008
28
Access Constraint
Specifies a constraint on what a
user or operation is permitted
to do
+ Access Constraint:
Only an employee, and the employee's
department manager, can see their salary
© Ellis Cohen 2001-2008
29
Generalizing Pre-Conditions
Access Constraints include
– pre-conditions
– generalized pre-conditions
Two ways to generalize pre-conditions
• Operation-Dependent
condition for a group of operations, rather than
just a specific one
• Operation-Independent
condition which doesn’t refer to operations at all,
but is just based on database/user/environment
(we'll focus on these)
© Ellis Cohen 2001-2008
30
Pre-Condition Generalization
Update Job Posting(
:postid, :title, :location, :descr )
– Updates the specified job posting
Pre-Conditions:
– The current user role must be
Company Representative
Operation-Independent
Access Constraint
Job postings can only be updated by
company representatives
© Ellis Cohen 2001-2008
31
Combined Pre-Condition Problem
Update Job Posting(
:postid, :title, :location, :descr )
– Updates the specified job posting
Pre-Conditions:
– The current user role must be
Company Representative
– :postid must identify a posting
by current user's company
What's a single operation-independent
preventive access constraint that addresses
and generalizes both of these pre-conditions?
© Ellis Cohen 2001-2008
32
Combined Pre-Condition Generalization
Update Job Posting(
:postid, :title, :location, :descr )
– Updates the specified job posting
Pre-Conditions:
– The current user role must be
Company Representative
– :postid must identify a posting
by current user's company
Operation-Independent
Access Constraint
A company’s job posting can only be
updated by that company's representative
© Ellis Cohen 2001-2008
33
Types of Transition Constraints
Main Focus
• State-Related Transition Constraints
characterizes resulting changes to
the database state -- e.g. the candidate's
job entries must have all been deleted
• Output-Related Transition Constraints
characterizes resulting properties of the
operation output -- usually situations
under which less information than
expected was displayed
• External Transition Constraints
characterizes resulting changes to the
external environment -- e.g. a message
was sent, etc.
© Ellis Cohen 2001-2008
34
Characterizing
Access Constraints
© Ellis Cohen 2001-2008
35
Access Constraint Uses
An Access Constraint specifies constraints on
what users and operations can do
Access Constraints may be
• Causal
constraints on the kinds of changes or
effects that can be caused
• Informational
constraints of the kinds of data that
can be read
© Ellis Cohen 2001-2008
36
Access Constraint Generalization
Generally
• Pre-conditions on actions 
Causal access constraints
• Pre-conditions on queries 
Informational access constraints
Also, output-related post-conditions on queries
often generalize to informational access constraints
© Ellis Cohen 2001-2008
37
Generalizing Action Pre-conditions 
General Causal Access Constraints
Update Job Posting(
:postid, :title, :location, :descr )
– Updates the specified job posting
Pre-Conditions:
– The current user role must be
Company Representative
– :postid must identify a posting
by the current company
Operation-Independent
Causal Access Constraint
A company’s job posting can only be
updated by that company's representative
© Ellis Cohen 2001-2008
38
Generalizing Query Pre-conditions 
General Informational Access Constraints
ShowJobCandidates( :postid )
– Shows a list of candidates who match the
specified job posted
Pre-Conditions:
– The current user role must be
Company Representative
– :postid must identify a posting
by the current company
Maybe or Maybe Not!
Operation-Independent
Informational Access Constraint
It is not
always
possible to
generalize
preconditions!
A company's representative can only see
the identities of candidates who match a
job the company has posted
© Ellis Cohen 2001-2008
39
Access Constraint Types
• Role-Based User Access Constraints
– based strictly on the current user/client's role
• Identity-Based User Access Constraints
– based on (information tied to the) identity of
the current user (or client)
• Status Access Constraints
– based on the current status of the database or
the environment, but not based on the role or
identity of the user (or client)
Note: soundness pre-conditions cannot be generalized to access constraints
© Ellis Cohen 2001-2008
40
System
Constraints
© Ellis Cohen 2001-2008
41
Kinds of System Constraints
State Constraints
Specifies constraints on the
state of the system
• an invariant property of the database
state (something that must always
be true and must be able to be
checked at ANY ARBITRARY TIME!)
Behavior Constraints
Specifies constraints on the
behavior of the system
• transition constraints
• access constraints
© Ellis Cohen 2001-2008
42
Behavior Constraints
Transition Constraint
Characterizes the results to be
produced by specified operations
or changes
Access Constraint
Specifies constraints on what a
user or operation is permitted to
do
© Ellis Cohen 2001-2008
43
Conceptual & Relational Constraints
System Constraint
Business rule enforced
by an information system
Conceptual Constraint
A system constraint, often written
informally, in terms of a conceptual
model (about entity classes, not tables!)
Relational Constraint
A system constraint written (often more
formally, e.g. using SQL), in terms of a
relational model (i.e. tables)
© Ellis Cohen 2001-2008
44
Example Conceptual Constraints
State Constraints
•
•
Each job entry's endDate must be after its startDate
Every JobEntry MUST have an associated Candidate
and Company (Mandatory Participation Constraint –
should be specified visually instead)
Transition Constraints
•
•
When a candidate is removed, all their job entries
must be deleted (Lifetime Dependency Constraint)
When a company's is deactivated, its status must be
set to INACTIVE, and all its job postings must be
deleted
Access Constraints
•
•
A job candidate's job entries may only be inserted,
updated or removed by that candidate
A company representative may only perform
representative-specific operations if the company's
status is ACTIVE.
What are some example for the Employee Database Application?
© Ellis Cohen 2001-2008
45
Employee Conceptual Constraints
Conceptual State Constraints
No employee may live in California
An employee's manager must work at the same
location that they do (except for employees
who are Dept Mgrs)
Conceptual Transition Constraints
When a department is destroyed, all employees
that were in that department must be
reassigned to a different department.
When a department loses its manager, the new
manager must become the person in the
department who has the highest salary.
Conceptual Access Constraints
An employee's salary may only be changed by
their department manager or the president
© Ellis Cohen 2001-2008
46
What is Constrained?
System constraints are limitations on
the database state/code:
– the state of the DATABASE or
– whether/when the
DATABASE/APPLICTION CODE ensures
that certain side effects do or do not
occur, or
– whether/when the
DATABASE/APPLICATION CODE allows
certain operations to execute or
provide information.
Constraints do not place limits or
requirements on how users must
act when they use the system!
© Ellis Cohen 2001-2008
47
Enforcing Constraints
There are two ways to enforce
constraints in database systems
• Application Enforcement:
Enforce constraints through user operation
code
• Database Enforcement:
Use built-in database features to
automatically enforce constraints in the
data-tier
(column constraints, table constraints,
assertions, triggers, access control & views)
© Ellis Cohen 2001-2008
48
Basis & Value of Conceptual Constraints
Where do conceptual constraints
come from?
– From requirements specifications and
assumed/unstated business
requirements
– From generalizing pre- and postconditions of early drafts of the
operations
Why are they important?
– Inspecting/enforcing them helps
ensure that database applications work
correctly!
© Ellis Cohen 2001-2008
49
Using Constraints vs Conditions
Typically
– Conceptual transition & access constraints are
specified early in the design process, based on
• requirements, or
• working through and generalizing the details of early
drafts of the operations
– Constraints can be mapped to final pre/postconditions, if necessary, when the details of
operations are worked out
It is (more) important to retain the
constraints because
– they probably will not change (or change less)
even as the operations go through many changes
– Constraints most naturally correspond to
database enforcement, while
– Pre/post-conditions correspond to application
enforcement
© Ellis Cohen 2001-2008
50
Constraints in UML
UML can be used to represent both
the
– conceptual model (using PK and DK
notations)
– relational model (using PK and FK
notations)
In either case, constraints can be
written formally using OCL (Object
Constraint Language)
© Ellis Cohen 2001-2008
51
Business Rules
Business Rules are requirements on
the conceptual capabilities of a
database application.
They determine
– Conceptual Model
– User Operations
– Conditions & Constraints
– Performance Requirements
© Ellis Cohen 2001-2008
52
Behavior Constraints
Associated with
Potential State
Constraint Violations
© Ellis Cohen 2001-2008
53
State Constraint Violations
User operations can potentially violate
state constraints.
Behavior constraints describe how a system
can ensure that the state constraint is not
violated
– Access constraints can describe when the
system should be prevented from executing an
operation that would violate the constraint
– Transition constraints can describe how the
system should correct violations
If only the state constraint is specified, the
developer has free rein to decide how to
deal with potential state constraint
violations
© Ellis Cohen 2001-2008
54
Mandatory Child Participation
Employee
works for
Dept
The
Mandatory Child Participation
state constraint requires that an
employee must work for a
department. This constraint can
potentially be violated when
– a new employee is created
– a department is deleted
What behavioral constraints could describe how to deal with
potential violations of this state constraint?
© Ellis Cohen 2001-2008
55
Mandatory Child Participation
Behavior Constraints
• Employee Creation
– Newly created clerks are assigned to the
Training Department
– Newly created clerks must be assigned to a
specified department
• Dept Deletion
– When a department is deleted, all employees in
that department
• are terminated
(Lifetime Dependency Constraint)
• are moved to other departments
• are moved to the Party Department
– [Access Constraint] A department cannot be
deleted if it has employees
© Ellis Cohen 2001-2008
56
Mandatory Parent Participation
Employee
works for
Dept
The
Mandatory Parent Participation
state constraint requires that a department
must have at least one employee. This
constraint can potentially be violated when
– a new department is created
– an employee is deleted
What behavioral constraints could describe how to deal with
potential violations of this state constraint?
© Ellis Cohen 2001-2008
57
Mandatory Parent Participation
Behavior Constraints
• Department Creation
– When a department is created
• a list of provided employees must be
assigned to it
• an employee from the Party department is
assigned to it.
• Employee Deletion
– When the last employee in a
department is removed
• the department is deleted
• an employee from the Party department is
moved into it
– [Access Constraint] The last employee
in a department may not be deleted
(either ever, or perhaps if the Party dept has
fewer than two employees)
© Ellis Cohen 2001-2008
58
Enforcing State Constraints
Any state constraint has related access or
transition constraints, depending upon how the
state constraint is enforced
By Rejection:
Disallow any operation which would violate the
state constraint. This is the default approach, and
corresponds to an access constraint.
By Correction:
Allow the operate to violate the state constraint,
but then execute corrective code (defined by a
transition constraint) which ensures the state
constraint is once again satisfied.
© Ellis Cohen 2001-2008
59