一、系统权限

创建4个用户

SQL> create user xax identified by xax;

用户已创建。

SQL> create user zhyp identified by zhyp;

用户已创建。

SQL> create user yiyi identified by yiyi;

用户已创建。

SQL> create user dayi identified by dayi;

用户已创建。

SQL> conn xax/xax
ERROR:
ORA-01045: user XAX lacks CREATE SESSION privilege; logon denied  ----用户xax不具有连接数据库的权限

警告: 您不再连接到 ORACLE。
SQL> conn /as sysdba
已连接。
SQL> grant create session,select any table,create table,create view to xax;

授权成功。

SQL> conn xax/xax
已连接。
SQL> grant create session,select any table to zhyp;
grant create session,select any table to zhyp                        
*
第 1 行出现错误:
ORA-01031: 权限不足                                -----如果在GRANT命令中使用了WITH ADMIN OPTION子句，被授予权限的用户可以进一步将这些系统权限授予其用户
SQL> conn /as sysdba
已连接。
SQL> revoke create session,select any table,create table,create view from xax;

撤销成功。

SQL> grant create session,select any table,create table,create view to xax
  2  with admin option;

授权成功。

SQL> desc dba_sys_privs
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 GRANTEE                                   NOT NULL VARCHAR2(30)
 PRIVILEGE                                 NOT NULL VARCHAR2(40)
 ADMIN_OPTION                                       VARCHAR2(3)

SQL> col grantee for a10
SQL> col privilege for a20
SQL> select * from dba_sys_privs
  2  where grantee = 'XAX';

GRANTEE    PRIVILEGE            ADM                                            
---------- -------------------- ---                                            
XAX        SELECT ANY TABLE     YES                                            
XAX        CREATE SESSION       YES                                            
XAX        CREATE VIEW          YES                                            
XAX        CREATE TABLE         YES                                            

SQL> conn xax/
已连接。
SQL> grant create session,select any table,create table,create view
  2  to zhyp with admin option;

授权成功。

SQL> conn zhyp/zhyp
已连接。
SQL> grant create session,select any table,create table,create view
  2  to yiyi with admin option;

授权成功。

SQL> grant create session,select any table,create table,create view to dayi;

授权成功。

SQL> conn /as sysdba
已连接。
SQL> set pagesize 30
SQL> select *
  2  from dba_sys_privs
  3  where grantee in('XAX','ZHYP','YIYI','DAYI');

GRANTEE    PRIVILEGE            ADM                                            
---------- -------------------- ---                                            
DAYI       CREATE SESSION       NO                                             
ZHYP       CREATE VIEW          YES                                            
XAX        SELECT ANY TABLE     YES                                            
YIYI       CREATE VIEW          YES                                            
DAYI       SELECT ANY TABLE     NO                                             
XAX        CREATE SESSION       YES                                            
YIYI       CREATE SESSION       YES                                            
XAX        CREATE VIEW          YES                                            
ZHYP       SELECT ANY TABLE     YES                                            
ZHYP       CREATE TABLE         YES                                            
XAX        CREATE TABLE         YES                                            
YIYI       CREATE TABLE         YES                                            
DAYI       CREATE TABLE         NO                                             
DAYI       CREATE VIEW          NO                                             
ZHYP       CREATE SESSION       YES                                            
YIYI       SELECT ANY TABLE     YES                                            

已选择16行。

SQL> a order by grantee
  3* where grantee in('XAX','ZHYP','YIYI','DAYI')order by grantee
SQL> l3
  3* where grantee in('XAX','ZHYP','YIYI','DAYI')order by grantee
SQL> c/)or/) or
  3* where grantee in('XAX','ZHYP','YIYI','DAYI') order by grantee
SQL> /

GRANTEE    PRIVILEGE            ADM                                            
---------- -------------------- ---                                            
DAYI       CREATE SESSION       NO                                             
DAYI       CREATE TABLE         NO                                             
DAYI       CREATE VIEW          NO                                             
DAYI       SELECT ANY TABLE     NO                                             
XAX        CREATE SESSION       YES                                            
XAX        CREATE TABLE         YES                                            
XAX        CREATE VIEW          YES                                            
XAX        SELECT ANY TABLE     YES                                            
YIYI       CREATE SESSION       YES                                            
YIYI       CREATE TABLE         YES                                            
YIYI       CREATE VIEW          YES                                            
YIYI       SELECT ANY TABLE     YES                                            
ZHYP       CREATE SESSION       YES                                            
ZHYP       CREATE TABLE         YES                                            
ZHYP       CREATE VIEW          YES                                            
ZHYP       SELECT ANY TABLE     YES                                            

已选择16行。

 

SQL> revoke create view from xax;

撤销成功。

SQL> select * from dba_sys_privs where grantee = 'XAX';

GRANTEE    PRIVILEGE            ADM                                            
---------- -------------------- ---                                            
XAX        SELECT ANY TABLE     YES                                            
XAX        CREATE SESSION       YES                                            
XAX        CREATE TABLE         YES                                            

SQL> select * from dba_sys_privs
  2  where grantee in('ZHYP','YIYI','DAYI');

GRANTEE    PRIVILEGE            ADM                                            
---------- -------------------- ---                                            
DAYI       CREATE SESSION       NO                                             
ZHYP       CREATE VIEW          YES                                            
YIYI       CREATE VIEW          YES                                            
DAYI       SELECT ANY TABLE     NO                                             
YIYI       CREATE SESSION       YES                                            
ZHYP       SELECT ANY TABLE     YES                                            
ZHYP       CREATE TABLE         YES                                            
YIYI       CREATE TABLE         YES                                            
DAYI       CREATE TABLE         NO                                             
DAYI       CREATE VIEW          NO                                             
ZHYP       CREATE SESSION       YES                                            
YIYI       SELECT ANY TABLE     YES                                            

已选择12行。

SQL> conn yiyi/yiyi
已连接。
SQL> revoke create session from xax;    -----用户yiyi可以撤销xax的权限

撤销成功。

SQL> conn xax/xax
ERROR:
ORA-01045: user XAX lacks CREATE SESSION privilege; logon denied

警告: 您不再连接到 ORACLE。
SQL> conn /as sysdba
已连接。
SQL> select * from dba_sys_privs where grantee in('XAX','ZHYP','YIYI','DAYI');

GRANTEE    PRIVILEGE            ADM                                            
---------- -------------------- ---                                            
DAYI       CREATE SESSION       NO                                             
ZHYP       CREATE VIEW          YES                                            
XAX        SELECT ANY TABLE     YES                                            
YIYI       CREATE VIEW          YES                                            
DAYI       SELECT ANY TABLE     NO                                             
YIYI       CREATE SESSION       YES                                            
ZHYP       SELECT ANY TABLE     YES                                            
ZHYP       CREATE TABLE         YES                                            
XAX        CREATE TABLE         YES                                            
YIYI       CREATE TABLE         YES                                            
DAYI       CREATE TABLE         NO                                             
DAYI       CREATE VIEW          NO                                             
ZHYP       CREATE SESSION       YES                                            
YIYI       SELECT ANY TABLE     YES                                            

已选择14行。

SQL> grant create session to xax;

授权成功。

SQL> revoke select any table from xax,zhyp,yiyi,dayi;

撤销成功。

 

二、对象权限

 

 

SQL> conn /as sysdba
已连接。
SQL> conn xax/xax
已连接。
SQL> create table t1(id int,name char(10));
create table t1(id int,name char(10))
*
第 1 行出现错误:
ORA-01950: 对表空间 'USERS' 无权限     -----用户没有得到表空间的配额分配

SQL>
SQL> desc user_sys_privs
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 USERNAME                                           VARCHAR2(30)
 PRIVILEGE                                 NOT NULL VARCHAR2(40)
 ADMIN_OPTION                                       VARCHAR2(3)

SQL> select * from user_sys_privs;

USERNAME                       PRIVILEGE            ADM                        
------------------------------ -------------------- ---                        
XAX                            CREATE SESSION       NO                         
XAX                            CREATE TABLE         YES                        

SQL>
SQL> select * from user_tab_privs;

未选定行

SQL> select * from user_role_privs;

未选定行

SQL> conn /as sysdba
已连接。
SQL> desc dba_role_privs
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 GRANTEE                                            VARCHAR2(30)
 GRANTED_ROLE                              NOT NULL VARCHAR2(30)
 ADMIN_OPTION                                       VARCHAR2(3)
 DEFAULT_ROLE                                       VARCHAR2(3)

SQL> select * from dba_role_privs where grantee like '%RESOU%';

未选定行

SQL> desc dba_user
ERROR:
ORA-04043: 对象 dba_user 不存在

SQL> desc dba_users
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 USERNAME                                  NOT NULL VARCHAR2(30)
 USER_ID                                   NOT NULL NUMBER
 PASSWORD                                           VARCHAR2(30)
 ACCOUNT_STATUS                            NOT NULL VARCHAR2(32)
 LOCK_DATE                                          DATE
 EXPIRY_DATE                                        DATE
 DEFAULT_TABLESPACE                        NOT NULL VARCHAR2(30)
 TEMPORARY_TABLESPACE                      NOT NULL VARCHAR2(30)
 CREATED                                   NOT NULL DATE
 PROFILE                                   NOT NULL VARCHAR2(30)
 INITIAL_RSRC_CONSUMER_GROUP                        VARCHAR2(30)
 EXTERNAL_NAME                                      VARCHAR2(4000)

SQL> select * from dba_sys_privs where grantee = 'RESOURCE';

GRANTEE    PRIVILEGE            ADM                                            
---------- -------------------- ---                                            
RESOURCE   CREATE TRIGGER       NO                                             
RESOURCE   CREATE SEQUENCE      NO                                             
RESOURCE   CREATE TYPE          NO                                             
RESOURCE   CREATE PROCEDURE     NO                                             
RESOURCE   CREATE CLUSTER       NO                                             
RESOURCE   CREATE OPERATOR      NO                                             
RESOURCE   CREATE INDEXTYPE     NO                                             
RESOURCE   CREATE TABLE         NO                                             

已选择8行。

SQL> alter user xax quota unlimited on users;

用户已更改。

SQL> conn xax/xax
已连接。
SQL> create table t2(id int,name char(10));

表已创建。

SQL> insert into t2 values(1,'xax');

已创建 1 行。

SQL> insert into t2 values(2,'zhyp');

已创建 1 行。

SQL> commit;

提交完成。

SQL> select * from t2;

        ID NAME                                                                
---------- ----------                                                          
         1 xax                                                                 
         2 zhyp                                                                

SQL> desc user_users
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 USERNAME                                  NOT NULL VARCHAR2(30)
 USER_ID                                   NOT NULL NUMBER
 ACCOUNT_STATUS                            NOT NULL VARCHAR2(32)
 LOCK_DATE                                          DATE
 EXPIRY_DATE                                        DATE
 DEFAULT_TABLESPACE                        NOT NULL VARCHAR2(30)
 TEMPORARY_TABLESPACE                      NOT NULL VARCHAR2(30)
 CREATED                                   NOT NULL DATE
 INITIAL_RSRC_CONSUMER_GROUP                        VARCHAR2(30)
 EXTERNAL_NAME                                      VARCHAR2(4000)

SQL> select * from user_users;

USERNAME                          USER_ID ACCOUNT_STATUS                       
------------------------------ ---------- --------------------------------     
LOCK_DATE      EXPIRY_DATE    DEFAULT_TABLESPACE                               
-------------- -------------- ------------------------------                   
TEMPORARY_TABLESPACE           CREATED        INITIAL_RSRC_CONSUMER_GROUP      
------------------------------ -------------- ------------------------------   
EXTERNAL_NAME                                                                  
--------------------------------------------------------------------------------
XAX                                    61 OPEN                                 
                              USERS                                            
TEMP                           10-6月 -11     DEFAULT_CONSUMER_GROUP           
                                                                               
                                                                               

SQL> show user
USER 为 "XAX"
SQL> grant select on t2 to public;

授权成功。

SQL> desc user_tab_privs_made
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 GRANTEE                                   NOT NULL VARCHAR2(30)
 TABLE_NAME                                NOT NULL VARCHAR2(30)
 GRANTOR                                   NOT NULL VARCHAR2(30)
 PRIVILEGE                                 NOT NULL VARCHAR2(40)
 GRANTABLE                                          VARCHAR2(3)
 HIERARCHY                                          VARCHAR2(3)

SQL> col table_name for a0
SP2-0246: 非法的 FORMAT 字符串"a0"
SQL> col table_name for a10
SQL> col grantor for a15
SQL> col privilege for a18
SQL> select *
  2  from user_tab_privs_made;

GRANTEE    TABLE_NAME GRANTOR         PRIVILEGE          GRA HIE               
---------- ---------- --------------- ------------------ --- ---               
PUBLIC     T2         XAX             SELECT             NO  NO                

SQL> gtant update(name on t2 to zhyp;
SP2-0734: 未知的命令开头 "gtant upda..." - 忽略了剩余的行。
SQL> grant update(name) on t2 to zhyp;

授权成功。

SQL> select *
  2  from user_tab_privs_made;

GRANTEE    TABLE_NAME GRANTOR         PRIVILEGE          GRA HIE               
---------- ---------- --------------- ------------------ --- ---               
PUBLIC     T2         XAX             SELECT             NO  NO                

SQL> alter table t2 add column addr(char(30));
alter table t2 add column addr(char(30))
                   *
第 1 行出现错误:
ORA-00904: : 标识符无效

SQL> alter table t2 add (addr char(30));

表已更改。

SQL> grant update(addr) on t2 to yiyi with grant option;

授权成功。

SQL> select * from user_col_privs_made;

GRANTEE    TABLE_NAME COLUMN_NAME                    GRANTOR                   
---------- ---------- ------------------------------ ---------------           
PRIVILEGE          GRA                                                         
------------------ ---                                                         
ZHYP       T2         NAME                           XAX                       
UPDATE             NO                                                          
                                                                               
YIYI       T2         ADDR                           XAX                       
UPDATE             YES                                                         
                                                                               

SQL> col column_name for a10
SQL> /

GRANTEE    TABLE_NAME COLUMN_NAM GRANTOR         PRIVILEGE          GRA        
---------- ---------- ---------- --------------- ------------------ ---        
ZHYP       T2         NAME       XAX             UPDATE             NO         
YIYI       T2         ADDR       XAX             UPDATE             YES        

SQL> conn zhyp/zhyp
已连接。
SQL> conn yiyi/yiyi
已连接。
SQL> grant update(addr) on xax.t2 to dayi with grant option;

授权成功。

SQL> conn zhyp/zhyp
已连接。
SQL> grant update(name) on xax.t2 to yiyi ;
grant update(name) on xax.t2 to yiyi
                          *
第 1 行出现错误:
ORA-01031: 权限不足

SQL> conn dayi/dayi
已连接。
SQL> grant update(addr) on xax.t2 to zhyp;

授权成功。

SQL> conn xax/xax
已连接。
SQL> select * from user_col_privs_made;

GRANTEE    TABLE_NAME COLUMN_NAM GRANTOR         PRIVILEGE          GRA        
---------- ---------- ---------- --------------- ------------------ ---        
ZHYP       T2         NAME       XAX             UPDATE             NO         
YIYI       T2         ADDR       XAX             UPDATE             YES        
DAYI       T2         ADDR       YIYI            UPDATE             YES        
ZHYP       T2         ADDR       DAYI            UPDATE             NO         

SQL> revoke update on t2 from yiyi;

撤销成功。

SQL> select * from user_col_privs_made;

GRANTEE    TABLE_NAME COLUMN_NAM GRANTOR         PRIVILEGE          GRA        
---------- ---------- ---------- --------------- ------------------ ---        
ZHYP       T2         NAME       XAX             UPDATE             NO         

SQL> spool off