RE: Can't see index from package
- From: "Ian Cary \(C\)" <Ian.Cary@xxxxxxxxxxxxxxxxxxxx>
- To: <knightjck_work@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>, <Mark.Bobak@xxxxxxxxxxxxxxx>
- Date: Mon, 18 Sep 2006 14:20:03 +0100
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
Other related posts:
