【DDL】GRANT和REVOKE

目录

 

简介

ORACLE权限分类

系统权限

对象权限

角色

用户的创建和管理

GRANT

with admin option

with grant option

REVOKE

---

简介

GRANT由管理员授予用户系统权限，由对象所有者授予对象权限

REVOKE由管理员回收用户系统权限，由对象所有者回收对象权限

 

ORACLE权限分类

要了解如何授权和回收权限，首先要了解oracle的权限分类

 

系统权限

oracle中有很多系统权限（12c中有256个），这些权限需要有数据库管理员来授予在system_privilege_map中可以查看

SQL> select name from system_privilege_map order by name;   NAME ---------------------------------------- ADMINISTER ANY SQL TUNING SET ADMINISTER DATABASE TRIGGER ADMINISTER KEY MANAGEMENT ADMINISTER RESOURCE MANAGER ADMINISTER SQL MANAGEMENT OBJECT ADMINISTER SQL TUNING SET ADVISOR ALTER ANY ANALYTIC VIEW ALTER ANY ASSEMBLY ALTER ANY ATTRIBUTE DIMENSION ALTER ANY CLUSTER ALTER ANY CUBE ALTER ANY CUBE BUILD PROCESS ALTER ANY CUBE DIMENSION ALTER ANY DIMENSION ALTER ANY EDITION ALTER ANY EVALUATION CONTEXT ALTER ANY HIERARCHY ALTER ANY INDEX ALTER ANY INDEXTYPE ALTER ANY LIBRARY ALTER ANY MATERIALIZED VIEW ALTER ANY MEASURE FOLDER ALTER ANY MINING MODEL ALTER ANY OPERATOR ALTER ANY OUTLINE ALTER ANY PROCEDURE ALTER ANY ROLE ALTER ANY RULE ALTER ANY RULE SET ALTER ANY SEQUENCE ALTER ANY SQL PROFILE ALTER ANY SQL TRANSLATION PROFILE ALTER ANY TABLE ALTER ANY TRIGGER ALTER ANY TYPE ALTER DATABASE ALTER DATABASE LINK ALTER LOCKDOWN PROFILE ALTER PROFILE ALTER PUBLIC DATABASE LINK ALTER RESOURCE COST ALTER ROLLBACK SEGMENT ALTER SESSION ALTER SYSTEM ALTER TABLESPACE ALTER USER ANALYZE ANY ANALYZE ANY DICTIONARY AUDIT ANY AUDIT SYSTEM BACKUP ANY TABLE BECOME USER CHANGE NOTIFICATION COMMENT ANY MINING MODEL COMMENT ANY TABLE CREATE ANALYTIC VIEW CREATE ANY ANALYTIC VIEW CREATE ANY ASSEMBLY CREATE ANY ATTRIBUTE DIMENSION CREATE ANY CLUSTER CREATE ANY CONTEXT CREATE ANY CREDENTIAL CREATE ANY CUBE CREATE ANY CUBE BUILD PROCESS CREATE ANY CUBE DIMENSION CREATE ANY DIMENSION CREATE ANY DIRECTORY CREATE ANY EDITION CREATE ANY EVALUATION CONTEXT CREATE ANY HIERARCHY CREATE ANY INDEX CREATE ANY INDEXTYPE CREATE ANY JOB CREATE ANY LIBRARY CREATE ANY MATERIALIZED VIEW CREATE ANY MEASURE FOLDER CREATE ANY MINING MODEL CREATE ANY OPERATOR CREATE ANY OUTLINE CREATE ANY PROCEDURE CREATE ANY RULE CREATE ANY RULE SET CREATE ANY SEQUENCE CREATE ANY SQL PROFILE CREATE ANY SQL TRANSLATION PROFILE CREATE ANY SYNONYM CREATE ANY TABLE CREATE ANY TRIGGER CREATE ANY TYPE CREATE ANY VIEW CREATE ASSEMBLY CREATE ATTRIBUTE DIMENSION CREATE CLUSTER CREATE CREDENTIAL CREATE CUBE CREATE CUBE BUILD PROCESS CREATE CUBE DIMENSION CREATE DATABASE LINK CREATE DIMENSION CREATE EVALUATION CONTEXT CREATE EXTERNAL JOB CREATE HIERARCHY CREATE INDEXTYPE CREATE JOB CREATE LIBRARY CREATE LOCKDOWN PROFILE CREATE MATERIALIZED VIEW CREATE MEASURE FOLDER CREATE MINING MODEL CREATE OPERATOR CREATE PLUGGABLE DATABASE CREATE PROCEDURE CREATE PROFILE CREATE PUBLIC DATABASE LINK CREATE PUBLIC SYNONYM CREATE ROLE CREATE ROLLBACK SEGMENT CREATE RULE CREATE RULE SET CREATE SEQUENCE CREATE SESSION CREATE SQL TRANSLATION PROFILE CREATE SYNONYM CREATE TABLE CREATE TABLESPACE CREATE TRIGGER CREATE TYPE CREATE USER CREATE VIEW DEBUG ANY PROCEDURE DEBUG CONNECT ANY DEBUG CONNECT SESSION DELETE ANY CUBE DIMENSION DELETE ANY MEASURE FOLDER DELETE ANY TABLE DEQUEUE ANY QUEUE DROP ANY ANALYTIC VIEW DROP ANY ASSEMBLY DROP ANY ATTRIBUTE DIMENSION DROP ANY CLUSTER DROP ANY CONTEXT DROP ANY CUBE DROP ANY CUBE BUILD PROCESS DROP ANY CUBE DIMENSION DROP ANY DIMENSION DROP ANY DIRECTORY DROP ANY EDITION DROP ANY EVALUATION CONTEXT DROP ANY HIERARCHY DROP ANY INDEX DROP ANY INDEXTYPE DROP ANY LIBRARY DROP ANY MATERIALIZED VIEW DROP ANY MEASURE FOLDER DROP ANY MINING MODEL DROP ANY OPERATOR DROP ANY OUTLINE DROP ANY PROCEDURE DROP ANY ROLE DROP ANY RULE DROP ANY RULE SET DROP ANY SEQUENCE DROP ANY SQL PROFILE DROP ANY SQL TRANSLATION PROFILE DROP ANY SYNONYM DROP ANY TABLE DROP ANY TRIGGER DROP ANY TYPE DROP ANY VIEW DROP LOCKDOWN PROFILE DROP PROFILE DROP PUBLIC DATABASE LINK DROP PUBLIC SYNONYM DROP ROLLBACK SEGMENT DROP TABLESPACE DROP USER EM EXPRESS CONNECT ENQUEUE ANY QUEUE EXECUTE ANY ASSEMBLY EXECUTE ANY CLASS EXECUTE ANY EVALUATION CONTEXT EXECUTE ANY INDEXTYPE EXECUTE ANY LIBRARY EXECUTE ANY OPERATOR EXECUTE ANY PROCEDURE EXECUTE ANY PROGRAM EXECUTE ANY RULE EXECUTE ANY RULE SET EXECUTE ANY TYPE EXECUTE ASSEMBLY EXEMPT ACCESS POLICY EXEMPT DDL REDACTION POLICY EXEMPT DML REDACTION POLICY EXEMPT IDENTITY POLICY EXEMPT REDACTION POLICY EXPORT FULL DATABASE FLASHBACK ANY TABLE FLASHBACK ARCHIVE ADMINISTER FORCE ANY TRANSACTION FORCE TRANSACTION GLOBAL QUERY REWRITE GRANT ANY OBJECT PRIVILEGE GRANT ANY PRIVILEGE GRANT ANY ROLE IMPORT FULL DATABASE INHERIT ANY PRIVILEGES INHERIT ANY REMOTE PRIVILEGES INSERT ANY CUBE DIMENSION INSERT ANY MEASURE FOLDER INSERT ANY TABLE KEEP DATE TIME KEEP SYSGUID LOCK ANY TABLE LOGMINING MANAGE ANY FILE GROUP MANAGE ANY QUEUE MANAGE FILE GROUP MANAGE SCHEDULER MANAGE TABLESPACE MERGE ANY VIEW ON COMMIT REFRESH PURGE DBA_RECYCLEBIN QUERY REWRITE READ ANY FILE GROUP READ ANY TABLE REDEFINE ANY TABLE RESTRICTED SESSION RESUMABLE SELECT ANY CUBE SELECT ANY CUBE BUILD PROCESS SELECT ANY CUBE DIMENSION SELECT ANY DICTIONARY SELECT ANY MEASURE FOLDER SELECT ANY MINING MODEL SELECT ANY SEQUENCE SELECT ANY TABLE SELECT ANY TRANSACTION SET CONTAINER SYSBACKUP SYSDBA SYSDG SYSKM SYSOPER SYSRAC TRANSLATE ANY SQL UNDER ANY TABLE UNDER ANY TYPE UNDER ANY VIEW UNLIMITED TABLESPACE UPDATE ANY CUBE UPDATE ANY CUBE BUILD PROCESS UPDATE ANY CUBE DIMENSION UPDATE ANY TABLE USE ANY JOB RESOURCE USE ANY SQL TRANSLATION PROFILE   256 rows selected.

 

对象权限

oracle中有十多种对象权限，针对不同的对象，对应的权限也不相同，数据库管理员具有授权对象权限的能力，不过对象权限一般由对象所有者来授予

权限	SELECT	INSERT	UPDATE	DELETE	ALTER	INDEX	EXECUTE	READ	REFERENCE
Directory								√
Funtion							√
Procedure							√
Package							√
DB Object							√
Library							√
Operation							√
Sequence					√
Table	√	√	√	√	√	√			√
Type							√
View	√	√	√	√

 

角色

参见【OBJ】角色ROLE

 

用户的创建和管理

 

参见【OBJ】用户USER

 

GRANT

创建用户user02密码user02，并制定使用表空间及限额

SQL> create user user02 identified by user02 default tablespace users quota 30m on users;   User created.

由管理员sys授权用户create session、create table、create view权限

SQL> grant create session,create table,create view to user02;   Grant succeeded.

测试用户权限是否生效

SQL> conn user02/user02; Connected. SQL> create table test01 (time date);   Table created.   SQL> create view v_test01 as select * from test01;   View created.

在dba_sys_privs中可以查看user02 拥有的系统权限

GRANTE PRIVILEGE ------ -------------------- USER02 CREATE TABLE USER02 CREATE VIEW USER02 CREATE SESSION

 

由对象所有者scott授予user02用户访问和更新dept表

SQL> conn scott/tiger Connected. SQL> grant select,update on dept to user02;   Grant succeeded.

查看权限是否生效

SQL> select * from scott.dept;    DEPTNO DNAME           LOC ------- --------------- ----------      10 ACCOUNTING      NEW YORK      20 RESEARCH        DALLAS      30 SALES           CHICAGO      40 OPERATIONS      BOSTON     SQL> update scott.dept set LOC='TJ CHINA' WHERE DEPTNO=10;   1 row updated.   SQL> ROLLBACK;   Rollback complete.

在dba_tab_privs中可以查看user02拥有的对象权限

SQL> select GRANTEE,OWNER,TABLE_NAME,PRIVILEGE from dba_tab_privs where GRANTEE='USER02';   GRANTE OWNER TABLE PRIVILEGE ------ ----- ----- -------------------- USER02 SCOTT DEPT  SELECT USER02 SCOTT DEPT  UPDATE

 

with admin option

个选项表示该用户可以将自己拥有的权限授权给别人，用于系统权限

重新给user02授权

SQL> grant create session,create table,create view to user02 with admin option;   Grant succeeded.

 

with grant option

个选项表示该用户可以将自己拥有的权限授权给别人，用于对象权限

SQL> conn scott/tiger Connected. SQL> grant select,update on dept to user02 with grant option;   Grant succeeded.

 

创建用户user03给任何角色及权限

SQL> create user user03 identified by user03   User created.

使用user02 将自己的权限授权给user03

SQL> conn user02/user02 Connected. SQL> grant create session,create table,create view to user03;   Grant succeeded.   SQL> grant create session,create table,create view to user03;   Grant succeeded.

授权成功

 

REVOKE

由管理员回收user02的create view权限

SQL> conn / as sysdba Connected. SQL> revoke create view from user02;   Revoke succeeded.

测试

SQL> conn user02/user02 Connected. SQL> create view v_test02 as select * from test01; create view v_test02 as select * from test01             * ERROR at line 1: ORA-01031: insufficient privileges

由scott回收user02更新dept的权限

SQL> conn scott/tiger Connected. SQL> revoke update on dept from user02;   Revoke succeeded.

测试

SQL> conn user02/user02 Connected. SQL> update scott.dept set loc='SUN' where deptno=10; update scott.dept set loc='SUN' where deptno=10              * ERROR at line 1: ORA-01031: insufficient privileges