Transcript Database Administration

D
A
T
A
B
A
S
E
Data Administration
 Data and information are
valuable assets.
 Data is used at many business
levels
 Operations and transactions.
 Tactical management.
 Strategic management.
 There are many databases and
applications in an organization.
 Someone has to be responsible
for organizing, controlling, and
sharing data.
Strategic
Management
Tactical
Management
Business Operations
 Data Administrator (DA)
1
D
A
T
A
B
A
S
E
Data Administrator (DA)
 Provide centralized control over the data.
 Data definition.
 Format
 Naming convention
 Data integration.
 Selection of DBMS.
 Act as data and database advocate.
 Application ideas.
 Decision support.
 Strategic uses.
 Coordinate data integrity, security, and control.
2
D
A
T
A
B
A
S
E
Database Administrator (DBA)
 Install and upgrade DBMS.
 Create user accounts and
monitor security.
 In charge of backup and
recovery of the database.
 Monitor and tune the
database performance.
 Coordinate with DBMS
vendor and plan for
changes.
 Maintain DBMS-specific
information for developers.
3
D
A
T
A
B
A
S
E
DBA Tools: Oracle Schema Manager
4
D
A
T
A
B
A
S
E
DBA Tools: SQL Server Enterprise Mgr.
5
D
A
T
A
B
A
S
E
Microsoft Access
6
D
A
T
A
B
A
S
E
DBA Tools: Performance Monitors
7
D
A
T
A
B
A
S
E
Microsoft Access: Analyze Performance
Tools
Analyze
Performance
8
D
A
T
A
B
A
S
E
SQL Server Query Analyzer
9
D
A
T
A
B
A
S
E
SQL Query Analyzer Detail
10
D
A
T
A
B
A
S
E
Oracle Query Analysis
ALTER SYSTEM SET TIMED_STATISTICS=true;
ALTER SYSTEM SET USER_DUMP_DEST= ‘newdir’;
ALTER SESSION SET SQL_TRACE = true;
Run your query
ALTER SESSION SET SQL_TRACE = false;
EXPLAIN PLAN
SET STATEMENT_ID = ‘Your title’
INTO output
FOR
Your query
Requires output table be setup first.
11
D
A
T
A
B
A
S
E
Oracle SQL Trace
call
count
----- -----Parse
1
Execute
1
Fetch
12
----- -----total
14
cpu elapsed disk query current
----- ------- ----- ----- ------0.02
0.04
0
0
0
0.00
0.00
0
0
0
0.00
0.00
0
823
4
----- ------- ----- ----- ------0.02
0.04
0
823
4
rows
---0
0
164
---164
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 26
Rows
------164
303
197
498
164
604
Row Source Operation
--------------------------------------------NESTED LOOPS
NESTED LOOPS
TABLE ACCESS FULL SALE
INDEX RANGE SCAN (object id 3398)
TABLE ACCESS BY INDEX ROWID MERCHANDISE
INDEX UNIQUE SCAN (object id 3388)
12
D
A
T
A
B
A
S
E
Database Administration
 Planning
 Determine hardware and software needs.
 Design
 Estimate space requirements, estimate performance.
 Implementation
 Install software, create databases, transfer data.
 Operation
 Monitor performance, backup and recovery.
 Growth and Change
 Monitor and forecast storage needs.
 Security
 Create user accounts, monitor changes.
13
D
A
T
A
B
A
S
E
Database Planning
 Estimation
 Data storage requirements
 Time to develop
 Cost to develop
 Operations costs
14
D
A
T
A
B
A
S
E
Managing Database Design
 Teamwork
 Data standards
 Data repository
 Reusable objects
 CASE tools
 Networks / communication
 Subdividing projects
 Delivering in stages
 User needs / priorities
 Version upgrades
 Normalization by user views
 Distribute individual sections
 Combine sections
 Assign forms and reports
15
D
A
T
A
B
A
S
E
Database Implementation
 Standards for application
programming.
 User interface.
 Programming standards.
 Layout and techniques.
 Variable & object definition.
 Test procedures.
 Data access and ownership.
 Loading databases.
 Backup and recovery plans.
 User and operator training.
16
D
A
T
A
B
A
S
E
Database Operation and Maintenance
 Monitoring usage
 Size and growth
 Performance / delays
 Security logs
 User problems
 Backup and recovery
 User support
 Help desk
 Training classes
17
D
A
T
A
B
A
S
E
Database Growth and Change
 Detect need for change
 Size and speed
 Structures / design
 Requests for additional data.
 Difficulties with queries.
 Usage patterns
 Forecasts
 Delays in implementing changes
 Time to recognize needs.
 Time to get agreement and approval.
 Time to install new hardware.
 Time to create / modify software.
18
D
A
T
A
B
A
S
E
Backup and Recovery
 Backups are crucial!
 Offsite storage!
 Scheduled backup.
 Regular intervals.
 Record time.
 Track backups.
Snapshot
 Journals / logs
 Checkpoint
 Rollback / Roll forward
Journal/Log
Changes
OrdID Odate Amount ...
192
2/2/01 252.35 …
193
2/2/01 998.34 …
OrdID
192
193
194
Odate Amount ...
2/2/01 252.35 …
2/2/01 998.34 …
2/2/01
77.23 ...
OrdID
192
193
194
195
Odate Amount ...
2/2/01 252.35 …
2/2/01 998.34 …
2/2/01
77.23 …
2/2/01 101.52 …
19
D
A
T
A
B
A
S
E
Database Security and Privacy
 Physical security
 Protecting hardware
 Protecting software and
data.
 Logical security
 Unauthorized disclosure
 Unauthorized modification
 Unauthorized withholding
 Security Threats
 Employees / Insiders
 Disgruntled employees
 “Terminated” employees
 Dial-up / home access
 Programmers
 Time bombs
 Trap doors
 Visitors
 Consultants
 Business partnerships
 Strategic sharing
 EDI
 Hackers--Internet
20
D
A
T
A
B
A
S
E
Data Privacy
Who owns data?
Customer rights.
International complications.
Do not release data to others.
Do not read data unnecessarily.
Report all infractions and problems.
21
D
A
T
A
B
A
S
E
 Hardware
Physical Security
 Preventing problems
 Fire prevention
 Site considerations
 Building design
 Hardware backup
facilities
 Continuous backup
(mirror sites)
 Hot sites
 Shell sites
 “Sister” agreements
 Telecommunication
systems
 Personal computers
 Data and software
 Backups
 Off-site backups
 Personal computers
 Policies and procedures
 Network backup
 Disaster planning
 Write it down
 Train all new employees
 Test it once a year
 Telecommunications
 Allowable time between
disaster and business
survival limits.
22
D
A
T
A
B
A
S
E
Physical Security Provisions




Backup data.
Backup hardware.
Disaster planning and testing.
Prevention.
 Location.
 Fire monitoring and control.
 Control physical access.
23
D
A
T
A
B
A
S
E
Managerial Controls
 “Insiders”





Hiring
Termination
Monitoring
Job segmentation
Physical access limitations
 Locks
 Guards and video monitoring
 Badges and tracking
 Consultants and Business alliances
 Limited data access
 Limited physical access
 Paired with employees
24
D
A
T
A
B
A
S
E
Logical Security
 Unauthorized disclosure.
 Unauthorized modification.
 Unauthorized withholding.
 Disclosure example
 Letting a competitor see the
strategic marketing plans.
 Modification example
 Letting employees change
their salary numbers.
 Withholding example
 Preventing a finance officer
from retrieving data needed
to get a bank loan.
25
D
A
T
A
B
A
S
E
User Identification
 User identification
 Accounts
 Individual
 Groups
 Passwords
 Do not use “real” words.
 Do not use personal (or pet)
names.
 Include non-alphabetic
characters.
 Use at least 6 (8)
characters.
 Change it often.
 Too many passwords!
 Alternative identification




Finger / hand print readers
Voice
Retina (blood vessel) scans
DNA typing
 Hardware passwords
 The one-minute password.
 Card matched to computer.
 Best method for open
networks / Internet.
26
D
A
T
A
B
A
S
E
Basic Security Ideas
3
5
 Limit access to hardware
 Physical locks.
 Video monitoring.
 Fire and environment
monitors.
 Employee logs / cards.
 Dial-back modems
 Monitor usage
 Hardware logs.
 Access from network nodes.
 Software and data usage.
 Background checks
 Employees
 Consultants
2
Jones 1111
Smith 2222
Olsen 3333
Araha 4444
phone
company
 Dialback modem





phone
company
4
1
User calls modem
Modem gets name, password
Modem hangs up phone
Modem calls back user
Machine gets final password
27
D
A
T
A
B
A
S
E
Access Controls
 Operating system
 Access to directories
 Read
 View / File scan
 Write
 Create
 Delete
 Access to files
 Read
 Write
 Edit
 Delete
 DBMS usually needs
most of these
 Assign by user or group.
 DBMS access controls








Read Data
Update Data
Insert Data
Delete Data
Open / Run
Read Design
Modify Design
Administer
 Owners and administrator
 Need separate user
identification / login to
DBMS.
28
D
A
T
A
B
A
S
E
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
29
D
A
T
A
B
A
S
E
Oracle Security Manager
30
D
A
T
A
B
A
S
E
SQL Server Security Manager
31
D
A
T
A
B
A
S
E
Using Queries for Control
 Permissions apply to entire
table or query.
 Use query to grant access to
part of a table.
 Example
 Employee table
 Give all employees read
access to name and phone
(phonebook).
 Give managers read access
to salary.
Employee(ID, Name, Phone, Salary)
Query: Phonebook
SELECT Name, Phone
FROM Employee
Security
Grant Read access to Phonebook
for group of Employees.
Grant Read access to Employee
for group of Managers.
 SQL
 Grant
 Revoke
Revoke all access to Employee
for everyone else (except Admin).
32
D
A
T
A
B
A
S
E
Separation of Duties
Supplier
SupplierID Name…
673
772
983
Acme Supply
Basic Tools
Common X
Referential
integrity
Purchasing
manager can add
new suppliers,
but cannot add
new orders.
Resource
Supplier table
PurchaseOrder table
PurchaseItem table
PurchaseOrder
OrderID SupplierID
8882
8893
8895
772
673
009
Purchasing
Manager
Select, Insert
Modify, Delete
Select
Purchasing
Clerk
Select
Select, Insert
Modify, Delete
Clerk must use SupplierID
from the Supplier table,
and cannot add a new
supplier.
33
D
A
T
A
B
A
S
E
Securing an Access Database
 Set up a secure workgroup
 Create a new Admin user.
 Enable security by setting a password
 Remove the original Admin user.
 Run the Security Wizard in the database to be secured.
 Assign user and group access privileges in the new
database.
 Encrypt the new database.
 Save it as an MDE file.
34
D
A
T
A
B
A
S
E
Encryption
 Protection for open transmissions
Plain text
message
 Networks
 The Internet
 Weak operating systems
 Single key
 Dual key
 Protection
 Authentication
DES
Key: 9837362
Single key: e.g., DES
Encrypted
text
 Trap doors / escrow keys
 U.S. export limits
 64 bit key limit
 Breakable by brute force
 Typical hardware:2 weeks
 Special hardware: minutes
Encrypted
text
Key: 9837362
DES
Plain text
message
35
D
A
T
A
B
A
S
E
Dual Key Encryption
Message
Transmission
Message
Encrypt+T+M
Makiko
Private Key
13
Use
Makiko’s
Private key
Encrypt+M
Encrypt+T
Takao
Public Keys
Makiko 29
Use Takao 17
Use
Takao’s
Makiko’s
Public key
Public key
Private Key
37
Use
Takao’s
Private key
 Using Takao’s private key ensures it came from him.
 Using Makiko’s public key means only she can read it.
36
D
A
T
A
B
A
S
E
Sally’s Pet Store: Security
Management
Sally/CEO
Sales Staff
Store manager
Sales people
Business Alliances
Accountant
Attorney
Suppliers
Customers
Products
Sales
Purchases
Receive products
Employees
Hiring/Release
Hours
Pay checks
Animals
Sales
Purchases
Animal Healthcare
Accounts
Payments
Receipts
Management Reports
Operations
Users
37
D
A
T
A
B
A
S
E
Sally’s Pet Store: Purchases
Purchase
Sally/CEO
Store Mgr.
Sales people
Accountant
Attorney
Suppliers
Customers
Purchase Query
Merchandise
Order
Supplier
W/A
W/A
W/A
R*
R
R*
R
R*
R
R*
-
Employee
R: ID, Name
R: ID, Name
R: ID, Name
R: ID, Name
-
City
R
R
R
R
R
-
PurchaseItem Query
Order
Item
Merchandise
W/A
W/A
A
R
R
R
R
R
R
R
-
*Basic Supplier data: ID, Name, Address, Phone, ZipCode, CityID
R: Read
W: Write
A: Add
38