Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Query on all_source table

Posted on 2004-08-05
2
Medium Priority
?
1,813 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 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 Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

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…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses
Course of the Month15 days, 13 hours left to enroll

580 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