Database Security Using examples from ORACLE and ACCESS

1
Database SecurityUsing examples from ORACLE and
ACCESS

• Security is one of the functions of the Database
  Administrator
• But the users must also play a part

2
ORACLE examples

• Security can be consider at four levels
• database
• table
• column
• row

3
Database level security

• Userid and passwords allocated by the DBA
• When you logon to ORACLE you have the privileges
  of the userid that you have entered
• GRANT RESOURCE,CONNECT,DBA TO username
  IDENTIFIED BY password

4
Privileges

• CONNECT - the ability to logon to the database
  and access and manipulate tables
• RESOURCE - the ability to create tables
• DBA - the ability to
• execute restricted commands
• manipulate data in any table
• create tables
• However these three standard roles can be added
  to by the DBA creating roles and adding privileges

5
ACCESS examples

• The DBA needs to set up
• Workgroups
• User Accounts (with or without passwords)
• Permissions

6
Granting permissions

• DBA can grant permission to Users or groups to
  perform various operations on
• the DB structure
• tables within the structure
• forms, reports, queries and macros

7
Permissions

• Permissions that can be granted depend on what
  you are looking at and who is being granted
  permission
• Database
• Open/Run
• Open Exclusive
• Tables
• Read, Modify design
• Read, Update, Insert, delete data

8
Managing permissions

• Three stages
• Planning
• Establishing the user groups
• Assigning permissions fro the database objects

9
Planning

• Determine overall strategy
• does each user need a unique ID?
• are there logical groupings?
• if multiple database systems are to be used will
  they share the same server (and hence same
  users/groups)?
• Plan will then consist of
• list of all DB applications
• list of all users
• list of logical groups

10
Assigning permissions for DB objects

• Each separate object (table, query, form etc.)
  can be assigned its own individual permissions
  for each user or group
• Permissions stored within the applications MDB
  file
• Possible to set permissions for all new objects
  therefore define settings early in development to
  save a lot of time
• See Access help under Permissions

11
Database Encryption

• simple process
• transparent to user
• prevents the use of file snoopers which read the
  system directly, hence bypassing the security
  system

12
Data Encryption in Oracle

• Oracle Database Security -- Data Encryption While
  data encryption should not be used as a
  substitute for access control, storing data
  encrypted does provide an additional layer of
  protection on storage media. This helps protect
  sensitive data such as credit card numbers in the
  event of media theft. Media theft can occur due
  to poor physical security, operating system
  configuration or backup processes.
• Oracle Database 10g Release 2 offers Transparent
  Data Encryption (included in Oracle Advanced
  Security), which makes encryption of sensitive
  data simple by transparently encrypting data when
  it is written to disk and decrypting it when it
  is read back to the user. Applications that
  display credit card numbers for further
  processing by a clerk don't have to be modified
  to handle the longer, encrypted strings.
  Applications and authorized users generally won't
  even notice the fact that the data has been
  encrypted on the storage media.
• http//www.oracle.com/technology/deploy/security/d
  b_security/htdocs/enc.html

13
Example from Oracle 10g

• Create table orders
• (order_id number(12) not null
• Customer_id number (12) not null
• Credit_card varchar2(19) encrypt)

14
Database Recovery

• Requirements of Recovery
• Recognition
• Assessment
• Transformation
• Objectives of Recovery
• Minimize loss
• Return to useful state ASAP
• Make recovery automatic as much as possible
• Make non-automatic recovery painless

15
Recovery Resources

• Documentation
• Training
• Backups
• Transaction logs
• Before-images
• After-images
• Diagnostic Fixup programs

16
Managing Recovery

• Recognize the failure
• Determine correct the cause
• Plan a strategy
• Establish a recovery point
• Carry out recovery operation
• Document the proceedings
• Run diagnostics
• Return database to operational status
• Conduct a post mortem
• Update documentation

17
The Cost of Recovery

• Additional I/O
• Additional Processing
• Additional Storage

...but consider the cost of not coping!