oracle 12C user调测

概述：群里有哥们求助说，在oracle12c中的pdb模式下，明明我的用户和密码都是正确的，为啥老提示我密码不对无法正确连接呢？

小知识点：其实在oracle12C中用户分为公共用户和私有用户；具体的差不请阅读官方文档https://docs.oracle.com/en/database/oracle/oracle-database/12.2/dbseg/managing-security-for-oracle-database-users.html#GUID-8C779885-01F2-48E0-9612-E33508885B19

我们这只是做个小的测试上面的错误提示及解决办法：

实验:

SYS@AS SYSDBA@PRODCDB> create user cube1 identified by cube1 container=all;
User created.
SYS@AS SYSDBA@PRODCDB> alter session set container=PDBPROD1;
Session altered.
SYS@AS SYSDBA@PRODCDB> create user cube2 identified by cube2 container=current;

User created.

SYS@AS SYSDBA@PRODCDB> grant create session to cube2 container=current;

Grant succeeded.
SYS@AS SYSDBA@PRODCDB> conn  / as sysdba
Connected.
SYS@AS SYSDBA@PRODCDB> grant create session to cube1 container=all;

Grant succeeded.

查看用户状态，是否为公共用户

SYS@AS SYSDBA@PRODCDB> select username,common,con_id from cdb_users where  username like 'CUBE%'  ;
USERNA COM     CON_ID
------ --- ----------
CUBE1  YES    1
CUBE1  YES    6
CUBE1  YES    4
CUBE1  YES    3
CUBE2  NO    3
CUBE1  YES    7
CUBE1  YES    5

7 rows selected.

SYS@AS SYSDBA@PRODCDB> grant dba to cube1;
Grant succeeded.
SYS@AS SYSDBA@PRODCDB> conn cube1/cube1 as sysdba
Connected.
SYS@AS SYSDBA@PRODCDB> show con_id

CON_ID
------------------------------

1

> conn cube1/cube1@pdbprod1    
Connected.
CUBE1@@pdbprod1> show con_id
CON_ID
------------------------------
3

SYS@AS SYSDBA@PRODCDB> conn cube2/cube2
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.

@@> 

SYS@AS SYSDBA@PRODCDB> alter session set container=pdbprod1;
Session altered.
SYS@AS SYSDBA@PRODCDB> conn cube1/cube1 as sysdba
Connected.
SYS@AS SYSDBA@PRODCDB> show con_id
CON_ID
------------------------------
1
SYS@AS SYSDBA@PRODCDB> show pdbs
    CON_ID CON_NAME  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED  READ ONLY  NO
3 PDBPROD1  READ WRITE NO
4 PDBPROD2  READ WRITE NO
5 PDBPROD3  READ WRITE NO
6 PDBPROD4  READ WRITE NO

7 PDBPROD5  READ WRITE NO

SYS@AS SYSDBA@PRODCDB> alter session set container=pdbprod1;
Session altered.
SYS@AS SYSDBA@PRODCDB> conn cube2/cube2
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
@@> conn / as sysdba
Connected.
SYS@AS SYSDBA@PRODCDB> alter session set container=pdbprod1;
Session altered.
SYS@AS SYSDBA@PRODCDB> conn cube2/cube2@pdbprod1

Connected.

SYS@AS SYSDBA@PRODCDB> alter session set container=pdbprod1;
Session altered.
SYS@AS SYSDBA@PRODCDB> conn cube2/cube2@host01:1521/PDBPROD1.example.com
Connected.

CUBE2@@host01:1521/PDBPROD1.example.com> 

总结：当用户为公共用户是不管公共用户在哪个pdb中不指定net service name连接符时公共用户cube1都是连接到cdb中，当指定net service name时进入指定的pdb数据库中；如果是私有用户连接数据库时请指定net service name连接或者使用easyconnect；