{"id":48370,"date":"2026-11-11T17:39:39","date_gmt":"2026-11-11T16:39:39","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/?p=48370"},"modified":"2026-11-11T17:39:41","modified_gmt":"2026-11-11T16:39:41","slug":"grant-temporary-login-access-to-an-oracle-schema","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/grant-temporary-login-access-to-an-oracle-schema\/","title":{"rendered":"Grant temporary login access to an Oracle schema"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">How to grant temporary access to an application-user?<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">First, a brief explanation of the terms of <a href=\"https:\/\/www.oracle.com\">Oracle<\/a> &#8220;user\u201d and \u201cschema\u201d. It is like in Unix where you have a &#8220;user&#8221;, &#8220;login&#8221;, an &#8220;account&#8221; which is similar to the Oracle &#8220;user&#8221;, and you have a &#8220;home&#8221; (a directory for your files). Similar, you have a &#8220;schema&#8221; <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">You have a production application schema APP1 that owns the objects, but normally no users will login as this application schema. But now, a developer needs temporary access. The developer requested a 2-hour maintenance window, and this was approved by the application manager.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">We as DBAs can unlock the account, give the developer the password, wait 2 hours, then lock the password and kill all sessions of the application user.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">It would be easier that Oracle will do that for us. Isn&#8217;t it?<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">My approach is to use a logon trigger creating a scheduler job to kill the session. <\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Check if it is a logon to APP1<\/li>\n\n\n\n<li>Check if there is already a scheduler job to kill the sessions of APP1<\/li>\n\n\n\n<li>If not, it is the 1st login and we create the job<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">The job will do:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>lock the account<\/li>\n\n\n\n<li>kill the sessions of APP1<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">In this example, I will use the SYSTEM user. It is also possible to use another user with the required privileges, e.g. &#8220;<code>ADMINISTER DATABASE TRIGGER<\/code>&#8220;. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">For the pl\/sql part, we need a direct grant to the SYSTEM user:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\n-- as sysdba:\ngrant select on v_$session to system;\ngrant create job to system;\ngrant alter user to system;\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\">To be flexible I will create a table with the users and timeouts<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\ncreate table USER_TIMEOUT (\n  USERNAME varchar2(30) not null,\n  TIMEOUT number not null\n);\nalter table USER_TIMEOUT\n  add constraint USER_TIMEOUT_PD \n  primary key ( USERNAME ) enable;\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\">First, create the procedure to lock the user and kill all existin sessions of it.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\ncreate or replace procedure EXPIRE_USER (v_username in varchar2) as\n  v_cnt number default 0;\n  v_error_msg VARCHAR2(4000);\nbegin\n  execute immediate &#039;alter user &#039;|| V_USERNAME ||&#039; ACCOUNT LOCK&#039;;\n  for s in (select SID, SERIAL# from V$SESSION where USERNAME = v_username) loop\n    begin\n      execute immediate &#039;alter system kill session &#039;&#039;&#039; || s.SID || &#039;,&#039; || s.SERIAL# || &#039;&#039;&#039; immediate&#039;;\n    exception\n      when others then null;  -- session already ended?\n    end;\n  \n  end loop;\n\nend;\n\/\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\">This procedure will be called by the job submitted by the logon-trigger<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\ncreate or replace NONEDITIONABLE TRIGGER app1_logon_timer\nAFTER LOGON ON DATABASE\nDECLARE\n  v_cnt NUMBER;\n  v_timeout number;\n  v_error_msg VARCHAR2(4000);\nBEGIN\n\n\n    select timeout into v_timeout from user_timeout where username=USER; \n    -- NO_DATA_FOUND is handled by WHEN OTHERS...\n    \n    select count(*) into v_cnt from user_scheduler_jobs\n    where job_name = &#039;EXPIRE_JOB_&#039;||USER;\n    if v_cnt = 0 then    \n      DBMS_SCHEDULER.CREATE_JOB(\n        job_name   =&gt; &#039;EXPIRE_JOB_&#039;||USER,\n        job_type   =&gt; &#039;STORED_PROCEDURE&#039;,\n        job_action =&gt; &#039;EXPIRE_USER&#039;,\n        number_of_arguments =&gt; 1,\n        start_date =&gt; SYSTIMESTAMP + NUMTODSINTERVAL(v_timeout, &#039;MINUTE&#039;),\n        enabled    =&gt; FALSE,\n        auto_drop  =&gt; TRUE,\n        comments   =&gt; &#039;Kills &#039;||USER||&#039; sessions and locks account &#039;||v_timeout||&#039; hour after first login&#039;);   \n        \n      DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(\n        job_name          =&gt; &#039;EXPIRE_JOB_&#039;||USER,\n        argument_position =&gt; 1,\n        argument_value    =&gt; USER\n      );\n\n      DBMS_SCHEDULER.ENABLE(&#039;EXPIRE_JOB_&#039;||USER);\n\n  end if;\nEXCEPTION\n  WHEN OTHERS THEN NULL;  -- never block logons if this fails\n\nEND;\n\/\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\">And for your convenience, a small pl\/sql procedure to unlock a user and to set a timeout. There is no errorhandling. It is up to you to add it or to enter correct data.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\ncreate or replace NONEDITIONABLE PROCEDURE set_user_timeout (\n  p_username IN VARCHAR2,\n  p_timeout  IN NUMBER\n) AUTHID DEFINER\nAS\nBEGIN\n  EXECUTE IMMEDIATE &#039;ALTER USER &#039; || p_username || &#039; ACCOUNT UNLOCK&#039;;\n\n  MERGE INTO user_timeout t\n  USING ( SELECT p_username AS username, p_timeout AS timeout    FROM dual) s\n  ON (t.username = s.username)\n  WHEN MATCHED THEN\n    UPDATE SET t.timeout = s.timeout\n  WHEN NOT MATCHED THEN\n    INSERT (username, timeout) VALUES (s.username, s.timeout);\n  COMMIT;\nEND;\n\/\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\">All the above PL\/SQL code is tested in my laboratory environment. Before using the code in a production environment, test it thoroughly to make sure it runs correctly in your environment. I cannot guarantee this. And of course, you can adapt the code to suit your needs.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>How to grant temporary access to an application-user? First, a brief explanation of the terms of Oracle &#8220;user\u201d and \u201cschema\u201d. It is like in Unix where you have a &#8220;user&#8221;, &#8220;login&#8221;, an &#8220;account&#8221; which is similar to the Oracle &#8220;user&#8221;, and you have a &#8220;home&#8221; (a directory for your files). Similar, you have a &#8220;schema&#8221; [&hellip;]<\/p>\n","protected":false},"author":123,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[],"type_dbi":[],"class_list":["post-48370","post","type-post","status-publish","format-standard","hentry","category-non-classifiee"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v28.8 (Yoast SEO v28.8) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>Grant temporary login access to an Oracle schema - dbi Blog<\/title>\n<meta name=\"description\" content=\"how to give temporary access to an application-user with killing all sessions after a timeout and locking the account\" \/>\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\/grant-temporary-login-access-to-an-oracle-schema\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Grant temporary login access to an Oracle schema\" \/>\n<meta property=\"og:description\" content=\"how to give temporary access to an application-user with killing all sessions after a timeout and locking the account\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/grant-temporary-login-access-to-an-oracle-schema\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2026-11-11T16:39:39+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2026-11-11T16:39:41+00:00\" \/>\n<meta name=\"author\" content=\"Martin Bracher\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Martin Bracher\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 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\\\/grant-temporary-login-access-to-an-oracle-schema\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/grant-temporary-login-access-to-an-oracle-schema\\\/\"},\"author\":{\"name\":\"Martin Bracher\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/2cbe1e2ea3cbf519c8ee3493042de8c9\"},\"headline\":\"Grant temporary login access to an Oracle schema\",\"datePublished\":\"2026-11-11T16:39:39+00:00\",\"dateModified\":\"2026-11-11T16:39:41+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/grant-temporary-login-access-to-an-oracle-schema\\\/\"},\"wordCount\":368,\"commentCount\":0,\"articleSection\":[\"Non classifi\u00e9(e)\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/grant-temporary-login-access-to-an-oracle-schema\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/grant-temporary-login-access-to-an-oracle-schema\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/grant-temporary-login-access-to-an-oracle-schema\\\/\",\"name\":\"Grant temporary login access to an Oracle schema - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2026-11-11T16:39:39+00:00\",\"dateModified\":\"2026-11-11T16:39:41+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/2cbe1e2ea3cbf519c8ee3493042de8c9\"},\"description\":\"how to give temporary access to an application-user with killing all sessions after a timeout and locking the account\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/grant-temporary-login-access-to-an-oracle-schema\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/grant-temporary-login-access-to-an-oracle-schema\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/grant-temporary-login-access-to-an-oracle-schema\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Grant temporary login access to an Oracle schema\"}]},{\"@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\\\/2cbe1e2ea3cbf519c8ee3493042de8c9\",\"name\":\"Martin Bracher\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/864a93d77bcd4cd44bab880a88f25fb5173ffbfac8e6e8775f0b4e056a4fbb56?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/864a93d77bcd4cd44bab880a88f25fb5173ffbfac8e6e8775f0b4e056a4fbb56?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/864a93d77bcd4cd44bab880a88f25fb5173ffbfac8e6e8775f0b4e056a4fbb56?s=96&d=mm&r=g\",\"caption\":\"Martin Bracher\"},\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/martinbracher\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Grant temporary login access to an Oracle schema - dbi Blog","description":"how to give temporary access to an application-user with killing all sessions after a timeout and locking the account","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\/grant-temporary-login-access-to-an-oracle-schema\/","og_locale":"en_US","og_type":"article","og_title":"Grant temporary login access to an Oracle schema","og_description":"how to give temporary access to an application-user with killing all sessions after a timeout and locking the account","og_url":"https:\/\/www.dbi-services.com\/blog\/grant-temporary-login-access-to-an-oracle-schema\/","og_site_name":"dbi Blog","article_published_time":"2026-11-11T16:39:39+00:00","article_modified_time":"2026-11-11T16:39:41+00:00","author":"Martin Bracher","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Martin Bracher","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/grant-temporary-login-access-to-an-oracle-schema\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/grant-temporary-login-access-to-an-oracle-schema\/"},"author":{"name":"Martin Bracher","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/2cbe1e2ea3cbf519c8ee3493042de8c9"},"headline":"Grant temporary login access to an Oracle schema","datePublished":"2026-11-11T16:39:39+00:00","dateModified":"2026-11-11T16:39:41+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/grant-temporary-login-access-to-an-oracle-schema\/"},"wordCount":368,"commentCount":0,"articleSection":["Non classifi\u00e9(e)"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/grant-temporary-login-access-to-an-oracle-schema\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/grant-temporary-login-access-to-an-oracle-schema\/","url":"https:\/\/www.dbi-services.com\/blog\/grant-temporary-login-access-to-an-oracle-schema\/","name":"Grant temporary login access to an Oracle schema - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2026-11-11T16:39:39+00:00","dateModified":"2026-11-11T16:39:41+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/2cbe1e2ea3cbf519c8ee3493042de8c9"},"description":"how to give temporary access to an application-user with killing all sessions after a timeout and locking the account","breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/grant-temporary-login-access-to-an-oracle-schema\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/grant-temporary-login-access-to-an-oracle-schema\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/grant-temporary-login-access-to-an-oracle-schema\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Grant temporary login access to an Oracle schema"}]},{"@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\/2cbe1e2ea3cbf519c8ee3493042de8c9","name":"Martin Bracher","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/864a93d77bcd4cd44bab880a88f25fb5173ffbfac8e6e8775f0b4e056a4fbb56?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/864a93d77bcd4cd44bab880a88f25fb5173ffbfac8e6e8775f0b4e056a4fbb56?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/864a93d77bcd4cd44bab880a88f25fb5173ffbfac8e6e8775f0b4e056a4fbb56?s=96&d=mm&r=g","caption":"Martin Bracher"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/martinbracher\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/48370","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\/123"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=48370"}],"version-history":[{"count":6,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/48370\/revisions"}],"predecessor-version":[{"id":48382,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/48370\/revisions\/48382"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=48370"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=48370"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=48370"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=48370"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}