Bài giảng Bảo mật CSDL - Chap 8: Application Data Auditing

Database Security and Auditing: Protecting Data Integrity and AccessibilityChapter 8Application Data AuditingDatabase Security and Auditing2ObjectivesUnderstand the difference between the auditing architecture of DML Action Auditing Architecture and DML changesCreate and implement Oracle triggersCreate and implement SQL Server triggersDefine and implement Oracle fine-grained auditingDatabase Security and Auditing3Objectives (continued) Create a DML statement audit trail for Oracle and SQL ServerGenerate a data manipulation historyImplement a DML statement auditing using a repositoryDatabase Security and Auditing4Objectives (continued)Understand the importance and the implementation of application errors auditing in OracleImplement Oracle PL/SQL procedure authorizationDatabase Security and Auditing5DML Action Auditing ArchitectureData Manipulation Language (DML): companies use auditing architecture for DML changesDML changes can be performed on two levels:Row levelColumn levelFine-grained auditing (FGA)Database Security and Auditing6DML Action Auditing Architecture (continued)Database Security and Auditing7DML Action Auditing Architecture (continued)Database Security and Auditing8Oracle TriggersStored PL/SQL procedure executed whenever:DML operation occurs Specific database event occursSix DML events (trigger timings): INSERT, UPDATE, and DELETEPurposes:Audits, controlling invalid dataImplementing business rules, generating valuesDatabase Security and Auditing9Oracle Triggers (continued)Database Security and Auditing10Oracle Triggers (continued)CREATE TRIGGERExecuted in a specific order:STATEMENT LEVEL triggers before COLUMN LEVEL triggersBEFORE triggers before AFTER triggersUSER_TRIGGERS data dictionary view: all triggers created on a tableA table can have unlimited triggers: do not overuse themDatabase Security and Auditing11Oracle Triggers (continued)Database Security and Auditing12SQL Server TriggersCREATE TRIGGER DDL statement: creates a triggerTrigger condition: Prevents a trigger from firingUPDATE() and COLUMNS_UPDATE() functionsLogical tables:DELETED contains original dataINSERTED contains new dataDatabase Security and Auditing13SQL Server Triggers (continued)Restrictions—Transact-SQL statements not allowed:ALTER and CREATE DATABASEDISK INIT and DISK RESIZEDROP DATABASE and LOAD DATABASELOAD LOGRECONFIGURERESTORE DATABASERESTORE LOGDatabase Security and Auditing14Implementation of an Historical Model with SQL ServerCreate a history table:Same structure as original tableHISTORY_ID columnCreate a trigger: inserts original row into the HISTORY tableDatabase Security and Auditing15Fine-grained Auditing (FGA) with OracleOracle provides column-level auditing: Oracle PL/SQL-supplied package DBMS_FGADBMS_FGA procedures:ADD_POLICYDISABLE_POLICYDROP_POLICYENABLE_POLICYDatabase Security and Auditing16Fine-grained Auditing (FGA) with Oracle (continued)ADD_POLICY parameters:OBJECT_SCHEMAOBJECT_NAMEPOLICY_NAMEAUDIT_CONDITIONAUDIT_COLUMNHANDLER_SCHEMADatabase Security and Auditing17Fine-grained Auditing (FGA) with Oracle (continued)ADD_POLICY parameters (continued):HANDLER_MODULEENABLESTATEMENT_TYPESDBA_FGA_AUDIT_TRAIL: view the audit trail of the DML activitiesDatabase Security and Auditing18DML Action Auditing with OracleRecord data changes on the table:Name of the person making the changeDate of the changeTime of the changeBefore or after value of the columns are not recordedDatabase Security and Auditing19DML Action Auditing with Oracle (continued)Database Security and Auditing20DML Action Auditing with Oracle (continued)Steps:Use any user other than SYSTEM or SYS; with privileges to create tables, sequences, and triggersCreate the auditing tableCreate a sequence objectCreate the trigger that will record DML operationsTest your implementationDatabase Security and Auditing21History Auditing Model Implementation Using OracleHistorical data auditing is simple to implement; main components are TRIGGER objects and TABLE objectsKeeps record of:Date and time the copy of the record was capturedType of operation applied to the recordDatabase Security and Auditing22History Auditing Model Implementation Using Oracle (continued)Steps:Use any user other than SYSTEM or SYS; with privileges to create tables, sequences, and triggersCreate history tableCreate the trigger to track changes and record all the values of the columnsTest your implementationDatabase Security and Auditing23DML Auditing Using Repository with Oracle (Simple 1)Simple Auditing Model 1Flag users, tables, or columns for auditingRequires less database administrative skills:Application administrators can do itUser interface is built in top of the repositoryAuditing flags are flexibleDoes not record before or after column values; only registers type of DML operationsDatabase Security and Auditing24DML Auditing Using Repository with Oracle (Simple 1) (continued)Database Security and Auditing25DML Auditing Using Repository with Oracle (Simple 1) (continued)Steps:Use any user other than SYSTEM or SYSCreate triggersCreate sequence objectBuild tables to use for applicationsPopulate application tablesDatabase Security and Auditing26DML Auditing Using Repository with Oracle (Simple 1) (continued)Steps (continued):Populate auditing repository with metadataCreate the stored package to be used with the triggerCreate triggers for application tablesTest your implementationDatabase Security and Auditing27DML Auditing Using Repository with Oracle (Simple 2)Simple Auditing Model 2: requires a higher level of expertise in PL/SQLStores two types of data:Audit data: value before or after a DML statementAudit table: name of the tables to be auditedDatabase Security and Auditing28DML Auditing Using Repository with Oracle (Simple 2) (continued)Database Security and Auditing29DML Auditing Using Repository with Oracle (Simple 2) (continued)Steps:Use any user other than SYSTEM or SYS; with privileges to create tables, and triggersCreate the auditing repositoryEstablish a foreign key in AUDIT_DATA table referencing AUDIT_TABLE tableCreate a sequence objectCreate the application schemaDatabase Security and Auditing30DML Auditing Using Repository with Oracle (Simple 2) (continued)Steps (continued):Add data to tablesA stored PL/SQL package will be used for auditing within the triggersCreate triggers for audited tablesAdd auditing metadataTest your implementationDatabase Security and Auditing31Auditing Application Errors with OracleApplication errors must be recorded for further analysisBusiness requirements mandate to keep an audit trail of all application errorsMaterials:Repository consisting of one tableMethodology for your applicationDatabase Security and Auditing32Auditing Application Errors with Oracle (continued)Steps:Select any user other than SYSTEM or SYS; with privileges to create tables, and proceduresPopulate tablesCreate the ERROR tableCreate a stored package to perform the UPDATE statementTest your implementation: perform and update using the CREATE packageDatabase Security and Auditing33Oracle PL/SQL Procedure AuthorizationOracle PL/SQL stored procedures are the mainstay of implementing business rulesSecurity modes: Invoker rights: procedure is executed using security credentials of the callerDefiner rights: procedure is executed using security credentials of the ownerDatabase Security and Auditing34Oracle PL/SQL Procedure Authorization (continued)Steps:Create a new userSelect a user with CREATE TABLE and PROCEDURE privilegesPopulate tablesCreate stored procedure to select rows in a tableGrant EXECUTE privileges on new procedureLog on as the new user and query the tableExecute procedureDatabase Security and Auditing35SummaryTwo approaches for DML auditing:Set up an audit trail for DML activitiesRegister all column values before or after the DML statement (column-level auditing)Fine-grained auditing (Oracle)Triggers:Stored PL/SQL procedure automatically executedOracle has six DML eventsDatabase Security and Auditing36Summary (continued)Triggers are executed in orderUSER_TRIGGERS data dictionary view: shows all triggersSQL Server 2000:CREATE TRIGGER DDL statementConditional functions: UPDATE() and COLUMNS_UPDATED()FGA allows generation of audit trail of DML activitiesDatabase Security and Auditing37Summary (continued)FGA is capable of auditing columns or tables; Oracle PL/SQL-supplied package DBMS_FGAPL/SQL stored procedures security modes:Invoker rightsDefiner rights