RE: Can't see index from package

Jonathan,  

Another thing you could try is setting invoker rights on the procedure being created as this enables the procedure use privileges granted to roles (something to do with process using the invokers namespace and privileges rather than having to know about the procedure owners privileges/namespace as well as the invoker).  

Anyhow as an example;  

SQL> show user

USER is "IAN"  

SQL> create table testtab(x date);  

Table created.  

SQL> create role testrole;  

Role created.  

SQL> grant select on testtab to testrole;  

Grant succeeded.  

SQL> grant testrole to dummy;  

Grant succeeded.  

Now connect to dummy  

  1 select table_name from all_tables

  2* where owner = 'IAN';  

TABLE_NAME

TESTTAB   SQL> create or replace procedure show_tabs as

  2 tab_count number;

  3 begin

  4 select count(*)

  5 into tab_count

  6 from all_tables

  7 where owner = 'IAN';

  8 dbms_output.put_line(tab_count);

  9 end;

 10 /  

Procedure created.  

SQL> exec show_tabs;

0 -- as expected  

PL/SQL procedure successfully completed.  

SQL> create or replace procedure show_tabs authid current_user as

  2 tab_count number;

  3 begin

  4 select count(*)

  5 into tab_count

  6 from all_tables

  7 where owner = 'IAN';

  8 dbms_output.put_line(tab_count);

  9 end;

 10 /  

Procedure created.  

SQL> exec show_tabs

1 -- you can see the table now  

PL/SQL procedure successfully completed.    

Hope this helps,  

Cheers,  

Ian

.

This email is only intended for the person to whom it is addressed and may contain confidential information. If you have received this email in error, please notify the sender and delete this email which must not be copied, distributed or disclosed to any other person.

Unless stated otherwise, the contents of this email are personal to the writer and do not represent the official view of Ordnance Survey. Nor can any contract be formed on Ordnance Survey's behalf via email. We reserve the right to monitor emails and attachments without prior notice.

Thank you for your cooperation.

Ordnance Survey
Romsey Road
Southampton SO16 4GU
Tel: 023 8079 2000
http://www.ordnancesurvey.co.uk

--
http://www.freelists.org/webpage/oracle-l