Privileges of Tables and Stored Proc.

Dear all,

I got some problems on setting the privileges of tables access. I would like to create a login with rights to access stored procedures but no right read tables.

I have created a package like this:

CREATE OR REPLACE PACKAGE BODY EMP_PKG
AS

FUNCTION emp_query (
i_empno IN sa.emp.emp_no%TYPE)
return cursorType
IS

   c_result cursorType;
BEGIN
   open c_result for
   select emp_no, emp_name from sa.emp
   where emp_no = i_empno;

   return c_result;
END;
END; However, when I call the stored procedures using java with syntax like: "begin :1 := sa.EMP_PKG.emp_query (:2) ; end;"

SQL Error returns that the login do not have such tables or views.

The problem does not occur when I grant 'select' right to that login.

Can I write a store procedure like this? If not, how can I do it by the other method?

Thanks for help.

--
Jasmine Cheung

jasminec_at_my-deja.com


Sent via Deja.com http://www.deja.com/
Before you buy.