Turbocharge Your Database Auditing With Oracle Unified Auditing - Integrigy
Turbocharge Your Database Auditingwith Oracle Unified AuditingFebruary 25, 2021Stephen KostPhil ReimannChief Technology OfficerDirector of Business DevelopmentIntegrigy CorporationIntegrigy Corporation
About IntegrigyERP ApplicationsDatabasesOracle E-Business Suiteand PeopleSoftOracle, Microsoft SQL Server,DB2, Sybase, MySQL, NoSQLProductsServicesAppSentryERP Application and DatabaseSecurity Auditing ToolAppDefendEnterprise Application Firewallfor Oracle E-Business Suiteand PeopleSoftValidatesand AuditsSecurityProtectsOracle EBS& PeopleSoftVerifySecuritySecurity AssessmentsERP, Database, Sensitive Data, Pen TestingEnsureComplianceCompliance AssistanceBuildSecuritySecurity Design ServicesIntegrigy Research TeamERP Application and Database Security ResearchSOX, PCI, HIPAA, GLBAAuditing, Encryption, DMZ
Oracle Database Releases12.2FamilySource – Oracle Database (RDBMS) Releases Support Status Summary (Doc ID 161818.1) – January 15, 2021
Auditing Design – Layered Design ExampleCompliance DBsAll DatabasesCommon EventsDatabase Events Database logins Database logoffs Failed database logins Privileges commandsSecurity/Auth Events Create/Alter/Drop User Create/Drop Role Grant/Revoke Privilegessystem, role, object Database profile changes Sensitive packagesDatabase Changes Database configurationchanges Auditing changes Database links Core object changesPCI Requirement 10.2 Access to card data inglobal list of tables Privileged account accessby global list of accountsGDPR Privileged account access byglobal list of accountsCompliance EventsSOX Database object changes Privileged account access byglobal list of accountsPer Database Events (defined during database on-boarding)Per DatabaseAnomaly Detection SQL Errors (defined list) Defined anomalous events Defined intrusion events Known securityvulnerabilitiesAccess to PII/Confidential Data Tables and columns containingPII and confidential data Select, Insert, Update, and/orDelete based on requirementsPrivileged Account Access Definition of accounts perapplication or database Exceptions to monitoring basedon location or type of accessHIPAA Privileged account access byglobal list of accounts Access to HIPAA data basedon global list of tables
Auditing Design – Oracle E-Business Suite ExampleCommon ty Events All database sessions All failed database logins All application sessions All failed application loginsDatabase Events SQL errors SQL errors by EBS end-user Sensitive packagesSOX Events and Reports Database user changes Database user password changes System privileges and roles changesAnomaly Detection SQL Errors (defined list) Defined anomalous events Defined intrusion events Known security vulnerabilitiesEBS End-UserEBS BatchDeploymentAPPS DBAAll OtherAll end-user application SQLis ignored, except specificstatements/objects for selectusers.All concurrent requests SQLis ignored.Deployment will tag allDDL/DML with changeticket number.All APPS DDL/DMLperformed by DBAs formanual changes, patching,and maintenance.All DDL/DML for all otherdatabase users, includingstandard Oracle DB, Oracle EBS,and individual database accounts.DB User: APPSDB User: APPSDB User: APPSDB User: APPSSource: App ServersSource: CM ServersSource: Deployment ServerSource: Not filtered priorApp User: Set and(not GUEST or SYSADMIN)App: STANDARD, Additional CapturePackage #Package DeployerOperating System IDDB User: All other Oracle – SYS, SYSTEM, Oracle EBS – APPLSYS,APPLSYSUB, 300 module Other – SSO, All-Deployment-No TicketAll-Deployment-With TicketDBA APPS LoginsDBA APPS Usage SummaryDBA APPS Usage DetailAll-DB LoginsAll-DB Usage SummaryAll-DB Usage DetailDBA-Changes WindowDBA-Changes Ad-hocUnath APPLSYSPUB UseApp: FRMWEB, SYSADMIN LoginsSYSADMIN Activity SummarySYSADMIN Activity DetailNoneGUEST Errors/SQL InjectionGUEST Large QueriesUnauth APPS Use SummaryUnauth APPS Use Details99.5% of all SQL statementsOperating System IDNon-App/Non-DBA DDL/DML
Integrigy Auditing Framework – Security Events and ActionsThe foundation of the framework is a set of key security events and actions derived from andmapped to compliance and security requirements that are critical for all organizations.E1 - LoginE8 - Modify roleE2 - LogoffE9 - Grant/revoke user privilegesE3 - Unsuccessful loginE10 - Grant/revoke role privilegesE4 - Modify auth mechanismsE11 - Privileged commandsE5 - Create user accountE12 - Modify audit and loggingE6 - Modify user accountE13 - Create, modify or delete objectE7 - Create roleE14 - Modify configuration settings
Integrigy Auditing Framework – Security Events MappingSecurity Eventsand ActionsPCIDSS 10.2SOX (COBIT)HIPAA(NIST 800-66)IT Security(ISO 27001)FISMA(NIST 800-53)E1 - Login10.2.5A12.3164.312(c)(2)A 10.10.1AU-2E2 - Logoff10.2.5DS5.5164.312(c)(2)A 10.10.1AU-2E3 - Unsuccessful login10.2.4DS5.5164.312(c)(2)A 10.10.1A.11.5.1AC-7E4 - Modify authentication mechanisms10.2.5DS5.5164.312(c)(2)A 10.10.1AU-2E5 – Create user account10.2.5DS5.5164.312(c)(2)A 10.10.1AU-2E6 - Modify user account10.2.5DS5.5164.312(c)(2)A 10.10.1AU-2E7 - Create role10.2.5DS5.5164.312(c)(2)A 10.10.1AU-2E8 - Modify role10.2.5DS5.5164.312(c)(2)A 10.10.1AU-2E9 - Grant/revoke user privileges10.2.5DS5.5164.312(c)(2)A 10.10.1AU-2E10 - Grant/revoke role privileges10.2.5DS5.5164.312(c)(2)A 10.10.1AU-2E11 - Privileged commands10.2.2DS5.5164.312(c)(2)A 10.10.1AU-2E12 - Modify audit and logging10.2.6DS5.5164.312(c)(2)A 10.10.1AU-2AU-9E13 - Objects Create/Modify/Delete10.2.7DS5.5164.312(c)(2)A 10.10.1AU-2AU-14E14 - Modify configuration settings10.2.2DS5.5164.312(c)(2)A 10.10.1AU-2
Oracle Unified Auditing Unified Auditing introduced in Oracle 12.1 Consolidates multiple audit trails into a single location (“unified”)– AUD , FGA LOG , DVSYS.AUDIT TRAIL now saved in UNIFIED AUDIT TRAIL Performance improvements in writing and reading (12.2 ) audit data– Uses an internal relational table Improved security of the audit trail– New roles to manage and view audit trail– No ability to delete audit trail except through audit trail management package Unified Auditing is always enabled Database initialization parameter no longer used for auditing– audit trail, audit file dest, audit sys operations are deprecated
Unified Auditing Enhancements by Version12.2 Audit policy conditionally based on database rolesRedesign of based Unified Auditing base tableIntegration with Transparent Sensitive Data Protection (TSDP)New audit events for Database Real Application Security (AUDIT GRANT/REVOKE PRIVILEGE)Ability to capture Virtual Private Database (VPD) predicates in the audit trail (rls info)Performance and stability improvements18c Write audit records to SYSLOG (see MOS ID 2623138.1)Unified Audit trail is automatically included in the Data Pump dump files Ability to audit only Top-Level SQL statements – exclude statements run from withinPL/SQL procedures or functionsAUDSYS.AUD UNIFIED system table has been redesigned to use partition pruning toimprove read performanceSYSLOG audit records now include PDB GUID to identify pluggable database where theaudit records originatedEVENT TIMESTAMP changes from TIMESTAMP(6) WITH LOCAL TZ to TIMESTAMP(6) 19c 21c Unified Auditing policy configuration changes effective immediately for currentsession and all active sessionsUnified Audit policies enforced on the current user (triggers, definer rights)Auditing for connections and requests to XML DB HTTP and FTP ServicesUnified Auditing on an editioned object now applies to all its editionsSYSLOG destination for common Unified Audit policies(UNIFIED AUDIT COMMON SYSTEMLOG)Deprecation of Traditional Auditing
Unified Auditing Issues12.1.0.2PSU or Bundle Patches queue write mode changesto immediate write modeUnified Auditing changes mode of writing in12.1, see MOS ID 2530035.112.1.0.xobject schema and object name contain incorrectvaluesPatch 2362448812.1.0.1Standard Edition unable to enable Unified AuditingPatch 1746685412.1.0.xPoor performancePatch 28186466, see MOS ID 2212196.112.1.0.2 AUDIT Commands Executed With CONTAINER ALL Inside The CDB Are Not Synchronized Into PDBSee MOS ID 2312141.112.1.0.1Audit ACTIONS ALL does not audit all actionsFixed in 12.1.0.2, See MOS ID 16714031.812.2.0.1 CREATE ANY JOB system privilege is not auditedFixed in 19.1.0, see MOS ID 27000076.812.1.0.2 Failed SYS logins may not be auditedFixed in 19.1.0, see MOS ID 2737820812.1.0.2 Newly created users are not audited by policieswith EXCEPT clausesSee MOS ID 2400613.1
Oracle Database Auditing Types and ModesTraditional Auditing Enabled based on database initialization parameter (audit trail)Audits based on audit statementAudit trail stored in AUD Unified Auditing Enabled by defaultAudits based on audit policiesAudit trail stored in UNIFIED AUDIT TRAIL Traditional and Unified Auditing are both enabledsimultaneouslyAudit data written based on Traditional Auditing auditstatements and Unified Auditing audit policiesMixed Mode Enable in database kernel, disables Traditional AuditingNo database initialization parameters (audit trail)Pure Unified Auditing cd ORACLE HOME/rdbms/lib make -f ins rdbms.mk uniaud on ioracle
FineMixed Mode – Traditional Auditing Unified Auditing5Fine GrainedAuditingFGA LOG tableDBMS FGA.add policyAUDIT FILE DESTdirDBNativeAUDIT TRAIL4StandardAuditingPrivileged32DB Alert LogNet1ListenerType of auditing and loggingAUD tableSYS.UNIFIEDAUDIT TRAILOS/XMLAUDIT SYSLOG LEVELSYSAuditingUnifiedAuditAUDIT FILE DESTdirSyslogAUDIT SYS OPERATIONSBG DUMP DEST dirLOGGING name ONAudit and logging parametersTNS ADMIN/log dirLocation of audit data
Fine ting2DB Alert Log1ListenerNativeFine5NetPure Unified AuditingType of auditing and loggingDBMS FGA.add policyUnified AuditSYS.UNIFIED AUDIT TRAILUnified AuditPoliciesBG DUMP DEST dirLOGGING name ONAudit and logging parametersTNS ADMIN/log dirLocation of audit data
Is Unified Auditing Enabled? Yes, enabled by default in Mixed Mode Only ORA SECURECONFIG and ORA LOGON FAILURES policies enabled by defaultV OPTIONParameter ’Unified Auditing’V PARAMETERaudit trail noneV PARAMETERaudit trail DB,OS, TRUE FALSEPureUnified AuditingNoUnified Auditing(UNIFIED AUDIT TRAIL)(no auditing)PureUnified AuditingMixedMode(UNIFIED AUDIT TRAIL)(AUD UNIFIED AUDIT TRIAL)
Traditional Auditing AuditAUDIT statement system privileges roles BY ALL user, user, WHENEVER [NOT] SUCCESSFUL Auditing limited to a list of users or ALL users– No granularity No conditions allowed by on the “context” of the database session Filtering and removing of noise done after the fact in alerting and reporting Unable to granularly capture audit trail of high volume users (e.g., APPS)
Unified Auditing – CREATE AUDIT POLICY StatementCREATE AUDIT POLICY name standard actions component actions system privileges roles WHEN audit condition expression EVALUATE PER STATEMENT SESSION INSTANCE [ONLY TOPLEVEL] Audit policies expanded to actions in –– Oracle Data Pump, Oracle SQL*Loader Direct Path Load, Oracle Label Security, OracleDatabase Real Application Security, and Oracle Database Vault Conditions based on SYS CONTEXT allow to base audit on the context of thedatabase session TOPLEVEL limits auditing to only SQL statements issued directly by a user ratherthan all statements include those in procedures, functions, and triggers
Unified Auditing – AUDIT statementAUDIT POLICY name [BY user, user, ][EXCEPT user, user, ][BY USERS WITH GRANTED ROLES roles, roles, ]WHENEVER [NOT] SUCCESSFUL Able to make audit policy granular to specific users or roles– Simple to change without recreating all audits as in Traditional Auditing BY USERS WITH GRANTED ROLES enables audit if users is in a role– Not the actions of the role, which is done in the audit policy EXCEPT important to audit all users, except high volume users (e.g., APPS)
Unified Audit Policies Installed by DefaultPolicy NameEnabledBy Default# of AuditsORA SECURECONFIGYes49ORA RAS POLICY MGMTNo35ORA RAS SESSION MGMTNo14ORA ACCOUNT MGMTNo9ORA DATABASE PARAMETERNo3ORA LOGON FAILURESNo1ORA DV AUDPOL2No19ORA CIS RECOMMENDATIONSNo35ORA DV AUDPOLNo2,180
Audit Policies are ObjectsSELECT owner, object name, object type, created, last ddl time, timestamp,oracle maintainedFROM dba objectsWHERE object type 'UNIFIED AUDIT POLICY'ORDER BY object name;OWNERSYSSYSSYSSYSSYSSYSSYSSYSSYSSYSOBJECT NAMEINTEGRIGY LOGON SUCCESSESORA ACCOUNT MGMTORA CIS RECOMMENDATIONSORA DATABASE PARAMETERORA DV AUDPOLORA DV AUDPOL2ORA LOGON FAILURESORA RAS POLICY MGMTORA RAS SESSION MGMTORA SECURECONFIG10 rows selected.OBJECT TYPEUNIFIED AUDITUNIFIED AUDITUNIFIED AUDITUNIFIED AUDITUNIFIED AUDITUNIFIED AUDITUNIFIED AUDITUNIFIED AUDITUNIFIED AUDITUNIFIED -1917-APR-19LAST DDL 02:512019-04-17:01:02:512019-04-17:01:14:31ORACLE MAINTAINEDNYYYYYYYYY
Unified Auditing and MultitenantServerCDB ROOTcommon users (c##), common roles, common profilesinitialization parametersUNIFIED AUDIT TRAILLocal Audit PoliciesPDB1Common AuditPolicies (c## only)PDB2 PDBnlocal users, local roles, local profilesinitialization parameters (override)local users, local roles, local profilesinitialization parameters (override)Local Audit Policies ORACLE BASE/auditUNIFIED AUDIT TRAILLocal Audit PoliciesTNS Listenersqlnet.ora, listener.oraUNIFIED AUDIT TRAIL
Unified Auditing and Multitenant Common policies only audit Common Users (C##) All Oracle pre-defined policies are not common policiesUserCommon (C##)Common Audit Policyrootcontainer ALLvisible in all PDBLocal Audit Policyroot or PDBcontainer CURRENTrootuser auditedPDBuser auditedrootuser auditedPDBuser auditedLocal (PDB)PDBnoPDBuser audited
Mandatory AuditingUnified Auditing always-on-auditing for SYSDBA – SYS, SYSDBA, SYSOPER, SYSASM, SYSBACKUP, SYSDG, SYSKMMandatory Auditing Events (SYS.UNIFIED AUDIT TRIAL) CREATE AUDIT POLICYALTER AUDIT POLICYDROP AUDIT POLICYAUDITNOAUDITDatabase Vault configurations DBMS FGA PL/SQL package DBMS AUDIT MGMT PL/SQL package ALTER TABLE attempts onthe AUDSYS audit trail
Recommended Database Logging – Security EventsFrameworkEventE1, E2, E3Oracle AuditStatementObjectResulting AuditedSQL StatementsSessionsessionDatabase logons and failed logonsE5, E6Usersusercreate/alter/drop userE7, E8Rolesrolecreate/alter/drop roleE13Database LinksPublic Database Linksdatabase linkpublic database linkcreate/drop database linkcreate/drop public database linkdrop public database linkE11Systemalter systemalter systemE14Databasealter databasealter databaseGrants(system privilegesand roles)system grantgrantrevokeProfilesprofilecreate/alter/drop profileSYSDBA and SYSOPERsysdbasysoperAll SQL executed with sysdba andsysoper privilegesE9, E10E4E11, E14See Integrigy Auditing and Logging Framework whitepaper for complete database auditing recommendations
Change Ticket Tracking – Create User ExampleCapture ticket numbers and other information for a database session based onspecial SQL executed by database users or applications.1DBA Workflow Process or ApplicationSELECT sys.ticket(1234)FROM dual;CREATE USER scott;3User CreationAuthorizedAuditor samples authorizedusers by reviewing tickets.User CreationUnauthorizedCreation without a ticket is apolicy violation and eachuser is investigated.Auditor Workflow Process2Audit TrailUSER IDBOBOS USERDOMAIN/BOBACTIONCREATE USEROBJECTScottCLIENT ID1234User CreationAuthorizedTicket # yesUser CreationUnauthorizedTicket # no
Oracle Database Security Changes Database users–Creation of users–Dropping of users–Alerting of users (password, profile, default tablespace, etc.) Profiles (password and resource controls) Roles Role and system privileges –Granting to users and roles–Revoking from users and rolesTable and object privileges– Granting and revoking of select, insert, update, delete, execute,etc. privilegesAuditing–Audit, noaudit–Fine-grained auditing (FGA) policies, Unified auditing policies, etc.–Purging of auditing tablesOracle Database Vault configuration and policiesChange Management Challenges Many changes are made by generic,privileged accounts and difficult todetermine the named DBA Database and application patchesmay result in database securitychanges
Oracle Database Changes Oracle Database patches Initialization parameters Packages, procedures and functions (PL/SQLcode objects) Some database changes are madeby automated application processesas part of standard transactionprocessing Tables/Views/Indexes TriggersMany changes are made by generic,privileged accounts and difficult todetermine the named DBA Database and application patchesmay result in hundreds of databasechanges Initialization parameters may bechanged in the database oroperating system files Materialized Views Database storage (tablespaces, data files, etc.) Other database objects (sequences, types, etc.)Change Management Challenges
Integrigy Contact Informationweb – www.integrigy.come-mail – info@integrigy.comStephen KostChief Technology OfficerIntegrigy Corporationblog – integrigy.com/oracle-security-blogyoutube – youtube.com/integrigylinkedin – linkedin.com/company/integrigytwitter – twitter.com/integrigyCopyright 2021 Integrigy Corporation
12.1.0.1 Standard Edition unable to enable Unified Auditing Patch 17466854 12.1.0.x Poor performance Patch 28186466, see MOS ID 2212196.1 12.1.0.2 AUDIT Commands Executed With CONTAINER ALL Inside The CDB Are Not Synchronized Into PDB See MOS ID 2312141.1 12.1.0.1 Audit ACTIONS ALL does not audit all actions Fixed in 12.1.0.2, See MOS ID .
Chapter 05 - Auditing and Advanced Threat Analytics 1h 28m Topic A: Configuring Auditing for Windows Server 2016 Overview of Auditing The Purpose of Auditing Types of Events Auditing Goals Auditing File and Object Access Demo - Configuring Auditing Topic B: Advanced Auditing and Management Advanced Auditing
Sentence Correction Study Guide iii About the Turbocharge your GMAT Series The highly acclaimed Turbocharge Your GMAT series is the result of the arduous ef-fort of Manhattan Review to offer the most comprehensive and clear treatment of the concepts tests in the GMAT. The Manhattan Review Turbocharge Your GMAT prepara-
of Auditing and Assurance-Introduction (Auditing 1) and Auditing and Assurance-Intermediate (Auditing 2). This course is designed to provide an introduction to auditing and assurance services. Level of Proficiency in Auditing 1: Foundation Subject Learning Outcome Upon completion of the subj
SECTION-1 (AUDITING) INTRODUCTION TO AUDITING STRUCTURE: 1.1 Objectives 1.2 Introduction -an overview of auditing 1.3 Origin and evolution 1.4 Definition 1.5 Salient features 1.6 Scope of auditing 1.7 Principles of auditing 1.8 Objects of audit 1.9 Detection and prevention of fraud 1.2 1.10 Concept of " true and fair view"
5 GMP Auditing 6 GCP Auditing 7 GLP Auditing 8 Pharmacovigilance Auditing 9 Vendor/Supplier Auditing 10 Remediation 11 Staff Augmentation 12 Data Integrity & Computer System Validation . the training it needs to maintain quality processes in the future. GxP Auditing, Remediation, and Staff Augmentation The FDAGroupcom 9
Introduction to Assurance and Financial Statement Auditing 1 Chapter 1 An Introduction to Assurance and Financial Statement Auditing 2 Tips for Learning Auditing 4 The Demand for Auditing and Assurance 5 Principals and Agents 5 The Role of Auditing 6 An Assurance Analogy: The Case of
Auditing-B.com 3rd Year Unit I Introduction to Auditing Meaning and Definition of Auditing The word Audit is derived from Latin word “Audire” which means ‘to hear’. Auditing is the verification of financial position as discl
Advanced Automotive Battery Conference Las Vegas, Nevada February 6-8, 2001 ABSTRACT Thermal management of batteries in electric vehicles (EVs) and hybrid electric vehicles (HEVs) is essential for .
