Chapter 9: The Client/Server Database Environment

quicksandwalleyeInternet και Εφαρμογές Web

31 Οκτ 2013 (πριν από 3 χρόνια και 7 μήνες)

65 εμφανίσεις

© 2007 by Prentice Hall

1

Chapter 9:

The Client/Server Database
Environment

(p.368
-
376)

Chapter 12:

Data Security

(p.495
-
504)

Chapter 9

© 2007 by Prentice Hall

2

Objectives


Three application components:
presentation, processing, and storage


Distinguish between file server, database
server, 3
-
tier approaches



Securing data: Authorization Rules

Chapter 9

© 2007 by Prentice Hall

3

Client/Server Systems


Networked computing model


Processes distributed between clients and
servers


Client

Workstation (usually a PC) that
requests and uses a service


Server

Computer (PC/mini/mainframe) that
provides a service


For DBMS, server is a database server


Chapter 9

© 2007 by Prentice Hall

4

Application Logic in Client/Server Systems

GUI Interface

Procedures, functions,

programs

DBMS activities

Processing Logic


I/O processing


Business rules


Data management

Storage Logic


Data storage/retrieval

Presentation Logic


Input

keyboard/mouse


Output

monitor/printer

Chapter 9

© 2007 by Prentice Hall

5

Client/Server Architectures


1. File Server Architecture


2. Two
-
tier Database Server Architecture


3. Three
-
tier Architecture

Client does
extensive processing

Client does little
processing

Chapter 9

© 2007 by Prentice Hall

6

Figure 9
-
2 File Server Architecture

FAT CLIENT

Chapter 9

© 2007 by Prentice Hall

7

1. File Server Architecture


All processing is done at the PC that requested
the data


Entire files are transferred from the server to the
client for processing


Problems:


1. High network traffic load


Huge amount of data transfer on the network


2. Powerful clients needed


Each client must contain full DBMS


3. Manage the shared database integrity


Client DBMSs must recognize shared locks, integrity checks,
etc.


FAT CLIENT

Chapter 9

© 2007 by Prentice Hall

8

Figure 9
-
3 Two
-
tier database server architecture

Thinner
clients

DBMS only
on server

Chapter 9

© 2007 by Prentice Hall

9

2. Two
-
Tier Database Server
Architectures


Front
-
end program: Client is
responsible for


I/O processing logic


Some business rules logic


Back
-
end program: Server performs all
data storage and access processing




DBMS is only on server

Chapter 9

© 2007 by Prentice Hall

10

Advantages of Two
-
Tier Approach


Clients do not have to be as powerful


Greatly reduces data traffic on the network


Improved data integrity since it is all
processed centrally


Stored procedures



DBMS code that
performs some business rules done on
server

Chapter 9

© 2007 by Prentice Hall

11

Figure 9
-
4 Three
-
tier architecture

Thinnest
clients

Business rules
on separate
server

DBMS only on
DB server

Chapter 9

© 2007 by Prentice Hall

12

3. Three
-
Tier Architectures


A client
-
server configuration


Also called: n
-
tier, multi
-
tier, or enhanced
client/server architecture


Three layers: a client layer and two server
layers


Usually store application programs on the
2
nd

layer


Popular for Internet applications

Chapter 9

© 2007 by Prentice Hall

13

Three
-
Tier Architectures

Thin Client



PC just for user interface and a little application
processing. Limited or no data storage (sometimes no
hard drive)

GUI interface

(I/O processing)

Browser

Business rules

Web Server

Data storage

DBMS

Client

Application server

Database server

Chapter 9

© 2007 by Prentice Hall

14

Advantages of Three
-
tier
Client/Server Architecture


Scalability


The ability to upgrade a system without a redesign


Technological flexibility


Easier to change the DBMS engines, move the middle tier to a different
platform, or to implement various interfaces


Long
-
term cost reduction


Use of off
-
the
-
shelf components for the middle tier


Improved customer service


Multiple interfaces to different clients


Ability to change and implement small modules of codes


To better match the system to business needs


To improve competitive advantage


To reduce risk


Chapter 9

© 2007 by Prentice Hall

15

Client/Server Security


Network environment


complex security
issues


Security levels:


System
-
level password security


for allowing access to the system


Secure client/server communication


Via encryption


Database
-
level password security


for determining access privileges to tables;
read/update/insert/delete privileges

Chapter 9

© 2007 by Prentice Hall

16

Securing Data at the Database
Level


Chapter 9

© 2007 by Prentice Hall

17

Threats to Data Security


Accidental losses attributable to:


Human error


Software failure


Hardware failure


Theft and fraud


Improper data access:


Loss of privacy (personal data)


Loss of confidentiality (corporate data)


Loss of data integrity


Loss of availability

Chapter 9

© 2007 by Prentice Hall

18

Database Security and the DBA


The database administrator (
DBA
)


the central authority for managing a database
system.


responsible for the overall security of the
database system.


The DBA’s responsibilities


granting privileges to users who need to use the
system


classifying users and data in accordance with the
policy of the organization

Chapter 9

© 2007 by Prentice Hall

19

Figure 12
-
5 Authorization matrix

Chapter 9

© 2007 by Prentice Hall

20

Authorization Rules


Controls incorporated in the data management
system



Restrict:


access to data


actions that people can take on data



Authorization matrix for:


Subjects


Objects


Actions


Constraints


Chapter 9

© 2007 by Prentice Hall

21

Actions: Privileges at Account Level


CREATE SCHEMA

or
CREATE TABLE

privilege, to
create a schema or base relation;


CREATE VIEW

privilege;


ALTER

privilege, to apply schema changes such
adding or removing attributes from relations;


DROP

privilege, to delete relations or views;


MODIFY

privilege, to insert, delete, or update tuples;


SELECT

privilege, to retrieve information from the
database by using a
SELECT

query.


Chapter 9

© 2007 by Prentice Hall

22

Figure 12
-
6a Authorization table for subjects (salespeople)

Figure 12
-
6b Authorization table for objects (orders)

Figure 12
-
7 Oracle privileges

Implementing
authorization
rules

Chapter 9

© 2007 by Prentice Hall

23

An Example


Suppose that the DBA creates four accounts


A1, A2, A3, A4


A1: able to create base relations.

GRANT

CREATETAB TO A1;


In SQL2, having the DBA issue a
CREATE
SCHEMA

command as follows:


CREATE

SCHAMA

EXAMPLE
AUTHORIZATION

A1;

Chapter 9

© 2007 by Prentice Hall

24

An Example(2)


A1
creates

the two base relations
EMPLOYEE

and
DEPARTMENT


A1 is then
owner

of these two relations and hence
all the relation privileges

on each of them.



Suppose that A1 wants to grant A2 the privilege
to insert and delete tuples in both of these
relations:


GRANT INSERT, DELETE ON



EMPLOYEE, DEPARTMENT

TO A2;

Chapter 9

© 2007 by Prentice Hall

25

Schema for Examples

Chapter 9

© 2007 by Prentice Hall

26

An Example (3)


A1 wants to allow A3 to retrieve information
from either of the two tables;


A1 can issue the command:


GRANT SELECT ON
EMPLOYEE,
DEPARTMENT

TO
A3
;


Chapter 9

© 2007 by Prentice Hall

27

An Example (4)


A1 decides to revoke the SELECT privilege
on the EMPLOYEE relation from A3;


A1 can issue:



REVOKE SELECT ON
EMPLOYEE

FROM
A3
;

Chapter 9

© 2007 by Prentice Hall

28

An Example (5)


A1 wants to give back to A3 a limited capability to
SELECT from the EMPLOYEE relation.


The limitation is to retrieve only the NAME, BDATE, and
ADDRESS attributes and only for the tuples with DNO=5.


A1 then create the view:


CREATE VIEW
A3EMPLOYEE

AS


SELECT
NAME, BDATE, ADDRESS


FROM
EMPLOYEE


WHERE
DNO = 5
;


After the view is created, A1 can grant
SELECT

on the
view A3EMPLOYEE to A3 as follows:



GRANT SELECT ON
A3EMPLOYEE

TO
A3
;

Chapter 9

© 2007 by Prentice Hall

29

An Example(6)


A1 wants to allow A4 to update only the SALARY
attribute of EMPLOYEE;


A1 can issue:


GRANT UPDATE ON
EMPLOYEE

(
SALARY
)
TO
A4
;


The
UPDATE

or
INSERT

privilege can specify
particular attributes that may be updated or inserted
in a relation.


Other privileges (
SELECT
,
DELETE
) are not attribute
specific.

Chapter 9

© 2007 by Prentice Hall

30

Your Turn:


Think about the subjects (people who can
access and use) for your project database.



Think about the different privileges each
type of subjects might have.