Oracle19c创建⽤户、授权实践
数据据库、⽤户、CDB与PDB之间的关系
基本概念：
Multitenant Environment：多租户环境
CDB（Container Databa）：数据库容器
PD（Pluggable Databa）：可插拔数据库
CDB与PDB关系图
COMMON USERS(普通⽤户)：经常建⽴在CDB层，⽤户名以C##或c##开头；
LOCAL USERS(本地⽤户)：仅建⽴在PDB层，建⽴的时候得指定CONTAINER。
在oracle 12c中，使⽤了⼀个container(容器)的概念，让我们先看看官⽅的对它的介绍，为了保留最原始的意思，这⾥引⽤英⽂⽽不翻译了。
The data dictionary in each container in a CDB is parate, and the current container is the container who data dictionary is ud for name resolution and for privilege authorization. The current container can be the root or a PDB.
Each ssion has exactly one current container at any point in time, but it is possible for a ssion to switch from one container to another.
Each container has a unique ID and name in a CDB. You can u 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> lect * from v$version;
在CDB上创建⽤户
1.PDB操作
查看所有pdb
3 ORCLPDB1 READ WRITE NO
查看当前pdb
SQL> lect sys_context ('USERENV', 'CON_NAME') from dual;
SYS_CONTEXT('USERENV','CON_NAME')
--------------------------------------------------------------------------------
CDB$ROOT
2. 新建⽤户
SQL> create ur C##test identified by testpass;
Ur created.
⽤户 C##test
密码 testpass
3. 配置权限
SQL> grant dba,connect,resource,create view to C##test;
SQL> grant create ssion to C##test;
SQL> grant lect any table to C##test;
SQL> grant update any table to C##test;
SQL> grant inrt any table to C##test;
SQL> grant delete any table to C##test;
4. 登录测试
[oracle@localhost bin]$ sqlplus C##test/testpass
SQL*Plus: Relea 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 rerved.
Last Successful login time: Mon Oct 19 2020 15:16:35 +08:00
Connected to:
Oracle Databa 19c Enterpri Edition Relea 19.0.0.0.0 - Production Version 19.3.0.0.0
SQL>
5. 删除⽤户
SQL> drop ur C##test cascade;
Ur dropped.
在PDB上创建⽤户
1. PDB操作
查看所有pdb
3 ORCLPDB1 READ WRITE NO
查看当前pdb
SQL> lect sys_context ('USERENV', 'CON_NAME') from dual;
SYS_CONTEXT('USERENV','CON_NAME')
--------------------------------------------------------------------------------CDB$ROOT
2. 进⼊PDB
SQL> alter ssion t container= ORCLPDB1;
Session altered.
3. 新建⽤户
SQL> create ur test2 identified by test2pass;
Ur created.
如果显⽰数据库未打开，操作见下。否则，忽略。
SQL> create ur test2 identified by test2pass;
create ur test2 identified by test2pass
*
ERROR at line 1:
ORA-01109: databa not open
SQL> startup;
Pluggable Databa opened.
⽤户名 test2
密码 test2pass
4. 配置权限
SQL> grant dba,connect,resource,create view to test2;
Grant succeeded.
SQL> grant lect any table to test2;
Grant succeeded.
SQL> grant update any table to test2;
Grant succeeded.
SQL> grant inrt any table to test2;
Grant succeeded.
SQL> grant delete any table to test2;
Grant succeeded.
SQL> grant create ssion to test2;
Grant succeeded.
5. 配置TNS
如果不配置登录失败，会报⽤户名密码错误，默认的登录是在CDB上的⽤户。需要修改a⽂件
linux 路径是在oracle安装路径network/admin下
我是⽤RPM安装，路径为：/opt/oracle/product/19c/dbhome_1/network/admin/
编辑 a
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 (a中新建PDB的名字(即ORCLPDB1)，不是SERVICE_NAME)举例：
sqlplus test2/test2pass@ORCLPDB1
说明
在使⽤navicat操作oracle时，有时需要sysdba权限。所以顺便给⽤户分配这个⾓⾊。grant sysdba to test2;
