- We have Oracle installed on a Solaris server. The owner of the software is "oracle," which is a member of the dba and oinstall OS groups. Now whenever I log in on the server through the "oracle" user, I am able to connect to the database without giving the password (i.e., connect /as sysdba). I want to restrict even the sys user to specify a password. Can I achieve this?
- Secondly, I want to know that users who are not members of the dba and oinstall OS groups can connect to local servers by giving passwords. Currently this is not possible.
By adding the following line to the sqlnet.ora file (located under $ORACLE_HOME/network/admin or wherever $TNS_ADMIN is defined), this prevents SYS from connecting locally to the database without providing a password:
SQLNET.AUTHENTICATION_SERVICE=(NONE)After adding this line, when you try to perform the following …
connect / as sysdba… you should receive an ORA-01031 (insufficient privileges) error. You will now have to connect 'sys as sysdba' and provide a password.
With respect to your second question, it's unclear whether users will be connecting to the database server locally and then attempting to connect to the database via SQL*Plus, or whether they want to connect remotely to the database from other workstations or servers.
If it is the first scenario, they must have Unix accounts on the database server. Create an application-related group (other than dba and oinstall) and assign this group to the newly created accounts. Ensure that the Unix accounts can invoke oraenv to set their environment or hardcode $ORACLE_HOME/bin into their path if there is only one $ORACLE_HOME on the server. This is so they can find the sqlplus executable. They must also have an account within the Oracle database so that when they invoke SQL*Plus, they can provide a username and password to connect to the database.
In the second scenario, only a database account is required. SQL*Plus would be invoked from the desktop and the person connecting to the remote database would provide a username, password and host string. This assumes that the workstation has a valid tnsnames.ora (and possibly sqlnet.ora) file, and that there is a listener running on the database server.
This was first published in April 2006