Solved

Query on all_source table

Posted on 2004-08-05
2
1,776 Views
Last Modified: 2008-01-09
Hi gurus,

I wrote a procedure, show_sources, that show all sources of users. The procedure is stored on user having DBA privilege. I found that the returned result from the procedure shows only sources of object granted execute to public. But if I take and run a SQL statement in the procedure via sqlplus, its result will shows all objects' source.

I would like to know why the results are different and how can I do the same returned result of sqlplus.

Thank U before hand,
P.Peach

Information
OS: Solaris SPARC 2.6
Oracle: 8.1.6
Procedure Code: CREATE OR REPLACE PROCEDURE show_sources is
cursor SHOW_ALL is
SELECT distinct owner, name
FROM all_source
WHERE owner in ('A', 'B');
BEGIN
dbms_output.enable(100000);
For rec in show_all Loop
dbms_output.put_line(rec.owner || '.' || rec.name );
End Loop;
END;
Procedure Return:
A.A2
B.B1
B.B4

SQLPlus:
sqlplus> SELECT distinct owner, name
2 FROM all_source
3 WHERE owner in ('A', 'B');

OWNER NAME
------------------------------ ------------------------------
A A1
A A2
A A3
B B1
B B2
B B3
B B4
B B5
B B6
0
Comment
Question by:peach_partner
2 Comments
 
LVL 2

Accepted Solution

by:
shuminzhou earned 125 total points
ID: 11724341
Try adding AUTHID CURRENT_USER :

CREATE OR REPLACE PROCEDURE show_sources AUTHID CURRENT_USER is
......
0
 
LVL 4

Expert Comment

by:oragenius
ID: 11724693
grant given to a user is not accessible thru stored procedure as it holds potential threat of access violation

for eg. user 'A' creates a table / procedure and want to give access to user 'B' only and if procedure / function can access object of 'A' in procedure / function then 'B' can create such procedure and will give access to his procedure to other users which 'A' may not like.

so purposly its not allowed.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
PL/SQL Two changes 7 41
Need SQL Query to Find Foreign-keys Without Indexed Columns 4 33
update statement in oracle 9 41
return value based on substr 10 51
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question