Friday, January 1, 2010

How to force sysdba user for password in order to login into database


Authentication Type
There are 2 types of authentication:
  1. OS (Operating System) Authentication
  2. Password File Authentication
OS (Operating System) Authentication
As you are probably aware, if an operating system user is a member of the known Oracle DBA, then they have implicit privileges to "connect / as sysdba" without using a password.
There are two ways to fix this:
1.        Make sure operating-system user does not have membership in the privileged DBA group.
Example: Use usermod command and change the dba group tpo some other, make sure to take full OH cold backup before applying this procedure.
2.       Edit your $ORACLE_HOME/rdbms/lib/config.s file to refer to a bogus, empty unix group then re-link all Oracle software components with the new, "empty-membered" Oracle DBA group.
Example:
Take full OH cold backup before applying these steps.
Make sure database services are down.
Take backup of config.c and config.o
Edit parameters  SS_DBA_GRP “dba” to SS_DBA_GRP “unknown” and SS_OPER_GRP “dba” to SS_OPER_GRP “unknown”.
Mv config.o config.o.sav
Make sure ORACLE_HOME and LD_LIBRARY_PATH variables are set accordingly.
Execute command Relink all.
If necessary execute  make -f ins_rdbms.mk ioracle
Password File Authentication
In case of password file authentication, we create a password file for our database. ORAPWD is the utility for creating a password file.
After creating password file, how your database will know that you have created password file and you are supposed to use the same. This is done by INIT.ORA parameter REMOTE_LOGIN_PASSWORDFILE. This
parameter can have 3 values (none - OS level authentication, shared/exclusive – password file authentication). So for using password file, you need to set the value of this parameter to either shared or exclusive.
SQLNET.AUTHENTICATION_SERVICES
Use the parameter SQLNET.AUTHENTICATION_SERVICES to enable one or more authentication services. If authentication has been installed, it is recommended that this parameter be set to either none or to one of the authentication methods.
Authentication Methods Available with Oracle Net Services:
***none for no authentication methods. A valid username and password can be used to access the database.
***all for all authentication methods
nts for Windows NT native authentication