Appliction Security and Misc' Topics

1
Appliction Security and Misc. Topics

• May 1, 2006

2
Application security

• Often an RDBMS is being used as a back end for a
  complex application
• Users rights and required data access depend on
  their role within the application
• Users access to data is indirect and occurs
  through their access to elements of the
  application (forms reports, etc.)
• Data base must include tables used to manage
  rights to components of the application
• Access to data may be directly managed or managed
  solely through controlling access to components
  of the application

3
Application Security Models

• Models
• Database role based
• Application role and function based
• Application table based

4
Security Model Based on Database Roles (continued)
5
Security Model Based on Database Roles (continued)

• Implementation in Oracle
• Create users
• Add content to your tables
• Add a row for an application user
• Look for application users role
• Activate the role for this specific session

6
Security Model Based on Application Roles and
Functions

• Combination of models
• Application authenticates users
• Application is divided into functions
• Roles are assigned to functions
• Functions are assigned to users
• Highly flexible model

7
Security Model Based on Application Roles and
Functions (continued)
8
Security Model Based on Application Tables

• Depends on the application to authenticate users
• Application provides privileges to the user based
  on tables not on a role or a function
• User is assigned access privilege to each table
  owned by the application owner

9
Security Model Based on Application Tables
(continued)
10
Overview of Virtual Private Databases

• Shared database schema
• Containing data that belongs to different users
• User view or update only data he or she owns
• Purposes/benefits
• Security requirements necessitate data access be
  restricted at row or column level (FGA)
• One database schema serves multiple unrelated
  groups or entities
• Similar to creating restricted VIEWS for
  users/roles
• But may be less complex, especially with many
  users
• Example WebCt
• 1,000s of students
• each can see only her/his data

11
Overview of Virtual Private Databases
12
Implementing Oracle Virtual Private Databases
(continued)
13
SQL Injection Problem

• Any application involving dynamic SQL may fall
  prey to a SQL injection
• Dynamic SQL occurs when the user supplies a
  portion of a SQL statement at execution time
• SQL injection occurs when a user can augment the
  intended user input to a SQL statement to obtain
  unintended results or make changes to the
  database

14
Example of the SQL Injection Problem

• SQLgt select
• 2 from ticket
• 3 where itinerary_no itinerary_no
• Enter value for itinerary_no 10 or 11
• old 3 where itinerary_no itinerary_no
• new 3 where itinerary_no 10 or 11
• ITINERARY_NO FLIGHT_NO FLIGHT_DA SEA
  FARE_CHARGED
• ------------ ---------- --------- ---
  ------------
• 31 101 22-FEB-06
  12
• 1 101 28-MAR-06
  45
• 32 102 01-APR-06
  129
• 1 101 01-NOV-05 1A
  48.5
• 1 102 02-NOV-05 1A
  156
• 2 104 01-NOV-05 2B
  48.5
• 2 604 02-NOV-05 2B
  109
• 3 101 15-JAN-06 3B
  48.5
• 3 104 21-FEB-06 4B
  48.5
• 3 102 15-JAN-06 2A
  156

15
SQL Injection Problem Areas

• Applications accessing a database through ODBC
  often access in ways that allow SQL injection to
  occur
• Solution
• applications accessing data using database stored
  procedures
• SQLPlus and PL/SQL anonymous procedures often
  allow SQL injection
• Solution
• Substitute stored procedures
• But must test input parameters as necessary
• String parameters particularly must be managed
  carefully

16
HIPAA

• Stands for
• Health Insurance Portability Accountability Act
• Passed in 1996

17
HIPAA and IS

• Act requires
• That patients can access their medical
  information at any time in standard format
• That information related to health insurance must
  be exchanged in a standard predefined way
• Privacy
• Medical records are private and must be protected
  
• Fines of up to 250,000 or 10 years imprisonment
  for violations

18
Security Requirements Under HIPAA

• Basically
• Must be able to demonstrate that all sensitive
  data have been restricted to access only by
  individuals with a legitimate need to know the
  information
• Must include an auditing system that records
  breaches or attempted breaches and
• May be required to audit all access to highly
  sensitive information

19
SOx

• Sarbannes-Oxley Act
• Passed after WorldCom and Enron Issues
• Basically makes executives much more fully
  responsible for corporate income statements
• Most be aware of and report any condition that
  materially affects earnings
• Requires consistency of information provided and
  transparency of key information

20
SOx and IS

• Chief executive must personally attest to the
  adequacy of internal audit procedures,
  auditability of systems, etc. and report any
  material deficiencies
• Thus, the act highlights the need for security
  and auditing procedures
• Makes integration of systems to provide a
  consistent financial picture of the firm more
  critical