Oracle 19c 创建用户、授权实践

数据据库、用户、CDB与PDB之间的关系

基本概念：

Multitenant Environment：多租户环境

CDB（Container Database）：数据库容器

PD（Pluggable Database）：可插拔数据库

CDB与PDB关系图

 COMMON USERS(普通用户)：经常建立在CDB层，用户名以C##或c##开头；

 LOCAL USERS(本地用户)：仅建立在PDB层，建立的时候得指定CONTAINER。

在oracle 12c中，使用了一个container(容器)的概念，让我们先看看官方的对它的介绍，为了保留最原始的意思，这里引用英文而不翻译了。

The data dictionary in each container in a CDB is separate, and the current container is the container whose data dictionary is used for name resolution and for privilege authorization. The current container can be the root or a PDB. Each session has exactly one current container at any point in time, but it is possible for a session to switch from one container to another.
Each container has a unique ID and name in a CDB. You can use the CON_ID and CON_NAME parameters in the USERENV namespace to determine the current container ID and name with the SYS_CONTEXT function.

查看更多，点击这里

DBA登录数据库

sqlplus / as sysdba

查看oracle版本

SQL> select * from v$version;

在CDB上创建用户

1.PDB操作

查看所有pdb

SQL> show pdbs;

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 ORCLPDB1			  READ WRITE NO

查看当前pdb

SQL> select sys_context ('USERENV', 'CON_NAME') from dual;

SYS_CONTEXT('USERENV','CON_NAME')
--------------------------------------------------------------------------------
CDB$ROOT

2. 新建用户

SQL> create user C##test identified by testpass;

User created.

用户 C##test
密码 testpass

3. 配置权限

SQL> grant dba,connect,resource,create view to C##test;
SQL> grant create session to C##test;
SQL> grant select any table to C##test;
SQL> grant update any table to C##test;
SQL> grant insert any table to C##test;
SQL>  grant delete any table to C##test;

4. 登录测试

[oracle@localhost bin]$  sqlplus C##test/testpass

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Oct 19 15:21:19 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Last Successful login time: Mon Oct 19 2020 15:16:35 +08:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL>

5. 删除用户

SQL> drop user C##test cascade;

User dropped.

在PDB上创建用户

1. PDB操作

查看所有pdb

SQL> show pdbs;

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 ORCLPDB1			  READ WRITE NO

查看当前pdb

SQL> select sys_context ('USERENV', 'CON_NAME') from dual;

SYS_CONTEXT('USERENV','CON_NAME')
--------------------------------------------------------------------------------
CDB$ROOT

2. 进入PDB

SQL> alter session set container= ORCLPDB1;

Session altered.

3. 新建用户

SQL> create user test2 identified by test2pass;

User created.

如果显示数据库未打开，操作见下。否则，忽略。

SQL> create user test2 identified by test2pass;
create user test2 identified by test2pass
*
ERROR at line 1:
ORA-01109: database not open


SQL> startup;
Pluggable Database opened.

用户名 test2
密码 test2pass

4. 配置权限

SQL> grant dba,connect,resource,create view to test2;

Grant succeeded.

SQL> grant select any table to test2;

Grant succeeded.

SQL> grant update any table to test2;

Grant succeeded.

SQL> grant insert any table to test2;

Grant succeeded.

SQL>  grant delete any table to test2;

Grant succeeded.

SQL> grant create session to test2;

Grant succeeded.

5. 配置TNS

如果不配置登录失败，会报用户名密码错误，默认的登录是在CDB上的用户。需要修改tnsnames.ora文件
linux 路径是在oracle安装路径network/admin下
我是用RPM安装，路径为：/opt/oracle/product/19c/dbhome_1/network/admin/
编辑 vi tnsnames.ora

ORCLCDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCLCDB)
    )
  )
#----------添加区间 start------------------
#不要再动其他地方
# ORCLPDB1 这个名字可以自定义
ORCLPDB1 =  
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCLPDB1)
      # SERVICE_NAME 这个就是你进入的pdb名称
    )
  )
#------------添加区间 end--------------
LISTENER_ORCLCDB =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

重启监听。
lsnrctl stop
lsnrctl start

6. 登录

使用登录方式 sqlplus 用户名/密码@PDB (tnsnames.ora中新建PDB的名字(即ORCLPDB1),不是SERVICE_NAME)
举例：
sqlplus test2/test2pass@ORCLPDB1

说明

在使用navicat操作oracle时，有时需要sysdba权限。所以顺便给用户分配这个角色。
grant sysdba to test2;

检查工具是否配置连接动态库oci.dll

本文实践步骤，参考自：
https://blog.csdn.net/ly_6118/article/details/109162165

关于表空间，OMF等，参考：
https://www.cnblogs.com/flycatnet/articles/5056080.html
https://www.cnblogs.com/javasl/p/14852114.html