Grants on privileges in other schemas

Most people have discovered that even as SYS or SYSTEM, you cannot grant privileges on objects owned in another schema. For example, if you try

SQL> connect system/manager
Connected.

SQL> grant select on scott.emp to john;

To resolve this problem, a couple of options are

• Tweak the password
  
  
  1. First select the encrypted value of the user's password from DBA_USERS
  2. Then the alter the user to something you know
  3. Connect as the user
  4. Alter the password back to the original value using
     
     alter user SCOTT identified by values 'xxx'
     where xxx is the encrypted value
     
     
  5. Perform the grant
  
  
• Create a procedure to do the grant. Since procedures are run with the privileges of the owner, creating a procedure in the schema owning the object will be able to perform the grant
  
  
  1. create or replace
     procedure scott.do_grant(p_grant varchar2) is
     begin
     execute immediate p_grant;
     end;
     
     
  2. SQL> exec scott.do_grant('grant select on emp to john');
  
  Note: I had a few crashes with 8.1.5 using 'execute immediate' in this way, which can be solved resorting to dbms_sql.