Skip to content

Enable Non-Oracle User Run SQL*Plus

Let's see how we enable an user other than the owner of Oracle product to run SQL*Plus.


In Windows platform, once oracle database has been installed in the server, every user in the system can run SQL*Plus without problem.


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

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.


Can we see the sqlplus command?

[erp@test ~]$ which sqlplus



Can we read tnsnames.ora?

[erp@test ~]$ tnsping orclpdb
Used TNSNAMES adapter to resolve the alias
OK (0 msec)



Can we make a connection?

[erp@test ~]$ sqlplus /nolog
SQL> connect hr/hr@orclpdb

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.

  • 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.

  • Add it to OSDBA Group
  • 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.

Leave a Reply

Your email address will not be published. Required fields are marked *