Password file
It is used to connect to remote database over NON-SECURED connections as a PRIVILEGED USER.
Password file contains list of users who have been given sysdba or sysoper system privilege.
You can only alter users in the password file ( i.e. grant/revoke sysdba|sysoper to a user;) if the parameter remote_login_passwordfile value is set to 'EXCLUSIVE'.
Parameter remote_login_passwordfile is a static parameter (can't be changed without bouncing database).
Grant & revoke sysdba privilege
grant sysdba to scott; ---> can't be used with ADMIN option.
Revoke sysdba from scott;
Grant & revoke sysoper system privilege
grant sysoper to scott;
revoke sysoper from scott;
similarly on ASM instance we can check for users with sysasm privilege.
Create password file by orapwd
orapwd file=name entries=# force=y|n ignorecase=y|n
Query to check the users who have been granted "sysdba" system privilege
SELECT USERNAME FROM V$PWFILE_USERS WHERE USERNAME != 'SYS' AND SYSDBA='TRUE';
Query to check the users who have been granted "sysoper" system privilege
SELECT USERNAME FROM V$PWFILE_USERS WHERE USERNAME != 'SYS' AND SYSOPER='TRUE';
