More clarification of the problem...

Zoaib Saifuddin wrote:
>
> Hello everybody,
> We are using ORACLE7.0;
> I'm trying to read NULLABLE field from ALL_TAB_COLUMNS system table with OWNER as some other user who has
> GRANTed SELECT on his object(Table). Using SQLPLUS, we are able to read records from ALL_TAB_COLUMNS for that
> table with owner as the other user.
> The other user also has a stored procedure, which reads records from ALL_TAB_COLUMNS with owner as its own
> oracle USERID and we are given EXECUTE permission on this procedure.
> Now when we execute this procedure, we are not able to read records from ALL_TAB_COLUMNS for that user and the
> table eventhough the query used for selection in the stored procedure gives positive results in SQLPLUS.
> Anyone has encountered this problem?
> If anybody has any comments or suggestions, i shall be pleased to hear from you.
> My e-mail addr: Zoaib.Saifuddin_at_ubs.com
> Thanks and regards,
> - Zoaib.

The problem i have encountered is no rows are retrieved by the query in the stored procedure, where as the same query will give positive results in SQLPLUS. For your information, i would like to include the query used in stored procedure ( say, 'SP_USER_1') here:

(query1)                  SELECT atc.nullable
		FROM   all_tab_columns atc
		WHERE  atc.owner          =    'USER_1'
		      AND  atc.table_name     =    'TABLE_XYZ'
		      AND  atc.column_name    =    'COL_ABC_IN_TABLE_XYZ';

ALL_TAB_COLUMNS is a PUBLIC synonym for the VIEW owned by SYS.

Scenario:
USER_1 has granted following permissions to USER_2: EXECUTE on SP_USER_1,
SELECT on TABLE_XYZ.

USER_2 can view/select records using the above query(query1) in SQLPLUS, but when executes the stored procedure SP_USER_1, the same query in the stored does not retrieve any rows. Awaiting reply.
Thanks and regards. Received on Wed Nov 06 1996 - 00:00:00 CST