oracle19c 用户、表空间、权限详解与相关操作

#! https://zhuanlan.zhihu.com/p/620803882

oracle19c 用户、表空间、权限详解与相关操作

1 用户相关操作

Oracle的用户管理主要是针对数据库管理员说的，只有管理员有权创建，修改，删除。
根据每个用户访问Oracle的需求不同，分配其不同的权限，合理的分配会提高数据安全性，反之则相反

1.1 查询用户

select * from dba_users; --查询全库所有的用户
select * from all_users; --查询当前用户可看到的用户
select * from user_users; --查询当前登录的用户

1.2 创建用户（创建者需要由dba权限，用户在本地的需要加C##）

create user username identified by passwd
default tablespace tablespace --设置默认表空间
temporary tablespace tablespace |tablespace_group_name设置临时表空间组
quota size |unlimited on tablespace --用户可以使用表空间的最大值
PROFILE profile --设置用户使用的概要文件名称
account lock|unlock

1.3 删除用户

drop user username cascade; --删除用户不会删除其表空间

1.4 修改密码

alter user username identified by passwd

2 权限相关操作，角色管理

a 系统权限：用户在系统层面的权限，如CREATE SESSION, SELECT ANY TABLE等不依赖与对象的权限
b 对象权限：用户关于某个具体对象（表对象，序列，触发器）的权限，如select, update, insert 等依赖于表，视图，存储过程的权限
c 角色role 一组权限的集合
注意public用户具有的权限，其他用户都会有，即修改public用户的权限，其他用户的也会对应的改变权限

2.1 查看权限、角色role

select * from user_tab_privs; --查看当前用户具有的对象权限
select * from dba_tab_privs where grantee='USERNAME' or grantor='USERNAME'; --全部用户具有的对象权限，查询特定用户
select * from user_sys_privs; --当前用户具有的系统权限
select * from dba_sys_privs where grantee='USERNAME'; --全部用户具有的系统权限，查询特定用户的
select * from user_role_privs;  --当前用户具有的角色
select * from dba_role_privs where grantee='USERNAME';--全部用户具有的角色，查询特定用户具有的

2.2 常用权限，角色

dba 管理员角色
connect 连接数据库角色，能修改、删除表及数据，不能创建表
resource 能创建表
create session 连接数据库权限
create table 建表权限
unlimited tablespace 操作表空间权限
select any table 查询任何表的权限
select on username.nook 查询某用户的book表的权限
一般来说，普通用户授予connect和resource角色就好，如果是管理员需要给dba权限
对于普哦用户操作表时报错‘[Err] ORA-01950: 对表空间 ‘xxx’ 无权限’，需要执行以下语句
alter user username quota unlimited on tablespace_name;

2.3 赋予权限、角色

grant create session to username;
grant create session to usernma with admin option; --授予的权限，该用户可以再授予给其他用户，具有传递性
grant create session, select any table to username;

2.4 撤销权限

revoke select any table from username;
revoke select any table, update any table from username;

2.5 角色管理

角色role即可以理解为一组权限的集合，角色可以授予多个用户，一个用户也可以被授予多个权限
create ROLE role not identified |identified by passwd;--创建角色
grant system_privilege |all privileges to role with admin option --授予角色权限
drop.role rolename;--删除角色
在授予角色权限时，数据库管理员必须有GRANT_ANY_PRIVILEGEs权限才可以给角色授予任何权限

3 表空间

相关概念：
一个Oracle数据库是由一个或多个表空间组成的，在表空间中存储数据文件。
Oracle默认的表空间有6个 system sysaux undotbs1 temp users EXAMPLE
EXAMPLE:用于安装Oracle11g 数据库使用示例数据库
SYSAUX：作为EXAMPLE的辅助表空间
SYSTEM：用来存储SYS用户的表，视图以及存储过程等数据库对象
TEMP：用于存储SQL语句处理的表和索引的信息
UNDOTBS1:用于存储撤销信息
USERS：存储数据库用户创建的数据库对象

3.1 查看所有默认的表空间

select tablespace_name from dba_tablespaces;

3.2 创建表空间 /修改表空间

create tablespace tablespace_name --指定表空间名称
datafile filename SIZE size  --指定在表空间中村饭数据文件的文件名，指定文件存放的路径
autoextend on/off next size --自动扩展
maxsize size  --自动扩展时的最大值
permanent | temporary  永久表空间或临时表空间
extent management dictionary |local --表空间的管理方式字典管理或者本地管理（默认）

alter database datafile 'tablespace_file_name.dbf' resize size; --设置大小
alter database datafile 'tablespace_file_name.dbf' autoextend on/off next 200m maxsize 2000m;--关闭打开自动

3.3 重命名表空间

alter tablespace oldname rename to newname;

3.4 设置表空间的读写状态/默认读写，还有只读

alter TABLESPACE tablespace read only write

3.5 设置表空间的可用状态

alter TABLESPACE tablespace online offline normal temporary immediate

3.6 建立大文件表空间

create bigfile TABLESPACE tablespace
datafile filename SIZE size;

3.7 删除表空间

表空间的管理可以使用本地管理的方式，也可以使用数据字典的方式，在删除表空间时
不同的管理方式，删除的速度也会受到影响，大量实验显示，本地删除速度更快，可以
先更改管理方式，再删除。

drop tablespace tablespace_name [including contents] [cascade constraints]

3.8 临时表空间创建

create temporary tablespace tablespace_name
tempfile 'filename.dbf' size

3.9 设置临时表空间为默认表空间

alter database default temporary TABLESPACE tablespace

3.10 查看临时表空间

select tablesapce_name from dba_temp_files;

3.11 创建临时表空间组

create temporary tablesapce tablespace_name
tempfile filename size size TABLESpace GROUP GROUP_NAME;

3.12 删除临时表空间组

drop tablespace tablespace_name including contentss and datafiles

4 数据文件管理

4.1 存放的数据文件所用的表空间设置为脱机/联机状态

alter tablespace tablespace_name offline/online;

4.2 更改数据文件的名称

alter tablespace tablespace_name rename datafile oldfilename to newfilename;

4.3 删除数据文件

在使用数据文件时经常会去除一些没有用的数据文件，但是删除数据文件也有前提条件
当数据文件处于以下三种情况时，他是不能够被删除的
·数据文件中存在数据
·数据文件是表空间中唯一或第一个的数据文件
·数据文件或数据文件所在的表空间处于只读状态
alter tablespace tablesoace_name drop datafile 'filename';

参考文献/博客网址

《Oracle从入门到精通》

https://www.cnblogs.com/javasl/p/14882468.html