Chapter 16 - DB Administration and Security

Dirty Data

Data that suffers from inaccuracies and inconsistencies

Data Quality

A comprehensive approach to ensuring accuracy, validity, and timeliness of data

Data profiling software

Programs that analyze data and metadata to determine patterns that can help assess data quality

Master data management (MDM) software

Software that helps prevent dirty data by coordinating common data across multiple systems

Database's predominant role

To support managerial decision making at all levels in the organization while preserving data privacy and security

Top-level management role

• Decision-making/planning
• Define/enforce policies
• Reduce costs
• Boost productivity

Middle-level management role

• Deliver data for tactical planning
• Monitor/evaluate resources and performance
• Enforce security and privacy of data

Operational-level management role

• Support/enhance company operations
• Produce query results within specified performance levels

Security

Activities and measures to ensure the confidentiality, integrity and availability (CIA) of an information system and its data

Privacy

The rights of individuals and organizations to determine access to data about themselves

Enterprise Database

The company's data represntation that provides support for all present and expected future operations

Three aspects of introducing a DBMS

Technological, managerial, cultural

Technological aspect (of introducing a DBMS)

Selecting, installing, configuring, and monitoring the DBMS to ensure that it operates efficiently

Managerial aspect (of introducing a DBMS)

Careful planning to create an appropriate organizational structure

Cultural aspect (of introducing a DBMS)

Listening to people's concerns about the system and explaining its uses and benefits

Information Systems (IS) department

A department responsible for all information technology services and production functions in an organization; divided into application development and DB operations

Service function of an IS department

Provide end users with data management support

Production function of an IS department

Provide end users with solutions for their information needs through integrated application management or information systems

Database Administrator (DBA)

The person responsible for control of the centralized and shared database; general manager of the DBA department

Systems Administrator

The person responsible for coordinating and performing day-to-day data processing activities

Data Administrator (DA)

The person responsible for managing the entire data resource; has more authority and responsibility than the DBA

Responsibilities of DBA managerial role

• Provide end-user support
• Enforce policies, procedures and standards for data in the database
• Manage data security, privacy and integrity
• Manage data backup and recovery
• Manage data distribution and use

Policy

Statement of direction used to manage company operations and support objectives

Standard

Detailed and specific set of instructions that describe minimum requirements for a given activity

Procedure

Written instructions that describe a series of steps to be followed during performance of an activity or process

Database Security Officer (DSO)

The person responsible for the security, integrity, backup, and recovery of the database.

Disaster Management

Planning, organizing and testing database contingency plans and recovery procedures following a physical disaster or a database integrity failure

Full backup (database dump)

A complete copy of an entire database saved and periodically updated in a separate location; ensures full recovery after physical disaster or integrity failure

Incremental backup

Only backs up the data that has changed since the last backup

Concurrent backup

A backup that takes place while one or more users are working on a database

What should backup and recovery measures include?

Backup identification, backup storage, physical protection of hardware/software, insurance coverage for data, and access control to DB software

Responsibilities of DBA technical role

• Evaluate, select, and install DBMS
• Design, implement, test, operate, and maintain databases and applications
• Train and support users

Confidentiality

Ensuring data is protected against unauthorized access

Compliance

Activities that meet data privacy and security reporting guidelines

Integrity

Keeping data consistent and free of errors or anomalies

Availability

Accessibility of data whenever required by authorized users and for authorized purposes

Security policy

A collection of standards, policies, and procedures created to guarantee the security of a system

Security vulnerability

A weakness in a system component that could be exploited to allow unauthorized access or service disruptions

Categories of security vulnerabilities

Technical, managerial, cultural, and procedural

Security threat

An imminent security violation caused by unchecked security vulnerabilities

Security breach

An event in which a security threat is exploited to endanger the CIA of the system; leads to either preserved or corrupted databases

Examples of security vulnerablities

Blank or non-secure passwords
Lost data due to a natural disaster, power failure or stolen laptop
Virus, DoS, or email attacks

Database security

Use of DBMS features and related measures that comply with the security requirements

Authorization management

Procedures to protect and guarantee database security and integrity

Authorization management procedures

User access management
Define data views
DBMS access control
DBMS usage monitoring

Audit log

A DBMS security feature that automatically records a brief description of DB operations performed by all users

Data Dictionary

A DBMS component that stores the definition/descriptions of all data objects that interact with the DB; aka information resource dictionary

Active Data Dictionary

Is automatically updated by the database management system every time the database is accessed (keeps info current)

Passive Data Dictionary

Requires a command initiated by an end user to update its data access statistics

Integrated Data Dictionary

Included with the DBMS; built-in

Standalone Data Dictionary

Third-party systems used for DBMS' that do not have built-in data dictionaries

Computer-Aided Systems Engineering (CASE)

Tools to automate part or all of the SDLC

Front-end CASE tools

Provids support for the planning, analysis, and design phases of the SDLC

Back-end CASE tools

Provides support for the coding and implementation phases of the SDLC

Components of a CASE tool

•Graphics
•Screen painters and report generators
•Integrated repository
•Analysis segment
•Program documentation generator

Information Engineering (IE)

A methodology that translates a company's strategic goals into helpful data and applications.

Information Systems Architecture (ISA)

Output of IE process; Basis for planning, developing, and controlling future information systems

What does the cloud services partner company provide for DBs?

• DBMS installation and updates
• Server/network management
• Backup and recovery operations

Oracle DBA Tools

Ensures the RDBMS starts automatically

Database instance

The collection of processes and data structures used to manage a specific database

Table space (file group)

A logical grouping space used to group related data

Data File

A named physical storage space where all data in a database is stored

SYSTEM table space

Stores the data dictionary data

USERS table space

Stores the table data created by the end users

TEMP table space

Stores the temporary tables and indexes created during the execution of SQL statements

UNDOTBS1 table space

Stores database transaction recovery information

User (in a DBMS)

A uniquely identifiable object that allows a given person/process to log on to the DB

Role (in a DBMS)

A named collection of DB access privileges that authorize a user to connect to a DB and use its system resources

Profile (in a DBMS)

A named collection of settings that controls how much of the database resource a given user can use