Database Systems Design Implementation and Management Eighth Edition

Database Systems: Design, Implementation, and Management Eighth Edition Chapter 15 Database Administration and Security 1

Objectives • In this chapter, you will learn: – Data are a valuable business asset requiring careful management – How a database plays a critical role in an organization – Introduction of a DBMS has technological, managerial, and cultural organizational consequences Database Systems, 8 th Edition 2

Objectives (continued) • In this chapter, you will learn: (continued) – Database administrator’s managerial and technical roles – Data security, database security, and the information security framework – Several database administration tools and strategies – How various database administration technical tasks are performed with SQL Server 2005 Database Systems, 8 th Edition 3

15. 1 Data as a Corporate Asset • Data are a valuable asset that require careful management – How many opportunities are lost if data about customers, suppliers, inventories, operations are missing? – What is the actual cost of data loss? • Data are a valuable resource that translate into information – The data user applies intelligence to data to produce information • Accurate, timely information triggers actions that enhance company’s position and generate wealth Database Systems, 8 th Edition 4

Data form the basis for decision making, strategic planning, control, and operation monitoring Database Systems, 8 th Edition 5

15. 2 The Need for and Role of Databases in an Organization • Database’s predominant role is to support managerial decision making at all levels while preserving data privacy and security • DBMS facilitates: – Interpretation and presentation of data – Distribution of data and information – Data Preservation and monitoring of data usage – Control over data duplication and use • Three levels to organization management: – Top, strategic decision – middle, tactical decision – Operational, daily operational decision Database Systems, 8 th Edition SKIP 15. 3 6

• Database at top management level – Provide information necessary for strategic decision making, strategic planning, policy formulation, and goals definition – Provide access to data to identify growth opportunities and to chart the direction of such growth – Provide a framework for defining and enforcing organizational policies – Improve the likelihood of a positive ROI by searching for new ways to reduce costs and/or by boosting productivity – Provide feedback to monitor whether the company is achieving its goals 7

• Database at middle management level – Deliver the data necessary for tactical decisions and planning – Monitor and control the allocation and use of company resources and evaluate departments’ performances – Provide a framework for enforcing and ensuring the security and privacy of data in the database • Database at operational management level – Represent and support the company operations as closely as possible with a flexible data model – Produce query results within specified performance levels – Enhance the company’s short-term operational ability by providing timely information for customer support and for application development and computer operations 8

15. 4 The Evolution of the Database Administration Function • Data administration has its roots in the old, decentralized world of the file system • Advent of DBMS and its shared view of data produced new level of data management sophistication – Data processing (DP) department evolved into information systems (IS) department • Data management became increasingly complex – Development of database administration function Database Systems, 8 th Edition 9

After the DP department evolved into the IS department, the responsibility of the IS department were broadened to include: • A service function to provide end users with active data management support • A production function to provide end users with specific solutions for their information needs through integrated application or management information systems • The application development segment was in charge of gathering DB requirements and logical DB design • The DB operations segment took charge of implementing, monitoring, and controlling the DBMS operations 10

To plan, define, implement, and enforce the policies, standards, and procedures in the data administration activity consulting 11

• No standard for how the DBA function fits in an organization’s structure, especially for the fast-paced technology changes: – Distributed databases impose new and complex coordinating activities on the system DBA – Internet-accessible data and growing data warehousing applications add to the DBA’s data modeling and design activities – PC-based DBMS packages invite data duplication and poor DB design 12

• DBA operations according to DBLC phases: – DB planning, including defining standards, procedures, and enforcement – DB requirement gathering and concept design – DB logical and transaction design – DB physical design and implementation – DB testing and debugging – DB operations and maintenance, including installation, conversion, and migration – DB training and support 13

14

coordinator of all DBAs Data Administrator (DA): reports directly to top management, and is given higher responsibility and authority than DBA. DA is responsible for controlling the overall corporate data resources, both computerized and manual 15

15. 5 The Database Environment’s Human Component • Even most carefully crafted database system cannot operate without human component • Effective data administration requires both technical and managerial skills • DA’s job has a strong managerial orientation with company-wide scope. DBA is focal point for data/user interaction – Both need diverse mix of skills Database Systems, 8 th Edition 16

Contrasting DA and DBA Activities and Characteristics • DA must set data administration goals – – Data “sharability” and time availability Data consistency and integrity Data security and privacy Extent and type of data use 17

A Summary of DBA Activities Database Systems, 8 th Edition Used as a general title that encompasses all appropriate data administration functions 18

19

The DBA’s Managerial Role • DBA responsible for: – Coordinating, monitoring, allocating DB administration resources: people and data – Defining goals and formulating strategic plans for the DBA function Database Systems, 8 th Edition 20

• End-User Support – Interacts with end user by providing data and information support services • • • Gathering user requirements, Building end-user confidence, Resolving conflicts and problems, Finding solutions to information needs, Ensuring quality and integrity of data and applications, managing the training and support of DBMS users 21

• Policies, standards, procedures – Policies are general statements of direction or action that communicate and support DBA goals – Standards describe the minimum requirements of a given DBA activity – Procedures are written instructions that describe a series of steps to be followed during the performance of a given activity • • • End-user DB requirement gathering; DB design and modeling; Documentation and naming conventions; Design , coding, and testing of DB application programs; DB software selection; DB security and integrity; DB backup and recovery; DB maintenance and operation; End-user training 22

• Data security, privacy, integrity – Distribution of data makes it difficult to maintain data control, security, and integrity – DBAs must team up with internet security experts to build security mechanisms handling attacks or unauthorized access • Data Backup and Recovery – Ensures data can be fully recovered – Disaster management • Includes all planning, organizing, and testing of DB contingency plans and recovery procedures – Periodic data and application backups – Proper backup identifications – Convenient and safe backup storage – Physical protection of hardware and software – Personal access control to the software of a DB 23 installation – Insurance coverage for the data in the DB

• Data backup and recovery – Data recovery and contingency plans must be thoroughly tested and evaluated, and they must be practiced frequently – Establish priorities concerning the nature and extent of the data recovery process • Data Distribution and Use – DBA is responsible for ensuring that the data are distributed to the right people, at the right time, and in the right format – Enabling end users to become self-sufficient in the acquisition and use of data can lead to more efficient use of data in the decision process. – Letting end users micromanage their data subsets could inadvertently sever the connection between those users and the data administration function. 24

The DBA’s Technical Role • Evaluates, selects, and installs DBMS and related utilities (p. 621) • Designs and implements databases and applications (p. 622) • Tests and evaluates databases and applications (p. 623) • Operates DBMS, utilities, and applications (p. 623 -624) • Trains and supports users (p. 625) • Maintains DBMS, utilities, and applications (p. 625) 細節請自己看課本，期末不考 Database Systems, 8 th Edition 25

15. 6 Security • Security refers to activities and measures to ensure the confidentiality, integrity, and availability of an information system and its data – Securing data entails securing overall information system architecture – Confidentiality: data protected against unauthorized access – Integrity: keep data consistent and free of errors or anomalies – Availability: accessibility of data by authorized users for authorized purposes Database Systems, 8 th Edition 26

Security Policies • Database security officer secures the information system and the data – Works with the database administrator • Security policy: collection of standards, policies, procedures to guarantee security – Ensures auditing and compliance • Security audit process identifies security vulnerabilities – A weakness in a system component that could be exploited to allow unauthorized access or cause service disruptions – Identifies measures to protect the system Database Systems, 8 th Edition 27

Security Vulnerabilities • Security threat: imminent security violation – Could occur at any time • Security breach yields a database whose integrity is: – Preserved • Action is required to avoid the repetition of similar security problems, but data recovery may not be necessary. Like unauthorized or unnoticed access for information purposes – Corrupted • Action is required to avoid the repetition of similar security problems, and the database must be recovered to a consistent state. Like virus or hacker. Database Systems, 8 th Edition 28

Sample Security Vulnerabilities and Related Measures Database Systems, 8 th Edition 29

Sample Security Vulnerabilities and Related Measures

Database Security • Database Security refers to the use of DBMS features and other measures to comply with security requirements • DBA secures DBMS from installation through operation and maintenance • Authorization management: – User access management • Define each DB user; Assign password to each user; Define user groups; Assign access privileges; Control physical access – View definition – DBMS access control – DBMS usage monitoring • auditing Database Systems, 8 th Edition 31

15. 6 Database Administration Tools • Two main types of data dictionaries: – Integrated: built-in – Standalone: third-party, for older type DBMS • Active data dictionary automatically updated by the DBMS with every database access • Passive data dictionary requires running a batch process • Main function: store description of all objects that interact with database Database Systems, 8 th Edition 32

• Data dictionary that includes data external to DBMS becomes flexible tool – Enables use and allocation of all organization’s information • Data dictionary typically includes: – – – – Data elements that are defined in all tables of all databases Tables defined in all databases Indexes defined for each database table Defined databases End users and administrators of the database Programs that access the database Access authorizations for all users of all databases Relationship among data elements • Metadata often the basis for monitoring database use – Also for assigning access rights to users • DBA uses data dictionary to support data analysis and design Database Systems, 8 th Edition 33

CASE Tools • Computer-Aided Systems Engineering – Automated framework for SDLC – Structured methodologies and powerful graphical interfaces • Front-end CASE tools provide support for planning, analysis, and design phases • Back-end CASE tools provide support for coding and implementation phases • Benefits associated with CASE tools – – Reduction in development time and costs Automation of the SDLC Standardization of system development methodologies Easier maintenance of developed application Database Systems, 8 th Edition 34

• Typical CASE tool has five components – Graphics designed to produce structured diagrams, such as DFD, ERD, class diagrams, and object diagrams – Screen painters and report generators – Integrated repository for storing and crossreferencing the system design data – An analysis segment to provide a fully automated check on system consistency, syntax, and completeness – A program documentation generator 35

An Example of a CASE tool: Visio Database Systems, 8 th Edition 36

CASE Tools COMPANY PRODUCT Computer Associates ERWin Microsoft Visio Oracle Designer Sybase Power Designer Skip 15. 8, 15. 9 37

Summary • Data management is a critical activity for any organization – Data should be treated as a corporate asset • DBMS is the most commonly used electronic tool for corporate data management • DBMS has impact on organization’s managerial, technological, and cultural framework • Data administration function evolved from centralized electronic data processing – Applications began to share common repository Database Systems, 8 th Edition 38

Summary (continued) • Database administrator (DBA) is responsible for managing corporate database • Broader data management activity is handled by data administrator (DA) • DA is more managerially oriented than more technically oriented DBA – DA function is DBMS-independent – DBA function is more DBMS-dependent • When there is no DA, DBA executes all DA functions Database Systems, 8 th Edition 39

Summary (continued) • Managerial services of DBA function: – Supporting end-user community – Defining and enforcing policies, procedures, and standards for database function – Ensuring data security, privacy, and integrity – Providing data backup and recovery services – Monitoring distribution and use of data in database Database Systems, 8 th Edition 40

Summary (continued) • Technical role of DBA: – Evaluating, selecting, and installing DBMS – Designing and implementing databases and applications – Testing and evaluating databases and applications – Operating DBMS, utilities, and applications – Training and supporting users – Maintaining DBMS, utilities, and applications Database Systems, 8 th Edition 41

Summary (continued) • Security ensures confidentiality, integrity, availability of information system and data • Security policy is a collection of standards, policies, and practices • Security vulnerability is a weakness in system component • Information engineering guides development of data administration strategy • CASE tools and data dictionaries translate strategic plans to operational plans Database Systems, 8 th Edition 42