Retrieve CLOB from stored procedure without granting SELECT on table

From: James Juran <juranj_at_mail.nih.gov>
Date: Fri, 11 Jan 2002 10:33:45 -0500
Message-ID: <3C3F05D9.5224176B@mail.nih.gov>

I'm trying to run a stored procedure that has a CLOB as an OUT parameter. If the user running the stored procedure has SELECT privileges on the table containing the data, it works fine. If the user running the procedure does not have SELECT privileges on the table, I get:

ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_LOB", line 512

This is with Oracle8i Enterprise Edition Release 8.1.6.0.0.

If I use an OUT parameter of a type other than CLOB, the stored procedure works as intended, even if the user running it does not have SELECT privileges on the source table. Is this the intended behavior for CLOBs?

I've created the following test program that shows this behavior:

set echo on
set serveroutput on

DROP TABLE t;
CREATE TABLE t (

    i	NUMBER PRIMARY KEY,
    c	CLOB);

CREATE OR REPLACE PROCEDURE ClobTest(p_clob OUT CLOB)   IS
    BEGIN

	SELECT c
	INTO p_clob
	FROM t
	WHERE i = 1;

    END ClobTest;
/
  

INSERT INTO t
VALUES (1, 'This is some lob text');

DECLARE
    c CLOB;
BEGIN
    sc.ClobTest(c);
    dbms_output.put_line(dbms_lob.GetLength(c)); END;
/

GRANT EXECUTE ON ClobTest TO pma_web_user;

CONNECT someuser/somepass_at_db;

DECLARE
    c CLOB;
BEGIN
    sc.ClobTest(c);
    dbms_output.put_line(dbms_lob.GetLength(c)); END;
/
Received on Fri Jan 11 2002 - 09:33:45 CST