Hi Maria.
  1. 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?
  2. 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.
My current setting for the remote_login_passwordfile parameter is EXCLUSIVE.

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: