1 / 37

Database Security and Auditing: Protecting Data Integrity and Accessibility

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

honorato-graham
Download Presentation

Database Security and Auditing: Protecting Data Integrity and Accessibility

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Database Security and Auditing: Protecting Data Integrity and Accessibility Chapter 8 Application Data Auditing

  2. 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 & Auditing: Protecting Data Integrity & Accessibility

  3. 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 & Auditing: Protecting Data Integrity & Accessibility

  4. Objectives (continued) • Understand the importance and the implementation of application errors auditing in Oracle • Implement Oracle PL/SQL procedure authorization Database Security & Auditing: Protecting Data Integrity & Accessibility

  5. 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 & Auditing: Protecting Data Integrity & Accessibility

  6. DML Action Auditing Architecture (continued) Database Security & Auditing: Protecting Data Integrity & Accessibility

  7. DML Action Auditing Architecture (continued) Database Security & Auditing: Protecting Data Integrity & Accessibility

  8. 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 & Auditing: Protecting Data Integrity & Accessibility

  9. Oracle Triggers (continued) Database Security & Auditing: Protecting Data Integrity & Accessibility

  10. 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 & Auditing: Protecting Data Integrity & Accessibility

  11. Oracle Triggers (continued) Database Security & Auditing: Protecting Data Integrity & Accessibility

  12. 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 & Auditing: Protecting Data Integrity & Accessibility

  13. 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 & Auditing: Protecting Data Integrity & Accessibility

  14. 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 & Auditing: Protecting Data Integrity & Accessibility

  15. 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 & Auditing: Protecting Data Integrity & Accessibility

  16. Fine-grained Auditing (FGA) with Oracle (continued) • ADD_POLICY parameters: • OBJECT_SCHEMA • OBJECT_NAME • POLICY_NAME • AUDIT_CONDITION • AUDIT_COLUMN • HANDLER_SCHEMA Database Security & Auditing: Protecting Data Integrity & Accessibility

  17. 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 & Auditing: Protecting Data Integrity & Accessibility

  18. 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 & Auditing: Protecting Data Integrity & Accessibility

  19. DML Action Auditing with Oracle (continued) Database Security & Auditing: Protecting Data Integrity & Accessibility

  20. 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 & Auditing: Protecting Data Integrity & Accessibility

  21. History Auditing Model Implementation Using Oracle • Historical data auditing is simple to implement; main components are TRIGGER objects and TABLE objects • Keeps record of: • Date and time the copy of the record was captured • Type of operation applied to the record Database Security & Auditing: Protecting Data Integrity & Accessibility

  22. History Auditing Model Implementation Using Oracle (continued) • Steps: • Use any user other than SYSTEM or SYS; with privileges to create tables, sequences, and triggers • Create history table • Create the trigger to track changes and record all the values of the columns • Test your implementation Database Security & Auditing: Protecting Data Integrity & Accessibility

  23. DML Auditing Using Repository with Oracle (Simple 1) • Simple Auditing Model 1 • Flag users, tables, or columns for auditing • Requires less database administrative skills: • Application administrators can do it • User interface is built in top of the repository • Auditing flags are flexible • Does not record before or after column values; only registers type of DML operations Database Security & Auditing: Protecting Data Integrity & Accessibility

  24. DML Auditing Using Repository with Oracle (Simple 1) (continued) Database Security & Auditing: Protecting Data Integrity & Accessibility

  25. DML Auditing Using Repository with Oracle (Simple 1) (continued) • Steps: • Use any user other than SYSTEM or SYS • Create triggers • Create sequence object • Build tables to use for applications • Populate application tables Database Security & Auditing: Protecting Data Integrity & Accessibility

  26. DML Auditing Using Repository with Oracle (Simple 1) (continued) • Steps (continued): • Populate auditing repository with metadata • Create the stored package to be used with the trigger • Create triggers for application tables • Test your implementation Database Security & Auditing: Protecting Data Integrity & Accessibility

  27. DML Auditing Using Repository with Oracle (Simple 2) • Simple Auditing Model 2: requires a higher level of expertise in PL/SQL • Stores two types of data: • Audit data: value before or after a DML statement • Audit table: name of the tables to be audited Database Security & Auditing: Protecting Data Integrity & Accessibility

  28. DML Auditing Using Repository with Oracle (Simple 2) (continued) Database Security & Auditing: Protecting Data Integrity & Accessibility

  29. DML Auditing Using Repository with Oracle (Simple 2) (continued) • Steps: • Use any user other than SYSTEM or SYS; with privileges to create tables, and triggers • Create the auditing repository • Establish a foreign key in AUDIT_DATA table referencing AUDIT_TABLE table • Create a sequence object • Create the application schema Database Security & Auditing: Protecting Data Integrity & Accessibility

  30. DML Auditing Using Repository with Oracle (Simple 2) (continued) • Steps (continued): • Add data to tables • A stored PL/SQL package will be used for auditing within the triggers • Create triggers for audited tables • Add auditing metadata • Test your implementation Database Security & Auditing: Protecting Data Integrity & Accessibility

  31. Auditing Application Errors with Oracle • Application errors must be recorded for further analysis • Business requirements mandate to keep an audit trail of all application errors • Materials: • Repository consisting of one table • Methodology for your application Database Security & Auditing: Protecting Data Integrity & Accessibility

  32. Auditing Application Errors with Oracle (continued) • Steps: • Select any user other than SYSTEM or SYS; with privileges to create tables, and procedures • Populate tables • Create the ERROR table • Create a stored package to perform the UPDATE statement • Test your implementation: perform and update using the CREATE package Database Security & Auditing: Protecting Data Integrity & Accessibility

  33. Oracle PL/SQL Procedure Authorization • Oracle PL/SQL stored procedures are the mainstay of implementing business rules • Security modes: • Invoker rights: procedure is executed using security credentials of the caller • Definer rights: procedure is executed using security credentials of the owner Database Security & Auditing: Protecting Data Integrity & Accessibility

  34. Oracle PL/SQL Procedure Authorization (continued) • Steps: • Create a new user • Select a user with CREATE TABLE and PROCEDURE privileges • Populate tables • Create stored procedure to select rows in a table • Grant EXECUTE privileges on new procedure • Log on as the new user and query the table • Execute procedure Database Security & Auditing: Protecting Data Integrity & Accessibility

  35. Summary • Two approaches for DML auditing: • Set up an audit trail for DML activities • Register all column values before or after the DML statement (column-level auditing) • Fine-grained auditing (Oracle) • Triggers: • Stored PL/SQL procedure automatically executed • Oracle has six DML events Database Security & Auditing: Protecting Data Integrity & Accessibility

  36. Summary (continued) • Triggers are executed in order • USER_TRIGGERS data dictionary view: shows all triggers • SQL Server 2000: • CREATE TRIGGER DDL statement • Conditional functions: UPDATE() and COLUMNS_UPDATED() • FGA allows generation of audit trail of DML activities Database Security & Auditing: Protecting Data Integrity & Accessibility

  37. Summary (continued) • FGA is capable of auditing columns or tables; Oracle PL/SQL-supplied package DBMS_FGA • PL/SQL stored procedures security modes: • Invoker rights • Definer rights Database Security & Auditing: Protecting Data Integrity & Accessibility

More Related