YOMEDIA
Lecture Database security and auditing - Protecting data integrity and accessibility - Chapter 8: Application Data Auditing
Chia sẻ: Hấp Hấp
| Ngày:
| Loại File: PPTX
| Số trang:37
67
lượt xem
2
download
Download
Vui lòng tải xuống để xem tài liệu đầy đủ
Lecture Database security and auditing - Protecting data integrity and accessibility - Chapter 8: Application Data Auditing presentation of content Create and implement Oracle triggers, create and implement SQL Server triggers, define and implement Oracle fine-grained auditing,... Mời các bạn cùng tham khảo.
AMBIENT/
Chủ đề:
Nội dung Text: Lecture Database security and auditing - Protecting data integrity and accessibility - Chapter 8: Application Data Auditing
- Database Security and
Auditing: Protecting Data
Integrity and Accessibility
Chapter 8
Application Data Auditing
- Objectives
•
Understand the difference between the auditing
architecture of DML Action Auditing Architecture
and DML changes
•
Create and implement Oracle triggers
•
Create and implement SQL Server triggers
•
Define and implement Oracle fine-grained
auditing
Database Security and Auditing 2
- Objectives (continued)
•
Create a DML statement audit trail for Oracle
and SQL Server
•
Generate a data manipulation history
•
Implement a DML statement auditing using a
repository
Database Security and Auditing 3
- Objectives (continued)
•
Understand the importance and the
implementation of application errors auditing in
Oracle
•
Implement Oracle PL/SQL procedure
authorization
Database Security and Auditing 4
- DML Action Auditing Architecture
•
Data Manipulation Language (DML): companies
use auditing architecture for DML changes
•
DML changes can be performed on two levels:
– Row level
– Column level
•
Fine-grained auditing (FGA)
Database Security and Auditing 5
- DML Action Auditing Architecture
(continued)
Database Security and Auditing 6
- DML Action Auditing Architecture
(continued)
Database Security and Auditing 7
- Oracle Triggers
•
Stored PL/SQL procedure executed whenever:
– DML operation occurs
– Specific database event occurs
•
Six DML events (trigger timings): INSERT,
UPDATE, and DELETE
•
Purposes:
– Audits, controlling invalid data
– Implementing business rules, generating values
Database Security and Auditing 8
- Oracle Triggers (continued)
Database Security and Auditing 9
- Oracle Triggers (continued)
•
CREATE TRIGGER
•
Executed in a specific order:
– STATEMENT LEVEL triggers before COLUMN
LEVEL triggers
– BEFORE triggers before AFTER triggers
•
USER_TRIGGERS data dictionary view: all
triggers created on a table
•
A table can have unlimited triggers: do not
overuse them
Database Security and Auditing 10
- Oracle Triggers (continued)
Database Security and Auditing 11
- SQL Server Triggers
•
CREATE TRIGGER DDL statement: creates a
trigger
•
Trigger condition:
– Prevents a trigger from firing
– UPDATE() and COLUMNS_UPDATE() functions
•
Logical tables:
– DELETED contains original data
– INSERTED contains new data
Database Security and Auditing 12
- SQL Server Triggers (continued)
•
Restrictions—Transact-SQL statements not
allowed:
– ALTER and CREATE DATABASE
– DISK INIT and DISK RESIZE
– DROP DATABASE and LOAD DATABASE
– LOAD LOG
– RECONFIGURE
– RESTORE DATABASE
– RESTORE LOG
Database Security and Auditing 13
- Implementation of an Historical Model
with SQL Server
•
Create a history table:
– Same structure as original table
– HISTORY_ID column
•
Create a trigger: inserts original row into the
HISTORY table
Database Security and Auditing 14
- Fine-grained Auditing (FGA) with
Oracle
•
Oracle provides column-level auditing: Oracle
PL/SQL-supplied package DBMS_FGA
•
DBMS_FGA procedures:
– ADD_POLICY
– DISABLE_POLICY
– DROP_POLICY
– ENABLE_POLICY
Database Security and Auditing 15
- Fine-grained Auditing (FGA) with
Oracle (continued)
•
ADD_POLICY parameters:
– OBJECT_SCHEMA
– OBJECT_NAME
– POLICY_NAME
– AUDIT_CONDITION
– AUDIT_COLUMN
– HANDLER_SCHEMA
Database Security and Auditing 16
- Fine-grained Auditing (FGA) with
Oracle (continued)
•
ADD_POLICY parameters (continued):
– HANDLER_MODULE
– ENABLE
– STATEMENT_TYPES
•
DBA_FGA_AUDIT_TRAIL: view the audit trail
of the DML activities
Database Security and Auditing 17
- DML Action Auditing with Oracle
•
Record data changes on the table:
– Name of the person making the change
– Date of the change
– Time of the change
•
Before or after value of the columns are not
recorded
Database Security and Auditing 18
- DML Action Auditing with Oracle
(continued)
Database Security and Auditing 19
- DML Action Auditing with Oracle
(continued)
•
Steps:
– Use any user other than SYSTEM or SYS; with
privileges to create tables, sequences, and
triggers
– Create the auditing table
– Create a sequence object
– Create the trigger that will record DML
operations
– Test your implementation
Database Security and Auditing 20
Thêm tài liệu vào bộ sưu tập có sẵn:
Báo xấu
LAVA
ERROR:connection to 10.20.1.101:9315 failed (errno=111, msg=Connection refused)
ERROR:connection to 10.20.1.101:9315 failed (errno=111, msg=Connection refused)
Đang xử lý...
Thêm vào BST
Báo xấu
