Database Systems: Design, Implementation, and Management Eighth Edition

1. Database Systems: Design, Implementation, and ManagementEighth Edition Chapter 15 Database Administration and Security

2. 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 administrator’s managerial and technical roles • Data security, database security, and the information security framework • Several database administration tools and strategies • Various database administration technical tasks Database Systems, 8th Edition

3. Data as a Corporate Asset • Data are a valuable asset that require careful management • Data are a valuable resource that translate into information • Accurate, timely information triggers actions that enhance company’s position and generate wealth Database Systems, 8th Edition

4. The Need for and Role of Databasesin an Organization • Database’s predominant role is to support managerial decision making at all levels • DBMS facilitates: • Interpretation and presentation of data in useful formats by transforming raw data into information. • Distribution of data and information to the right people at the right time. • Data preservation and monitoring the data usage for adequate periods of time. • Control over data duplication and use, both internally and externally. • DBMS must provide tools that give each level of management different view of data and support required level of decision making. (operational, tactical and strategic – Ex: pricing) Database Systems, 8th Edition

5. Introduction of a Database: Special Considerations • Introduction of a DBMS is likely to have a profound impact • Might be positive or negative, depending on how it is administered. Having DB does not guarantee right decision • Three aspects to DBMS introduction: • Technological-DBMS software and hardware • includes selecting, installing, configuring, and monitoring the DBMS • Managerial-Administrative functions • planning for proper people to be DBAs, monitoring, controlling. • Cultural-Corporate resistance to change • One role of DBA department is to educate end users about system uses and benefits Database Systems, 8th Edition

6. The DBA’s Managerial Role • focused on personnel management and on interactions with the end-user community • DBA responsible for: • Coordinating, monitoring, allocating resources • Resources include people and data • Defining goals and formulating strategic plans • Interacts with end user by providing data and information • Enforces policies, standards, procedures • Manages security, privacy, integrity • Ensures data can be fully recovered • Ensures data distributed appropriately Database Systems, 8th Edition

7. Policies, standards, and procedures • Policies • All users must have passwords. • Passwords must be changed every six months. • Standards • A password must have a minimum of five characters. • A password must have a maximum of 12 characters. • Social Security numbers, names, and birth dates cannot be used as passwords. • Procedures • To create a password, • (1) the end user sends to the DBA a written request for the creation of an account; • (2) the DBA approves the request and forwards it to the computer operator; • (3) the computer operator creates the account, assigns a temporary password, and sends the account information to the end user; • (4) a copy of the account information is sent to the DBA; and • (5) the user changes the temporary password to a permanent one.

8. The DBA’s Technical Role • Evaluates, selects, and installs DBMS and related utilities • Designs and implements databases and applications • Tests and evaluates databases and applications • Evaluation of the written documentation to ensure that the documentation and procedures are accurate and easy to follow. • Observance of standards for naming, documenting, and coding. • Data duplication conflicts with existing data. • The enforcement of all data validation rules. • Operates DBMS, utilities, and applications • System support. • Performance monitoring and tuning • Backup and recovery. • Security auditing and monitoring. • Trains and supports users • Maintains DBMS, utilities, and applications Database Systems, 8th Edition

9. Security • Security refers to activities and measures to ensure the confidentiality, integrity, and availability of an information system and its main asset - data • Securing data entails securing overall information system architecture (SW, HW, Network, people) • Security goals include: • Confidentiality: data protected against unauthorized access – prevent disclosure of information • Integrity: keep data consistent and free of errors or anomalies • Availability: accessibility of data whenever required by authorized users for authorized purposes Database Systems, 8th Edition

10. Security Policies • Database security officer secures the system and the data • Works with the database administrator • Securitypolicy: collection of standards, policies, procedures to guarantee security • Ensures auditing and compliance • Security audit process identifies security vulnerabilities (ex: blank passwords) and measures to protect the system (ex: enforce complex password policy) • Compliance refers to activities undertaken to meet data privacy and security reporting guidelines. Database Systems, 8th Edition

11. Security Vulnerabilities • Securityvulnerability: weakness in a system component • Could allow unauthorized access or cause service disruptions • The nature of such vulnerabilities could be of multiple types: • Technical: a flaw in the operating system or Web browser), • Managerial: not educating users about critical security issues), • Cultural: hiding passwords under the keyboard or not shredding confidential reports • Procedural: not requiring complex passwords or not checking user IDs • Securitythreat: imminent security violation • Could occur at any time due to unchecked security vulnerability. • Securitybreach yields a database whose integrity is: • Preserved: unauthorized and unnoticed access, does not disrupt the database, Action is required to avoid the repetition of similar security problems • Corrupted: access by computer viruses and by hackers whose actions are intended to destroy or alter data Database Systems, 8th Edition

12. 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. • examples: change default system passwords, set up auditing logs, implement network security • Authorizationmanagement: • User access management: Users and their rights • View definition: DBA can restrict views • DBMS access control :restrict query and reports • DBMS usage monitoring: audit trial logs Database Systems, 8th Edition

13. Database Administration Tools • Data dictionary: a DBMS component that stores the definition of data characteristics and relationships.” You may recall that such “data about data” are called metadata. They might be: • Integrated: included in new DBMS (built in) • Standalone: DBA uses third party data dictionary (in old DBMS) • Activedatadictionary: automatically updated by the DBMS with every database access • Passivedatadictionary: requires running a batch process • Data dictionary access information is normally used by the DBMS for query optimization purposes. • The main function of data dictionary is to store description of all objects that interact with database, and for query optimization. • DBA uses data dictionary to support data analysis and design Database Systems, 8th Edition

14. Database Administration Tools • SYSTABLES stores one row for each table or view. • SYSCOLUMNS stores one row for each column of each table or view. • SYSTABAUTH stores one row for each authorization given to a user for a table or view in a database. • Example 1 List the names and creation dates of all tables created by the user ALI in the current database. SELECT NAME, CTIME FROM SYSTABLES WHERE CREATOR = ‘ALI'; • Example 2 List the names of the columns for all tables created by ALI in the current database. SELECT NAME FROM SYSCOLUMNS WHERE TBCREATOR = “ALI'; • Example 3 List the names of all tables for which user ALI has DELETE authorization. SELECT TTNAME FROM SYSTABAUTH WHERE GRANTEE = ALI' AND DELETEAUTH = 'Y';

15. 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 Database Systems, 8th Edition

16. The DBA at Work: Using Specific DBMS for Database Administration • Technical tasks handled by the DBA in a specific DBMS: • Creating and expanding database storage structures • Managing database objects like tables and indexes • Managing end-user database environment like type of DB access. • Customizing database initialization parameters • All DBMS vendors provide programs to perform database administrative tasks Database Systems, 8th Edition

17. Managing the Database Objects • Databaseinstance: separate location in memory reserved to run the database • - May have several databases running in memory at the same time • Databaseobject: any object created by end users • Schema: logical section of the database that belongs to a given user • Schema identified by a username • Within the schema, users create their own tables and other objects • Normally, users authorized to access only the objects that belong to their own schemas Database Systems, 8th Edition

18. Managing Users and Establishing Security • User: uniquely identifiable object • Allows a given person to log on to the database • Role: a named collection of database access privileges • Authorizes a user to connect to the database and use system resources • Profile: named collection of settings • Controls how much of a resource a given user can use, Like how long a user can be connected, how much idle time may be used before the user is disconnected. How much storage space a user can use Database Systems, 8th Edition