asked on

Oracle 11.2.0.1.0: permission problem by using dba_tables

hello all,

i have an oracle database 'testdb' and a java application which using this database.

when any database user connect to this database by this java application, it should be detected at first, if this database exisits and does not have any tables. i.e, the database should be empty.

following statement works if the database user has whole permission on database.

select count(*) from dba_tables where owner='testdb'

the question is:

if database user doesn't have permission on "dba_tables", this statement can not be executed.
are there any way to let such database use to find out, if this database exisits and does not have any tables?

thanks,
wantime

Christoffer Swanström

Any user can query all_tables, which will show only the tables accessible to that user (dba_tables would show all tables in the system).

SOLUTION

for_yan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

gplana

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

OP_Zaharin

"if database user doesn't have permission on "dba_tables", this statement can not be executed.
are there any way to let such database use to find out, if this database exisits and does not have any tables"

- if the user you are using does not have privileged to query on dba_tables use, you can query to user_tables to list all the table within that user itself. that user however, are now allowed to see.query to other user tables.

wantime

ASKER

thanks, perhaps i should explain my question more clearly:

i have a database called 'testdb', and there is a database user 'tester' who doesn't have permission on database 'testdb' and 'dba_tables'.

this database user 'tester' would like to find out, if database 'testdb' exists and is empty, which oracle sql statement should he use?

ps.
if i login as 'tester' and try to execute
select count(*) from dba_tables where owner='testdb'

then i get Error message ORA-00942: table or view does not exist

SOLUTION

Wasim Akram Shaik

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Christoffer Swanström

You could log in as a user that has access to dba_tables and the create a function that returns the number of tables in TESTDB (or -1 if TESTDB does not exist). Then grant execute on that function to the user TESTER.

SOLUTION

Christoffer Swanström

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

OP_Zaharin

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

CEHJ

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Christoffer Swanström

As far as I understand what wantime refers to as a database is really a schema in Oracle. If the database did not exist, no user can connect to check if the database exists and has tables... Using the proper terminology would make the original question clearer though.

SOLUTION

awking00

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

slightwv (䄆 Netminder)

>>User needs SELECT ANY DICTIONARY privilege to access the DBA_xxx views.

You sure about that?

C:\>sqlplus /nolog

SQL*Plus: Release 10.2.0.3.0 - Production on Wed Jan 11 14:35:31 2012

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

SQL> conn / as sysdba
Connected.
SQL> drop user bob cascade;

User dropped.

SQL> create user bob identified by bob;

User created.

SQL> grant create session to bob;

Grant succeeded.

SQL> grant select on dba_tables to bob;

Grant succeeded.

SQL> conn bob/bob
Connected.
SQL> select * from DBA_TABLES where rownum=1;

OWNER                          TABLE_NAME
------------------------------ ------------------------------
TABLESPACE_NAME                CLUSTER_NAME
------------------------------ ------------------------------
IOT_NAME                       STATUS     PCT_FREE   PCT_USED  INI_TRANS
------------------------------ -------- ---------- ---------- ----------
 MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE
---------- -------------- ----------- ----------- ----------- ------------
 FREELISTS FREELIST_GROUPS LOG B   NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE
---------- --------------- --- - ---------- ---------- ------------ ----------
 CHAIN_CNT AVG_ROW_LEN AVG_SPACE_FREELIST_BLOCKS NUM_FREELIST_BLOCKS DEGREE
---------- ----------- ------------------------- ------------------- ----------
INSTANCES  CACHE TABLE_LO SAMPLE_SIZE LAST_ANAL PAR IOT_TYPE     T S NES BUFFER

---------- ----- -------- ----------- --------- --- ------------ - - --- ------

ROW_MOVE GLO USE DURATION        SKIP_COR MON CLUSTER_OWNER
-------- --- --- --------------- -------- --- ------------------------------
DEPENDEN COMPRESS DRO
-------- -------- ---
SYS                            ICOL$

Open in new window

wantime

ASKER

thanks for all your suggestion.

i prefer to use all_tables to resolve the problem, and it works actually.