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

Thêm vào BST

Báo xấu

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.

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

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