14用户权限及角色管理

<h1>用户权限及角色管理</h1> <ul> <li>开发人员使用DML，表设计人员使用DDL，DBA使用DCL（用户权限管理 ）</li> </ul> <h2>用户管理</h2> <h3>用户创建</h3> <ul> <li> <p>创建永久表空间、临时表空间</p> <pre><code>SELECT * FROM dba_tablespaces ; CREATE TABLESPACE mldn_date DATAFILE 'D:\tablespaces\mldn_data01.dbf' SIZE 50M AUTOEXTEND ON NEXT 2M LOGGING; CREATE TEMPORARY TABLESPACE mldn_temp TEMPFILE 'D:\tablespaces\mldn_temp02.dbf' SIZE 50M AUTOEXTEND ON NEXT 2M;</code></pre> </li> <li> <p>删除表空间</p> <pre><code>alter tablespace mldn_temp offline; drop tablespace mldn_temp including contents;</code></pre> </li> <li> <p>创建用户，创建完成的用户无权限，无法使用 用户名、密码、表空间、临时表空间、两个表空间上使用的空间大小、不锁定账号、用户登陆强制修改密码</p> <pre><code>CREATE USER mldnuser IDENTIFIED BY java_android DEFAULT TABLESPACE mldn_date TEMPORARY TABLESPACE mldn_temp QUOTA 30M ON mldn_date QUOTA 20M ON USERS ACCOUNT UNLOCK PASSWORD EXPIRE;</code></pre> </li> <li> <p>查看用户信息，lock_date是锁定时间，创建非锁定用户该字段为null <code>SELECT * FROM dba_users WHERE username='MLDNUSER';</code></p> </li> <li>查看用户表空间,每个用户都有多个表空间可用 <code>SELECT * FROM dba_ts_quotas WHERE username='MLDNUSER'</code></li> </ul> <h3>概要文件(profiles)P346</h3> <ul> <li> <p>定义概要文件</p> <pre><code>create profile mldn_profile limit cpu_per_session 10000 logical_reads_per_session 20000 connect_time 30 idle_time 30 sessions_per_user 10 failed_login_attempts 3 password_lock_time unlimited password_life_time 60 password_reuse_time 30 password_grace_time 6 ;</code></pre> </li> <li> <p>查看profiles文件数据字典dba_profiles <code>select * from dba_profiles where profile='MLDN_PROFILE'</code></p> </li> <li> <p>创建用户时指定profiles文件 <code>create user mldnjava identified by hello profile mldn_profile;</code></p> </li> <li> <p>配置已经存在用户profiles文件 <code>alter user mldnuser profile mldn_profile</code></p> </li> <li> <p>查看dba_users 数据字典，看用户的定义 <code>select * from dba_users where username in ('MLDNJAVA','MLDNUSER')</code></p> </li> <li> <p>修改概要文件</p> <pre><code>alter profile mldn_profile limit cpu_per_session 1000 password_life_time 10;</code></pre> </li> <li>删除概要文件 <code>drop profile mldn_profile cascade;</code></li> </ul> <h3>维护用户</h3> <ul> <li> <p>修改用户密码 <code>alter user mldnuser identified by helojava;</code></p> </li> <li> <p>控制用户锁定\解锁</p> <pre><code>alter user mldnuser account lock ; alter user mldnuser account unlock;</code></pre> </li> <li> <p>通过dba_users 数字字典查看用户锁定状态 <code>select * from dba_users where username='MLDNUSER'</code></p> </li> <li> <p>让密码失效 <code>alter user mldnuser password expire;</code></p> </li> <li> <p>修改用户表空间配额</p> <pre><code>alter user mldnuser quota 20M on system quota 35M on users;</code></pre> </li> <li> <p>通过 dba_ts_quotas 数据字典查看mldnuser用户新的表空间配额 <code>select * from dba_ts_quotas where username='MLDNUSER';</code></p> </li> <li>删除用户，当用户下有表，要使用 cascade <code>drop user mldnuser;</code></li> </ul> <h2>权限管理</h2> <h3>系统权限</h3> <ul> <li> <p>sys 授权 create session 的权限给 mldnuser ，此时可以连接了 <code>grant create session to mldnuser;</code></p> </li> <li> <p>测试创建表的权限，提示权限不足</p> <pre><code>create table mytab( id number, name varchar2(20) );</code></pre> </li> <li> <p>sys 授权给mldnuser，授权后可以使用了了，12c的授权后直接可以使用，低版本要重新登陆，with admin option 可以使mldnuser用户继续往下授权 <code>grant create table ,create sequence ,create view to mldnuser with admin option;</code></p> </li> <li> <p>去mldnuser用户的session 上执行，可行的。 <code>grant create table,create sequence to mldnjava;</code></p> </li> <li> <p>mldnuser用户是无法执行以下语句的，原因是没有 with admin option <code>grant create session to mldnjava;</code></p> </li> <li> <p>查看 dba_sys_privs 数据字典，查看用户的权限 <code>select * from dba_sys_privs where grantee in('MLDNUSER','MLDNJAVA') order by grantee desc;</code></p> </li> <li> <p>撤销权限，只撤销了mldnuser用户的权限，对mldnuser用户授权给mldnjava用户的权限无影响 <code>revoke create table,create view from mldnuser ;</code></p> </li> <li>除了管理员回收权限，还可以通过 mldnuser 回收mldnjava的权限 <code>revoke create sequence from mldnjava;</code></li> </ul> <h3>对象权限</h3> <ul> <li> <p>对象权限： 查询(select)、增加(insert)、更新(update)、删除(delete) 执行(execute)、修改(alter)、索引(index)、关联(references)</p> </li> <li> <p>授权查询，insert其他用户的表的权限给 mldnuser，之后可以对该表执行insert和select语句 <code>grant select ,insert on hr.pdm to mldnuser;</code></p> </li> <li> <p>授权更新某列的权限给mldeuser</p> <pre><code>grant update(name) on hr.pdm to mldnuser update hr.pdm set name='李兴华' --成功，有更新该列的权限 update hr.pdm set id=1 --失败，权限不足 </code></pre> </li> <li> <p>查看当前登陆用户所有对象权限，只能看整个对象，不能看指定的列。其他语句是标准化窗口的格式</p> <pre><code>col owner for A10; col table_name for A10; col grantor for A10; col privilege for A10; select * from user_tab_privs_recd ;</code></pre> <p><img src="https://www.showdoc.com.cn/server/api/attachment/visitfile/sign/027055fde33a5e16ccb7ef4e4ff37472?showdoc=.jpg" alt="" /></p> </li> <li> <p>查看当前登陆用户对象的列的权限</p> <pre><code>col owner for A10; col table_name for A10; col column_name for A10; col grantor for A10; col privilege for A10; select * from user_col_privs_recd;</code></pre> <p><img src="https://www.showdoc.com.cn/server/api/attachment/visitfile/sign/a475d781ddc62f8d1dcfa6b896aed125?showdoc=.jpg" alt="" /></p> </li> <li> <p>查看数据库对象分配了哪些权限出去，暂时看不懂</p> <pre><code>select * from user_tab_privs_made; select * from user_col_privs_made;</code></pre> </li> <li>回收对象权限，授予列权限是指定字段，但是回收是不能指定字段的。 <pre><code>revoke select,insert on hr.pdm from mldnuser; revoke update on hr.pdm from mldnuser;</code></pre></li> </ul> <h2>角色</h2> <h3>角色创建</h3> <ul> <li>角色是一组权限的集合</li> <li> <p>创建无密码角色 <code>create role mldn_role_a;</code></p> </li> <li> <p>创建有密码角色 <code>create role mldn_role_b identified by hellojava;</code></p> </li> <li> <p>禁用所有角色 <code>set role none ;</code></p> </li> <li> <p>启用所有角色 <code>set role all ;</code></p> </li> <li> <p>启用有密码的角色 mldn_role_b <code>set role mldn_role_b identified by hellojava;</code></p> </li> <li>查看角色是否有密码 dba_roles <code>select * from dba_roles where role in ('MLDN_ROLE_A','MLDN_ROLE_B');</code></li> </ul> <h3>角色授权</h3> <ul> <li> <p>角色 mldn_role_a 授权创建基本数据库对象的能力 <code>grant create session, create table, create view, create sequence to mldn_role_a ;</code></p> </li> <li> <p>角色 mldn_role_b 授权索引对象的相关操作。 <code>grant create session, create any table,insert any table to mldn_role_b ;</code></p> </li> <li>查看角色权限 role_sys_privs <code>select * from role_sys_privs where role in ('MLDN_ROLE_A','MLDN_ROLE_B') order by role ;</code></li> </ul> <h3>为用户授予角色</h3> <ul> <li> <p>将角色授予用户</p> <pre><code>grant mldn_role_a to mldnuser; grant mldn_role_a,mldn_role_b to mldnjava;</code></pre> </li> <li>查看当前用户的权限，授权后，要重新登陆 <pre><code>connect mldnuser/oracle col privilege for A30 ; select * from session_privs ;</code></pre></li> </ul> <h3>修改角色及回收角色权限</h3> <ul> <li> <p>修改角色密码</p> <pre><code>alter role mldn_role_a identified by hellomldn; alter role mldn_role_b not identified;</code></pre> </li> <li> <p>通过角色回收权限 <code>revoke create session from mldn_role_a ;</code></p> </li> <li> <p>查看用户拥有的权限 <code>SELECT * FROM dba_sys_privs ;</code></p> </li> <li> <p>查看用户拥有的角色 <code>select * FROM dba_role_privs ;</code></p> </li> <li>查看角色拥有的权限 <code>SELECT * FROM dba_sys_privs ;</code></li> </ul> <h3>删除角色</h3> <p><code>drop role mldn_role_b;</code></p> <h3>预定义角色</h3> <ul> <li>授予用户最典型的权限(connect)、授予开发人员的权限(resource)</li> <li> <p>方便的做法是直接把这两个权限授予用户 <code>grant connect,resource to mldnuser;</code></p> </li> <li>查看这两个角色的权限 <code>select * from role_sys_privs where role in ('CONNECT','RESOURCE') order by role ;</code></li> </ul> <h2>小结</h2> <ul> <li>oracle中用户、角色全部属于oracle对象，可以使用create 创建，alter 修改，drop删除。</li> <li>概要文件定义了用户登陆后相关操作配置，通过概要文件，可以实现用户的控制。</li> <li>oracle中的权限分为系统权限、对象权限，如果要操作前必须使用grant为用户分配权限，使用revoke回收用户权限。</li> <li>多个权限可以通过角色进行统一管理，角色包含若干个权限。</li> <li>为方便管理，可以直接使用预定义角色connect、resource两个角色进行授权。</li> </ul>