Do not understand permssions on SAPDAT

[Former Member]

I have an account with select_catalog_role in SAP BI 7.0 Oracle Database.

I found that most of the SAP tables are in SAPDAT schema.

In sqlplus., When I say:

Desc SAPDAT.user_tables

It shows me definition of user_tables

But when I say

Select * from SAPDAT.user_tables

I get Oracle error:

select * from SAPDAT.user_tables

*

ERROR at line 1:

ORA-00942: table or view does not exist

Why? I thought if I have select_catalog_role I should be able to view anything

[stefan_koehler]

Hello Zigzagdna,

that has nothing to do with the permission that your user have. Just check the definitions of the object USER_TABLES

SQL> select owner, object_type from dba_objects where object_name = 'USER_TABLES';
OWNER                          OBJECT_TYPE
------------------------------ -------------------
SYS                            VIEW
PUBLIC                         SYNONYM

SQL> select owner,  table_owner, table_name from dba_synonyms where synonym_name = 'USER_TABLES';
OWNER                          TABLE_OWNER                    TABLE_NAME
------------------------------ ------------------------------ ------------------------------
PUBLIC                         SYS                            USER_TABLES

SQL> select DBMS_METADATA.GET_DDL('VIEW', 'USER_TABLES', 'SYS') from dual;
CREATE OR REPLACE FORCE VIEW "SYS"."USER_TABLES" ("TABLE_NAME", "TABLESPACE_NAME", "CLUSTER_NAME", 
"IOT_NAME", "STATUS", "PCT_FREE", "PCT_USED", "INI_TRANS", "MAX_TRANS",
....
....
....
where o.owner# = userenv('SCHEMAID')
  and o.obj# = t.obj#
  and bitand(t.property, 1) = 0
....
....

So as you can see the object "USER_TABLES" itself is just a public synonym for the view USER_TABLES in the SYS schema. The view itself is defined with a WHERE clause on "userenv('SCHEMAID')" - that's the way how oracle determines the schema for the user corresponding tables. So you can not use the view USER_TABLES to get table information for a different user.

For more information about the object name resolution - please read the documentation:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/depend.htm#CNCPT319

Regards

Stefan