[20230124]12c訪問login.sql指令碼.txt

[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。

--//

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
-- 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定義多個訪問目錄路徑,這樣容易混亂.