Chapter 9: The Client/Server Database Environment (p.368-376)

1. Chapter 9:The Client/Server Database Environment (p.368-376) Chapter 12: Data Security (p.495-504) © 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

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

4. Application Logic in Client/Server Systems Presentation Logic • Input–keyboard/mouse • Output–monitor/printer GUI Interface Processing Logic • I/O processing • Business rules • Data management Procedures, functions, programs Storage Logic • Data storage/retrieval DBMS activities

5. Client does extensive processing Client does little processing Client/Server Architectures • 1. File Server Architecture • 2. Two-tier Database Server Architecture • 3. Three-tier Architecture

6. Figure 9-2 File Server Architecture FAT CLIENT

7. 1. File Server Architecture FAT CLIENT • 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.

8. Figure 9-3 Two-tier database server architecture Thinner clients DBMS only on server

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

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

11. Figure 9-4 Three-tier architecture Thinnest clients Business rules on separate server DBMS only on DB server

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 2nd layer • Popular for Internet applications

13. Three-Tier Architectures GUI interface (I/O processing) Browser Client • Application server Web Server Business rules • Database server Data storage DBMS Thin Client • PC just for user interface and a little application processing. Limited or no data storage (sometimes no hard drive)

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

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

16. Securing Data at the Database Level

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

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

19. Figure 12-5 Authorization matrix

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

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.

22. Figure 12-6a Authorization table for subjects (salespeople) Implementing authorization rules Figure 12-6b Authorization table for objects (orders) Figure 12-7 Oracle privileges

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: CREATESCHAMA EXAMPLE AUTHORIZATION A1;

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;

25. Schema for Examples

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;

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;

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;

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.

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.