Tuesday, September 14, 2010

For Reference- Sysdba priviledge to any user

For any user to have sysdba priv:

1) Oracle maintains an encrypted password file, which has entries of all the users who have SYSDBA privilege. This file is located in $ORACLE_HOME/dbs location. In order to create password file, orapwd utility is used:
orapwd file=[file_name] password=[sys pwd] entries=[max entries of users in passwordfile]

2) show parameter remote_login_passwordfile
  • EXCLUSIVE - Password file would be exclusive to this DB instance.
  • SHARED - Password file can be shared to multiple databases

3) select * from v$pwfile_users - Since the password file has been freshly created, the view v$pwfile_users would have the entry of SYS user only.

4) grant sysdba to [user]

5) select * from v$pwfile_users - would display the [user] as well.

The [user] would be added to the entries in the password file, and hence would have sysdba privs.
Password file is used only when the user tries to connect a database from remote server. The other way to connect to the database is .. OS level authentication, wherein the user logs in to the database from the same server where the database resides.
sqlplus dsingh as sysdba - Oracle uses OS level authentication.
sqlplus "dsingh@w140p.world as sysdba" - Network authentication
(Also, in case of OS level authentication, whatever follows sqlplus is ignored. If the current logged in user is a part of dba group, it would be allowed to login as sysdba. )

No comments:

Post a Comment