Oracle数据库管理 DBA必会知识点(二)

select * from user_ts_quotas;
TABLESPACE_NAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS DRO
------------------------------ ---------- ---------- ---------- ---------- ---
USERS 0 -1 0 -1 NO
EXAMPLE 0 1048576 0 128 NO

-----取消用户的表空间配额限制
SQL> alter user yuanlei quota unlimited on users;
User altered.
SQL> alter user yuanlei quota unlimited on example;
User altered.
SQL> select * from dba_ts_quotas;
TABLESPACE_NAME USERNAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS DRO
------------------------------ ------------ ---------- ---------- ---------- ---------- ---
SYSAUX OLAPSYS 7667712 -1 936 -1 NO
SYSAUX SYSMAN 76939264 -1 9392 -1 NO
SYSAUX FLOWS_FILES 0 -1 0 -1 NO
EXAMPLE YUANLEI 0 -1 0 -1 NO
USERS YUANLEI 0 -1 0 -1 NO
SYSAUX APPQOSSYS 0 -1 0 -1 NO
6 rows selected.

----锁定用户
SQL> alter user yuanlei account lock;
----解锁用户
SQL> alter user yuanlei account unlock;
----强制用户修改密码
SQL> alter user yuanlei password expire;
User altered.

----删除用户
SQL> drop user yuanlei;
User dropped.
如果用户模式非空
drop user yuanlei cascade;

查看所有系统权限
select * from system_privilege_map;
查看所有对象权限
select * from table_privilege_map;
查看用户的系统权限
SELECT * FROM DBA_SYS_PRIVS
SELECT * FROM USER_SYS_PRIVS;
查看用户对象权限
SELECT * FROM DBA_TAB_PRIVS;
SELECT * FROM ALL_TAB_PRIVS;
SELECT * FROM USER_TAB_PRIVS;

----sys用户下查看所有用户和角色的系统权限授予情况
select grantee,count(*) from dba_sys_privs
group by grantee order by grantee;
----当前用户下查看当前用户拥有的系统权限
SQL> select * from user_sys_privs;
USERNAME PRIVILEGE ADM
------------------------------ --------------------- ---
SCOTT SELECT ANY DICTIONARY NO
SCOTT UNLIMITED TABLESPACE NO
----查看当前用户授给其他用户的对象权限
SQL> select * from user_tab_privs;
GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRA HIE
---------- ---------- ---------- ---------- ---------- --- ---
ORACLE SCOTT EMP SCOTT SELECT NO NO

-----连带管理系统权限，权限转移后权限不会被级联回收
grant select any table to u1 with admin option;
-----对象权限会级联回收
grant delete on emp to u1 with grant option;

-----查看所有角色
SQL>select * from dba_roles;
-----查看莫一个角色拥有的系统权限
SQL> select * from dba_sys_privs where grantee='DBA';
SQL> select * from role_sys_privs where role='RESOURCE';
SQL> select * from role_sys_privs where role in('CONNECT','RESOURCE');
SQL> select * from dba_sys_privs where grantee in('CONNECT','RESOURCE');

-----查看角色之间的嵌套关系和所授予的用户
SQL> select * from dba_role_privs order by 2;

-----修改用户所最大拥有的角色个数
SQL> alter system set max_enabled_roles=148 scope=spfile;

System altered.

----查看角色属性
SQL> select * from role_tab_privs where role='R1';

ROLE OWNER TABLE_NAME COLUMN_NAME PRIVILEGE GRA
---------- ---------- --------------- --------------- ------------ ---
R1 SC