一、系统权限
创建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行。
----可以看出那些用户具有“ADM”权限,即授予别人权限的权力
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
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9333283/viewspace-697641/,如需转载,请注明出处,否则将追究法律责任。