II. Selected Database Issues

1. 3C13/D6 II. Selected Database Issues Part 1: Security Lecture 2 Lecturer: Chris Clack

2. 1. Content Content • 2.0 Objectives • 2.1 Countermeasures: computer-based controls (BRAVE) • - 2.1.1 Encryption • 2.2 Authentication and Authorisation in Microsoft Access DBMS • - 2.2.1 Authentication: Setting a password • - 2.2.2 Authorisation: User-level security • 2.3 Authorisation in Oracle DBMS • - 2.3.1 Privileges

3. 2.0 Objectives Objectives In this Lecture you will learn: • The final element of “BRAVE” security control mechanisms: • Encryption • Microsoft Access authentication and authorisation measures • Oracle DBMS security measures

4. 2.1 BRAVE: Encryption

5. 2.1 Countermeasures: computer-based controls Encryption • Encryption: the encoding of the data by a special algorithm that renders the data unreadable by any program without the decryption key. • Encode data of a sensitive nature as a precaution against possible external threats. The DBMS can access the data after decoding it, although there is a degradation in performance as it takes time to decode. • Encryption protects data transmitted over communication lines. There are a number of techniques for encoding data…

6. 2.1 Countermeasures: computer-based controls Encryption • Irreversible encryption • Does not permit the original data to be known. • However, the encrypted data can be used to obtain valid statistical information. • Reversible encryption • More common. • To transmit data securely over an insecure network requires the use of a cryptosystem.

7. 2.1 Countermeasures: computer-based controls Encryption • Cryptosystem Includes: • Encryption key to encrypt the data (plaintext). • Encryption algorithm that, with the encryption key, transforms the plaintext into ciphertext • Decryption key to decrypt the ciphertext • Decryption algorithm that, with the decryption key, transforms the ciphertext back into plaintext. text text cypher text encryption algorithm decryption algorithm encryption key decryption key

8. 2.1 Countermeasures: computer-based controls Encryption • Symmetric Encryption: • Uses the same key for encryption and decryption. • Requires a secure communication line for exchanging the key. Most users don't have a secure line. • To be really secure the key should be as long as the message, most however use shorter keys. • Asymmetric Encryption: • Uses different keys. Symmetric Example:DES (Data Encryption Standard) • Developed by IBM. • Not universally regarded as being secure. Some authors say a larger key is required. • PGP Pretty Good Privacy uses a 128-bit symmetric algorithm for bulk encryption of the data it sends.

9. 2.1 Countermeasures: computer-based controls Encryption • Keys with 64-bits are now considered breakable by major governments, though at substantial cost. • This technology will be within the reach of organized criminals, major organizations and smaller governments either already or very soon • It is probable that keys with 128-bits will remain unbreakable for the foreseeable future. • The terms strong authentication and weak authentication are sometimes used to distinguish between algorithms that cannot be broken with existing technologies and knowledge (strong) and those that can (weak).

10. 2.1 Countermeasures: computer-based controls Encryption Asymmetric Example:Public key systems. Two keys are used. One is public and one is private. The encryption algorithm may also be public. Anyone can send an encoded message using the public key and algorithm given by the owner. But only the owner of the private key may decipher the message. • A digital signature can also be used to prove the message came from the person who claimed to have sent it (see later lecture). • RSA is the most well known asymmetric encryption (the name derives from the initials of the algorithm designers). • Generally, asymmetric algorithms are much slower to execute. In practice the two types of encryption are used together.

11. 2.2 Authentication and authorisation in Microsoft Access DBMS

12. 2.2 Authentication & authorisation in Microsoft Access DBMS Authentication and authorisation in Microsoft Access DBMS Prerequisite course covered Microsoft Access 2000 DBMS and SQL GRANT and REVOKE statements • Access instead of GRANT and REVOKE statements, Access provides: • system security: setting a password for opening a database (authentication) • data security: user-level security, which limits the parts of the database a user can read or update (authorisation)

13. 2.2 Authentication & authorisation in Microsoft Access DBMS Authentication in Microsoft Access DBMS Example (Password): Password set from Tools, Security menu Secure. Access encrypts password

14. 2.2 Authentication & authorisation in Microsoft Access DBMS Authorisation in Microsoft Access DBMS Example (user-level security) Within the Microsoft Access workgroup information File users are identified as belonging to a group. • Default groups: • administrator (Admin group) • - users (Users group).

15. 2.2 Authentication & authorisation in Microsoft Access DBMS Authorisation in Microsoft Access DBMS Example (user-level security: Permissions) • Permissions granted to groups and users • Dialog box used to regulate how they can work with given objects • No. of possible permissions: Open/Run, Read Design, Modify Design, Update Data, Read Data, Delete Data etc...

16. 2.3 Authorisation in Oracle DBMS

17. 2.3 Authorisation in Oracle DBMS Authorisation in Oracle DBMS Privileges • Privilege: the right to execute a particular type of SQL statement or to access another user’s objects. • Some examples of Oracle privileges are: • connect to the database (create a session) • create a table • select rows from another user’s table

18. 2.3 Authorisation in Oracle DBMS Authorisation in Oracle DBMS Privileges • There are two distinct categories of privileges in Oracle: • 1. System privileges: the right to perform a particular action, or to perform an action on schema objects of a certain type. Control privileges associated with some schema objects (clusters, indexes and triggers). • Example: creation of table spaces or of users in a database. • Privileges granted or revoked in two ways: • Grant System Privileges/Roles dialog box and Revoke System Privileges/Roles dialog box of Oracle Security Manager. • SQL GRANT and REVOKE statements.

19. 2.3 Authorisation in Oracle DBMS Authorisation in Oracle DBMS Privileges 2. Object privileges: the right to perform a particular action on a specific table, view, sequence, procedure, function or package. Example: the privilege to delete rows from the Staff table. A user automatically has all object privileges for schema objects contained in their schema. They can grant object privileges on any schema object owned. The inclusion in the grant of the WITH GRANT OPTION (of the GRANT statement) allows the user to further grant the object privileges to other users.

20. 2.3 Authorisation in Oracle DBMS Authorisation in Oracle DBMS Privileges • A user can receive a privilege in two different ways: • Privileges can be granted to users explicitly. • Example: GRANT INSERT ON PropertyForRent TO • Beech, means the object privilege “insert rows” has • been granted to Mr Beech on the said table. • 2. Privileges can be granted to a role. • The role can then be granted to one or more • users. • Example: the object privileges insert, delete • and update could be granted to the role • `Assistant’ User Beech can then be granted the • role Assistant. `Role ’ : a named group of privileges A user can access several roles and several users can be assigned the same role. This is a better and easier way to manage privileges.

21. 2.4 Summary Summary • 1.3 The final “BRAVE” countermeasure • E ncryption • 2.2 Authentication and authorisation in Microsoft Access DBMS • Setting a password • User-level security • 2.3 Authorisation in Oracle DBMS • Privileges NEXT LECTURE: DBMS and Web Security - Firewalls - Signatures - Certificates