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.
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.