8. Database Security and Administration

Why do we care?
Data is valuable! it must be strictly controlled and managed, as with any corporate resource
The DBMS must ensure that the database is secure
Database security covers not only DBMS, but also its environment,

• Hardware, software (DBMS, application software, OS, etc…), people data, procedures

Security

• Confidentiality – Secrecy over data
• Integrity – Protect against invalid or corrupted data
• Availability – Data or systems available to legitimate users
• Authentication
• Non-repudiation
  CIA of security
  

Database Security

Protect against:

• Loss of confidentiality on the secrecy over data critical to the organisation
• Loss of privacy on data about individuals
• Loss of integrity with invalid or corrupted data
• Loss of availability as data or systems are unavailable to legitimate users
• Theft and fraud of data, identity, programs, or hardware
  Threat – Any situation or event, whether intentional or unintentional, that will adversely affect a system and consequently an organisation
• Need to consider not only the data held in a database but the whole ecosystem
• Failure to protect could lead to loss of confidentiality, loss of competitiveness, loss of privacy, legal action, corrupted data, financial loss, etc…
  
  
  Countermeasures:
• Physical Controls
• Procedures
• Computer-Based Controls

Computer-Based Controls

Authorisation

The granting of a right or privilege, which enables a subject to legitimately have access to a system or a system’s object

• Involves authentication which determines whether a user is, who he or she claims to be
• A common authentication method is through the user of a password (we recommend a passphrase)
• How strong is your password
• Other methods? Multi-factor authentication such as Duo

Access Control

Methods used to enforce authorisation

• Based on the granting and revoking privileges

• A privilege allows a user to create or access (that is read, write, or modify) some database object (such as a relation, view, or index) or to run certain DBMS utilities

• Privileges are granted to users to accomplish the tasks required for their jobs

• Most DBMSs provide an approach called Discretionary Access Control (DAC)

• SQL standard supports DAC through the GRANT and REVOKE commands*

• The GRANT command gives privileges to users, and the REVOKE command takes away privileges
  *SQLite does not support GRANT and REVOKE.
  Access control is through the API at the application level
  Examples:

GRANT SELECT, UPDATE (salary)
ON Staff
TO Personnel, Director;

GRANT SELECT
ON Branch
TO PUBLIC

Views

Hiding parts of the database from certain users

Backup & Recovery

Backup data at regular intervals to a secure location
Journaling

• Keep and maintain a log file (or journal) of all changes made to the database to enable effective recovery in the event of failure

Integrity Constraints

Through enforcing integrity constraints

• Domain constraints
• Entity integrity
• Referential integrity

Encryption

• Applying an encryption algorithm on data
• The encoding of the data by a special algorithm that renders the data unreadable by any program without the decryption key
• Four components: encryption key, encryption algorithm, decryption key, decryption algorithm
• Symmetric encryption – same key
  - Caesar cipher, Vigenere, DES, 3DES, Blowfish, AES, ChaCha
• Asymmetric encryption – different key
  - RSA (Rivest–Shamir–Adleman), Elliptical Curve, Cryptography (ECC), Diffie-Hellman
  Asymmetric Encryption:
  A set of keys: secrete/private key, public key
  Challenges:
  • Key generation
  • Authentication of the public key $\to$ Public key infrastructure (PKI)
    Usage:
  • Public key encryption
  • Digital signatures

Public-key Infrastructure (PKI)
A set of roles, policies, hardware, software and procedures needed to create, manage, use, store, and revoke digital certificates and manage public-key encryption
CA - Certificate Authority

• Creates, issues and manages the PKs as a trusted third party
  RA - Registration Authority
• Ensures an authorised identity is requesting a certificate
  VA - Validation Authority
• Authenticates and validates issued certificates
  

Hashing

Process of taking a group of characters, apply a hash function to it, to generate a fixed length hash value

• One way encryption as the hashes are irreversible
  Examples: MD5, SHA, SHA-2, SHA-3, DSA, Whirlpool, BLAKE, BLAKE-2, BLAKE-3
  Usage:
  • Verify integrity of files and messages
  • Digital signatures
  • Password verification

DBMSs and Web Security

Internet communication relies on TCP/IP as the underlying protocol. However, TCP/IP and HTTP were not designed with security in mind. Without special software, all internet traffic travels ‘in the clear’ and anyone who monitors traffic can read it

You must ensure transmission over the Internet is:

• Inaccessible to anyone but the send and receiver (privacy/confidentiality?)
• Not changed during transmission (integrity)
• Receiver can be sure it came from the sender (authenticity)
• Sender can be sure receiver is genuine (non-fabrication)
• Sender cannot deny they sent it (non-repudiation)

Security measures include:

• Proxy servers
• Firewalls
• Message digest algorithms and digital signatures
• Digital certificates
• Kerberos
• Secure Sockets Layer (SSL) and Secure HTTP (S-HTTP)
• Secure Electronic Transactions (SET) and Secure Transaction Technology (SST)

Data & DB Administration

Data administrator (DA)

• Managing data resources, including database planning, development, maintenance of standards, policies, procedures, and conceptual and logical database design
  Database administrator (DBA)
• Application/physical database design to operational management including setting security and integrity control, and monitoring system performance

Data Administration Tasks

• Selecting appropriate productivity tasks
• Assisting in the development of the corporate IT/IS and enterprise strategies
  • $\text{Information Tech and Information Systems}$
• Undertaking feasibility studies and planning for database development
• Developing a corporate data model
• Determining the organisation’s data requirements
• Setting data collection standards & data formats
• Estimating volumes of data and likely growth
• Determining patterns and frequencies of data usage
• Determining data access requirements and safeguards for both legal and enterprise requirements
• Undertaking conceptual and logical database design
• Liaising with other parties involved with the database to ensure all requirements are met.
• Educating users on data standards & legal responsibilities
• Keeping up to date with IT/IS and enterprise developments
• Ensuring the documentation is up to data and complete
• Managing the data dictionary
• Liasing with users to determine new requirements and to resolve difficulties over data access or performance
• Developing a security policy

Database Administration Tasks

• Evaluating and selecting DBMS products
• Physical database designs and implementation on target DBMS
• Defining security and integrity constraints
• Liasing with database application developers
• Developing test strategies
• Training users
• “Signing off” on the implemented database system
• Monitoring system performance and tuning the database
• Performing routine backups
• Ensuring recovery mechanisms & procedures are in place
• Ensuring that documentation is complete
• Keeping software & hardware up to date, including cost