Real world scenario:

I have users: A_DBA, B_JUNIOR, C_MAIN, D_USERS.

I want my junior, user B_JUNIOR,  to grant execute privilege on C_MAIN.testProc to D_USERS, without having him execute privilege on C_MAIN.testProc.

So I want my junior to grant(JUST grant) object privilege on one schema to another schema .


GRANT ANY OBJECT PRIVILEGE  is the privilege which solves it.

SQL> connect A_DBA/ta@MYDB

Connected to Oracle Database 11g Enterprise Edition Release 

Connected as A_DBA


Grant succeeded

SQL> connect B_JUNIOR/a@MYDB

Connected to Oracle Database 11g Enterprise Edition Release 

Connected as B_JUNIOR 

SQL> grant execute on C_MAIN.testProc to D_USERS

Grant succeeded

————————————BUT, junior can’t execute itself

SQL> exec C_MAIN.testProc;

begin C_MAIN.testProc; end;

ORA-06550: line 2, column 7:

PLS-00201: identifier 'C_MAIN.TESTPROC' must be declared

ORA-06550: line 2, column 7:

PL/SQL: Statement ignored

What is orapwd?

Orapwd is the Oracle utility to create password file.  The syntax is the following:

orapwd file=file_name
password=password for SYS

file– is the password file name. If you do not indicate the full path, then file will be created in the current directory.
password-is the password for sys user.
entries– is the maximum number of users that can be granted sysdba or sysoper privileges.
force-if the value of this parameter is Y then the existing password file will be overwritten.
ignorecase– password will be case insensitive.

Note: parameters that are enclosed by ‘[‘ and ‘]’ are optional.

For example:

 orapwd file= pwdorcl password=sys entries=20


Password file, called pwdorcl.ora, will be created in the current directory. The password for sys user will be sys and maximum 20 users can be granted sysdba or sysoper privileges.

Note: Default location of the password file on Windows is %ORACLE_HOME%\database\ and name is pwd%ORACLE_SID%.ora ….. On Linux $ORACLE_HOME\dbs and name orapw$ORACLE_SID. If you do not consider this you will get error:  ORA-01017

In addition to password file creation:

The initialization parameter remote_login_passwordfile must be set to the appropriate value:

  • None: means that Oracle will behave like that the password file doesn’t exist. Which will cause that no privileged connections will be allowed over nonsecure connections.
  • Exclusive: means that the password file will be used with the only one database instance. Setting this value gives the ability to grant/revoke sysdba or sysoper privileges to/from users(Note that granting or revoking privs. causes the password file modifications). It also enables you to change password of SYS user with ALTER USER command. It is the default value.
  • Shared: It is used by multiple DBs, which are running on the same server, or with RAC. Setting this value prohibits you from changing the password file. If you try to change the password file generates the error. To make available to modify this file, first change this parameter to exclusive, modify file and change it back to the share value.

To see how many users are added to the password file run the following command:

select *
from v$pwfile_users

–My output is the following

SYS      |TRUE   |TRUE

Deleting password file

To remove the password file, first delete it and then set the initialization parameter remote_login_passwordfile to none. After that, the users that can authenticate by the operating system will be able to connect  to the database as sysdba/sysoper.