Transcript Database Management Systems

D
B
S
Y
S
T
E
M
S
University of Manitoba
Asper School of Business
3500 DBMS
Bob Travica
Chapter 10
DB System
Administration (Part II)
Based on G. Post, DBMS: Designing & Building Business Applications
Updated 2007
1 of 13
1
D
B
S
Y
S
T
E
M
S
Outline
User Identification
Access Privileges
Separation of Duties
Encryption
2 of 13
D
B
S
Y
S
T
E
M
S
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.
3 of 13
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
4 of 13
D
B
Oracle Security Manager
S
Y
S
T
E
M
S
5 of 13
D
B
S
Y
S
T
E
M
S
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).
6 of 13
D
B
Separation of Duties
Supplier
SupplierID Name…
S
Y
S
T
E
M
S
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 enters order data but
not supplier data. He must
use SupplierID from the
Supplier table.
7 of 13
D
B
Securing an MS Access Database
 Set up a secure workgroup
S
Y
S
T
E
M
S
 Accounts
 Passwords
 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.
8 of 13
D
B
S
Y
S
T
E
M
S
Procedure for Setting Up Password in Access
1.
2.
3.
4.
Start Access
Click File/Open
Mark the mdb file to be opened
On the Open button (lower part of Open window) click
arrow and choose Open Exclusive
5. Click Tools/Security/Set Password
6. In Set Database Password window type the password
Next time you try to open the database you will be asked to
enter the password.
9 of 13
D
B
S
Y
S
T
E
M
S
Encryption
 Data transmission, distributed databases
Single Key Method –
Message
DES - Encrypt
shared private key
 Data Encryption Standard - DES;
Advanced Encryption System – AES,
128-256 bits)
Encrypted
Message
DES - Decrypt
 64 bit key can be broken within
a few minutes to 2 weeks; 128 bit+
keys still safe
Message
10 of 13
D
B
S
Y
S
T
E
M
S
RECEIVER
Dual Key Method
- private and public key
Message
SENDER
Message
Mary
Decripts
Mary’s
Private Key
Ted
Encrypts
Ted’s
Public Key
Mary’s
Public Key
Ted’s
Private Key
 Using Ted’s private key ensures it came from him.
 Using Mary’s public key ensures only she can read it.
 Useful for changing partners context (supply chain, virtual
organization)
11 of 13
D
B
S
Y
S
T
E
M
S
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
12 of 13
D
B
Sally’s Pet Store: Purchases
Purchase
S
Y
S
T
E
M
S
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
Legend:
R: Read
W: Write
A: Add
13 of 13