Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question.

Asked: December 01, 2020 - 5:47 am UTC

Last updated: December 01, 2020 - 10:05 am UTC

Version: 12c

Viewed 1000+ times

You Asked

I have a query regarding username and it's roles.

1) When I try to connect pdb user user1@mypdb1 using sysdba and I fire user_tables then I do not get all my tables.
 but using the same connection i use dba_tables and i filter user1 it shows only my tables.
 
2) same user user1@mypdb1 connect using normal and i fire user_tables work fine like show only those tables which i created.
dba_tables not work means dba_* view not accessible.

So what is wrong with me? Can you help me?

Please check below my output.

***************************************************************************************************
Environment
---------------------------------------------------------------------------------------------------

Using plsql developer Tool

Oracle Database :- Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit
***************************************************************************************************

select * from dba_users where username='USER1' order by 1;
Output >

Column Name  Data
USERNAME :- USER1
USER_ID :- 107
PASSWORD :- 
ACCOUNT_STATUS :- OPEN
LOCK_DATE :- 
EXPIRY_DATE :- 16-03-2021 15:02
DEFAULT_TABLESPACE :- USERS
TEMPORARY_TABLESPACE :- TEMP
LOCAL_TEMP_TABLESPACE :- TEMP
CREATED :- 28-11-2018 12:59
PROFILE :- DEFAULT
INITIAL_RSRC_CONSUMER_GROUP :- DEFAULT_CONSUMER_GROUP
EXTERNAL_NAME :- 
PASSWORD_VERSIONS :- 10G 11G 12C 
EDITIONS_ENABLED :- N
AUTHENTICATION_TYPE :- PASSWORD
PROXY_ONLY_CONNECT :- N
COMMON :- NO
LAST_LOGIN :- 28-NOV-20 12.18.39.000000000 PM +05:30
ORACLE_MAINTAINED :- N
INHERITED :- NO
DEFAULT_COLLATION :- USING_NLS_COMP
IMPLICIT :- NO
ALL_SHARD :- NO

***********************************
Connection string user1@mypdb1 and connect as sysdba
***********************************
sql> select * from user_tables;
I got 1484 rows from table but i do not get my right table here.

***********************************
Connection string user1@mypdb1 and connect as sysdba
***********************************
sql> select * from dba_tables  where owner='USER1';
I got my tables only. that is right.

***********************************
Connection string user1@mypdb1 and connect as normal
***********************************

sql> select * from user_tables;


I got my tables only. that is right.

and Chris said...

SYSDBA = special.

When you connect as this, you're really connecting as sys:

SQL> grant create session, sysdba to u
  2    identified by u;

Grant succeeded.

SQL>
SQL> conn u/u@pdb1
Connected.
SQL>
SQL> sho user
USER is "SYS"
SQL>
SQL> conn u/u@pdb1
Connected.
SQL>
SQL> sho user
USER is "U"


So when you've connected as sysdba, you're really SYS. So the tables for USER1 won't appear user_* views in because that's a different user.

Only connect as sysdba for admin tasks that require it. For standard database access connect normally.

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Security

All of the vital components for a secure database are covered in the Security guide.