Transcript System Administration - University of Manitoba

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 2016
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’s Task 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 technicalities 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
DBA: System Upgrade
 Determines need for change
S
Y
S
T
E
M
S
 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
S
Y
S
T
E
M
S
DBA’s Task 2.1 - Users’ Access
 Control via:
 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’s Task 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’s Task 2.3 - Database Backup
Full
 Backups are crucial!
S
Y
S
T
E
M
S
 Offsite storage needed
 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
Operational
Database
(Op DB)
Backup
Manager
Copies
Entire
OpDB
Backup
overwrites Database
(Bkp DB)
Partial
(part of
DBMS)
t1
t2
new data
Partial backup
Operational
grows
Database new data
(Op DB)
Partial backup
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
C*R*A*S*H
Transaction 106 incomplete. Steps before Checkpoint (Savepoint) are saved. Those after it
must be canceled in TL and run again until completion of Transaction 106.
16 of 22
D
B
S
Y
S
T
E
M
S
DBA’s Task 2.4 - Database Security
 Security Threats
 Physical security
 Protecting hardware
 Protecting software and
data.
 Logical security
 Unauthorized disclosure
 Unauthorized modification
 Unauthorized withholding





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.)
 Data and software
 Backups, Off-site
backups (!)
 Disaster planning
 Plans, training & testing
 Telecommunication
systems for backup
 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