Oracle数据库个人整理常用的表空间、用户、授权操作

2、用户相关sql

<span style="color: #008080">--</span><span style="color: #008080"> 新建用户 test_user/test_password，指定表空间为test_tablespace_name</span>
<span style="color: #0000ff">create</span> <span style="color: #ff00ff">user</span> test_user identified <span style="color: #0000ff">by</span> test_password <span style="color: #0000ff">default</span> tablespace test_tablespace_name <span style="color: #0000ff">temporary</span> tablespace <span style="color: #0000ff">temp</span><span style="color: #000000">;
</span><span style="color: #008080">--</span><span style="color: #008080"> 修改用户密码</span>
<span style="color: #0000ff">ALTER</span> <span style="color: #ff00ff">USER</span> test_user identified <span style="color: #0000ff">by</span> <span style="color: #800000; font-weight: bold">123456</span><span style="color: #000000">;
</span><span style="color: #008080">--</span><span style="color: #008080"> 删除用户</span>
<span style="color: #0000ff">DROP</span> <span style="color: #ff00ff">USER</span> test_user <span style="color: #0000ff">CASCADE</span><span style="color: #000000">;
</span><span style="color: #008080">--</span><span style="color: #008080"> 锁定用户</span>
<span style="color: #0000ff">alter</span> <span style="color: #ff00ff">user</span><span style="color: #000000"> test_user account lock;
</span><span style="color: #008080">--</span><span style="color: #008080"> 解锁用户</span>
<span style="color: #0000ff">alter</span> <span style="color: #ff00ff">user</span><span style="color: #000000"> test_user account unlock;
</span><span style="color: #008080">--</span><span style="color: #008080"> 查询用户信息</span>
<span style="color: #0000ff">select</span> <span style="color: #808080">*</span> <span style="color: #0000ff">from</span><span style="color: #000000"> all_users;
</span><span style="color: #008080">--</span><span style="color: #008080"> 更详细的用户信息</span>
<span style="color: #0000ff">select</span> <span style="color: #808080">*</span> <span style="color: #0000ff">from</span> dba_users;

3、授权相关sql

<span style="color: #008080">--</span><span style="color: #008080"> 授予test_user用户dba权限</span>
<span style="color: #0000ff">grant</span> resource, dba, connect, <span style="color: #0000ff">create</span> <span style="color: #808080">any</span> <span style="color: #0000ff">table</span>, <span style="color: #0000ff">create</span> <span style="color: #808080">any</span> <span style="color: #0000ff">index</span>, <span style="color: #0000ff">create</span> <span style="color: #808080">any</span> sequence, unlimited tablespace <span style="color: #0000ff">to</span><span style="color: #000000"> test_user;
</span><span style="color: #008080">--</span><span style="color: #008080"> 撤销test_user用户dba权限</span>
<span style="color: #0000ff">REVOKE</span> resource, dba, connect, <span style="color: #0000ff">create</span> <span style="color: #808080">any</span> <span style="color: #0000ff">table</span>, <span style="color: #0000ff">create</span> <span style="color: #808080">any</span> <span style="color: #0000ff">index</span>, <span style="color: #0000ff">create</span> <span style="color: #808080">any</span> sequence, unlimited tablespace <span style="color: #0000ff">FROM</span><span style="color: #000000"> test_user;
</span><span style="color: #008080">--</span><span style="color: #008080">查看当前用户的系统权限</span>
<span style="color: #0000ff">select</span> <span style="color: #808080">*</span> <span style="color: #0000ff">from</span><span style="color: #000000"> user_sys_privs;
</span><span style="color: #008080">--</span><span style="color: #008080">查看当前用户的对象权限</span>
<span style="color: #0000ff">select</span> <span style="color: #808080">*</span> <span style="color: #0000ff">from</span><span style="color: #000000"> user_tab_privs;
</span><span style="color: #008080">--</span><span style="color: #008080">查看当前用户的所有角色</span>
<span style="color: #0000ff">select</span> <span style="color: #808080">*</span> <span style="color: #0000ff">from</span> user_role_privs;

yexiangyang

moyyexy@gmail.com

Oracle数据库个人整理常用的表空间、用户、授权操作

标签：max   weight   创建表   log   unlock   ext   nts   .com   contents