Search code examples
linuxoraclesecurityubuntusysdba

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


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?


Solution

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