Transcript Database Systems: Design, Implementation, and Management

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
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
4
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
5
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
6
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
8
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
9
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
– 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
10
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
11
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
12
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)
• 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.
• DBA uses data dictionary to support data analysis and design
13
Database Systems, 8th Edition
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';
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
15
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
16
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
17
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
18