14.oracle DCL和TCL语句

DCL语句--数据控制语句

--创建用户同时设置密码：
CREATE USER USER_NAME IDENTIFIED BY PASSWORD;
--修改用户登录密码
alter user user_NAME IDENTIFIED BY PASSWORD;
--锁定用户
alter USER USER_NAME ACCOUNT LOCK;
--解锁用户
alter USER USER_NAME ACCOUNT UNLOCK;
--在进行创建用户时，使用sys或其他管理员用户登录
注意：
/*1.用户名不区分大小写，在创建、删除、登录用户时可以任意使用大小写；密码严格区分大小写，
    无论在创建用户、修改密码还是登录时都要注意
  2.此时仅仅是在数据库中注册了用户，但没有任何权限，甚至登录数据库的权限都没有
  3.每个用户下有自己各自的内容，互相之间不能直接访问，管理员用户可以直接访问其他用户的内容
  4.普通用户间可以通过赋予权限实现互相访问*/
  

--为用户添加权限：
GRANT PRIVILEGE TO USER_NAME;  --PRIVILEGE权限名

--为用户添加创建会话（连接数据库）的权限
GRANT CREATE SESSION TO USER_NAME; --仅能连接数据库 

--为用户添加查询某表的权限(所有者用户执行)
GRANT SELECT ON USER1.TB_NAME TO USER2;  

--为用户添加基础连接角色CONNECT和基础资源角色RESOURCE
GRANT CONNECT,RESOURCE TO USER_NAME;

--为用户添加管理员角色DBA    
GRANT DBA TO USER_NAME;

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

--收回用户权限：
REVOKE PRIVILEGE FROM USER_NAME;

--删除用户； 
DROP USER USER_NAME;--用户下无任何对象
DROP USER USER_NAME CASCADE; --连同用户下的对象一并删除

--数据字典
SELECT * FROM DBA_USERS; --查询所有用户

--注意：
--在数据字典中，所有用户的用户名都是以大写格式存储的，查询时要注意

DCL --角色
带有一系列权限的集合。
--作用：
用户被赋予某角色，相当于同时被赋予了该角色下的所有权限

分类：
---系统角色：数据库自带角色
---自定义角色：用户根据自身实际需求，自行创建的角色

--创建角色：
CREATE ROLE ROLE_NAME;
--赋予角色权限和赋予用户权限语法一致
--删除角色：
DROP ROLE ROLE_NAME;

给用户赋予角色或从用户收回角色：与赋予权限或收回权限的语法一致
GRANT SELECT ON SCOTT.EMP TO ROLE74;
GRANT CREATE TABLE TO ROLE74;
GRANT ROLE_NAME TO ROLE74;

CREATE USER LISI IDENTIFIED BY LISI;

查询角色拥有的权限：
--对象权限：如查询数据、增加数据、删除数据等
SELECT * FROM DBA_TAB_PRIVS; 
系统权限：如创建表、删除索引、连接数据库等
SELECT * FROM DBA_SYS_PRIVS; 
注意点：
/*
1.通过角色赋予给用户的权限，不能通过【查询某用户具有的权限】查到;只有直接赋予给用户的权限，
  才能通过【查询某用户具有的权限】查到
2.想要确定某个用户到底有什么权限，一方面需要直接查询该用户下有哪些权限，另一方面需要查询该用户下所拥
  有的的角色具有什么权限
3.想要赋予权限给用户，可以直接将该权限赋予给用户，或将具有该权限的角色赋予给用户*/

查询某用户下具有的所有对象权限：
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 FROM DBA_SYS_PRIVS T WHERE T.GRANTEE = ' USER_NAME '
UNION 
SELECT T.PRIVILEGE FROM DBA_SYS_PRIVS T WHERE T.GRANTEE IN (SELECT GRANTED_ROLE FROM 
       DBA_ROLE_PRIVS T WHERE T.GRANTEE = ' USER_NAME ');

DCL --用户、角色与权限的关系
/*
用户：数据库的使用者，比如SYS、SCOTT
权限：指能否在数据库中做某种操作，比如创建表、查询数据
角色：带有一系列权限的集合，拥有角色，即拥有该角色下所有的权限，比如CONNECT,RESOURCE,DBA
*/

系统权限：
  ADMINISTER DATABASE TRIGGER：允许管理数据库级别的触发器。
  ALTER DATABASE：允许更改数据库的属性，如字符集、时区等。
  CREATE ANY DIRECTORY：允许创建任意目录对象。
  CREATE ANY JOB：允许创建任意作业对象。
  CREATE ANY LIBRARY：允许创建任意共享库对象。
  CREATE ANY PROCEDURE：允许创建任意存储过程对象。
  CREATE ANY TABLE：允许创建任意表格对象。
  CREATE ANY VIEW：允许创建任意视图对象。
  CREATE SESSION：允许用户连接到数据库。
  CREATE USER：允许创建新的数据库用户。
  DROP ANY DIRECTORY：允许删除任意目录对象。
  DROP ANY JOB：允许删除任意作业对象。
  DROP ANY LIBRARY：允许删除任意共享库对象。
  DROP ANY PROCEDURE：允许删除任意存储过程对象。
  DROP ANY TABLE：允许删除任意表格对象。
  DROP ANY VIEW：允许删除任意视图对象。
  EXEMPT ACCESS POLICY：允许豁免访问策略。
  GRANT ANY OBJECT PRIVILEGE：允许授予任意对象权限。
  SELECT ANY DICTIONARY：允许访问数据字典视图。
  select any table：    允许查询所有表
对象权限：
  ALTER：允许修改表格或视图的结构。
  DELETE：允许删除表格中的行。
  INDEX：允许创建和删除索引。
  INSERT：允许向表格中插入行。
  REFERENCES：允许在外键关系中引用此表格。
  SELECT：允许查询表格中的行。
  UPDATE：允许更新表格中的行。
  EXECUTE：允许执行存储过程或函数。
  FLASHBACK：允许使用闪回功能。
  
--要授予对象权限给其他用户，可以使用GRANT语句。GRANT语句的语法如下：
GRANT privilege_list ON object_name TO user_name;
--privilege_list是要授予的权限列表，可以是单个权限或多个权限的组合，多个权限之间用逗号分隔。
--object_name是要授予权限的对象名称，可以是表格、视图、存储过程等。
--user_name是要授予权限给哪个用户或角色。

例如
GRANT SELECT, UPDATE ON employees TO john;

收回对象权限，可以使用REVOKE语句。REVOKE语句的语法如下：
REVOKE privilege_list ON object_name FROM user_name;
--privilege_list、object_name和user_name的含义与GRANT语句相同。
例如
REVOKE SELECT, UPDATE ON employees FROM john;



=======================================TCL====================================================
              
事务：/*为了完成某项业务（任务、操作），由一系列看得见的SQL或看不见的后台进程组成的一系列逻辑工作单元。
       整个事务在处理过程中一直处于未提交状态，直到全部过程执行成功才可以被提交，若中间某步出现错误，则整个事
       务会全部回滚到事务执行之前的状态并报错。*/

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

TCL关键词：
COMMIT; --事务提交  
ROLLBACK; --事务回滚
SAVEPOINT; --保存点
–在接下来的DML操作可能有风险的情况下在DML过程中使用
保存点语法
设立保存点：
SAVEPOINT SP_NAME;

回滚到某保存点：
ROLLBACK TO SAVEPOINT SP_NAME;

事务的提交和回滚：
    显式提交：由COMMIT决定是否提交当前事务，例如DML语句中的 
              INSERT UPDATE DELETE ;
    隐式提交：一旦整个事务执行成功，ORACLE会自动提交该事务，例如：
              CREATE ALTER DROP GRANT REVOKE ;
    自动回滚：一旦事务进行过程中出现任何错误，ORACLE会自动回滚当前事务。

--数据库关闭
SHUTDOWN [NORMAL|TRANSACTION|IMMEDIATE|ABORT]
          NORMAL: 正常关闭方式（默认）
          阻止新用户连接并等待已连接用户主动断开连接后关闭
          TRANSACTION: 事务关闭
          阻止新用户连接和新事务，等待所有事务提交完，用户断开连接后关闭
          IMMEDIATE: 立即关闭方式（常用）
          立即终止当前用户连接，强行停止并回退事务，关闭数据库
          ABORT: 终止关闭方式（慎用）
          具有强制性和一定的破坏性

数据库启动
STARTUP