Re: Privileges of Tables and Stored Proc.

I'm not sure about your environment and your db users. Then the following could be a mistake.

Let SA the owner of table emp
Let JV the user that you connect from java

The procedure/package EMP_PKG should be created by SA SA should grant EXECUTE privilege on EMP_PKG to JV JV doesn't need SELECT privilege on SA.EMP

--
be happy

Eugenio
remove _nospam from reply address

Opinions are mine and do not necessarily reflect those of my company

=======================================================
Jasmine Cheung wrote in message <8icnj4$8vo$1_at_nnrp1.deja.com>...


>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.