| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: More clarification of the problem...
The owner of the procedure must have access to the database object in question via a role (eg: the owner of the procedure can SEE the object in SQL*Plus but only because some role they have gives them access).
You cannot create stored objects (views/procedures) that reference objects you can see because of a role. The data dictionary views (all_*, user_*) go out of their way to ensure that when you query them, they can see ONLY what you are allowed to access in the given environment.
Try the following in sql*plus:
SQL> select * from session_roles;
ROLE
COUNT(*)
4082
SQL> set role none;
Role set.
SQL> select * from session_roles;
no rows selected
SQL> select count(*) from all_objects;
COUNT(*)
1028
SQL> As you can see, when you "set role none" (turn off all enabled roles), you see significantly less objects. In my case, a lot less since the DBA role has select any table. Once I disable DBA, most of the tables in my database are no longer visible.
When you execute a query inside of a stored procedure, no roles are ever enabled. Therefore, when you execute a query in a stored procedure, it will return the results you see when you issue "set role none" in sql*plus.
If you need your procedure to see that object, then the owner of the procedure will need select on that object granted directly to them.
On Wed, 06 Nov 1996 18:36:50 +0100, Zoaib Saifuddin <Zoaib.Saifuddin_at_ubs.com> wrote:
>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.
Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com
 |
 |