第十四章 管理权限

<p>[TOC]</p> <h2>权限分类</h2> <ul> <li>系统权限、对象权限</li> </ul> <h2>系统权限</h2> <ul> <li>系统权限列表</li> </ul> <h2>系统权限限制</h2> <ul> <li>一个参数限制select any table 是否可以访问sys对象</li> </ul> <h2>授予系统权限</h2> <ul> <li> <p>查看用户 <code>select * from dba_users where lock_date is null;</code></p> </li> <li> <p>创建用户</p> <pre><code>create user dog identified by wangwang; create user cat identified by miaomiao; create user pig identified by hengheng; create user fox identified by loveyou;</code></pre> </li> <li> <p>管理员授权用户</p> <pre><code>connect system/oracle; grant create session,select any table,create table,create view to cat;</code></pre> </li> <li> <p>用户联接，再分配权限其他用户</p> <pre><code>connect cat/miaomiao grant create session,select any table to dog;--失败</code></pre> </li> <li> <p>管理员回收权限重启授权</p> <pre><code>connect system/oracle; revoke create session,select any table,create table,create view from cat; grant create session,select any table,create table,create view to cat with admin option;</code></pre> </li> <li> <p>查看用户权限 <code>select * from dba_sys_privs where grantee='CAT';</code></p> </li> <li> <p>再分配权限</p> <pre><code>connect cat/miaomiao grant create session,select any table,create table,create view to dog with admin option;</code></pre> </li> <li> <p>再分配权限</p> <pre><code>connect dog/wangwang grant create session,select any table,create table,create view to fox with admin option; grant create session,select any table,create table,create view to pig;</code></pre> </li> <li>查看四个用户权限,admin_option列说明是否可以往下授权 <code>select * from dba_sys_privs where grantee in ('CAT','DOG','PIG','FOX');</code></li> </ul> <h2>回收系统权限：可回收用户或者角色的系统权限</h2> <ul> <li> <p>回收cat用户create view 权限</p> <pre><code>connect system/oracle revoke create view from cat;</code></pre> </li> <li> <p>查看权限，<strong>系统权限的回收不是联级的</strong> <code>select * from dba_sys_privs where grantee in ('CAT','DOG','PIG','FOX');</code></p> </li> <li>联级授权，可以向上回收权限 <pre><code>connect fox/loveyou select * from session_privs; revoke create session from cat; --cat不能再登陆了 connect cat/miaomiao connect system/oracle select * from dba_sys_privs where grantee in ('CAT','DOG','PIG','FOX');</code></pre></li> </ul> <h2>对象权限</h2> <ul> <li>8种对象权限</li> </ul> <h2>对象权限的授权和回收</h2> <ul> <li> <p>重新授予cat create session 权限，回收用户选择任何表权限</p> <pre><code>grant create session to cat; revoke select any table from cat,dog,pig,fox; --了解这个包的作用 connect sys/oracle as sysdba; grant execute on DBMS_SPACE_ADMIN to system; --了解每列的作用 select * from dba_tab_privs where privilege like 'EXE%' and table_name='DBMS_SPACE_ADMIN'; --创建scott用户 select * from dba_users where username='SCOTT' select * from dba_tables where owner='SCOTT' --scott用户分配三种权限 connect scott/tiger grant select on emp to public; col grantee for a8 col table_name for a10 col grantor for a8 col privilege for a8 select * from user_tab_privs_made; grant update(sal) on emp to cat; grant update(job) on emp to cat with grant option; col column_name for a10 select * from user_col_privs_made; --cat用户使用scott授予的权限区别 connect cat/miaomiao grant update(job) on scott.emp to pig,dog with grant option; grant update(sal) on scott.emp to pig;--失败 --dog用户继续使用scott授予cat，cat授予dog的权限 connect dog/wangwang grant update(job) on scott.emp to fox;</code></pre> </li> <li> <p>查看用户对象的权限详情</p> <pre><code>connect scott/tiger col column_name for a10 select * from user_col_privs_made;</code></pre> </li> <li> <p>回收对象权限联级影响</p> <pre><code>revoke update on emp from cat; col column_name for a10 select * from user_col_privs_made;</code></pre> </li> <li>重要视图 <code>select * from dba_col_privs where owner='SCOTT';</code></li> </ul> <h2>与select any table 权限相关的应用实例</h2> <pre><code>connect scott/tiger select * from session_privs; connect sys/oracle as sysdba show parameter O7_DICTIONARY_ACCESSIBILITY grant select any table to scott; connect scott/tiger select * from session_privs where privilege like 'S%'; col host_name for a15 col instance_name for a15 select host_name,instance_name,version from v$instance; connect sys/oracle as sysdba alter system set O7_DICTIONARY_ACCESSIBILITY=true; alter system set O7_DICTIONARY_ACCESSIBILITY=true scope=spfile; show parameter O7_DICTIONARY_ACCESSIBILITY shutdown immediate startup show parameter O7_DICTIONARY_ACCESSIBILITY connect scott/tiger select host_name,instance_name,version from v$instance;</code></pre>