Does your client app have access to this stored procedure?
I'd suggest setting up a public synonym for the stored procedure and
granting execute to your user.
In this way your user can only execute the SP and has no view of other
tables.
--
Jim Harrison
Colway Software and Design
www.colway.net
Jasmine Cheung <jasminec_at_my-deja.com> wrote in message
news: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.
Received on Sat Jun 17 2000 - 00:00:00 CDT
