Transcript CIS-496 / I.S. Auditing

Chapter 3:
Data Management Systems
IT Auditing & Assurance, 2e, Hall & Singleton
DATA-FLAT FILES
 e.g., Figure 3.1 [p.94]
 Disadvantages





Data storage
Data updating
Currency of information
Task-data dependency (limited access)
Data integration (limited inclusion)
 Do not use accounting data to support
decisions
 Manipulate existing data to suit unique needs
 Obtain additional private sets of data,
incurring costs and operational problems
IT Auditing & Assurance, 2e, Hall & Singleton
DATA-DATABASE
 e.g., Figure 3.2 [p.96]
 How database approach eliminates
the five disadvantages of flat files




Data storage
Data updates
Currency of information
Task-data dependency (limited
access)
 Data integration (limited inclusion)
IT Auditing & Assurance, 2e, Hall & Singleton
CENTRALIZED DATABASE
SYSTEM
 Figure 3.3 [p.98]
Database Environment




DBMS
Users
Database administrator
Physical database
IT Auditing & Assurance, 2e, Hall & Singleton
DBMS
 Typical features
 Program development
 Backup and recovery
 Database usage reporting
 Database access
IT Auditing & Assurance, 2e, Hall & Singleton
DBMS
 Data definition language (DDL)
 Views
 Figure 3.4
[p.99]
 Internal / physical view
 Conceptual / logical view
 External / user view
IT Auditing & Assurance, 2e, Hall & Singleton
USERS




Formal access: application interfaces
Data manipulation language (DML)
DBMS operations: 7 steps [Figure 3.4]
Informal access: query
 Define query
 SQL
 is industry de facto standard query language
 Select, from, where commands
 Review Figure 3.5 [p.101] – SQL process
 QBE
IT Auditing & Assurance, 2e, Hall & Singleton
DBA
 DBA
 Manages the database resources
Table 3.1 [p.102]
 Database planning
 Database design
 Database implementation
 Database operations & maintenance
 Change & growth
 Data dictionary
 Interactions
[Figure 3-6, p.103]
IT Auditing & Assurance, 2e, Hall & Singleton
PHYSICAL DATABASE
 Data structures
 Data organization
 Sequential
 Random
 Data access methods
 Data hierarchy
 Attribute/field
 Record
 Associations
 File
 Database
 Enterprise database
IT Auditing & Assurance, 2e, Hall & Singleton
DATABASE MODELS
 Hierarchical
 Network
 Relational
IT Auditing & Assurance, 2e, Hall & Singleton
RELATIONAL MODEL:
2-dimensional
IT Auditing & Assurance, 2e, Hall & Singleton
RELATIONAL MODEL - TERMS
 TABLE = file
 COLUMN = field
 ROW = record
IT Auditing & Assurance, 2e, Hall & Singleton
RULE #1
 Entries in the table cells MUST be
single-valued
 Cannot be null
 Cannot be multi-values
 Example
IT Auditing & Assurance, 2e, Hall & Singleton
RULE #2
 “Consistency” applies to columnar
values – same class
IT Auditing & Assurance, 2e, Hall & Singleton
RULE #3
 Column names are distinct
 Example “cost” for sales price and
unit cost columns
IT Auditing & Assurance, 2e, Hall & Singleton
RULE #4
 Each row contains distinctively
different data from all other rows
 Requires use of “key field(s)”
IT Auditing & Assurance, 2e, Hall & Singleton
RELATIONAL MODEL
 Figure 3-13, p. 112
IT Auditing & Assurance, 2e, Hall & Singleton
DATABASE IN DDP
 Data concurrency problem
 Deadlock (illustrated in Figure 3-17, p. 118)
Time 1: User 1 loads File A, User 2 loads File C User 3 loads File E
Time 2: User 1 locks File A, User 2 locks File C, User 3 locks File E
Time 3: User 1 tries to load File C … “wait”
User 2 tries to load File E … “wait”
Use 3 tries to load File A … “wait”
DEADLOCK!!
 Deadlock Resolution
IT Auditing & Assurance, 2e, Hall & Singleton
DATABASE IN DDP
 Distributed database
 Partitioned
 Replicated
 Concurrency control
 Classified
 Time-stamps
IT Auditing & Assurance, 2e, Hall & Singleton
CONTROLLING & AUDITING
DBMS
 Access controls
 User views / subschema [see Figure 3-20,
p.121]
 Database authorization table [Table 3-3,
p.122]
 User-defined procedures
 Mother’s maiden name
 Data encryption
 Biometric devices
 Inference controls (query)
 example (p. 123)
IT Auditing & Assurance, 2e, Hall & Singleton
CONTROLLING & AUDITING DBMS:
Audit Procedures
OBJECTIVE: Verify that database access
authority and privileges are granted to users
in accordance with legitimate needs.

Tables and subschemas







Review policy and job descriptions
Examine programmer authority tables for access to
DDL
Interview programmers and DBA
Appropriate access authority
Biometric controls
Inference controls
Encryption controls
IT Auditing & Assurance, 2e, Hall & Singleton
CONTROLLING & AUDITING DBMS:
Audit Procedures
OBJECTIVE: Verify that backup controls in
place are effective in protecting data files
from physical damage, loss, accidental
erasure, and data corruption through system
failures and program errors.
 Backups
 Logs
 Checkpoint
 Recovery module
IT Auditing & Assurance, 2e, Hall & Singleton
CONTROLLING & AUDITING DBMS:
Audit Procedures
OBJECTIVE: Verify that controls over the
data resource are sufficient to preserve the
integrity and physical security of the
database.
IT Auditing & Assurance, 2e, Hall & Singleton
Chapter 3:
Data Management Systems
IT Auditing & Assurance, 2e, Hall & Singleton