Skip to content

Upgrade your Database - NOW!

Mike Dietrich's Blog About Oracle Database Upgrades… Mostly

  • Blog
  • Slides
  • Hands-On Lab
    • 1. Setup
    • 2. Load
    • 3. Capture and Preserve
    • 4. AutoUpgrade
    • 5. AWR Diff
    • 6. SQL Perf Analyzer
    • 7. SQL Plan Mgmt
    • 8. SQL Tuning Advisor
    • 10. Plugin UPGR => CDB2
    • 11. Migrate FTEX
    • 12. Un-/ Plug / Upgrade
    • 13. Fallback Strategies
      • 14a. Issues During Upgrade
      • 14b. Issues After Upgrade
    • 15. AutoUpgrade – Special
    • AutoUpgrade – HOL 19c – Japanese
  • Events
  • Videos
  • Scripts
  • Links
  • Oracle Documentation
  • Privacy
    • Cookie Policy (EU)
    • GDPR – Request personal data
  • About

Security change in Oracle Database 12.2 with login.sql

Posted on April 17, 2017 by Mike.Dietrich Behavior Change Oracle Database 12.2.0.1 Security 12

Sometimes there are tiny little changes in a new release – and sometimes they are not documented very well yet. One of these changes is the new behavior of login.sql for SQL*Plus in Oracle Database 12.2.0.1.

Behavior before Oracle Database 12.2

In all releases until Oracle Database 12.1.0.2 by default SQL*Plus searches for the user profile (login.sql) in your current directory first, and then in the directories you specify with the ORACLE_PATH environment variable.

Please be aware that this change can be introduced with a PSU or a BP from 2017 as well.

Behavior since Oracle Database 12.2

Since Oracle Database 12.2.0.1 SQL*Plus will only search for the user profile (login.sql) in the directories you specify with the ORACLE_PATH environment variable on Linux (or SQLPATH on Windows). That means, you have a login.sql in your working directory – but it will be silently ignored unless you set explicitly ORACLE_PATH or SQLPATH to point to this directory. I’d just blindly guess that the same behavior change applies to SQLcl as well.

Workaround/Solution

Users currently using a login.sql to customize a session will need to set the ORACLE_PATH variable accordingly, and move login.sql to the directory defined in ORACLE_PATH.

If that doesn’t work for you maybe using a prologue for scripts, including it explicitly with @@ calls would be a doable workaround.

On Windows please be aware of (credits to Tim Hall):

  • Bug 25804573
    SQL PLUS 12.2 NOT OBSERVING SQLPATH IN REGISTRY OR ENV VARIABLE FOR LOGIN.SQL

Related information:

  • MOS Note 2241021.1
    SQL*Plus 12.2.0.1.0 Change in Behavior for Search Path of Login.sql
  • Franck Pachot (dbi services):
    https://blog.dbi-services.com/oracle-12cr2-changes-for-login-sql/

–Mike

Share this:
  •  

Related

Tags: 12.2.0.1Behavior ChangeBug 18440095Changelogin.sqlOracle 12.2.0.1Oracle Database 12.2.0.1Oracle Database 12c Release 2

  • Next Unified Auditing – Performance Improvements in Oracle 12.1.0.2
  • Previous Oracle Database 12.2 for AIX and HP-UX is available

You may also like...

  • Oracle 12.2.0.1 – Clarification for Limited Error Correction Support

  • Oracle Database 12.2 for AIX and HP-UX is available

    Oracle Database 12.2 for AIX and HP-UX is available

  • Fallback Strategy: Flashback Pluggable Database

    Fallback Strategy: Flashback Pluggable Database

12 Responses

  • Comments10
  • Pingbacks2
  1. Rajeshwaran, Jeyabal says:
    April 19, 2017 at 08:26

    earlier it was easy for us to remember the urls like this.

    blogs.oracle.com/optimizer
    blogs.oracle.com/developer
    blogs.oracle.com/upgrade.

    but now looks like most of you are getting move out, ( like Maria colgan got moved into sqlmaria.com and now you into this new site ) – it is hard to remember the website name these days.

    Reply
    • Mike.Dietrich says:
      April 19, 2017 at 16:07

      I apologize – and I see your point. Hopefully you’ll remember MikeDietrichDE.com as well as it is my twitter handle 🙂 MikeDietrich.com was open for an offer at roughly $3000 😉 But the blogs.oracle.com will undergo a migration to a more marketing look&feel which is not appropriate for a tech blog. Therefore the move – and I’m just guessing that others will follow.

      Cheers
      Mike

      Reply
  2. Rajeshwaran, Jeyabal says:
    April 20, 2017 at 06:22

    Mike,

    Is it possible for you to add the notification about a new blog post from this blog to appear under the “Blogs” section of this link. ( like how you do previously with blogs.oracle.com/upgrade, when a new post got published it get reflected in “Blogs” of this OTN link )

    http://www.oracle.com/technetwork/dbadev/index.html

    that would help us to keep track of new things coming up in this blog.

    Hope you would help us on this.

    Reply
    • Mike.Dietrich says:
      April 25, 2017 at 22:08

      Rajeshwaran,

      thanks for this hint – but unfortunately I have no influence on this page – and the people owning it will strictly enforce only Oracle-hosted content to be displayed. Furthermore I checked some links and they are pretty outdated. For instance, my “Hitchhiker’s Guide” presentation dates from April 2014 😉

      But you can subscribe on the blog directly – see on the main page at https://MikeDietrichDE.com and scroll down a bit on the right side to SUBSCRIBE. That should work pretty straight forward – and you will ONLY notified when a new post gets published.

      Cheers
      Mike

      Reply
  3. Naveen Garg says:
    April 21, 2017 at 16:12

    We just completed an upgrade to 12.2 (from 12.1) and ran into a strange problem.

    With sec_case_sensitive_logon=TRUE (the default), the password sensitivity must match. I created a test user naveen with password Naveen, and it gives an error when I login as naveen/naveen. Which is expected.

    Next I changed sec_case_sensitive_logon=FALSE and now I cannot login as user naveen. I tried both naveen/Naveen and naveen/naveen and in both cases I get ORA-01017: invalid username/password; logon denied.

    Not sure what is going on here??

    Reply
    • Mike.Dietrich says:
      April 25, 2017 at 22:03

      Hi Naveen,

      the secret is first the SQLNET.ALLOWED_LOGON_VERSION_SERVER in your sqlnet.ora – it defaults to 12, and in order to have this working you will have to lower it to 11 and potentially restart the listener. Furthermore you will have to ALTER the user, e.g. ALTER USER naveen identified by Naveen;. Once you do that you can connect now with naveen/naveen or naveen/NAVEEN etc.

      Cheers
      Mike

      Reply
  4. Meeta Verma says:
    March 23, 2021 at 21:29

    Is it applicable in 19c Env as well? I have a customer who has this issue 19c client: SQL*PLUS does not consistently load the login.sql file from the SQLPATH. They are on 19.10.0.0.0

    Reply
    • Mike.Dietrich says:
      March 30, 2021 at 22:45

      As far as I know, yes.

      Cheers,
      Mike

      Reply
      • Avi Vainshtein says:
        July 13, 2022 at 11:08

        I am in 19.15 at linux and also met that issue. The suggested workaround/solution doesn’t work…
        Could you please detail the idea of “If that doesn’t work for you maybe using a prologue for scripts, including it explicitly with @@ calls would be a doable workaround.” ?
        Regards,
        Avi

        Reply
        • Mike.Dietrich says:
          August 8, 2022 at 11:25

          Hi Avi,

          please check with Oracle Support if the proposed w/a does not work for you.

          Thanks
          Mike

          Reply
  1. SQL*Plus 12.2 | Oracle and other
    April 20, 2017

    […] ad usare questa versione se non al puro scopo di testarla e tal proposito mi sono ricordato di un post di Mike Dietrich (anche perché l’ho letto ieri 🙂 ) dove si parla che una fra le novità di questa versione […]

  2. How To Make login.sql Work Again In 12.2 and 11.2.0.4 – DBA Paradise
    August 31, 2017

    […] 12cR2: changes for login.sql – by Franck Pachot Security change in Oracle Database 12.2 with login.sql – by Mike Dietrich Doc ID 2241021.1 SQL*Plus 12.2.0.1.0 and 11.2.0.4 PSU Change in Behavior […]

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Archives

Categories

Upgrade @YouTube

RSS RSS Feed for Upgrade your Database – NOW!

  • Hide and Seek with RU 19.18.0
  • Suppress nasty error messages and traces during datapatch
  • MRP3 for Oracle 19.17.0 adds an interesting surprise
  • Patching my environments with the January 2023 Bundle Patches
  • Important alert for Oracle Database and GI RU 19.18.0 on Linux

Subscribe via Email

Join 6,589 other subscribers

Follow me on Twitter

My Tweets
Privacy & Cookies: This site uses cookies. By continuing to use this website, you agree to their use.
To find out more, including how to control cookies, see here: Cookie Policy

Recent Posts

  • Hide and Seek with RU 19.18.0
  • Suppress nasty error messages and traces during datapatch
  • MRP3 for Oracle 19.17.0 adds an interesting surprise
  • Patching my environments with the January 2023 Bundle Patches
  • Important alert for Oracle Database and GI RU 19.18.0 on Linux

Upgrade your Database – NOW! © 2023. All Rights Reserved.

Manage Cookie Consent
We use cookies to optimize our website and our service.
Functional Always active
The technical storage or access is strictly necessary for the legitimate purpose of enabling the use of a specific service explicitly requested by the subscriber or user, or for the sole purpose of carrying out the transmission of a communication over an electronic communications network.
Preferences
The technical storage or access is necessary for the legitimate purpose of storing preferences that are not requested by the subscriber or user.
Statistics
The technical storage or access that is used exclusively for statistical purposes. The technical storage or access that is used exclusively for anonymous statistical purposes. Without a subpoena, voluntary compliance on the part of your Internet Service Provider, or additional records from a third party, information stored or retrieved for this purpose alone cannot usually be used to identify you.
Marketing
The technical storage or access is required to create user profiles to send advertising, or to track the user on a website or across several websites for similar marketing purposes.
Manage options Manage services Manage vendors Read more about these purposes
View preferences
{title} {title} {title}