{"id":7836,"date":"2021-03-30T12:28:00","date_gmt":"2021-03-30T10:28:00","guid":{"rendered":"https:\/\/www.carajandb.com\/?p=7836"},"modified":"2021-03-30T12:28:09","modified_gmt":"2021-03-30T10:28:09","slug":"who-is-system","status":"publish","type":"post","link":"https:\/\/carajandb.com\/en\/2021\/03\/30\/who-is-system\/","title":{"rendered":"Who is SYSTEM?"},"content":{"rendered":"

In many companies the DBAs – and not only them – are working with the user “SYSTEM<\/em>“. Is that wrong? Not necessarily but you might what to check “who did what?” for auditing purposes. And the GDPR is pushing you to thing about if using build in users or security relevant build in objects is helpful.
\n<\/p>\n

Which priviliges does SYSTEM has?<\/h3>\n

If you cannot answer this question – shame on you! You are using an acccount and you do not know what this account is allowed to do? If we have a look with a graphical tool like Toad it looks quite simple: SYSTEM is granted the role DBA<\/code> and AQ_ADMINISTRATOR_ROLE<\/code>.<\/p>\n

But if you have a closer look you might find that the role DBA<\/code> has 393 (Oracle 19c) system privileges. Many of them multiple times via sub roles. And I’m wondering if there is any one who can explain their meaning and if they are required.<\/p>\n

\"DBA
DBA Role<\/figcaption><\/figure>\n

So please don’t use interal users but create your own ones. And lock the SYSTEM account. There is no reason why you should use it. The Oracle documentation says: “For production systems, Oracle recommends creating individual database administrator accounts and not using the generic SYSTEM account for database administration operations.<\/em>” (Source: Database 2 Day + Security Guide Version 11.1).<\/p>\n

Default Roles DBA, RESOURCE, CONNECT<\/h3>\n

So lets have a closer look to the build in roles DBA, RESOURCE, and CONNECT<\/code>. They are relics from an ancient (Oracle 6) time. The Oracle documentations says: “The first three roles in Table 11-1 namely, CONNECT, RESOURCE, and DBA, are provided to maintain compatibility with previous versions of Oracle and may not be created automatically in future versions of Oracle. Oracle recommends that you design your own roles for database security, rather than relying on these roles.<\/em>” (Source: Oracle Security Guide Version 10.2!).
\nSo please don’t user the build in roles DBA, RESOURCE, CONNECT<\/code>. Look back to the upgrade from Oracle 11g to 12c. You might remember the issues you had because UNLIMITED TABLESPACE<\/code> was no longer granted to the role RESOURCE. Similar experiences are possible for using DBA as well.<\/p>\n

Default Profile<\/h3>\n

And now the third chapter of the story. Every database user has a profile. And similar to users and roles Oracle offeres some build in objects like the DEFAULT<\/code> profile. And likewise users and roles this is a example how to create your own profiles – nothing more. Remember again the changes starting with Oracle 12c. The LIMIT<\/code> for PROFILE_LIFE_TIME<\/code> has changed from UNLIMITED<\/code> to 180 days.
\nI received several calls from customers and DBAs because the application doesn’t work or users are unable to connect. Asking “when did you upgrade to Oracle 12c” the answer was “approx. 6 month ago”<\/em>…
\nSo please use your own well defined profiles!<\/p>\n

Password Complexity Check<\/h3>\n

And the last chapter. This was a tough experience for me. For some releases Oracle offers some PL\/SQL procedures and functions to verify the password complexity. The functions can be used in the profile limit PASSWORD_VERIFY_FUNCTION<\/code>.
\nPlease don’t use that for the DEFAULT profile because you might have problems with the installation of database options like spatial. That installation procedure creates a default user with a default passwort – which might not meet your password requirements.<\/p>\n

And this is the problem: Oracle offers a script named <ORACLE_HOME>\/rdbms\/catpvf.sql<\/code> as a base for a password complexity check. The procedure is well defined and easy to understsand. So there is no reason why you shouldn’t use it …
\nExample:<\/p>\n

CREATE OR REPLACE FUNCTION carajan_pwd_verify\n(username varchar2,\npassword varchar2,\nold_password varchar2)\nRETURN boolean IS\n   differ integer;\n   pw_lower varchar2(256);\n   db_name varchar2(40);\n   i integer;\n   simple_password varchar2(10);\n   reverse_user varchar2(32);\nBEGIN\n   IF NOT ora_complexity_check(\n             password, chars => 10, letter => 1, upper => 1, \n             lower => 1, digit => 1) THEN\n      RETURN(FALSE);\n   END IF;\n ...\n<\/code><\/pre>\n
This procedure checks if the password has a minimum length of 10 characters with minimum one upper, one lower character, and one digit.
\nUnfortunately in version 18 Oracle changed the procedure ora_complexity_check<\/code>, which is the base for the check. The variables upper<\/code> and lower<\/code> have been changed to uppercase<\/code> and lowercase<\/code> – no idea why.
\nThis little change leads to the fact that the function carajan_pwd_verify<\/code> becomes “invalid<\/em>” and the users are no longer able to change their passwords. Thats annoying and ends up in the fact that you always have to check the database version to use the correct variable names.
\nThe better way is to use your own Complexitycheck<\/em> procedure. You can use the one Oracle provides as a template and make the changes as necessary. That’s helpful because it will probably work even if you migrate to version 21 or higher.<\/p>\n
By the way neither did I found a hint in the Oracle documentation that the script is only a template nor any information about the two variables.<\/p>\n","protected":false},"excerpt":{"rendered":"
In many companies the DBAs – and not only them – are working with the user “SYSTEM“. Is that wrong? Not necessarily but you might what to check “who did what?” for auditing purposes. And the GDPR is pushing you to thing about if using build in users or security relevant build in objects is helpful.<\/p>\n","protected":false},"author":3,"featured_media":9387,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_crdt_document":"","_uag_custom_page_level_css":"","site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"disabled","footer-sml-layout":"","ast-disable-related-posts":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"default","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"footnotes":""},"categories":[406],"tags":[415,477],"class_list":["post-7836","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-oracle-en","tag-profile","tag-system"],"acf":{"occupation":"Write the Occupation of the Person","person_can-be-speaker":true,"person_is-team":true,"person_related-user-account":null,"person_ordering-number":50,"publication_speakers":null,"publication_content-language":["de"],"publication_files":null,"publication_event":"","publication_date":null},"spectra_custom_meta":{"_edit_lock":["1629186623:3"],"_encloseme":["1"],"_edit_last":["3"],"_wpml_word_count":["{\"total\":842,\"to_translate\":{\"de\":842}}"],"rank_math_news_sitemap_robots":["index"],"rank_math_robots":["a:1:{i:0;s:5:\"index\";}"],"astra_style_timestamp_css":["1773689566"],"rank_math_og_content_image":["a:2:{s:5:\"check\";s:32:\"acf4be63f158ea08eadf8472bf12d470\";s:6:\"images\";a:1:{i:0;i:7800;}}"],"rank_math_internal_links_processed":["1"],"wpil_sync_report3":["1"],"wpil_links_inbound_internal_count":["6"],"wpil_links_inbound_internal_count_data":["eJztVE2P0zAQ\/SuRkbj1I+nHhukBCcSNBaRFWnGKXGfaDuvake1oqVa98gP4ifwSxnEoXSqkvbbsLZ7Mm5n3ZvQkzOGBYLz4CPkMxG1Durq2NerqPZk7AfkEHjxcgdD8rKgWi5jsYQKidVrw13wKYhNCA6ORQy1bozbFuJgOlXTyqzT1cqjsdnSPbkA+DJDWaIImtRn4nQ+4HW7CtqsTy1gf4mcxB\/HvWjGjBEEmoDOSsUvIE77p8CdMPsVwz6RgYEdiVs4jD84MFDSKxYdUI+waFMflmCEIH2RofUpiMZTl5iak9yyJ4w8lvG7X6cFIxVx\/\/yu7mhTImhSIg1q3loZUFZxcrUgdgFhTsK7Py0FItar+7musUd3o+w4iWa8I4fXwGLfoMpY8+yN59vJFWRTjxc2Xm8\/vrsVr0Y8g6xrrarmrGp6cTFR0nMbVVsk07mPeLOS4v4n7DfkGXaUcyoCHA4kjk69kG2xMOw4zU3VHZn10TjG8tXWrsXqUXfRNusbfQorviRd+Offq4Grxn62vuKD1PdVuZpNnuznXe51c0L06mJYnfpPH\/REjNHo0GZmsNR4dbrM32q6zn99\/nO\/yphe0vCebzfzZbM71XmcXdK8OylcnZsPE3zJnMi1mLGzNckXHsa3LlmfuNvtfXL5QCg=="],"wpil_links_outbound_internal_count":["0"],"wpil_links_outbound_internal_count_data":["eJxLtDKwqq4FAAZPAf4="],"wpil_links_outbound_external_count":["0"],"wpil_links_outbound_external_count_data":["eJxLtDKwqq4FAAZPAf4="],"wpil_sync_report2_time":["2024-08-27T11:47:23+00:00"],"adv-header-id-meta":[""],"stick-header-meta":[""],"ast-featured-img":["disabled"],"theme-transparent-header-meta":[""],"_thumbnail_id":["9387"],"_wp_attachment_metadata":[""],"_uag_css_file_name":["uag-css-7836.css"]},"uagb_featured_image_src":{"full":["https:\/\/carajandb.com\/wp-content\/uploads\/2021\/03\/system-benutzer-datenbanken-dsgvo-sicherheit.png",903,408,false],"thumbnail":["https:\/\/carajandb.com\/wp-content\/uploads\/2021\/03\/system-benutzer-datenbanken-dsgvo-sicherheit-200x200.png",200,200,true],"medium":["https:\/\/carajandb.com\/wp-content\/uploads\/2021\/03\/system-benutzer-datenbanken-dsgvo-sicherheit-450x203.png",450,203,true],"medium_large":["https:\/\/carajandb.com\/wp-content\/uploads\/2021\/03\/system-benutzer-datenbanken-dsgvo-sicherheit-768x347.png",768,347,true],"large":["https:\/\/carajandb.com\/wp-content\/uploads\/2021\/03\/system-benutzer-datenbanken-dsgvo-sicherheit.png",903,408,false],"1536x1536":["https:\/\/carajandb.com\/wp-content\/uploads\/2021\/03\/system-benutzer-datenbanken-dsgvo-sicherheit.png",903,408,false],"2048x2048":["https:\/\/carajandb.com\/wp-content\/uploads\/2021\/03\/system-benutzer-datenbanken-dsgvo-sicherheit.png",903,408,false]},"uagb_author_info":{"display_name":"Johannes Ahrends","author_link":"https:\/\/carajandb.com\/en\/author\/9aa6cdb2095bd409\/"},"uagb_comment_info":0,"uagb_excerpt":"In many companies the DBAs – and not only them – are working with the user “SYSTEM“. Is that wrong? Not necessarily but you might what to check “who did what?” for auditing purposes. And the GDPR is pushing you to thing about if using build in users or security relevant build in objects is…","_links":{"self":[{"href":"https:\/\/carajandb.com\/en\/wp-json\/wp\/v2\/posts\/7836","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/carajandb.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/carajandb.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/carajandb.com\/en\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/carajandb.com\/en\/wp-json\/wp\/v2\/comments?post=7836"}],"version-history":[{"count":1,"href":"https:\/\/carajandb.com\/en\/wp-json\/wp\/v2\/posts\/7836\/revisions"}],"predecessor-version":[{"id":7837,"href":"https:\/\/carajandb.com\/en\/wp-json\/wp\/v2\/posts\/7836\/revisions\/7837"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/carajandb.com\/en\/wp-json\/wp\/v2\/media\/9387"}],"wp:attachment":[{"href":"https:\/\/carajandb.com\/en\/wp-json\/wp\/v2\/media?parent=7836"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/carajandb.com\/en\/wp-json\/wp\/v2\/categories?post=7836"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/carajandb.com\/en\/wp-json\/wp\/v2\/tags?post=7836"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}