Transcript Lesson11 Database Administrationx

Database Administration
Advanced Database
Dr. AlaaEddin Almabhouh
Topic & Structure of Lesson
At the end of this chapter, you will be able to:





Explain the evolution of Database administration
Discuss the role of the Data Administrator
Discuss the role of the Database Administrator
Discuss the various database administration strategies
Data Control Language (DCL)
Slide 2 (of 33)
Database Administration



DBMS is just a tool for managing data and must be
used effectively to produce the desired results.
Introduction of a DBMS represents a big change and
can have a big impact
 Positive or negative depending on how its
administered
Process includes 3 important aspects
 Technological : DBMS software and hardware
 Managerial: administrative functions
 Cultural: Corporate resistance to change
Technological Aspect

Selecting, installing, configuring and monitoring
the DBMS


To make sure it handles data storage, access and
security efficiently
Managed by people with technical skills necessary
to provide adequate support for all users


Programmers, managers and end-users
Database administration staffing is a key
consideration

Personnel must have mix of technical and managerial skills
Managerial Aspect
Requires careful planning to create appropriate
organisational structure
 Personnel must have excellent interpersonal
and communication skills combined with broad
organisational and business understanding
 Top management must be committed to the
new system and support the database
administration functions goals and roles

Cultural Impact



DBMS is likely to have an effect on people, functions
and interactions
 Additional personnel may be required
 New roles allocated
 Employee performance might be evaluated
Individual department managers surrender ownership
to data administration function and share data with
the rest of the company
Database administration department will need to
listen to end users concerns and act by educating
about benefits
Data and Database Administration

Many large organisations have two main roles for
managing the company data

Data Administrator and Database Administrator


Data Administration: A high-level function that is responsible
for the overall management of data resources in an
organization, including maintaining corporate-wide
definitions and standards

Database Administration: A technical function that is
responsible for physical database design and for dealing with
technical issues such as security enforcement, database
performance, and backup and recovery
In a small organisation often the same person
Data Administrator - DA

High level corporate function

Responsible for controlling the overall corporate data resources both
computerised and manual also data outside the scope of DBMS

Covers a larger area of operations than DBA

Strong managerial orientation with company wide scope

Sets data administration goals

Sets data policies, procedures, standards

Long term planning

Will be involved in the conceptual and logical design of a database system

Database security is a concern of DA in association with DBA

DBA may report to DA dependent on structure of the organisation
Database Administrator - DBA




Low level more technically oriented than DA
Involved in physical design of database systems
Has narrower DBMS specific scope
On the data management side will be concerned with
the issue of controlling the database

Centralised and shared databases



Distributed databases can force organisation to de-centralise data
administration
DBA define and delegate responsibilities of each local DBA
Imposes more complex co-ordinating activities on system DBA
DBA’s Responsibilty

Coordinating, monitoring and allocating database
administration resources











End User Support
Enforcing Policies Procedures and Standards
Data Security Privacy and Integrity
Authorisation Management
Data Backup and Recovery
Data Distribution and Use
Selection of hardware and software
Installing and upgrading DBMS
Tuning database performance
Improving query processing performance
Physical database design and implementation
End User Support





User requirements gathering
Conflict and problem resolution
Finding solutions to information needs
Ensuring quality and integrity of applications and data
Managing training and support of DBMS users
Policies Procedures and Standards

Continuous enforcement of the policies procedures
and standards



Policies
 General statements of direction that support goals
Standards
 More detailed and specific than policies and describe minimum
requirements
Procedures
 Written instructions that describe a series of steps to be
followed
Data Security Privacy and Integrity


Of great concern to DBA’s
With distributed data:



More difficult to maintain data control, security and integrity
Must use the security and integrity mechanisms
provided by the DBMS
Must team up with internet security experts

To build firewalls, proxy services to safeguard from possible
external attacks
Data Backup and Recovery






Periodic data and applications backups
 Tools to ensure backup and recovery of the data
Proper backup identification
 Detailed descriptions and date information to ensure correct
backups are used
Convenient and safe backup storage
 Multiple backups stored at different locations
Physical protection of both hardware and software
 Restricted access, fire protection, air conditioning
Personal access control to the database software
 Multilevel passwords and privileges
Insurance coverage for the data in the database
 Secure an insurance policy to provide financial protection
Data Distribution and Use

Data is only useful if it reaches the right users in a timely
fashion in the right format

Time consuming in typical applications programming
environment

Internet has opened databases to corporate users


Make use of more sophisticated query tools

Make use of Internet web front ends

Reduce dependency on application programmers
DBA must ensure procedures and standards are being adhered
to
Selection of DBMS, Utilities and Hardware

Important technical responsibility, needs to consider:















DBMS model
DBMS storage capacity
Application development support
Security and integrity
Backup and recovery
Concurrency control
Performance
DBA tools
Data distribution
Portability and standards
Hardware
Data dictionary
Vendor training and support
Available third-party tools
Cost
Design and Implementation of Databases
and Applications



DBA provides data modelling and design services to
the end-user community
Implementation of the physical database
Develop test and implement the operational
procedures required by new system




Training, security and backup and recovery plans
Assigning responsibility for database control and
maintenance
Authorise application of users to access database
Fine tuning and/or reconfiguration of DBMS
Authorisation Management

User Access Management






View definition


Define data views to protect and control the scope of the data that are
accessible to users
DBMS utilities access control


Define each user to the database
Assign passwords to each user
Define user groups
Assign access privileges
Physical access control
Placing limits on the use of query and reporting tools
DBMS usage monitoring


Audit the use of the data in the database.
Create an audit log
Connect, Resource and DBA

When a company receives a new Oracle system

It comes with 2 users automatically installed


Super users with DBA privileges
First thing DBA does is:


Change passwords for these 2 users
Then enrol new users


Need distinct username, password and level of privilege granted to user
3 classes of user



Connect
 Look at other users data only, perform data manipulation tasks specified by
DBA and create views
Resource
 Create tables and indexes and grant other users access to these tables and
indexes
DBA
 Permit access to any users data and allows granting and revoking of access
privileges to any user in the database
Contrasting DA and DBA
Activities and Characteristics
DA
DBA
Strategic planning
Control and supervision
Sets long term goals
Executes plans to reach goals
Sets policies and standards
Enforces policies and procedures
Enforces programming standards
Broad scope
Narrow scope
Long term
Short term focus on daily operations
Managerial Orientation
Technical orientation
DBMS independent
DBMS specific
Q &A
Slide 81 (of 82)