Oracle--用户管理-CSDN博客

本文链接：https://blog.csdn.net/lerp020321/article/details/147366538

前言：本博客仅作记录学习使用，部分图片出自网络，如有侵犯您的权益，请联系删除

用户管理在 Oracle 数据库中至关重要。一个服务器通常只运行一个 Oracle 实例，而一个 Oracle 用户代表一个用户群，他们通过该用户登录并操作数据库对象（如表、视图等）。实际开发中，不同项目组通常使用独立的 Oracle 用户，避免相互干扰，同时通过权限控制实现跨模块的数据访问。一个 Oracle 用户可以看作是一个业务模块，不同模块间的关联通过权限管理实现。

1、用户模式

Oracle 数据库引入了模式（Schema）概念来管理用户创建的数据库对象。每个用户创建的对象都属于该用户的模式。

模式内的对象默认不可被其他模式直接访问，即使有权限，也需要通过“模式名.对象名”方式访问（如 scott.dept）

# 当其他用户账号下，比如system账号下，要访问scott账号下创建的表dept，需要在表名前加上该表的所属账号scott
select * from scott.dept;

2、用户登录数据库

SQLPlus 是 Oracle 自带的客户端工具，用于与数据库交互。连接身份分为三种：

连接身份代表着该用户连接数据库后享受的权限，oracle有三种身份如下：

sysdba：数据库管理员身份，具有最高权限（如启动/关闭数据库、备份恢复等）
sysoper：数据库操作员身份，权限低于 sysdba
normal：普通用户，默认身份，只能操作自身模式的对象

sys用户必须用sysdba才能登录（使用：sys as sysdba），system用户（数据库管理员身份，直接使用：system）

sqlplus登录用户语法：sqlplus username/password@serviceName [as 连接身份]

 # 以系统管理员登录数据库：
 sqlplus system/Oracle23ai@FREE

切换用户：connect username/password@serviceName [as 连接身份]

 connect sys/Oracle23ai@free as sysdba

SQLPlus 支持连接本地和远程数据库。

 语法：conn 用户名/密码@服务器连接字符串 as 连接身份（此身份需要解锁）
 比如：connect sys/oracle23ai@192.168.1.133:1521/free as sysdba;

3、基本概念

CDB--Container Database，即容器数据库；
PDB--Pluggable Database，即可插拔数据库；
公共用户--CDB公共用户，必须以C##或c##开头，Oracle会在每个PDB中同时创建该用户；
本地用户--PDB的用户，本地用户所在PDB中必须是唯一的，每个PDB可以有不同的本地用户；

CDB和PDB是数据库中的两种重要概念

CDB是一个容器数据库，名称为CDB$ROOT，其作用就是系统数据库，sys等账号都保存在里面，同时它可以管理PDB数据库。可以容纳多个PDB类似于一个父容器。在CDB环境中，用户称为公共用户，而在PDB环境中，用户称为本地用户。

PDB是一种可插拔数据库，它可以在CDB中独立地创建、停止和启动。每个PDB都有自己的数据文件、控制文件和日志文件，并且可以独立的进行备份和恢复操作。PDB提供了更高的隔离性和安全性，因为每个PDB都有自己的安全上下文，并且只能通过指定的用户访问。

3.1、常用命令

 --打开PDB
 alter pluggable database pdb名称 open;
 --关闭PDB
 alter pluggable database pdb名称 close;
 --切换PDB
 alter session set container=pdb名称(指定容器);
 --查看当前所在容器
 show con_name;
 
 --查看所有的PDB
 show pdbs;
 
 --切换到mypdb
 alter session set container=mypdb;

3.2、创建用户

每个用户都有自己的用户名和密码，以及权限和角色，用户访问和管理数据库中的对象
使用CREATE和DROP来创建和删除用户。用户对它所拥有的对象具有所有的增删改的权限
可以使用ALTER USER命令来修改用户的属性，如密码、默认表空间以及存储配额
用户可以被授予不同的权限或角色

 sqlplus system/Oracle23ai@free    --连接数据库(管理员角色)
 
 # CDB(Contaniner Database)容器中 创建用户时，前面必须添加C##，而PDB(Pluggable Database)数据库不需要加前缀
 #COMMON USERS(普通用户):经常建立在CDB层，用户名以C##或c##开头;
 #LOCAL USERS(本地用户):仅建立在PDB层，建立的时候得指定CONRAINER
 
 --看数据库是否为CDB
 select CDB from v$database;
                                                          
 --创建用户
 create user C##fox identified by Oracle23ai;
 
 --配置权限
 grant dba,connect,resource,create view to C##fox;
 grant create session to C##fox;
 grant select any table to C##fox;
 grant update any table to C##fox;
 grant insert any table to C##fox;
 
 --连接登录测试
 conn c##fox/Oracle23ai@free
 
 --修改用户密码、锁定状态
 alter user c##fox
     identified by ******    --修改密码
     account lock;           --修改用户处理锁定状态或者解锁状态
     
 --删除用户
 drop user c##fox;

虽然创建了用户，但还不能使用，需要给予用户授于数据库角色和设置用户权限

3.3、数据库角色

Oracle数据库角色是若干系统权限的集合，给Oracle用户进行授权数据库角色，就是等于赋予该用户若干数据库系统权限。常用数据库角色如下：

CONNECT：允许用户连接数据库，建立会话。
RESOURCE：允许用户创建数据库对象（如表、视图、存储过程等）。
DBA：拥有所有系统权限，可管理数据库和授权其他用户。

三个数据库角色对应有三个连接身份：

4、用户权限

Oracle数据库用户权限分为：系统权限和对象权限两种

系统权限：如 create session（登录权限）、create table（创建表权限）。
对象权限：如对表的 select、insert、update、delete操作权限。

5、授权操作

 --GRANT 数据库角色 to 用户
 grant connect to C##fox;        --授于connect角色(必须)
 grant resource to C##fox;       --授于resource角色
 grant dba to C##fox;            --授于管理员dba角色
 
 --GRANT 用户的系统权限 to 用户
 grant create session to C##fox;     --授于用户登录数据库的权限
 
 --授于用户操作表空间的权限
 grant unlimited tablespace to C##fox;
 grant create tablespace to C##fox;
 grant alter tablespace to C##fox;
 grant drop tablespace to C##fox;
 grant manage tablespace to C##fox;
 
 --操作表
 grant create table to C##fox;   (包含有create index权限、alter table、drop table权限)
 --操作视图
 grant create view to C##fox;    (包含有alter view、drop view权限)
 --操作触发器
 grant create trigger to C##fox; (包含有alter trigger、drop trigger权限)
 --操作存储过程
 grant create procedure to C##fox;(包含有alter precedure,drop procedure和function以及package权限)
 --操作序列
 grant create sequence to C##fox;;   (包含有创建、修改、删除以及选择序列)
 --回退段
 grant create rollback segment to C##fox;
 grant alter rollback segment to C##fox;
 grant drop rollback segment to C##fox;
 --同义词
 grant create synonym to C##fox; (包含drop sysnonym权限)
 grant create public sysnonym to C##fox;
 grant drop public sysnonym to C##fox;
 --关于用户
 grant create user to C##fox;
 grant alter user to C##fox;
 grant become user to C##fox;
 grant drop user to C##fox;
 --关于角色
 grant create role to C##fox;
 --操作概要文件
 grant create profile to C##fox;
 grant alter profile to C##fox;
 grant drop profile to C##fox;
 --从sys用户所拥有的数据字典表中进行选择
 grant select any dictionary to C##fox;
 
 --GRANT 用户的对象权限 on 对象 TO 用户
 grant select,insert,update,delete on emp to C##fox;
 --把emp表的全部操作权限授于fox用户
 grant all on emp to C##fox;

6、查看用户的权限或角色

6.1、查看用户

 show user;          //查看当前用户名
 
 --查看所有用户
 select * from dba_users;
 select * from all_users;
 select * from user_users;   //查看当前用户

6.2、查看角色

 --1、当前用户被激活的全部角色
 select * from session_roles;
 --2、当前用户被授于的角色
 select * from user_role_privs;
 --3、全部用户被授于的角色
 select * from dba_role_privs;
 --4、查看某个用户所拥有的角色
 select * from dba_role_privs where grantee='用户名';
 --5、一个角色包含的系统权限
 select * from dba_sys_privs where grantee='角色名';
 select * from dba_sys_privs where grantee='CONNECT';       --connect要大写或者
 select * from dba_sys_privs where role='角色名'；
 --6、一个角色包含的对象权限
 select * from dba_roles;
 --7、查看所有角色
 select * from dba_roles;

6.3、查看权限

 --1、基本权限查询
 select * from session_privs;    --当前用户所拥有的全部权限
 select * from user_sys_privs;   --当前用户的系统权限
 select * from user_tab_privs;   --当前用户的对象权限
 select * from dba_sys_privs;    --查询某个用户所拥有的系统权限
 select * from role_sys_privs;   --查看角色(只能查看登录用户拥有的角色)所包含的权限
 --2、查看用户的系统权限(直接赋值给用户或角色的系统权限)
 select * from dba_sys_privs;
 select * from user_sys_privs;
 --3、查看用户的对象权限
 select * from dba_tab_privs;
 select * from all_tab_privs;
 select * from user_tab_privs;
 --4、查看哪些用户有sysdba或sysoper系统权限(查询时需要相应权限)
 select * from v$pwfile_users;

6.4、补充

 --1、以下语句可以查看Oracle提供的系统权限
 select name from sys.system_privilege_map
 --2、查看一个用户的所有系统权限(包含角色的系统权限)
 select privilege from dba_sys_privs where grantee='SYSTEM'
 union
 select privilege from dba_sys_privs where grantee in (select granted_role from dba_role_privs where grantee='SYSTEM');
 --3、查询当前用户可以访问的所有数据字典视图
 select * from dict where comments like '%grant%';
 --4、显示当前数据库的全称
 select * from global_name

7、取消用户权限

 --Revoke 对象权限 on 对象 from 用户
 revoke select,insert,update,delete on emp from C##fox;
 
 --Revoke 系统权限 from 用户
 grant create session to C##fox;     --授于用户登录数据库的权限
 revoke SELECT ANY TABLE from C##fox;
 
 --Revoke 角色(role) from 用户
 revoke RESOURCE from C##fox;