Transcript Database System Administration

University of Manitoba
Asper School of Business
3500 DBMS
Bob Travica
Chapter 10
DB System
Administration
Based on G. Post, DBMS: Designing & Building Business Applications
Updated 2015
D
B
S
Y
S
T
E
M
S
Outline
 Data Administrator/Analyst (DA)
 Database Administrator (DBA)
 DBA’s duties
 DB system backup & recovery
 DB system security
2 of 22
D
B
Data Administration
 Data make a valuable asset.
S
Y
S
T
E
M
S
 Data are used at many business
levels
 There are many databases and
database systems in an
organization.
 Responsibility for managing data Data Administrator (DA) or Data
Analyst
TPS=Transaction Processing Sys.
MIS=Management Info. Sys.
DSS=Decisions Support Sys.
EIS=Executive Info. Sys.
3 of 21
D
B
S
Y
S
T
E
M
S
Data Administration/Administrator (DA)
 DA (sometimes called data architect or even business
analyst) is a type of professional that resides in the IS
function or in a unit interfacing with the IS function.
 Focus on informing in function of business, users (reports,
output forms, queries) rather than IT
 Data definition and integration (e.g., Customer entity in CRM
systems cutting across Sales, Marketing, R+D…).
 Decision support.
 Ideas for system design, involvement in system development.
 Data governance and security.
4 of 21
D
B
Database Administrator (DBA)
 DBA is focused on technology.
S
Y
S
T
E
M
S
 1. DBA actively participates in DB system life cycle (plan,
develop, install, manage, upgrade…).
 2. DBA manages DB system:
 2.1 Users: Creating user accounts, assigning use privileges
 2.2 System performance: Monitoring and tuning
 2.3 Backup & recovery: Supervising backups & system restoration
after crashes
 2.4 Security: Monitoring
5 of 21
D
B
DBA: (1) System Planning & Design
 Estimation & Design (logical, physical)
S
Y
S
T
E
M
S
 Data storage requirements, forms & reports needed
(costs of development), hardware needs, matching
organizational needs with DBMS products
 Time, labor & cost to develop
 Data modeling – coordinates with Data Analyst in the
domain of logical design (e.g., class diagrams, user
interface). Also DA and DBA cooperate on schemas.
 In charge of physical design (types of files, access
structures, DBMS product, hardware)
6 of 21
D
B
DBA: System Development & Implementation
 Defining technology standards:
S
Y
S
T
E
M
S
 Programming standards.
 Layout and techniques.
 Variable & object definition.
 User interface.
 System testing techniques.
 Loading databases.
 Backup and recovery plans.
 User and operator training.
7 of 21
D
B
S
Y
S
T
E
M
S
DBA: System Upgrade
 Determines need for change
 Size and speed of the DB system
 Usage patterns
 System output:
 Additional reports & queries (coop. with DA and
business analysts)
 Forecasting needs
8 of 21
D
B
DBA: (2.1) Users’ Access
 Control via:
S
Y
S
T
E
M
S
 1. Operating system
 Access to directories
 Access to files
 Assigned to individuals or groups.
 2. DBMS functions
(Read, write, modify… data; Administer system) 
9 of 21
D
B
S
Y
S
T
E
M
S
SQL Security Commands
 GRANT privileges
 REVOKE privileges
 Privileges include




SELECT
DELETE
INSERT
UPDATE
 Objects include
 Table
 Table columns (SQL 92+)
 Query
GRANT INSERT
ON Bicycle
TO OrderClerks
REVOKE DELETE
ON Customer
FROM Assemblers
 Users include
 Name/Group
 PUBLIC
10 of 21
D
B
DBA: User Identification
 User identification
S
Y
S
T
E
M
S
 Accounts
 Individual
 Alternative identification
 Finger & hand print readers
 Voice…
 Groups
 Passwords
 Disposable passwords
11 of 21
D
B
DBA: (2.2) System Performance:
Performance Monitors
S
Y
S
T
E
M
S
12 of 22
D
B
SQL Server Query Analyzer
S
Y
S
T
E
M
S
13 of 22
D
B
DBA: (2.3) Database Backup
 Backups are crucial!
Full
 Offsite storage needed
S
Y
S
T
E
M
S
 Types of backup
 Full – in longer intervals
(e.g., once a week);
a copy of all tables made
 Partial (Differential) – in
shorter intervals (e.g., day);
just new data are backed
up; reduced risk but higher
cost
Backup
Manager
Copies
Entire
OpDB
Operational
Backup
Database
Database
(Op DB)
(Bkp DB)
overwrites
Partial
(part of
DBMS)
time 1: copies new
data from Op DB
time 2: copies new
data from Op DB
Partial backup
grows
Partial backup
time 3: copies final
partial backup to BkP
DB
Bkp DB
14 of 21
D
B
S
Y
S
T
E
M
S
DBA: 2.3 Database Recovery
 Recovery needed if problems with
software, hardware, incorrect user input,
viruses, natural causes
 Recovery = getting databases to correct
state (previous example of transferring $
from savings to checking account)
 Key facilities:
 Recovery Manager (part of DBMS)
 Transactions log (TL) file
 ROLLBACK procedure
 Alternative: User works with operational
DB, and TL engaged only if former fails.
Transaction
Log (TL)
(managed by
Backup
Manager)
Transactions
…
copied to
Savepoint
Operational
database
Transaction
recovers
unfinished uses
System
Recovery
crash!
Manager
uses
Backup
databases
15 of 22
D
B
S
Y
S
T
E
M
S
Transaction Log (TL)
Transaction ID Pointer to
previous
Transaction
TL record
Log ID
Pointer to
next
TL record
Table
Database
task
Key
Attribute
Old
value
New
value
Checkpoint (Savepoint) is when results of all new transactions are stored permanently (e.g., in Op
DB). COMMIT saves changes to TL. Just the first transaction saved permanently. Recovery Mgr rolls
processing back to Checkpoint and runs steps/transactions that haven’t been saved after it.
16 of 22
D
B
DBA: (2.4) Database Security
 Physical security
S
Y
S
T
E
M
S
 Protecting hardware
 Protecting software and
data.
 Logical security
 Unauthorized disclosure
 Unauthorized modification
 Unauthorized withholding
 Security Threats





Employees (!)
Programmers
Visitors
Consultants
Business partnerships
 Strategic sharing
 EDI (Electronic Data
Interchange & other interorg. networks)
 Hackers (Internet)
17 of 21
D
B
S
Y
S
T
E
M
S
Data Privacy
• A security issue
• Who owns data? (a governance issue)
• Customer rights
• International issues (e.g., strict privacy regulations in West
Europe; Canada stricter than the US)
18 of 22
D
B
S
Y
S
T
E
M
S
Physical Security
 Hardware-related
 Preventing problems
(fire, water…)
 Hardware backup
facilities (“Hot sites” etc.)
 Telecommunication
systems for backup
 Data and software
 Backups, Off-site
backups (!)
 Disaster planning
 Plans, training & testing
 Personal computers
challenge (use file servers
for backup)
19 of 21
D
B
Managerial Controls
 Insiders
S
Y
S
T
E
M
S
 Employee selection & Job termination
 Monitoring suspicious behavior
 Job segmentation (who can do what with data*)
Physical & Logical access limitations
 Outsiders
 Physical access limitations
 “Shadowing”
20 of 21
D
B
S
Y
S
T
E
M
S
Logical Security
 1. Unauthorized disclosure (e.g., letting a competitor see the
strategic marketing plans)
 2. Unauthorized modification (e.g., letting employees change
their salary figures)
 3. Unauthorized withholding (e.g., preventing a finance officer
from retrieving data needed to get a bank loan)
21 of 21