{"id":8734,"date":"2016-08-23T09:28:16","date_gmt":"2016-08-23T07:28:16","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/audit_sys_operations-and-top-level-operation\/"},"modified":"2016-08-23T09:28:16","modified_gmt":"2016-08-23T07:28:16","slug":"audit_sys_operations-and-top-level-operation","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/audit_sys_operations-and-top-level-operation\/","title":{"rendered":"AUDIT_SYS_OPERATIONS and top-level operation"},"content":{"rendered":"

By Franck Pachot<\/h2>\n

.
\nSYSDBA is powerful. You should use it only when you need to. And you should audit what is done when connected SYSDBA. This is achieved by AUDIT_SYS_OPERATION. But do you have an idea how much audit log will be generated? Note that in 12c<\/em>, AUDIT_SYS_OPERATION=true is the default.
\n
\nThe documentation<\/a> says:
\nAUDIT_SYS_OPERATIONS enables or disables the auditing of top-level operations, which are SQL statements directly issued by users when connecting with the SYSASM, SYSBACKUP, SYSDBA, SYSDG, SYSKM, or SYSOPER privileges. (SQL statements run from within PL\/SQL procedures or functions are not considered top-level.)
\n<\/em><\/p>\n

From the documentation, you can expect that only the statements that are issued by a SYSDBA user call are logged.
\nNot the recursive statements.
\nNot the scheduler jobs.<\/p>\n

However, this is not what happens. Look at the following:<\/p>\n

\"CaptureEventLog001\"<\/a><\/p>\n

On Windows, the SYS Audit goes to the Event log and it is full of those kind of statements. Those are no statements that I’ce issued when connected as SYSDBA. Those are recursive statements from the automatic statistic gathering job that run everyday. Do I want to audit that? probably not. But it fills my system log.<\/p>\n

On Unix, this is not better. Here is a database created a few days ago with nearly no activity since then:<\/p>\n

\n[oracle@CDB adump]$ ls -alrt | head\ntotal 2341264\ndrwxr-xr-x 5 oracle oinstall      4096 Jul 29 16:12 ..\n-rw-r----- 1 oracle oinstall      2699 Aug 19 03:15 CDB_m000_10396_20160819031532925620143795.aud\n-rw-r----- 1 oracle oinstall      1150 Aug 19 03:15 CDB_ora_10402_20160819031534799868143795.aud\n-rw-r----- 1 oracle oinstall      1134 Aug 19 03:15 CDB_ora_10404_20160819031535178281143795.aud\n-rw-r----- 1 oracle oinstallcat      1131 Aug 19 03:15 CDB_ora_10406_20160819031535558089143795.aud\n-rw-r----- 1 oracle oinstall      1139 Aug 19 03:15 CDB_ora_10408_20160819031535936355143795.aud\n-rw-r----- 1 oracle oinstall      1156 Aug 19 03:15 CDB_ora_10410_20160819031536306359143795.aud\n-rw-r----- 1 oracle oinstall      1165 Aug 19 03:15 CDB_ora_10412_20160819031536679750143795.aud\n[oracle@CDB adump]$ ls -alrt | tail\n-rw-r----- 1 oracle oinstall      1243 Aug 23 08:31 CDB_ora_9044_20160823083113966954143795.aud\n-rw-r----- 1 oracle oinstall      1243 Aug 23 08:31 CDB_ora_9047_20160823083114497136143795.aud\n-rw-r----- 1 oracle oinstall      1243 Aug 23 08:31 CDB_ora_9048_20160823083114567197143795.aud\n-rw-r----- 1 oracle oinstall      1243 Aug 23 08:31 CDB_ora_9049_20160823083115041317143795.aud\n-rw-r----- 1 oracle oinstall      1243 Aug 23 08:31 CDB_ora_9050_20160823083115311603143795.aud\n-rw-r----- 1 oracle oinstall     54568 Aug 23 08:31 CDB_ora_9036_20160823083113459749143795.aud\n-rw-r----- 1 oracle oinstall      1243 Aug 23 08:31 CDB_ora_9052_20160823083115922822143795.aud\n-rw-r----- 1 oracle oinstall     54568 Aug 23 08:31 CDB_ora_9045_20160823083114267635143795.aud\ndrwxr-xr-x 5 oracle oinstall  64274432 Aug 23 08:31 .\n[oracle@CDB adump]$ du -hs\n8.5G    .\n<\/code><\/pre>\n
Yes, this is two million files. 8GB of text. What will you do with that?
\nThat’s the problem with auditing. It’s activated by default to comply with security policies, but there’s nothing to purge or archive them by default. Size grows and you cannot do anything relevant with it.<\/p>\n
If I look at a subset of files:<\/p>\n
\n[oracle@CDB adump]$ ls CDB_ora_*_20160823*aud | wc -l\n8184\n<\/code><\/pre>\n
Only two of them are from user connections. The others have no terminal:<\/p>\n
\n[oracle@CDB adump]$ grep -l \"^CLIENT TERMINAL:\\[0\\]\" CDB_ora_*_20160823*aud | wc -l\n8182\n<\/code><\/pre>\n
Expected feature<\/h3>\n
It’s not a bug. Oracle has decided to log everything in the SYS audit. Because if they don’t, a SYSDBA connection can run a procedure or a job that looks harmless when only the top-level call is logged. That procedure may be created by any user and so do not appear in the audit trail.<\/p>\n
So what?<\/h3>\n
Whatever the default is, the audit trail is something to manage. On Windows, the event log has a maximum size. On Linux, it fills the adump directory (or syslog if you enabled it). So you should have housekeeping jobs. You can also disable the audit so only connections are logged, but not the statements. In this case you should control who has access to SYSDBA.<\/p>\n","protected":false},"excerpt":{"rendered":"
By Franck Pachot . SYSDBA is powerful. You should use it only when you need to. And you should audit what is done when connected SYSDBA. This is achieved by AUDIT_SYS_OPERATION. But do you have an idea how much audit log will be generated? Note that in 12c, AUDIT_SYS_OPERATION=true is the default.<\/p>\n","protected":false},"author":27,"featured_media":8736,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[667,209,25],"type_dbi":[],"class_list":["post-8734","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-database-administration-monitoring","tag-audit","tag-oracle-12c","tag-security"],"acf":[],"yoast_head":"\nAUDIT_SYS_OPERATIONS and top-level operation - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/audit_sys_operations-and-top-level-operation\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"AUDIT_SYS_OPERATIONS and top-level operation\" \/>\n<meta property=\"og:description\" content=\"By Franck Pachot . SYSDBA is powerful. You should use it only when you need to. And you should audit what is done when connected SYSDBA. This is achieved by AUDIT_SYS_OPERATION. But do you have an idea how much audit log will be generated? Note that in 12c, AUDIT_SYS_OPERATION=true is the default.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/audit_sys_operations-and-top-level-operation\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2016-08-23T07:28:16+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/CaptureEventLog001-1.png\" \/>\n\t<meta property=\"og:image:width\" content=\"747\" \/>\n\t<meta property=\"og:image:height\" content=\"694\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Oracle Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Oracle Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/audit_sys_operations-and-top-level-operation\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/audit_sys_operations-and-top-level-operation\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"AUDIT_SYS_OPERATIONS and top-level operation\",\"datePublished\":\"2016-08-23T07:28:16+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/audit_sys_operations-and-top-level-operation\/\"},\"wordCount\":426,\"commentCount\":1,\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/audit_sys_operations-and-top-level-operation\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/CaptureEventLog001-1.png\",\"keywords\":[\"audit\",\"Oracle 12c\",\"Security\"],\"articleSection\":[\"Database Administration & Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/audit_sys_operations-and-top-level-operation\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/audit_sys_operations-and-top-level-operation\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/audit_sys_operations-and-top-level-operation\/\",\"name\":\"AUDIT_SYS_OPERATIONS and top-level operation - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/audit_sys_operations-and-top-level-operation\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/audit_sys_operations-and-top-level-operation\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/CaptureEventLog001-1.png\",\"datePublished\":\"2016-08-23T07:28:16+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/audit_sys_operations-and-top-level-operation\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/audit_sys_operations-and-top-level-operation\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/audit_sys_operations-and-top-level-operation\/#primaryimage\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/CaptureEventLog001-1.png\",\"contentUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/CaptureEventLog001-1.png\",\"width\":747,\"height\":694},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/audit_sys_operations-and-top-level-operation\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"AUDIT_SYS_OPERATIONS and top-level operation\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\",\"name\":\"Oracle Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"caption\":\"Oracle Team\"},\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"AUDIT_SYS_OPERATIONS and top-level operation - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/audit_sys_operations-and-top-level-operation\/","og_locale":"en_US","og_type":"article","og_title":"AUDIT_SYS_OPERATIONS and top-level operation","og_description":"By Franck Pachot . SYSDBA is powerful. You should use it only when you need to. And you should audit what is done when connected SYSDBA. This is achieved by AUDIT_SYS_OPERATION. But do you have an idea how much audit log will be generated? Note that in 12c, AUDIT_SYS_OPERATION=true is the default.","og_url":"https:\/\/www.dbi-services.com\/blog\/audit_sys_operations-and-top-level-operation\/","og_site_name":"dbi Blog","article_published_time":"2016-08-23T07:28:16+00:00","og_image":[{"width":747,"height":694,"url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/CaptureEventLog001-1.png","type":"image\/png"}],"author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/audit_sys_operations-and-top-level-operation\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/audit_sys_operations-and-top-level-operation\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"AUDIT_SYS_OPERATIONS and top-level operation","datePublished":"2016-08-23T07:28:16+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/audit_sys_operations-and-top-level-operation\/"},"wordCount":426,"commentCount":1,"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/audit_sys_operations-and-top-level-operation\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/CaptureEventLog001-1.png","keywords":["audit","Oracle 12c","Security"],"articleSection":["Database Administration & Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/audit_sys_operations-and-top-level-operation\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/audit_sys_operations-and-top-level-operation\/","url":"https:\/\/www.dbi-services.com\/blog\/audit_sys_operations-and-top-level-operation\/","name":"AUDIT_SYS_OPERATIONS and top-level operation - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/audit_sys_operations-and-top-level-operation\/#primaryimage"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/audit_sys_operations-and-top-level-operation\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/CaptureEventLog001-1.png","datePublished":"2016-08-23T07:28:16+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/audit_sys_operations-and-top-level-operation\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/audit_sys_operations-and-top-level-operation\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/audit_sys_operations-and-top-level-operation\/#primaryimage","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/CaptureEventLog001-1.png","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/CaptureEventLog001-1.png","width":747,"height":694},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/audit_sys_operations-and-top-level-operation\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"AUDIT_SYS_OPERATIONS and top-level operation"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee","name":"Oracle Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","caption":"Oracle Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/8734","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/27"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=8734"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/8734\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media\/8736"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=8734"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=8734"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=8734"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=8734"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}