Grant issues.

From: mehul <mzaveri_at_eppco.co.ae>
Date: Wed, 16 Jun 1999 12:10:17 +0400
Message-ID: <7k8iid$b7u1_at_news.emirates.net.ae>



Hi friends,
Can some body help??

Case :-

  Platform :- Oracle Enterprise 8.0.4.2.1 on Aix 4.3.

  Role dvl for developers,
  granted all possible object level privileges (select,insert,update,delete,alter)
  to dvl role for tables of other schemas.   granted create table, create procedure rights to allow develper to create thier

  own tables and/or procedures etc.

  connect as developer (This user has default role of dvl)   create a procedure p1 trying to acces other_schema.table_name;   While compiling p1, error message appears about lack of privileges.

  Now the problem is how do you give object level grants of other schemas to   dvl role? so that p1 procedure can be compiled w/o. errors.

  Although the following works

       connect as other_schema
       grant select on table_name to developer with grant option
       grant insert on table_name to developer with grant option
       grant update  on table_name to developer with grant option
       grant delete on table_name to developer with grant option
  We do not want to give any grants directly to developer user (As far as possible).
  The grants should be given to dvl role who in turns has been granted dvl role.

  I am not able to understand, why database procedure needs this special treatment?
  What other alternates can be deviced to manage such granting issues?

 Any solutions/ suggestions/ advices would be highly appreciated. Also if I can be pointed to some white paper which explains proper management of grant related issues?

 Thanks in advance.

Kind regards,
Mehul Zaveri
Oracle DBA
Email:mzaveri_at_eppco.co.ae
Emirates Petroleum Products Company.
Dir:+9714-3031557,Brd:+9714-372131
Dubai. Received on Wed Jun 16 1999 - 10:10:17 CEST

Original text of this message