Skip to Main Content

Breadcrumb

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 100+ 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 we 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.

More to Explore

Security

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