一、查询用户拥有哪里权限：
 SQL> select * from dba_role_privs ;  ---DBA_ROLE_PRIVS describes the roles granted to all users and roles in the database　
　　
 SQL> select * from dba_sys_privs;   ---DBA_SYS_PRIVS describes system privileges granted to users and roles

 SQL> select * from role_sys_prive;   ---ROLE_SYS_PRIVS describes system privileges granted to roles.

 SQL> select * from role_role_privs;   ---ROLE_ROLE_PRIVS describes the roles granted to other roles. Information is provided only about roles to which the user has access.

1、查看用户的system privilege:

 SQL>  select * from dba_sys_privs where grantee='TEST';

 no rows selected

2、查看test 的role :
 SQL> SELECT * FROM  dba_role_privs  where grantee='TEST';

 GRANTEE              GRANTED_ROLE         ADM DEF
 -------------------- -------------------- --- ---
 TEST                 USER_ROLE            NO  YES

3、查看role　privileges:

 方法一：
 SQL> select * from dba_sys_privs where GRANTEE='USER_ROLE';
 
 GRANTEE              PRIVILEGE                                ADM
 -------------------- ---------------------------------------- ---
 USER_ROLE            CREATE VIEW                              NO
 USER_ROLE            CREATE CLUSTER                           NO
 USER_ROLE            CREATE INDEXTYPE                         NO
 USER_ROLE            CREATE TYPE                              NO
 USER_ROLE            CREATE OPERATOR                          NO
 USER_ROLE            CREATE TABLE                             NO
 USER_ROLE            CREATE SESSION                           NO
 USER_ROLE            CREATE TRIGGER                           NO
 USER_ROLE            CREATE PROCEDURE                         NO
 USER_ROLE            CREATE SEQUENCE                          NO
 
 10 rows selected.

 方法二：
 SQL> select * from role_sys_privs where role='USER_ROLE';
 
 ROLE                           PRIVILEGE                                ADM
 ------------------------------ ---------------------------------------- ---
 USER_ROLE                      CREATE CLUSTER                           NO
 USER_ROLE                      CREATE VIEW                              NO
 USER_ROLE                      CREATE TYPE                              NO
 USER_ROLE                      CREATE INDEXTYPE                         NO
 USER_ROLE                      CREATE TABLE                             NO
 USER_ROLE                      CREATE OPERATOR                          NO
 USER_ROLE                      CREATE SESSION                           NO
 USER_ROLE                      CREATE TRIGGER                           NO
 USER_ROLE                      CREATE PROCEDURE                         NO
 USER_ROLE                      CREATE SEQUENCE                          NO
 
 10 rows selected.

4、查看role 下面有什么role权限:

 SQL> select role,granted_role,admin_option from role_role_privs where role='USER_ROLE';

 ROLE                           GRANTED_ROLE                   ADM
 ------------------------------ ------------------------------ ---
 USER_ROLE                      RESOURCE                       NO
 USER_ROLE                      CONNECT                        NO

二、创建 role of user_role:

把RESOURCE,connect  权限给新role (user_role) :
select 'grant '||privilege||' to user_role;' from role_sys_privs where role='RESOURCE';

select 'grant '||privilege||' to user_role;' from role_sys_privs where role='CONNECT';

grant create view to user_role;       

三、查看数据库账户属性                               
 查看user info:
   select * from dba_users;    
     
 查看user quota:
    select * from dba_ts_quotas;
    
 查看该用户拥有的模式对象：
   select owner,object_name from dba_objects where wner = 'TEST';
   
   
 查看当前登录数据库的用户:                     
        select username from v$session where type='USER';
 
 查找用户相关会话：
  select sid,serial#,username from v$session;

       终止要被删除的用户相关的会话：
  alter system kill session '133,45357';