Bài giảng Bảo mật CSDL - Chap 9: Auditing Database Activities

Database Security and Auditing: Protecting Data Integrity and AccessibilityChapter 9Auditing Database ActivitiesDatabase Security and Auditing2ObjectivesUse Oracle database activitiesLearn how to create DLL triggers with OracleAudit database activities using OracleDatabase Security and Auditing3Objectives (continued) Audit server activities with Microsoft SQL Server 2000Audit database activities using Microsoft SQL ProfilerUse SQL Server for security auditing Database Security and Auditing4Using Oracle Database ActivitiesSeveral types of activities:Application activities: SQL statements issued against application tablesAdministration activities: commands issued for maintenance and administrative purposesDatabase events: events that occur when a specific activity occursDatabase Security and Auditing5Creating DDL Triggers with OracleAudit program provides:Audit trail for all activitiesOpportunity for using process controlsDatabase activities statements (in addition to DML):Data Definition Language (DDL)Data Control LanguageDatabase eventsSQL statements audit trailDatabase Security and Auditing6Creating DDL Triggers with Oracle (continued)Use CREATE TRIGGER:DDL statementsDatabase eventsDatabase Security and Auditing7Example of LOGON and LOGOFF Database EventsSteps:Log on as SYSTEMCreate the APP_AUDIT_LOGINS tableCreate two triggers:One that fires after the logon eventOne that fires before the logoff eventLog on as DBSEC; disconnect after a few minutesLog on as SYSTEM to check the auditing tableDatabase Security and Auditing8DDL Event ExampleSteps:Log on as SYSTEMCreate a trigger that fires before an ALTER statement is completedLog on as DBSEC and alter a tablePseudocolumns:ora_dict_obj_nameora_dict_obj_ownerora_syseventDatabase Security and Auditing9Auditing Code with OracleSteps:Log on as DBSECCreate an auditing tableCreate a table and populate it with two recordsCreate a trigger to track codeUpdate the new tableLook at the contents of the APP_AUDIT_SQLS tableDatabase Security and Auditing10Auditing Database Activities with OracleOracle provides mechanisms for auditing all:Who creates or modifies the structureWho is granting privileges to whomTwo types of activities based on the type of SQL command statement used:Defined by DDL (Data Definition Language)Defined by DCL (Data Control Language)Database Security and Auditing11Auditing DDL ActivitiesUse a SQL-based AUDIT commandVerify auditing is on:Check the AUDIT_TRAIL parameterValues:DBDB_EXTENDEDOSNONEDatabase Security and Auditing12Auditing DDL Activities (continued)Database Security and Auditing13DDL Activities Example 1Steps:Use any user other than SYS or SYSTEM to create a tableAdd three rows into the tableLog on as SYSTEM or SYS to enable auditing: For ALTER and DELETELog in as DBSEC:Delete a rowModify the structure of the tableDatabase Security and Auditing14DDL Activities Example 1 (continued)Steps (continued):Check the audit recordsLog in as SYSTEM and view the DBA_AUDIT_TRAIL tableTurn off the auditing optionCheck the content of the DBA_AUDIT_OBJECT to see auditing metadataDatabase Security and Auditing15DDL Activities Example 1 (continued)Database Security and Auditing16DDL Activities Example 1 (continued)Database Security and Auditing17DDL Activities Example 2Steps:Log in as SYSTEM or SYS to enable auditing for the TABLE statement; ALTER, CREATE, and DROP TABLE statementsLog on as DBSEC and create a table, then drop the tableLog on as SYSTEM; view the content of DBA_AUDIT_TRAILTurn off auditing for the TABLE statementDatabase Security and Auditing18DCL Activities ExampleSteps:Log on as SYSTEM or SYS and issue an AUDIT statementLog on as DBSEC and grant SELECT and UPDATE to SYSTEMLog on as SYSTEM and display the contents of DBA_AUDIT_TRAILReview audit data dictionaryDatabase Security and Auditing19DCL Activities Example (continued)Database Security and Auditing20Example of Auditing User ActivitiesSteps:Log on as SYSTEM or SYS, to issue an audit statementLog on as DBSEC and create a temporary tableGo back to SYSTEM to view the contents of DBA_AUDIT_TRAILDatabase Security and Auditing21Audit Trail File DestinationSteps:Modify the initialization parameter file, INIT.ORA; set parameter AUDIT_TRAIL to the value OSCreate a folder/directorySet AUDIT_FILE_DEST to the new directoryShut down and restart the databaseConnect as DBSECDatabase Security and Auditing22Oracle Alert LogAudits database activities:Errors:Errors related to physical structure are recorded in the Alert logMonitor errors every five to ten minutes; can be done using a Windows or UNIX scriptSyntactical errors are not recordedStartup and shutdownDate and time of each occurrenceDatabase Security and Auditing23Oracle Alert Log (continued)Database Security and Auditing24Oracle Alert Log (continued)Database activities (continued):Modified initialization parameters, each time a database is startedCheckpoints: configure Oracle to record checkpoint timeArchiving: view the timing for all redo log sequences, as well as archiving timesPhysical database changesDatabase Security and Auditing25Oracle Alert Log (continued)Database Security and Auditing26Auditing Server Activity with Microsoft SQL Server 2000Way to track and log activity for each SQL Server occurrenceMust be a member of the sysadmin fixed server roleTwo types of auditing for server events:AuditingC2 auditingAuditing affects performance and can be costlyDatabase Security and Auditing27Implementing SQL ProfilerUser interface for auditing eventsFor each event you can audit:Date and time of the eventUser who caused the event to occurType of eventSuccess or failure of the eventOrigin of the requestName of the object accessedText SQL statementDatabase Security and Auditing28Implementing SQL Profiler (continued)Database Security and Auditing29Security Auditing with SQL ServerSteps for setting security auditing level:Open Enterprise ManagerExpand the appropriate SQL Server groupRight-click on the desired serverClick PropertiesOn the security tab, select the desired security levelDatabase Security and Auditing30Security Auditing with SQL Server (continued)Database Security and Auditing31Security Auditing with SQL Server (continued)Auditable events:ADD DB USERADD LOGIN TO SERVER ROLEADD MEMBER TO DB ROLEADD ROLEAPP ROLE CHANGE PASSWORDBACKUP/RESTORECHANGE AUDITDatabase Security and Auditing32Security Auditing with SQL Server (continued)Auditable events (continued):DBCCLOGINLOGOUTLOGIN CHANGE PASSWORDLOGIN CHANGE PROPERTYLOGIN FAILEDLogin GDR (GRANT, DENY, REVOKE)Database Security and Auditing33Security Auditing with SQL Server (continued)Auditable events (continued):Object Derived PermissionsObject GDRObject PermissionsServer Start and StopStatement GDRStatement PermissionDatabase Security and Auditing34Security Auditing with SQL Server (continued)Database Security and Auditing35Security Auditing with SQL Server (continued)New trace information:A name for the traceThe server you want to auditThe base template to start withWhere to save the audit data, either to a file or to a database tableA stop time, if you don’t want the trace to run indefinitelyDatabase Security and Auditing36Security Auditing with SQL Server (continued)Database Security and Auditing37Security Auditing with SQL Server (continued)Database Security and Auditing38Security Auditing with SQL Server (continued)Steps to add Login Change Password eventExpand the Security Audit node under Available event classesClick Audit Login Change Password EventClick the Add buttonDatabase Security and Auditing39Security Auditing with SQL Server (continued)Database Security and Auditing40Data Definition AuditingAudit DDL statements:Object:CreatedObject:DeletedWill audit all CREATE and DROP statements Database Security and Auditing41Data Definition Auditing (continued)Database Security and Auditing42Database Auditing with SQL ServerDatabase Security and Auditing43Database Errors Auditing with SQL ServerDatabase Security and Auditing44SummaryActivities types:Application activitiesAdministration activitiesDatabase eventsOracle triggers provide a way to create an audit trailAuditable Oracle database activities: logon, logoff, startup and shutdownDatabase Security and Auditing45Summary (continued)Oracle provides the SQL AUDIT command: initialization parameter AUDIT_TRAILNOAUDIT used to stop auditingDBA_AUDIT_TRAIL data dictionary viewOracle Alert Log:Database errorsModified initialization parametersCheckpointsDatabase Security and Auditing46Summary (continued)Microsoft SQL Server 2000: way to track and log SQL Server activityMust be a member of sysadmin fixed role to enable or modify auditingSQL Profiler:Visualization toolAudit errors that occur within the database