DB System Administration: User Identification, Access Privileges, Separation of Duties and Encryption

1. University of ManitobaAsper School of Business3500 DBMSBob Travica Chapter 10 DB System Administration (Part II) Based on G. Post, DBMS: Designing & Building Business Applications Updated 2007 1

2. User Identification Access Privileges Separation of Duties Encryption Outline

3. User Identification • Alternative identification • Finger / hand print readers • Voice • Retina (blood vessel) scans • DNA typing • Hardware passwords • The one-minute password. • Card matched to computer. • 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!

4. GRANT privileges REVOKE privileges Privileges include SELECT DELETE INSERT UPDATE Objects include Table Table columns (SQL 92+) Query Users include Name/Group PUBLIC SQL Security Commands GRANT INSERT ON Bicycle TO OrderClerks REVOKE DELETE ON Customer FROM Assemblers

5. Oracle Security Manager

6. 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. SQL Grant Revoke Using Queries for Control 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. Revoke all access to Employee for everyone else (except Admin).

7. Separation of Duties Supplier Purchasing manager can add new suppliers, but cannot add new orders. SupplierID Name … 673 Acme Supply 772 Basic Tools 983 Common X Referential integrity PurchaseOrder Clerk enters order data but not supplier data. He must use SupplierID from the Supplier table. OrderID SupplierID 8882 772 8893 673 8895 009

8. Set up a secure workgroup 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. Securing an MS Access Database

9. Procedure for Setting Up Password in Access • 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.

10. Data transmission, distributed databases Data Encryption Standard - DES; Advanced Encryption System – AES, 128-256 bits) 64 bit key can be broken within a few minutes to 2 weeks; 128 bit+ keys still safe Encryption Message Single Key Method – shared private key DES - Encrypt Encrypted Message DES - Decrypt Message

11. 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) Dual Key Method - private and public key RECEIVER SENDER Message Message Mary Decripts Ted Encrypts Ted’s Public Key Ted’s Private Key Mary’s Private Key Mary’s Public Key

12. 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 Animals Sales Purchases Animal Healthcare Employees Hiring/Release Hours Pay checks Accounts Payments Receipts Management Reports Operations Users

13. Sally’s Pet Store: Purchases *Basic Supplier data: ID, Name, Address, Phone, ZipCode, CityID Legend: R: Read W: Write A: Add