环境搭建
|
SQL> create user dev identified by oracle; User created. SQL> grant create session to dev; Grant succeeded. SQL> show user; USER is "DEV" |
一．授予用户create session 的权限
1.1 检查用户的系统权限
|
SQL> desc user_sys_privs; Name Null? Type ----------------------------------------- -------- ---------------------------- USERNAME VARCHAR2(30) PRIVILEGE NOT NULL VARCHAR2(40) ADMIN_OPTION VARCHAR2(3) SQL> select username,privilege from user_sys_privs; USERNAME PRIVILEGE ------------------------------ ---------------------------------------- DEV CREATE SESSION Dev只有 创建会话 的权限 |
1.2 检查用户的对象权限
|
SQL> desc user_tab_privs; Name Null? Type ----------------------------------------- -------- ---------------------------- GRANTEE NOT NULL VARCHAR2(30) OWNER 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> select table_name,privilege from user_tab_privs; no rows selected |
1.3 检查用户的角色权限
|
SQL> desc user_role_privs; Name Null? Type ----------------------------------------- -------- ---------------------------- USERNAME VARCHAR2(30) GRANTED_ROLE VARCHAR2(30) ADMIN_OPTION VARCHAR2(3) DEFAULT_ROLE VARCHAR2(3) OS_GRANTED VARCHAR2(3) SQL> select username,default_role from user_role_privs; no rows selected |
1.4 检查角色的系统权限
|
SQL> desc role_sys_privs; Name Null? Type ----------------------------------------- -------- ---------------------------- ROLE NOT NULL VARCHAR2(30) PRIVILEGE NOT NULL VARCHAR2(40) ADMIN_OPTION VARCHAR2(3) SQL> select role,privilege from role_sys_privs; no rows selected |
1.5 检查角色的对象权限
|
SQL> desc role_tab_privs; Name Null? Type ----------------------------------------- -------- ---------------------------- ROLE NOT NULL VARCHAR2(30) OWNER NOT NULL VARCHAR2(30) TABLE_NAME NOT NULL VARCHAR2(30) COLUMN_NAME VARCHAR2(30) PRIVILEGE NOT NULL VARCHAR2(40) GRANTABLE VARCHAR2(3) SQL> select table_name,column_name,privilege from role_tab_privs; no rows selected |
二．授予用户select any table 的权限
|
SQL> grant select any table to dev; Grant succeeded. 官方文档关于 select any table 的描述： 当把这个权限授予用户之后，用户可以查询 任何用户的 表、视图、物化视图（权限太大） |
2.1 检查用户的系统权限
|
SQL> desc user_sys_privs; Name Null? Type ----------------------------------------- -------- ---------------------------- USERNAME VARCHAR2(30) PRIVILEGE NOT NULL VARCHAR2(40) ADMIN_OPTION VARCHAR2(3) SQL> select username,privilege from user_sys_privs; USERNAME PRIVILEGE ------------------------------ ---------------------------------------- DEV CREATE SESSION DEV SELECT ANY TABLE Dev已经有select any table 的权限 |
2.2 检查用户的对象权限
|
SQL> desc user_tab_privs; Name Null? Type ----------------------------------------- -------- ---------------------------- GRANTEE NOT NULL VARCHAR2(30) OWNER 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> select table_name,privilege from user_tab_privs where table_name='EMP'; no rows selected |
2.3 检查用户的角色权限
|
SQL> desc user_role_privs; Name Null? Type ----------------------------------------- -------- ---------------------------- USERNAME VARCHAR2(30) GRANTED_ROLE VARCHAR2(30) ADMIN_OPTION VARCHAR2(3) DEFAULT_ROLE VARCHAR2(3) OS_GRANTED VARCHAR2(3) SQL> select username,default_role from user_role_privs; no rows selected |
2.4 检查角色的系统权限
|
SQL> desc role_sys_privs; Name Null? Type ----------------------------------------- -------- ---------------------------- ROLE NOT NULL VARCHAR2(30) PRIVILEGE NOT NULL VARCHAR2(40) ADMIN_OPTION VARCHAR2(3) SQL> select role,privilege from role_sys_privs; no rows selected |
2.5 检查角色的对象权限
|
SQL> desc role_tab_privs; Name Null? Type ----------------------------------------- -------- ---------------------------- ROLE NOT NULL VARCHAR2(30) OWNER NOT NULL VARCHAR2(30) TABLE_NAME NOT NULL VARCHAR2(30) COLUMN_NAME VARCHAR2(30) PRIVILEGE NOT NULL VARCHAR2(40) GRANTABLE VARCHAR2(3) SQL> select table_name,column_name,privilege from role_tab_privs; no rows selected |
2.6 测试
|
更新 SQL> update scott.emp set empno=empno*1; update scott.emp set empno=empno*1 * ERROR at line 1: ORA-01031: insufficient privileges SQL> rollback; Rollback complete. 删除 SQL> delete from scott.emp ; delete from scott.emp * ERROR at line 1: ORA-01031: insufficient privileges SQL> rollback; Rollback complete. 插入 SQL> insert into scott.emp(empno) values(123); insert into scott.emp(empno) values(123) * ERROR at line 1: ORA-01031: insufficient privileges SQL> rollback; Rollback complete. |
三．授予用户对其他用户下 单个对象 只读的权限
|
SQL> grant all on scott.emp to dev; Grant succeeded. SQL> revoke delete on scott.emp from dev; Revoke succeeded. SQL> revoke update on scott.emp from dev; Revoke succeeded. |
3.1 检查用户的系统权限
|
SQL> desc user_sys_privs; Name Null? Type ----------------------------------------- -------- ---------------------------- USERNAME VARCHAR2(30) PRIVILEGE NOT NULL VARCHAR2(40) ADMIN_OPTION VARCHAR2(3) Rollback complete. SQL> select username,privilege from user_sys_privs; USERNAME PRIVILEGE ------------------------------ ---------------------------------------- DEV CREATE SESSION Dev已经有CREATE SESSION 的权限 |
3.2 检查用户的对象权限
|
SQL> desc user_tab_privs; Name Null? Type ----------------------------------------- -------- ---------------------------- GRANTEE NOT NULL VARCHAR2(30) OWNER 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> select table_name,privilege from user_tab_privs; TABLE_NAME PRIVILEGE ------------------------------ ---------------------------------------- EMP FLASHBACK EMP DEBUG EMP QUERY REWRITE EMP ON COMMIT REFRESH EMP REFERENCES EMP SELECT EMP INSERT EMP INDEX EMP ALTER |
3.3 检查用户的角色权限
|
SQL> desc user_role_privs; Name Null? Type ----------------------------------------- -------- ---------------------------- USERNAME VARCHAR2(30) GRANTED_ROLE VARCHAR2(30) ADMIN_OPTION VARCHAR2(3) DEFAULT_ROLE VARCHAR2(3) OS_GRANTED VARCHAR2(3) SQL> select username,default_role from user_role_privs; USERNAME DEF ------------------------------ --- DEV YES |
3.4 检查角色的系统权限
|
SQL> desc role_sys_privs; Name Null? Type ----------------------------------------- -------- ---------------------------- ROLE NOT NULL VARCHAR2(30) PRIVILEGE NOT NULL VARCHAR2(40) ADMIN_OPTION VARCHAR2(3) SQL> select role,privilege from role_sys_privs; no rows selected |
3.5 检查角色的对象权限
|
SQL> desc role_tab_privs; Name Null? Type ----------------------------------------- -------- ---------------------------- ROLE NOT NULL VARCHAR2(30) OWNER NOT NULL VARCHAR2(30) TABLE_NAME NOT NULL VARCHAR2(30) COLUMN_NAME VARCHAR2(30) PRIVILEGE NOT NULL VARCHAR2(40) GRANTABLE VARCHAR2(3) SQL> select table_name,column_name,privilege from role_tab_privs; no rows selected |
3.6 测试
|
更新 SQL> update scott.emp set empno=empno*1; update scott.emp set empno=empno*1 * ERROR at line 1: ORA-01031: insufficient privileges SQL> rollback; Rollback complete. 删除 SQL> delete from scott.emp ; delete from scott.emp * ERROR at line 1: ORA-01031: insufficient privileges SQL> rollback; Rollback complete. 插入 SQL> insert into scott.emp(empno) values(123); 1 row created. SQL> commit; Rollback complete. |