【Oracle】第12天 DCL语句、TCL语句

DQL数据查询语言

SELECT

DML数据操纵语言

INSERT/DELETE/UPDATE

DDL数据定义语言

CREATE/DROP/ALTER/TRUNCATE/RENAME

DCL数据控制语言

GRANT/REVOKE

作用

1.用户维护
2.角色维护

用户、角色、权限的关系：
一个角色的权限是相对有限固定的
一个用户的权限是我们自定义的，我们可以给用户自定义权限的个数及种类
角色可以赋予给用户

语法

用户创建

CREATE USER USER_NAME IDENTIFIED BY PASSWORD;

用户维护

1.为用户添加权限

GRANT PRIVILEGE TO USER_NAME;

2.为用户添加创建会话（连接数据库）的权限

GRANT CREATE SESSION TO USER_NAME;	

3.为用户添加查询某表的权限

GRANT SELECT ON USER1.TB_NAME TO USER2; 

4.为用户添加基础连接角色CONNECT 和基础资源角色RESOURCE

GRANT CONNECT,RESOURCE TO USER1;

5.为用户添加管理员角色DBA

GRANT DBA TO USER1;

• CONNECT：该角色具有连接数据库的权限
  RESOURCE：该角色具有数据库的基本角色，包括查询对象、部分对象的创建修改删除
  DBA：数据库管理员，拥有数据库的最高权限

6.收回用户权限：

REVOKE PRIVILEGE FROM USER_NAME;

7.修改用户密码

ALTER USER USER_NAME IDENTIFIED BY PASSWORD;

8.锁定/解锁用户

ALTER USER USER_NAME ACCOUNT UNLOCK|LOCK;

9.删除无对象用户

DROP USER USER_NAME ;

10.删除用户及其附属对象

DROP USER USER_NAME CASCADE;

创建角色

CREATE ROLE ROLE_NAME;

删除角色

DROP ROLE ROLE_NAME;

数据字典

查询所有用户

SELECT * FROM DBA_USERS;

查询角色拥有的权限

• 注意，借助角色赋予给用户的权限，并不能用此方法↓查询到
• 想要确定某个用户到底有什么权限，必须再补充查询用户拥有角色的权限

表权限

SELECT * FROM DBA_TAB_PRIVS;

系统权限

SELECT * FROM DBA_SYS_PRIVS;

查询用户拥有的权限

• 想要确定某个用户到底有什么权限，必须再补充查询用户拥有角色的权限

表权限

SELECT T.PRIVILEGE,T.OWNER,T.TABLE_NAME FROM DBA_TAB_PRIVS T WHERE T.GRANTEE='USER_NAME'
UNION
SELECT T.PRIVILEGE,T.OWNER,T.TABLE_NAME FROM DBA_TAB_PRIVS T WHERE T.GRANTEE IN(SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS T WHERE T.GRANTEE='USER_NAME');

系统权限

SELECT T.PRIVILEGE,T.OWNER,T.TABLE_NAME FROM DBA_TAB_PRIVS T WHERE T.GRANTEE = 'USER_NAME' 
UNION 
SELECT T.PRIVILEGE,T.OWNER,T.TABLE_NAME FROM DBA_TAB_PRIVS T WHERE T.GRANTEE IN(SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS T WHERE GRANTEE ='USER_NAME');

注意

1.只有密码严格区分大小写
2.新创建的用户是没有任何权限的

TCL事务控制语言

COMMIT/ROLLBACK/SAVEPOINT

事务

定义

为了完成某项业务（任务、操作），由一系列看得见的SQL或看不见的后台进程组成的一系列逻辑工作单元

事务的自动提交与自动回滚

整个事务在处理过程中一直处于未提交状态，直到全部过程执行成功才可以被提交
若中间某步出现错误，则整个事务会全部回滚到事务执行之前的状态并报错

事务的四个属性（ACID）

• 1.原子性：事务是一个整体的工作单元，要么全部执行，要么全部取消。某一步执行失败，则全部回滚。
• 2.一致性：所有相关数据必须保持状态一致，以保持数据的完整性，若事务执行成功，所有相关数据全部变为新状态，若失败，所有相关数据处于原始状态。
• 3.隔离性：当前事务在进行数据修改时，其他事务只能查看修改之前的状态，等到当前事务执行结束（提交后），数据的修改才能被看到。
• 4.持久性：事务提交后，所做的修改就会永久保存，直到下一次事务来改变它。

因为事务的四个属性可能产生的问题

因为隔离性导致的问题
1.脏读：脏读是指在一个事务处理过程里读取了另一个未提交的事务中的数据
2.不可重复读：同一个事务同一个sql读取的数据不一致
3.幻读：同一个事务同一个sql读取的数据量发生改变（别的事务进行了插入操作）

隔离级别

• 从高到低

1.Serializable（串行化）：可避免脏读、不可重复读、幻读的发生
2.Repeatable read（可重复读）：可避免脏读、不可重复读的发生
3.Read committed（读已提交）：可避免脏读，Oracle默认隔离级别
4.Read uncommitted（读未提交）：最低级别，无法保证

SAVEPOINT

SAVEPOINT是在执行有风险的DML语句之前的一个保险措施

保存SAVEPOINT

SAVEPOINT SP_NAME

回滚到SAVEPOINT

ROLLBACK TO SP_NAME;