Oracle权限-系统权限与对象权限

系统权限与对象权限
一、系统权限
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

2、给cat赋权
SQL> grant create session, select any table,create table,create view to cat;
Grant succeeded

3、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

4、收回权限 revoke

SQL> revoke create session, select any table,create table,create view from cat;
Revoke succeeded

5、权限查看
SQL> select * from dba_sys_privs where grantee='CAT';
GRANTEE                        PRIVILEGE            ADMIN_OPTION
------------------------------ -------------------- ------------


二、对象权限8个
    execute,alter,select,insert,update,delete,index,references

1、授权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

2、授权表中某列给用户

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