【Oracle ERP Note 】sqlplus: Permission denied

Problem

平常週期性跑報表的 script，執行時突然冒出錯誤訊息:


WIP Defect Analysis Daily Report Generate: 01-DEC-2014
./gen_wip_prod_def.sh: line 23: /u01/proddb/proddb/11.2.2/bin/sqlplus: Permission denied


Study

依據 Reference 1，檢查 $ORACLE_HOME/bin, lib, oracore, sqlplus 權限，發現 sqlplus其他帳號無 read / execute 權限，但 PATH 中有指向其路徑。

[proddba]$ ls -l sql*
-rwxr-x--x 1 proddba proddba 1489856 Apr  4  2013 sqlldr
-rwxr-x--- 1 proddba proddba       0 Sep  5  2010 sqlldrO
-rwxr-x--- 1 proddba proddba    9181 Apr  4  2013 sqlplus


Solution

更改 sqlplus 的權限後，以 which sqlplus來檢驗 OK，而此時可跑週期性的報表

[proddba]$ chmod 755 sqlplus
[proddba]$ ls -l sql*
-rwxr-x--x 1 proddba proddba 1489856 Apr  4  2013 sqlldr
-rwxr-x--- 1 proddba proddba       0 Sep  5  2010 sqlldrO
-rwxr-xr-x 1 proddba proddba    9181 Apr  4  2013 sqlplus


Reference

1. Permitting a user on UNIX / Linux to use sqlplus, http://databaseoracle.blogspot.tw/2006/11/permitting-user-on-unix-linux-to-use.html, Oracle Database Technology

Thanks Anshu Singare for his great work. I refer to his article shown as below:


For any normal user (not a part of "oinstall" / "dba" groups ) to be able to run sqlplus and access an ORACLE database , read/execute permissions are required for these 4 directories :
$ORACLE_HOME/bin
$ORACLE_HOME/lib
$ORACLE_HOME/oracore
$ORACLE_HOME/sqlplus

In addition, these 4 parameters should also be set in the user's environment (.profile) :
ORACLE_HOME,
LD_LIBRARY_PATH,
ORACLE_SID,
PATH