当前位置： 首页 > article >正文

Oracle 创建本地用户，授予权限，创建表并插入数据

article 2025/1/7 13:58:29

目录

  • 一. 用户的种类
  • 二. 切换session为PDB
  • 三. 创建用户并授予权限
  • 四. 创建表空间
  • 五. 为用户分配默认表空间并指定表空间配额
  • 六. 通过创建的用户进行登录
  • 七. 创建脚本，简化登录
  • 八. 查看用户信息
  • 九. 创建表，并插入数据
    • 9.1 查看当前用户的schema
    • 9.2 插入数据
    • 9.3 查看

一. 用户的种类

⏹在 Oracle 多租户架构中（从 Oracle 12c 开始），用户分为

  • 普通用户（Common User）
    • 用户的名称必须以 C## 开头，在CDB中创建
    • 适用于管理员用户，用户管理CDB下的多个PDB
  • 本地用户（Local User）
    • 用户的名称无需以 C## 开头，在PDB中创建
    • 更适合应用开发和租户管理
    • 我们平时开发时用的多是本地用户

⏹数据库刚被安装后，并没有本地用户，我们需要通过system用户登录Oracle之后，创建本地用户。

二. 切换session为PDB

⏹SHOW CON_NAME;：显示当前会话所连接的容器名称

  • 在包含 CDB（容器数据库）和 PDB（可插拔数据库）的环境中，显示会话所连接的容器的名称。
  • 容器可以是根容器（CDB$ROOT）、种子数据库（PDB$SEED）或某个具体的 PDB。
apluser@ubuntu24-01:~$ sqlplus system/oracle@192.168.118.137/XE

SQL*Plus: Release 21.0.0.0.0 - Production on Wed Jan 1 09:00:28 2025
Version 21.16.0.0.0

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

Last Successful login time: Tue Dec 31 2024 23:15:56 +09:00

Connected to:
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

SQL>
SQL> SHOW CON_NAME;

CON_NAME
------------------------------
CDB$ROOT
SQL>

⏹查看数据库中所有的PDB

  • SELECT PDB_ID, PDB_NAME, STATUS FROM DBA_PDBS;
  • SELECT NAME FROM V$PDBS;
-- 方式1
SQL> SELECT PDB_ID, PDB_NAME, STATUS FROM DBA_PDBS;

 PDB_ID PDB_NAME   STATUS
------- ---------- ----------
      3 XEPDB1     NORMAL
      2 PDB$SEED   NORMAL
      
-- 方式2
SQL> SELECT NAME FROM V$PDBS;

NAME
--------------
PDB$SEED
XEPDB1

⏹切换当前用户的session为PDB

  • ALTER SESSION SET CONTAINER = XEPDB1;
  • 💥我们只有在PDB的session中创建的才是PDB的用户，如果不切换session的话，创建的是CDB的用户。💥
-- 切换session到根容器
SQL> ALTER SESSION SET CONTAINER = CDB$ROOT;

Session altered.

-- 切换session到PDB
SQL> ALTER SESSION SET CONTAINER = XEPDB1;

Session altered.

-- 查看当前容器名称
SQL> SHOW CON_NAME;

CON_NAME
------------------------------
XEPDB1
SQL>

三. 创建用户并授予权限

⏹创建用户并指定密码

SQL> CREATE USER db_user IDENTIFIED BY oracle;

User created.

⏹为创建的用户赋予权限

-- 授予用户登录的权限
GRANT CREATE SESSION TO db_user;

-- 授予用户创建 表，视图，存储过程，序列对象的权限
GRANT CREATE TABLE, CREATE VIEW, CREATE PROCEDURE, CREATE SEQUENCE TO db_user;

⏹查看创建好的PDB用户

  • COMMON = 'NO'：滤出PDB的本地用户
  • COMMON = 'YES'：滤出CDB的普通用户
SQL> SELECT USER_ID, USERNAME, ACCOUNT_STATUS, COMMON, TO_CHAR(LAST_LOGIN, 'YYYY/MM/DD HH24:MI:SS') LAST_LOGIN FROM DBA_USERS WHERE COMMON = 'NO';

 USER_ID USERNAME   ACCOUNT_STATUS   COM  LAST_LOGIN
-------- ---------- ---------------- ---- -------------------
     108 PDBADMIN   OPEN             NO
     110 DB_USER    OPEN             NO

四. 创建表空间

  • 默认表空间：用户在不指定表空间的情况下创建的对象（如表）会存储到默认表空间。
  • 临时表空间：用户在执行排序操作（如 ORDER BY 或 GROUP BY）时会使用临时表空间。
  • SIZE 100M AUTOEXTEND ON：表空间大小为100M，当空间不足时，会自动增加
-- 创建默认表空间并指定表空间文件
CREATE TABLESPACE DB_STUDY_LOCAL_01 
DATAFILE 'C:/app/FengYeHong/product/21c/custom_table_space/local_db_study_01.dbf' SIZE 100M AUTOEXTEND ON;

-- 创建临时表空间并指定临时表空间文件
CREATE TEMPORARY TABLESPACE DB_STUDY_TMP_LOCAL_01 
TEMPFILE 'C:/app/FengYeHong/product/21c/custom_table_space/local_db_study_tmp_01.dbf' SIZE 100M AUTOEXTEND ON;

⏹查看创建的表空间

  • 查看数据库中所有的表空间与状态
SQL> SELECT TABLESPACE_NAME, CONTENTS, STATUS FROM DBA_TABLESPACES;

TABLESPACE_NAME                CONTENTS              STATUS
------------------------------ --------------------- ---------
SYSTEM                         PERMANENT             ONLINE
SYSAUX                         PERMANENT             ONLINE
UNDOTBS1                       UNDO                  ONLINE
TEMP                           TEMPORARY             ONLINE
USERS                          PERMANENT             ONLINE
DB_STUDY_LOCAL_01              PERMANENT             ONLINE
DB_STUDY_TMP_LOCAL_01          TEMPORARY             ONLINE

7 rows selected.
  • 查看临时表空间的路径，字节，状态
SQL> set markup csv on
SQL>
SQL> select name,bytes,status from v$tempfile;

"NAME","BYTES","STATUS"
"C:\APP\FENGYEHONG\PRODUCT\21C\ORADATA\XE\XEPDB1\TEMP01.DBF",36700160,"ONLINE"
"C:\APP\FENGYEHONG\PRODUCT\21C\CUSTOM_TABLE_SPACE\LOCAL_DB_STUDY_TMP_01.DBF",104857600,"ONLINE"
  • 查看默认表空间的路径，字节，状态
SQL> set markup csv on
SQL>
SQL> SELECT FILE_NAME, TABLESPACE_NAME, BYTES, STATUS FROM DBA_DATA_FILES;

"FILE_NAME","TABLESPACE_NAME","BYTES","STATUS"
"C:\APP\FENGYEHONG\PRODUCT\21C\ORADATA\XE\XEPDB1\SYSTEM01.DBF","SYSTEM",293601280,"AVAILABLE"
"C:\APP\FENGYEHONG\PRODUCT\21C\ORADATA\XE\XEPDB1\SYSAUX01.DBF","SYSAUX",408944640,"AVAILABLE"
"C:\APP\FENGYEHONG\PRODUCT\21C\ORADATA\XE\XEPDB1\UNDOTBS01.DBF","UNDOTBS1",104857600,"AVAILABLE"
"C:\APP\FENGYEHONG\PRODUCT\21C\ORADATA\XE\XEPDB1\USERS01.DBF","USERS",5242880,"AVAILABLE"
"C:\APP\FENGYEHONG\PRODUCT\21C\CUSTOM_TABLE_SPACE\LOCAL_DB_STUDY_01.DBF","DB_STUDY_LOCAL_01",104857600,"AVAILABLE"

⏹如果要删除表空间的话，可以使用下面的命令。

DROP TABLESPACE DB_STUDY_01 INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE DB_STUDY_TMP_01 INCLUDING CONTENTS AND DATAFILES;

五. 为用户分配默认表空间并指定表空间配额

⏹将用户和表空间关联起来，为用户分配默认表空间和临时表空间。

ALTER USER db_user 
DEFAULT TABLESPACE DB_STUDY_LOCAL_01 
TEMPORARY TABLESPACE DB_STUDY_TMP_LOCAL_01;

⏹确认用户和表空间的关联

SQL> SELECT username, default_tablespace, temporary_tablespace FROM dba_users WHERE username = 'DB_USER';

USERNAME     DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE
------------ ------------------------------ ------------------------------
DB_USER      DB_STUDY_LOCAL_01              DB_STUDY_TMP_LOCAL_01

SQL>

⏹用户有了默认表空间之后，并不意味着可以创建表并成功插入数据，还需要向用户分配表空间的配额，指定用户可以使用的存储配额。

  • DBA_TS_QUOTAS 是 Oracle 数据库中的一个数据字典视图，主要用于显示用户在各个表空间上的配额（Quota）信息。
  • 记录用户在某个表空间中被分配的存储空间限制（配额）。
  • 可以查看配额是有限制的（如 1GB）还是无限制的（UNLIMITED）。
  • 表空间配额示例
    • ALTER USER db_user QUOTA 50M ON DB_STUDY_LOCAL_01;：指定用户有50M的配额。
    • ALTER USER db_user QUOTA UNLIMITED ON DB_STUDY_LOCAL_01;：指定用户有无限的配额。
-- 注意，需要在PDB的session中执行
SQL> ALTER SESSION SET CONTAINER = XEPDB1;

Session altered.

SQL>
-- 在未分配配额之前，查询不到任何数据
SQL> SELECT * FROM DBA_TS_QUOTAS WHERE USERNAME = UPPER('db_user');

no rows selected

SQL>
-- 指定 db_user 用户对 DB_STUDY_LOCAL_01 表空间有无限的配额，可以随意使用
SQL> ALTER USER db_user QUOTA UNLIMITED ON DB_STUDY_LOCAL_01;

User altered.

SQL>
-- 分配完成之后，进一步查看
SQL> SELECT * FROM DBA_TS_QUOTAS WHERE USERNAME = UPPER('db_user');

"TABLESPACE_NAME","USERNAME","BYTES","MAX_BYTES","BLOCKS","MAX_BLOCKS","DROPPED"
"DB_STUDY_LOCAL_01","DB_USER",0,-1,0,-1,"NO"

六. 通过创建的用户进行登录

⏹我们创建的是本地用户，因此通过sqlplus命令进行登录的时候，必须明确的指出使用的是名称为XEPDB1的PDB

  • 在 Oracle 的多租户架构中，你需要确保连接到正确的 PDB（可插入数据库）而不是 CDB（容器数据库）。
  • 在连接数据库时，服务名称决定了连接的是哪个数据库实例。
  • CDB 是容器数据库，通常是你管理和创建多个 PDB 的地方。
  • 每个 PDB 是一个独立的数据库，可以有自己的用户、数据和表空间等。
apluser@ubuntu24-01:~$ sqlplus db_user/oracle@192.168.118.137/XEPDB1

SQL*Plus: Release 21.0.0.0.0 - Production on Wed Jan 1 10:11:43 2025
Version 21.16.0.0.0

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

Last Successful login time: Tue Dec 31 2024 22:58:55 +09:00

Connected to:
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

SQL> SHOW CON_NAME;

CON_NAME
------------------------------
XEPDB1
SQL>

⏹修改tnsnames.ora文件

  • 如果我们在多台服务器上有多个PDB的话，可以在sqlplus客户端安装的机器上，配置tnsnames.ora文件的内容，便于登录
apluser@ubuntu24-01:~$ cat $ORACLE_HOME/network/admin/tnsnames.ora
SERVICE_XE_CLIENT =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.118.137)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

SERVICE_XEPDB1_CLIENT =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.118.137)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XEPDB1)
    )
  )
apluser@ubuntu24-01:~$
  • 修改完成之后，就可以通过下面这种方式进行登录了
apluser@ubuntu24-01:~$ sqlplus db_user/oracle@SERVICE_XEPDB1_CLIENT

SQL*Plus: Release 21.0.0.0.0 - Production on Wed Jan 1 10:21:32 2025
Version 21.16.0.0.0

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

Last Successful login time: Wed Jan 01 2025 10:11:44 +09:00

Connected to:
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

SQL>

七. 创建脚本，简化登录

  • 每次登录oracle数据库，都要输入一长串的命令，很繁琐。可以创建一个脚本简化登录。
apluser@ubuntu24-01:~$ which oralce_db_connect
/home/apluser/bin/oralce_db_connect
apluser@ubuntu24-01:~$
apluser@ubuntu24-01:~$ ls -l /home/apluser/bin/oralce_db_connect
-rwxrwxr-x 1 apluser apluser 912 Jan  1 08:43 /home/apluser/bin/oralce_db_connect
apluser@ubuntu24-01:~$
apluser@ubuntu24-01:~$ cat /home/apluser/bin/oralce_db_connect
#!/bin/bash
# ################################################
# 简介:
# 动态连接 Oracle 数据库
#
# 完整方式连接数据库
# sqlplus db_user/oracle@192.168.118.137/XEPDB1
#
# 用法
# 1. oralce_db_connect
# 2. oralce_db_connect dba
# # ################################################

# 默认参数
USERNAME="db_user"
PASSWORD="oracle"
SERVICE="SERVICE_XEPDB1_CLIENT"
MSG="通过普通用户登录..."

# 如果传入参数为 "dba"，则使用 system 用户登录
if [ "$1" == "dba" ]; then
  USERNAME="system"
  PASSWORD="oracle"
  SERVICE="SERVICE_XE_CLIENT"
  MSG="通过dba用户登录..."
fi

# 构造连接oracle数据库的命令
connect_oracle_db_command="sqlplus ${USERNAME}/${PASSWORD}@${SERVICE}"

# 打印连接oracle数据库的命令
echo "${connect_oracle_db_command}"

# 打印提示消息
echo -e "\e[1;31m$MSG\e[0m"

# 连接oracle数据库
eval "${connect_oracle_db_command}"
  • 登录效果

在这里插入图片描述

八. 查看用户信息

⏹USER_USERS 表

  • 不需要DBA用户
  • 显示当前会话用户的基本信息，例如用户名、创建时间、默认表空间等。
  • 仅适用于当前登录用户，无法查看其它用户的信息。
SQL> SELECT USERNAME, USER_ID, ACCOUNT_STATUS FROM USER_USERS;

"USERNAME","USER_ID","ACCOUNT_STATUS"
"DB_USER",110,"OPEN"

⏹DBA_USERS表

  • 需要DBA用户
  • 显示数据库中所有用户的详细信息，包括用户名、账户状态、默认表空间、密码有效期等。
  • 数据库管理员（DBA）管理用户时使用，查看所有用户的账户状态，如是否锁定、密码是否过期等。
SQL> SELECT USER_ID, USERNAME, ACCOUNT_STATUS, COMMON, TO_CHAR(LAST_LOGIN, 'YYYY/MM/DD HH24:MI:SS') LAST_LOGIN FROM DBA_USERS WHERE USERNAME = 'DB_USER';

   USER_ID USERNAME          ACCOUNT_STATUS        COM LAST_LOGIN
---------- ----------------- --------------------- --- -------------------
       110 DB_USER           OPEN                  NO  2025/01/01 13:13:49

⏹ALL_USERS表

  • 不需要DBA用户
  • 显示数据库中所有用户的基本信息，但比 DBA_USERS 提供的信息少。
SQL> SELECT USER_ID, USERNAME, COMMON FROM ALL_USERS WHERE USERNAME = 'DB_USER';

"USER_ID","USERNAME","COMMON"
110,"DB_USER","NO"

⏹DBA_SYS_PRIVS表

  • 需要DBA用户
  • 查看用户所有的权限
SQL> SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = 'DB_USER';

GRANTEE PRIVILEGE                                ADM COM INH
------- ---------------------------------------- --- --- ---
DB_USER CREATE PROCEDURE                         NO  NO  NO
DB_USER CREATE SEQUENCE                          NO  NO  NO
DB_USER CREATE VIEW                              NO  NO  NO
DB_USER CREATE TABLE                             NO  NO  NO
DB_USER CREATE SESSION                           NO  NO  NO

九. 创建表，并插入数据

9.1 查看当前用户的schema

⏹一般来说，用户默认的schema和用户名相同，也可以通过下面这种方式进行查看。

SQL> SELECT SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') AS DEFAULT_SCHEMA FROM DUAL;

"DEFAULT_SCHEMA"
"DB_USER"

9.2 插入数据

⏹在创建表的时候，指定表空间，如果不指定的话，将会使用当前用户默认的表空间。

  • TABLESPACE DB_STUDY_LOCAL_01：指定表空间
  • DB_USER.PERSON_TABLE：指定schema
CREATE TABLE DB_USER.PERSON_TABLE (
    id NUMBER PRIMARY KEY,
    name VARCHAR2(50),
    age NUMBER,
    email VARCHAR2(100),
    created_date DATE
) TABLESPACE DB_STUDY_LOCAL_01;

⏹写一个脚本，自动向表中插入100条数据

BEGIN
    FOR i IN 1..100 LOOP
        INSERT INTO DB_USER.PERSON_TABLE (id, name, age, email, created_date)
        VALUES (
            i,
            'Name_' || i,
            TRUNC(DBMS_RANDOM.VALUE(18, 60)), -- 随机年龄
            'user' || i || '@example.com',
            SYSDATE - DBMS_RANDOM.VALUE(0, 365) -- 随机日期
        );
    END LOOP;
    COMMIT;
END;
/

9.3 查看

⏹本地用户查看表名所在的表空间 👉 USER_TABLES 

SQL> SELECT TABLE_NAME, TABLESPACE_NAME FROM USER_TABLES WHERE TABLE_NAME = 'PERSON_TABLE';

"TABLE_NAME","TABLESPACE_NAME"
"PERSON_TABLE","DB_STUDY_LOCAL_01"

⏹DBA用户查看表名所在的表空间 👉 DBA_TABLES

SQL> SELECT TABLE_NAME, OWNER, TABLESPACE_NAME FROM DBA_TABLES WHERE TABLE_NAME = 'PERSON_TABLE';

"TABLE_NAME","OWNER","TABLESPACE_NAME"
"PERSON_TABLE","DB_USER","DB_STUDY_LOCAL_01"

⏹可以看到，数据插入成功后可以被查询到。

在这里插入图片描述


http://www.kler.cn/a/468013.html

相关文章：

  • 067B-基于R语言平台Biomod2模型的物种分布建模与数据可视化-高阶课程【2025】
  • Gin框架中间件原理
  • 【C++】P5733 【深基6.例1】自动修正
  • 基于32单片机的智能语音家居
  • 《Opencv》基础操作详解（5）
  • Echart实现3D饼图示例
  • SQL中，# 和 $ 用于不同的占位符语法
  • 在 Python 中合并多个 Word 文档
  • spring防止重复点击，两种注解实现（AOP）
  • [开源]C++代码分享
  • 基于Spring Boot微信小程序的房产交易租赁服务平台
  • 慧集通iPaaS集成平台低代码训练-实践篇
  • 术业有专攻，遨游工业三防手机筑牢“危急特”通信防线
  • Ubuntu离线登入mysql报错缺少libncurses.so.5问题
  • CSS 之 响应式设计 前世今生
  • Java 集合框架之 List、Set 和 Map 的比较与使用
  • ABAP弹出对对话框错误信息设计
  • 在 SQL 中，区分 聚合列 和 非聚合列（nonaggregated column）
  • C#中鼠标点击获取Chart图形上的坐标值
  • Nginx整理
  • TP8 前后端跨域访问请求API接口解决办法
  • 数据结构考前总结
  • 【光纤通信】光纤结构
  • protobuf: 通讯录2.2
  • Git关联多个仓库
  • HALCON中用于分类的高斯混合模型create_class_gmm