[20230124]12c访问login.sql脚本.txt

--//家里笔记本电脑,发现一个奇怪的现象,发现无法访问SQLPATH环境变量定义的路径下login.sql文件.
--//找到一个链接:
--//https://mikedietrichde.com/2017/04/17/security-change-in-oracle-database-12-2-with-login-sql/

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.

--//翻译如下:

由于Oracle数据库12.2.0.1 SQL*Plus将只搜索在Linux上使用ORACLE_PATH环境变量(或)指定的目录中的用户配置文件(login.sql)。这
意味着，您的工作目录中有一个login.sql——但是它将被静默地忽略，除非您显式地设置ORACLE_PATH或SQLPATH指向这个目录。我只是
盲目地猜测，同样的行为改变也适用于SQLcl。

--//https://oracle-base.com/articles/12c/sqlplus-enhancements-12c

12c Release 2 (12.2)
login.sql

In previous releases, SQL*Plus checked the current working directory for the User Profile (login.sql) file, followed by
the directories specified in the ORACLE_PATH environment variable on UNIX/Linux or SQLPATH on Windows.

The 12.2 version of SQL*Plus no longer searches in the working, so the location of the "login.sql" file must be
referenced in the ORACLE_PATH or SQLPATH environment variable, depending on your operating system.

# UNIX/Linux
export ORACLE_PATH=/path/to/my/scripts/

Rem Windows
set SQLPLATH=C:\path\to\my\scripts

Alternatively, include a direct reference to the script at the start of your other scripts.

@@/path/to/my/scripts/login.sql

The 12.2 behaviour may be back-ported to 12.1 in a future security patch.

Check out these resources.

--//https://www.dbi-services.com/blog/oracle-12cr2-changes-for-login-sql/
--//按照上面的介绍,如果我按照OS需求定义ORACLE_PATH或者SQLPLATH环境变量,访问应该是没有问题的.
--//测试如下:

1.环境:
SCOTT@test01p> @ver1
PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.2.0.1.0     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0

d:\tmp> echo %SQLPATH%
d:\tools\sqllaji;d:\tools\sqllaji\tpt

d:\tmp> cat D:\tools\sqllaji\tpt\login.sql
-- Copyright 2018 Tanel Poder. All rights reserved. More info at http://tanelpoder.com
-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms & conditions.

-- calling init.sql which will set up sqlpus variables
prompt Hello from D:\tools\sqllaji\tpt\login.sql
@init.sql
-- i.sql is the "who am i" script which shows your session/instance info and
-- also sets command prompt window/xterm title
-- @i.sql

-- you can put your own login scripts here

d:\tmp> sqlplus -s /nolog
quit

d:\tmp> sqlplus -s /nolog  @ D:\tools\sqllaji\tpt\login.sql
Hello from D:\tools\sqllaji\tpt\login
quit

--//即使我修改定义如下:
d:\tmp> set SQLPATH=d:\tools\sqllaji\tpt
set SQLPATH=d:\tools\sqllaji\tpt

d:\tmp> sqlplus -s /nolog
quit

d:\tmp> set ORACLE_PATH=d:\tools\sqllaji\tpt
set ORACLE_PATH=d:\tools\sqllaji\tpt

d:\tmp> sqlplus -s /nolog
quit

--//都是无法访问d:\tools\sqllaji\tpt\login.sql脚本.
--//如果仔细看https://www.dbi-services.com/blog/oracle-12cr2-changes-for-login-sql/,链接给出许多测试.
--//测试都应该在linux下进行的,不知道是windoOS版本的问题,或者windows完全取消这样的功能.
--//当然手工解决也很简单,自己打入执行:

d:\tmp> sqlplus -s /nolog
@login
Hello from D:\tools\sqllaji\tpt\login.sql
quit

2.linux下测试:
--//在11g下测试:
$ ORACLE_PATH=~/ORACLE_PATH1:~/ORACLE_PATH2 SQLPATH=~/SQLPATH1:~/SQLPATH2 strace -e trace=file -o aa3.txt sqlplus -s /nolog
quit

$ grep -i login aa3.txt
access("/home/oracle/ORACLE_PATH1/login.sql", F_OK) = -1 ENOENT (No such file or directory)
access("/home/oracle/ORACLE_PATH2/login.sql", F_OK) = -1 ENOENT (No such file or directory)
access("/home/oracle/ORACLE_PATH1/login.sql", F_OK) = -1 ENOENT (No such file or directory)
access("/home/oracle/ORACLE_PATH2/login.sql", F_OK) = -1 ENOENT (No such file or directory)
access("/home/oracle/ORACLE_PATH1/login.sql", F_OK) = -1 ENOENT (No such file or directory)
access("/home/oracle/ORACLE_PATH2/login.sql", F_OK) = -1 ENOENT (No such file or directory)
access("/home/oracle/ORACLE_PATH1/login.sql", F_OK) = -1 ENOENT (No such file or directory)
access("/home/oracle/ORACLE_PATH2/login.sql", F_OK) = -1 ENOENT (No such file or directory)
stat("login.sql", 0x7fff9a9b2f70)       = -1 ENOENT (No such file or directory)
access("/home/oracle/ORACLE_PATH1/login.sql", F_OK) = -1 ENOENT (No such file or directory)
access("/home/oracle/ORACLE_PATH2/login.sql", F_OK) = -1 ENOENT (No such file or directory)
access("/home/oracle/ORACLE_PATH1/login.sql", F_OK) = -1 ENOENT (No such file or directory)
access("/home/oracle/ORACLE_PATH2/login.sql", F_OK) = -1 ENOENT (No such file or directory)
stat("/home/oracle/SQLPATH1/login.sql", 0x7fff9a9acf50) = -1 ENOENT (No such file or directory)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
stat("/home/oracle/SQLPATH2/login.sql", 0x7fff9a9acf50) = -1 ENOENT (No such file or directory)
stat("/u01/app/oracle/product/11.2.0.4/dbhome_1/sqlplus/admin/glogin.sql", {st_mode=S_IFREG|0644, st_size=1708, ...}) = 0
access("/u01/app/oracle/product/11.2.0.4/dbhome_1/sqlplus/admin/glogin.sql", F_OK) = 0
statfs("/u01/app/oracle/product/11.2.0.4/dbhome_1/sqlplus/admin/glogin.sql", {f_type="EXT2_SUPER_MAGIC", f_bsize=4096, f_blocks=398913534, f_bfree=64408982, f_bavail=43818472, f_files=411828224, f_ffree=411762556, f_fsid={2019274029, -1076953238}, f_namelen=255, f_frsize=4096}) = 0
open("/u01/app/oracle/product/11.2.0.4/dbhome_1/sqlplus/admin/glogin.sql", O_RDONLY) = 7
stat("/u01/app/oracle/product/11.2.0.4/dbhome_1/sqlplus/admin/zzlogin.sql", {st_mode=S_IFREG|0644, st_size=3070, ...}) = 0
access("/u01/app/oracle/product/11.2.0.4/dbhome_1/sqlplus/admin/zzlogin.sql", F_OK) = 0
statfs("/u01/app/oracle/product/11.2.0.4/dbhome_1/sqlplus/admin/zzlogin.sql", {f_type="EXT2_SUPER_MAGIC", f_bsize=4096, f_blocks=398913534, f_bfree=64408982, f_bavail=43818472, f_files=411828224, f_ffree=411762556, f_fsid={2019274029, -1076953238}, f_namelen=255, f_frsize=4096}) = 0
open("/u01/app/oracle/product/11.2.0.4/dbhome_1/sqlplus/admin/zzlogin.sql", O_RDONLY) = 8
--//可以看出linux下oracle仅仅识别环境变量ORACLE_PATH路径下的login.sql文件.
--//我不知道为什么出现使用stat打开/home/oracle/SQLPATH1/login.sql的情况.但是并不是acess操作.
--//难道如果stat能定位login.sql,接下来的操作是打开吗? 如果按照这个理解11g下会自动定位ORACLE_PATH,然后查找SQLPATH.是否存
--//在login.sql文件.

--//在19c下测试:
$ ORACLE_PATH=~/ORACLE_PATH1:~/ORACLE_PATH2 SQLPATH=~/SQLPATH1:~/SQLPATH2 strace -e trace=file -o aa3.txt sqlplus -s /nolog
quit

$ grep -i login aa3.txt
access("/home/oracle/ORACLE_PATH1/login.sql", F_OK) = -1 ENOENT (No such file or directory)
access("/home/oracle/ORACLE_PATH2/login.sql", F_OK) = -1 ENOENT (No such file or directory)
access("/home/oracle/ORACLE_PATH1/login.sql", F_OK) = -1 ENOENT (No such file or directory)
access("/home/oracle/ORACLE_PATH2/login.sql", F_OK) = -1 ENOENT (No such file or directory)
stat("/u01/app/oracle/product/19/db_1/sqlplus/admin/glogin.sql", {st_mode=S_IFREG|0644, st_size=356, ...}) = 0
access("/u01/app/oracle/product/19/db_1/sqlplus/admin/glogin.sql", F_OK) = 0
statfs("/u01/app/oracle/product/19/db_1/sqlplus/admin/glogin.sql", {f_type=0x58465342, f_bsize=4096, f_blocks=13100545, f_bfree=9352992, f_bavail=9352992, f_files=52427776, f_ffree=52292527, f_fsid={63747, 0}, f_namelen=255, f_frsize=4096, f_flags=ST_VALID|ST_RELATIME}) = 0
open("/u01/app/oracle/product/19/db_1/sqlplus/admin/glogin.sql", O_RDONLY) = 9
stat("/u01/app/oracle/product/19/db_1/sqlplus/admin/zzlogin.sql", {st_mode=S_IFREG|0644, st_size=3020, ...}) = 0
access("/u01/app/oracle/product/19/db_1/sqlplus/admin/zzlogin.sql", F_OK) = 0
statfs("/u01/app/oracle/product/19/db_1/sqlplus/admin/zzlogin.sql", {f_type=0x58465342, f_bsize=4096, f_blocks=13100545, f_bfree=9352992, f_bavail=9352992, f_files=52427776, f_ffree=52292527, f_fsid={63747, 0}, f_namelen=255, f_frsize=4096, f_flags=ST_VALID|ST_RELATIME}) = 0
open("/u01/app/oracle/product/19/db_1/sqlplus/admin/zzlogin.sql", O_RDONLY) = 10
--//同样的情况,19c就不同,oracle仅仅查询ORACLE_PATH环境变量.

3.总结:
--//1.windows下至少我的测试版本12c对于ORACLE_PATH,SQLPATH定义无效,指缺省访问login.sql文件,以下情况类似.
--//2.linux下11g版本可以定义ORACLE_PATH,SQLPATH有效,建议仅仅使用ORACLE_PATH环境变量.
--//3.linux下12c以后版本仅仅定义ORACLE_PATH有效.
--//4.也是oracle出于安全的原因做出这样的修改操作.
--//4.链接https://blog.dbi-services.com/oracle-12cr2-changes-for-login-sql/给出许多测试.
--//5.我个人建立不要ORACLE_PATH或者SQLPATH定义多个访问目录路径,这样容易混乱.