Restrict user to use ‘conn /as sysdba’ from OS Level

Question

It is possible to allow the use of sqlplus at OS level to a certain user or group, but restrict the use of "sqlplus / as sysdba" to the same user o group?

Answer 1

If I understood your question correctly the answer to the question is YES, as long as the user is not part of the dba group. The execute flag is set for "others" by default, so any user can run sqlplus and connect with a username/password. You don't even have to setup a new user or group specifically, just make sure that the user is not part of the dba group:

Default rights for sqlplus have set the execution flag for others:

$ cd /opt/oracle/product/12.2.0.1/dbhome_1/bin
$ ls -al sqlplus
-rwxr-xr-x 1 oracle oinstall 25168 Sep 22 16:48 sqlplus

User gerald is not part of dba group and therefore not allowed to connect via sqlplus / as sysdba:

$ id
uid=54322(gerald) gid=54331(gerald) groups=54331(gerald)

$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sat Sep 23 04:22:33 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name:

However, the user gerald can still run sqlplus and connect via username/password:

$ id
uid=54322(gerald) gid=54331(gerald) groups=54331(gerald)

$ sqlplus sys/gerald as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sat Sep 23 04:27:58 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Obviously, if you don't want to give users SYS access at all, don't share the SYS password with them!

Answer 2

Normally anyone with an Oracle username assigned to them can use SQLPLus but only members of the Oracle owner OS group can use sqlplus as sysdba without needing a password. This privilege is normally assigned to OS group DBA, but can be different. I have worked on a system where members of the DBA group could not connect using as sysdba since Oracle as set up only in oinstall. This is configured at install time. The answer to your question as asked is, No. If you assign them to the privileged group then they have the privilege.