Transcript DB2_Ch15

Database Systems: Design,
Implementation, and
Management
Eighth Edition
Chapter 15
Database Administration and Security
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
2
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
3
Database Systems, 8th Edition
4
The Need for and Role of Databases
in 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
th
Database Systems, 8 Edition
6
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
7
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
The Evolution of the Database
Administration Function
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
10
Database Systems, 8th Edition
11
The Database Environment’s
Human Component
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
13
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.
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
15
Database Systems, 8th Edition
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
16
Security Policies
• Database security officer secures the system and the
data
– Works with the database administrator
• Security policy: 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
17
Security Vulnerabilities
• Security vulnerability: 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
• Security threat: imminent security violation
– Could occur at any time due to unchecked security vulnerability.
• Security breach 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
18
Database Systems, 8th Edition
19
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 enduser and collect some type of data from the victim.
Database Systems, 8th Edition
20
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
• Authorization management:
–
–
–
–
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
21
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)
• Active data dictionary: automatically updated by the DBMS
with every database access
• Passive data dictionary: 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.
22
Database Systems, 8th Edition
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
23
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';
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
25
Database Systems, 8th Edition
26
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.
Developing a Database
Administration Strategy
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
29
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
30
Managing the Database Objects
• Database instance: separate location in memory reserved
to run the database
- May have several databases running in memory at the
same time
• Database object: 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
31
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
32
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
Database Systems, 8th Edition
33