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

5. 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

6. 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

7. Sun 13-4 The Evolution of the Database Administration Function • Data administration has its roots in the old, decentralized world of the file system. (Centralized data administration led to DP Dept) • Advent of DBMS and its shared view of data produced new level of data management sophistication and led DP (Data Processing) department to evolve into information systems (IS) department ( Service and production functions) • Data management became increasingly complex job, thus leading to development of database administration function- distribution, Internet, UI Database Systems, 8th Edition

8. The Evolution of the Database Administration Function Staff Position: DBA is able to devise the data administration strategy but does not have the authority to enforce it Line position: DBA has both the responsibility and the authority to plan, define, implement, and enforce the policies, standards, and procedures

9. The Evolution of the Database Administration Function

10. 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 • SA: System Admin is the general coordinator of all DBAs • DA: responsible for controlling the overall corporate data resources, both computerized and manual. • DBA is focal point for data/user interaction. He need diverse mix of skills • DBA: a general title that encompasses all appropriate data administration functions (managerial & technical) Database Systems, 8th Edition

11. Database Systems, 8th Edition

12. The Database Environment’s Human Component

13. Tue 22-4 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

14. 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.

15. 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

16. 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

17. 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 • Compliance refers to activities undertaken to meet data privacy and security reporting guidelines. • Security audit process identifies security vulnerabilities and measures to protect the system Database Systems, 8th Edition

18. 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

19. Database Systems, 8th Edition

20. In general, cross-site scripting refers to that hacking technique that leverages vulnerabilities in the code of a web application to allow an attacker to send malicious content from an end-user and collect some type of data from the victim. Database Systems, 8th Edition

21. 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

22. 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. • Two main types of data dictionaries: • 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. Database Systems, 8th Edition

23. Database Administration Tools • Data dictionary that includes data external to DBMS (information don’t have its roots in the database data) becomes flexible tool (information resource dictionary) • Enables use and allocation of all organization’s information • 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, 8th Edition

24. 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 the user JONESVI has DELETE authorization. SELECT TTNAME FROM SYSTABAUTH WHERE GRANTEE = 'JONESVI' AND DELETEAUTH = 'Y';

25. Sun 27-4 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 • Typical CASE tool has five components • Graphics designed to produce structured diagrams (like DFD) • Screen painters and report generators (I/O formats) • comprehensive data dictionary. (repository of the design data) • A program documentation generator • analysis segment: check on system consistency, syntax, and completeness Database Systems, 8th Edition

26. Database Systems, 8th Edition

27. Developing a Data Administration Strategy • Critical step for any organization is to ensure its information system supports strategic plans for each of the company’s business areas • Several methodologies are available to ensure the compatibility of data administration and information systems plans and to guide the strategic plan development • Information engineering (IE) translates strategic goals into data and applications. placing the emphasis on data not on processes, IE helps decrease the impact on systems when processes change. • Information systems architecture (ISA) is the output of IE process. Serves as the basis for planning, development, and control of future information systems.

28. Developing a Database Administration Strategy

29. 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

30. Tue 29-4 Creating Tablespaces and Datafiles • Database composed of one or more tablespaces • Tablespace is a logical storage space • Physically stored in one or more datafiles • Datafile physically stores the database’s data • Each datafile can reside in a different directory on the hard disk • Database has 1:M relationship with tablespaces • Tablespace has 1:M relationship with datafiles Database Systems, 8th Edition

31. 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

32. 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

33. Customizing the Database Initialization Parameters Fine-tuning requires modification of database configuration parameters Some are changed in real time using SQL Some affect database instance For example, the “db_cache_size” parameter sets the amount of memory reserved for database caching. This parameter should be set to a value that is large enough to support all concurrent transactions. Others affect entire RDBMS and all instances Initialization parameters reserve resources used by the database at run time After modifying parameters, may need to restart the database 33 Database Systems, 8th Edition