|
These examples are based on the (sample)
Travel Agency application. For reasons of simplicity, only two of
the end-user roles are used here, the FRONT-OFFICE and the
GUIDES.
Front-office personnel take
reservations from customers. Taking a reservation involves entering
details of the person booking, all the appropriate information
about the reservation itself, such as the scheduled tour the
reservation is for, and details of all booked participants. In
addition, front-office personnel change reservations on customer
request, and inform people about available tours, for example, when
they talk on the telephone to a customer who does not have a
catalogue.
Guides are native residents of
the destination country of scheduled tours, and speak the local
language. They have a remote connection to the Travel Agency
headquarters database, in order to view their schedule. This
schedule includes details of scheduled tours they have been planned
to guide, as well as details of all participants of those scheduled
tours. Guides do not see reservation details such as discount,
price and status data. Guides do not manipulate system data: they
only use issue queries.
It is best to translate your
organization's functional model into a matrix for each user-group.
In this matrix, you plot the right, the scope and the condition
against the tables and the columns, as shown in the following
tables.
|
User-group Front Office
|
|
Tables
|
Right
|
Scope
|
Condition
|
|
TOUR
|
Select
|
Fg+Bg
|
|
|
SCHEDTOUR
|
Select
|
Fg+Bg
|
|
|
RESERVATION
|
All
|
Fg+Bg
|
|
|
PERSON
|
Select
|
Fg+Bg
|
|
|
PERSON
|
Delete
|
Fg+Bg
|
guide = 'N' OR staff = 'N'
|
|
PERSON
|
Update
|
Fg+Bg
|
|
|
PERSON.GUIDE
|
Update
|
None
|
|
|
PERSON.STAFF
|
Update
|
None
|
|
|
PERSON
|
Insert
|
Fg+Bg
|
guide = 'N' OR staff = 'N'
|
|
GUIDE
|
Select
|
Fg+Bg
|
|
|
STAFF
|
Select
|
Fg+Bg
|
|
|
PARTICIPANT
|
All
|
Fg+Bg
|
|
|
User-group Guide
|
|
Tables
|
Right
|
Scope
|
Condition
|
|
TOUR
|
Select
|
Fg+Bg
|
|
|
SCHEDTOUR
|
Select
|
Fg+Bg
|
EXISTS
(SELECT 'x'
FROM guide
WHERE guide.person_id = schedtour.guide
AND guide.db_user = USER)
|
|
PARTICIPANT
|
Select
|
Fg+Bg
|
EXISTS
(SELECT 'x'
FROM reservation r, schedtour s, guide g
WHERE r.res_id = participant.res_id
AND s.schedtour_id = r.schedtour_id
AND g.person_id = s.guide
AND g.db_user = USER)
|
|
PERSON
|
Select
|
Fg+Bg
|
EXISTS
(SELECT 'x'
FROM participant p, reservation r, schedtour s, guide g
WHERE
(p.person_id= person.person_id AND r.res_id = p.res_id
AND s.schedtour_id = r.schedtour_id
AND g.person_id = s.guide
AND g.db_user = USER)
OR
(p.person_id = g.person_id
AND g.db_user = USER))
|
|
PERSON.
AREA_CODE
|
None
|
|
|
|
PERSON.
DISCOUNT
|
None
|
|
|
|
PERSON.
GENDER
|
None
|
|
|
|
RESERVATION
|
Select
|
Fg+Bg
|
EXISTS
(SELECT 'x' FORM schedtour s, guide g
WHERE g.db_user = USER
AND g.person_id = s.guide
AND reservation.schedtour_id = s.schedtour_id)
|
|
RESERVATION.
BOOK_DATE
|
Select
|
None
|
|
|
RESERVATION.
CANCEL_DATE
|
Select
|
None
|
|
|
RESERVATION.
DEALT_WITH_BY
|
Select
|
None
|
|
|
RESERVATION.
FAMILY_NAME
|
Select
|
None
|
|
|
RESERVATION.
GROUP_
DISCOUNT
|
Select
|
None
|
|
|
RESERVATION.
PAYMENT
|
Select
|
None
|
|
|
RESERVATION.
PERSON_
DISCOUNT
|
Select
|
None
|
|
|
RESERVATION.
PRICE
|
Select
|
None
|
|
|
RESERVATION.
RETURN_DATE
|
Select
|
None
|
|
|
RESERVATION.
START_DATE
|
Select
|
None
|
|
|
RESERVATION.
MADE_BY
|
Select
|
None
|
|
|
RESERVATION.
STATUS
|
Select
|
None
|
|
|
GUIDE
|
Select
|
Fg+Bg
|
db_user = USER
|
NOTE 1:
Conditions specified in the Authorizer are added to the query
executed to retrieve records in the data windows. That is why you
need to add foreground and background select rights on columns as
part of the Authorizer condition.
NOTE 2:
In order to restrict a guide's access to their own data, a
column has been added to the GUIDE table containing the guide's
database user name.
|
