Q

Restricting sys user with password

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 want to restrict even the sys user to specify a password. Can I achieve this?

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.

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

Dig deeper on Oracle database installation, upgrades and patches

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close