?
Solved

Query on all_source table

Posted on 2004-08-05
2
Medium Priority
?
1,787 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 2

Accepted Solution

by:
shuminzhou earned 500 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Via a live example, show how to take different types of Oracle backups using RMAN.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

762 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