Let's see how we enable an user other than the owner of Oracle product to run SQL*Plus.
Windows
In Windows platform, once oracle database has been installed in the server, every user in the system can run SQL*Plus without problem.
Linux
In Linux or Unix platform, installed SQL*Plus in database server can be executed by anyone. Let's see its permission mode.
[oracle@test ~]$ ls -l $ORACLE_HOME/bin/sqlplus
-rwxr-xr-x 1 oracle oinstall 29144 Jul 2 2020 /u01/app/oracle/product/19.3.0/dbhome_1/bin/sqlplus
As we can see, the permission set shows that other users can read and execute the file.
Which means, any user other than the product owner (usually oracle) can also run sqlplus without problem. But how to make it? how to configure it?
In this post, we introduce the steps to configure and make SQL*Plus executable to other non-oracle users.
Edit Profile
We need to add some environment variables to the shell for the user, the best place to add them is user's profile.
In this case, the user is erp who is a normal user and not in oinstall group.
[erp@test ~]$ vi ~/.bash_profile
...
ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1
PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_HOME PATH
As you can see, we do the following things in profile:
- Add ORACLE_HOME path.
- Add binary directory of ORACLE_HOME to PATH.
- Export the above 2 variables.
Please note that, the profile file could be ~/.bash_profile for Linux (BASH) or ~/.profile for AIX (KSH) and Solaris.
Make Profile Effective
We can make the new profile effective immediately by souring it.
[erp@test ~]$ . ~/.bash_profile
The new environment variables have been added to the user's shell.
Please note that, for scripting the database connection, it's safer to add the command to the first line of your script.
Make TNSNAMES Readable
If the user wants to use local naming (TNSNAMES) to connect to the database, we should make tnsnames.ora readable to others. But this is an optional step.
[oracle@test ~]$ chmod o+r $ORACLE_HOME/network/admin/tnsnames.ora
In this step, we logged in OS as oracle, and then changed permission mode to readable to other users.
However, you can also run sqlplus without using server's tnsnames.ora, so I said this step is optional.
Connection Test
Let' do some simple tests.
PATH
Can we see the sqlplus command?
[erp@test ~]$ which sqlplus
/u01/app/oracle/product/19.3.0/dbhome_1/bin/sqlplus
Passed.
TNSNAMES
Can we read tnsnames.ora?
[erp@test ~]$ tnsping orclpdb
...
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = test.example.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCLPDB)))
OK (0 msec)
Passed.
CONNECT
Can we make a connection?
[erp@test ~]$ sqlplus /nolog
...
SQL> connect hr/hr@orclpdb
Connected.
We made it.
To know how to connect to the database through sqlplus, we have some ways to do it.
OS Authentication
This is an optional step.
To make the user also has the ability to connect to the database through sqlplus / as sysdba, you need 2 more steps.
- Set ORACLE_SID
- Add it to OSDBA Group
You have to make sure that ORACLE_SID environment variable has been set correctly in the shell or user's profile. Otherwise, you will see ORA-12162.
To have the proper permissions to operate the database through OS authentication, you have to add the user to OSDBA group. Otherwise, you will see ORA-01017.