{"id":9842,"date":"2017-03-07T21:58:17","date_gmt":"2017-03-07T20:58:17","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/oracle-12cr2-changes-for-login-sql\/"},"modified":"2017-03-07T21:58:17","modified_gmt":"2017-03-07T20:58:17","slug":"oracle-12cr2-changes-for-login-sql","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/oracle-12cr2-changes-for-login-sql\/","title":{"rendered":"Oracle 12cR2: changes for login.sql"},"content":{"rendered":"

By Franck Pachot<\/h2>\n

.
\nIf you use a login.sql script to set the SQL*Plus environment from your current working directory, you will see that it will not run anymore in 12.2. This is a security feature, and a good occasion to explain how sqlplus finds the scripts to run, on Linux.
\n<\/p>\n

For my test, I have login.sql, LOGIN.SQL, and script.sql in the following directories<\/p>\n

$ tree \/tmp\/mytest\/\n\/tmp\/mytest\/\n\u251c\u2500\u2500 a\n\u2502\u00a0\u00a0 \u251c\u2500\u2500 login.sql\n\u2502\u00a0\u00a0 \u251c\u2500\u2500 LOGIN.SQL\n\u2502\u00a0\u00a0 \u2514\u2500\u2500 script.sqlL\n\u251c\u2500\u2500 b\n\u2502\u00a0\u00a0 \u251c\u2500\u2500 login.sql\n\u2502\u00a0\u00a0 \u251c\u2500\u2500 LOGIN.SQL\n\u2502\u00a0\u00a0 \u2514\u2500\u2500 script.sql\n\u251c\u2500\u2500 login.sql\n\u251c\u2500\u2500 LOGIN.SQL\n\u2514\u2500\u2500 script.sql<\/code><\/pre>\n
I’m going to the parent directory<\/p>\n
cd \/tmp\/mytest<\/code><\/pre>\n
The scripts display their name:<\/p>\n
+ head login.sql LOGIN.SQL script.sql\n==> login.sql  LOGIN.SQL  script.sql <==\nprompt Hello from \/tmp\/mytest\/script.sql<\/code><\/pre>\n
I’ll run commands from bash -x so that they are displayed, and environment variables are set only for the command to be run.<\/p>\n
login.sql<\/h3>\n
+ sqlplus -s \/nolog<\/code><\/pre>\n
Nothing displayed here. This is what has changed in 12.2 for security reasons the login.sql in the current working directory is not run anymore.<\/p>\n
+ sqlplus -s \/nolog @ login.sql<\/code><\/pre>\n
This is probably a side effect of the implementation of this new security feature: even when I explicitly want to run the login.sql script it is ignored<\/p>\n
+ sqlplus -s \/nolog @ login\nHello from \/tmp\/mytest\/login.sql<\/code><\/pre>\n
Here, I rely on the implicit ‘.sql’ added and the script is run. Probably the implementation of the security feature is done before this implicit extension.<\/p>\n
+ sqlplus -s \/nolog @ \/tmp\/mytest\/login.sql\nHello from \/tmp\/mytest\/login.sql<\/code><\/pre>\n
With full path, the script is run.<\/p>\n
Actually, the only way to get the current directory login.sql run implicitly when starting sqlplus or connecting is to set the current directory in ORACLE_PATH:<\/p>\n
+ ORACLE_PATH=.\n+ sqlplus -s \/nolog\nHello from \/tmp\/mytest\/login.sql\n<\/code><\/pre>\n
Note that this defeats the security feature, in the same way it is not recommended to add ‘.’ to your shell PATH. It is better to put only absolute paths in the PATH, with directories where you know nobody can add a trojan script.<\/p>\n
LOGIN.SQL<\/h3>\n
+ sqlplus -s \/nolog @ LOGIN.SQL<\/code><\/pre>\n
The implementation of this new feature is case insensitive. LOGIN.SQL is ignored even when specified in the command line.<\/p>\n
+ sqlplus -s \/nolog @ .\/LOGIN.SQL\nHello from \/tmp\/mytest\/LOGIN.SQL<\/code><\/pre>\n
Only when using less or more characters to specify it is is used.<\/p>\n
Note that the implicit login.sql is case sensitive on Linux:<\/p>\n
+ rm login.sql\n+ ORACLE_PATH=.\n+ sqlplus -s \/nolog\n<\/code><\/pre>\n
Even with ORACLE_PATH it is not found.<\/p>\n
SQLPATH<\/h3>\n
+ SQLPATH=\/tmp\/mytest\n+ sqlplus -s \/nolog<\/code><\/pre>\n
SQLPATH is not a solution to find login.sql unlike ORACLE_PATH<\/p>\n
Note that the documentation tells very different things in http:\/\/docs.oracle.com\/database\/122\/SQPUG\/configuring-SQL-Plus.htm#SQPUG012<\/a><\/p>\n
Update 28-AUG-17 – see Edward Rusu about the bug and patch.<\/p>\n
script.sql<\/h3>\n
Now, because SQLPATH and ORACLE_PATH was already a mess, I’ll try with a script that is not login.sql<\/p>\n
+ sqlplus -s \/nolog @ script.sql\nHello from \/tmp\/mytest\/script.sql<\/code><\/pre>\n
Current directory is still searched for non-login scripts<\/p>\n
+ sqlplus -s \/nolog @ \/tmp\/mytest\/script.sql\nHello from \/tmp\/mytest\/script.sql<\/code><\/pre>\n
Absolute path can be used, or we can sete a PATH to search:<\/p>\n
+ SQLPATH=\/tmp\/mytest\n+ sqlplus -s \/nolog @ script\nHello from \/tmp\/mytest\/script.sql\n<\/code><\/pre>\n
Unlike login.sql, SQLPATH can be used to find a script in another directory<\/p>\n
+ cd \/tmp\/mytest\/a\n+ SQLPATH=\/tmp\/mytest\n+ sqlplus -s \/nolog @ script\nHello from \/tmp\/mytest\/a\/script.sql<\/code><\/pre>\n
But current directory is still the first one that is searched<\/p>\n
+ rm script.sql\n+ SQLPATH=\/tmp\/mytest\n+ sqlplus -s \/nolog @ script\nHello from \/tmp\/mytest\/script.sql<\/code><\/pre>\n
Only when the script is not in the current directory it is searched in SQLPATH<\/p>\n
+ rm \/tmp\/mytest\/script.sql\n+ SQLPATH=\/tmp\/mytest\n+ sqlplus -s \/nolog @ script\nSP2-0310: unable to open file \"script.sql\"\n<\/code><\/pre>\n
Again, the documentation is wrong. Only specified directories are looked-up, not sub-directories. But if I specify the subdirectory relative to SQLPATH (I am still in \/tmp\/mytest\/a where there is no script.sql)<\/p>\n
+ SQLPATH=\/tmp\/mytest\n+ sqlplus -s \/nolog @ b\/script\nHello from \/tmp\/mytest\/b\/script.sql<\/code><\/pre>\n
The b\/script was resolved from the SQLPATH=\/tmp\/mytest<\/p>\n
In SQLPATH, we can add multiple paths<\/p>\n
+ SQLPATH=\/tmp\/mytest:\/tmp\/mytest\/a:\/tmp\/mytest\/b\n+ sqlplus -s \/nolog @ script\nHello from \/tmp\/mytest\/b\/script.sql<\/code><\/pre>\n
Here I have a script.sql only in the ‘b’ sub-directory and sqlplus finds it when this directory is listed in SQLPATH<\/p>\n
ORACLE_PATH<\/h3>\n
Running the same with ORACLE_PATH instead of SQLPATH<\/p>\n
+ ORACLE_PATH=\/tmp\/mytest:\/tmp\/mytest\/a:\/tmp\/mytest\/b\n+ sqlplus -s \/nolog @ script\nHello from \/tmp\/mytest\/a\/login.sql\nHello from \/tmp\/mytest\/b\/script.sql<\/code><\/pre>\n
We can have also multiple paths for ORACLE_PATH (this is not in the documentation) and it acts as with SQLPATH but there are a few differences.<\/p>\n
First, you have seen that the login.sql script is run.<\/p>\n
Then, if I have the script in my current directory, but not in ORACLE_PATH<\/p>\n
+ cd \/tmp\n+ echo 'prompt Hello from \/tmp' > script.sql\n+ ORACLE_PATH=\/tmp\/mytest:\/tmp\/mytest\/a:\/tmp\/mytest\/b\n+ sqlplus -s \/nolog @ script\nHello from \/tmp\/mytest\/a\/login.sql\nHello from \/tmp\/mytest\/b\/script.sql<\/code><\/pre>\n
The ORACLE_PATH one is used first<\/p>\n
+ ORACLE_PATH=\/tmp\/mytest:\/tmp\/mytest\/a\n+ sqlplus -s \/nolog @ script\nHello from \/tmp\/mytest\/a\/login.sql\nHello from \/tmp<\/code><\/pre>\n
The current directory is considered only when not found in ORACLE_PATH.<\/p>\n
+ ORACLE_PATH=\/tmp\/mytest:\/tmp\/mytest\/a\n+ sqlplus -s \/nolog @ b\/script\nHello from \/tmp\/mytest\/a\/login.sql\nHello from \/tmp\/mytest\/b\/script.sql<\/code><\/pre>\n
As with SQLPATH, subdirectory is accessible if mentioned.<\/p>\n
Both, in order<\/h3>\n
If you don’t want to keep it simple, you can specify both ORACLE_PATH and SQLPATH<\/p>\n
+ cd mytest\n+ ORACLE_PATH=\/tmp\n+ SQLPATH=\/tmp\/mytest\/b\n+ sqlplus -s \/nolog @ script\nHello from \/tmp<\/code><\/pre>\n
and ORACLE_PATH is read first. So the order is:<\/p>\n
    \n
  1. ORACLE_PATH, in order of paths specified<\/li>\n
  2. Current directory (except for login.sq)<\/li>\n
  3. SQLPATH (except for login.sql) in order of paths specified<\/li>\n<\/ol>\n
    strace<\/h3>\n
    Better than documentation or testing all combinations, in Linux we can trace the system calls when sqlplus is looking for the script.<\/p>\n
    I’ve set non-existing paths \/ORACLE_PATH1 and \/ORACLE_PATH2 for ORACLE_PATH, and \/SQLPATH1 and \/SQLPATH2 for SQLPATH and run ‘script’ without the extension<\/p>\n
    ORACLE_PATH=\/ORACLE_PATH1:\/ORACLE_PATH2 SQLPATH=\/SQLPATH1:\/SQLPATH2 strace -e trace=file sqlplus -s \/nolog @ script<\/code><\/pre>\n
    This traces all system calls with a file name:<\/p>\n
    \naccess(\"\/ORACLE_PATH1\/script\", F_OK)    = -1 ENOENT (No such file or directory)\naccess(\"\/ORACLE_PATH2\/script\", F_OK)    = -1 ENOENT (No such file or directory)\naccess(\"\/ORACLE_PATH1\/script.sql\", F_OK) = -1 ENOENT (No such file or directory)\naccess(\"\/ORACLE_PATH2\/script.sql\", F_OK) = -1 ENOENT (No such file or directory)\naccess(\"\/ORACLE_PATH1\/script\", F_OK)    = -1 ENOENT (No such file or directory)\naccess(\"\/ORACLE_PATH2\/script\", F_OK)    = -1 ENOENT (No such file or directory)\naccess(\"\/ORACLE_PATH1\/script.sql\", F_OK) = -1 ENOENT (No such file or directory)\naccess(\"\/ORACLE_PATH2\/script.sql\", F_OK) = -1 ENOENT (No such file or directory)\nstat(\"script.sql\", 0x7fff01921400)      = -1 ENOENT (No such file or directory)\naccess(\"\/ORACLE_PATH1\/script\", F_OK)    = -1 ENOENT (No such file or directory)\naccess(\"\/ORACLE_PATH2\/script\", F_OK)    = -1 ENOENT (No such file or directory)\naccess(\"\/ORACLE_PATH1\/script.sql\", F_OK) = -1 ENOENT (No such file or directory)\naccess(\"\/ORACLE_PATH2\/script.sql\", F_OK) = -1 ENOENT (No such file or directory)\nstat(\"\/SQLPATH1\/script.sql\", 0x7fff0191b430) = -1 ENOENT (No such file or directory)\nstat(\"\/SQLPATH2\/script.sql\", 0x7fff0191b430) = -1 ENOENT (No such file or directory)\nSP2-0310: unable to open file \"script.sql\"\naccess(\"\/ORACLE_PATH1\/login.sql\", F_OK) = -1 ENOENT (No such file or directory)\naccess(\"\/ORACLE_PATH2\/login.sql\", F_OK) = -1 ENOENT (No such file or directory)\naccess(\"\/ORACLE_PATH1\/login.sql\", F_OK) = -1 ENOENT (No such file or directory)\naccess(\"\/ORACLE_PATH2\/login.sql\", F_OK) = -1 ENOENT (No such file or directory)\nstat(\"\/u01\/app\/oracle\/product\/12.2.0\/dbhome_1\/sqlplus\/admin\/glogin.sql\", {st_mode=S_IFREG|0644, st_size=342, ...}) = 0\naccess(\"\/u01\/app\/oracle\/product\/12.2.0\/dbhome_1\/sqlplus\/admin\/glogin.sql\", F_OK) = 0\nstatfs(\"\/u01\/app\/oracle\/product\/12.2.0\/dbhome_1\/sqlplus\/admin\/glogin.sql\", {f_type=\"EXT2_SUPER_MAGIC\", f_bsize=4096, f_blocks=6676009, f_bfree=2866104, f_bavail=2521221, f_files=1703936, f_ffree=1663469, f_fsid={-1731931108, 1057261682}, f_namelen=255, f_frsize=4096}) = 0\nopen(\"\/u01\/app\/oracle\/product\/12.2.0\/dbhome_1\/sqlplus\/admin\/glogin.sql\", O_RDONLY) = 9\n<\/code><\/pre>\n
    This is very interesting. First, we see that the paths are searched multiple time, and I don’t know why. Second, when passing a name without extension (i.e without dot in the name) the exact name is used first for ORACLE_PATH, but lookup in the current directory and in SQLPATH automatically adds ‘.sql’. The system calls are also different: ORACLE_PATH has no stat() call before access(), which is different with current directory and SQLPATH. Finally, login.sql is read from ORACLE_PATH only and glogin.sql from ORACLE_HOME\/sqlplus\/admin.<\/p>\n
    Change occurred between 12.2.0.1 and 12.2.0.1<\/h3>\n
    As a comparison, sqlplus 12.1.0.2 and even 12.2.0.1 DBaaS version (built in October) has the following additional calls to look for login.sql in current path and in SQLPATH:<\/p>\n
    stat(\"login.sql\", 0x7fffc14d5490)       = -1 ENOENT (No such file or directory)\nstat(\"\/SQLPATH1\/login.sql\", 0x7fffc14cf4c0) = -1 ENOENT (No such file or directory)\nstat(\"\/SQLPATH2\/login.sql\", 0x7fffc14cf4c0) = -1 ENOENT (No such file or directory)<\/code><\/pre>\n
    This has disappeared in 12.2.0.1 on-premises version (build in January).<\/p>\n
    So what?<\/h3>\n
    \n
    WTF login.sql is not run, even when explicitly started
    Except when mentioning it as 'login' or as '.\/login.sql' pic.twitter.com\/ClfvAUizO6<\/a><\/p>\n
    — Franck Pachot (@FranckPachot) March 7, 2017<\/a><\/p><\/blockquote>\n