系統權限與對象權限
1、系統權限
一、建立用戶
SQL> create user dog identified by wangwang;
User created
SQL> create user pig identified by hengheng;
User created
SQL> create user fox identified by youyou;
User created
SQL> create user cat identified by miaomiao;
User created
二、給cat賦權
SQL> grant create session, select any table,create table,create view to cat;
Grant succeeded
三、cat給dog賦權
SQL> conn cat/miaomiao;
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as cat
SQL> grant create session, select any table,create table,create view to dog;
grant create session, select any table,create table,create view to dog
ORA-01031: 權限不足
給cat賦權時沒有再賦權的權力，需加
:WITH ADMIN OPTION
四、收回權限 revoke
SQL> revoke create session, select any table,create table,create view from cat;
Revoke succeeded
五、權限查看
SQL> select * from dba_sys_privs where grantee='CAT';
GRANTEE PRIVILEGE ADMIN_OPTION
------------------------------ -------------------- ------------
2、對象權限8個 execute,alter,select,insert,update,delete,index,references 一、受權emp表給全部用戶 SQL> grant select on emp to public; Grant succeeded 查看 SQL> select * from user_tab_privs_made; GRANTEE TABLE_NAME GRANTOR PRIVILEGE GRANTABLE HIERARCHY ------------------------------ ------------------------------ ------------------------------ -------------------- --------- --------- PUBLIC EMP SCOTT SELECT NO NO 二、受權表中某列給用戶 SQL> grant update(sal) on emp to cat; Grant succeeded SQL> grant update(job) on emp to cat; Grant succeeded 查看 SQL> select * from user_col_privs_made; GRANTEE TABLE_NAME COLUMN_NAME GRANTOR PRIVILEGE GRANTABLE ------------------------------ ------------------------------ ------------------------------ ------------------------------ -------------------- --------- CAT EMP SAL SCOTT UPDATE NO CAT EMP JOB SCOTT UPDATE NO