1.查看所有用户:
select * from dba_users;
select * from all_users;
select * from user_users;
2.查看用户或角色系统权限(直接赋值给用户或角色的系统权限):
select * from dba_sys_privs;
select * from user_sys_privs;
3.查看角色(只能查看登陆用户拥有的角色)所包含的权限
sql>select * from role_sys_privs;
4.查看用户对象权限:
select * from dba_tab_privs;
select * from all_tab_privs;
select * from user_tab_privs;
5.查看所有角色:
select * from dba_roles;
6.查看用户或角色所拥有的角色:
select * from dba_role_privs;
select * from user_role_privs;
7.查看哪些用户有sysdba或sysoper系统权限(查询时需要相应权限)
select * from V$PWFILE_USERS
比如我要查看用户 wutong的拥有的权限:
SQL> select * from dba_sys_privs where grantee=‘WUTONG’;
GRANTEE PRIVILEGE ADMIN_OPTION
WUTONG CREATE TRIGGER NO
WUTONG UNLIMITED TABLESPACE NO
比如我要查看用户 wutong的拥有的角色:
SQL> select * from dba_role_privs where grantee=‘WUTONG’;
GRANTEE GRANTED_ROLE ADMIN_OPTION DEFAULT_ROLE
WUTONG DBA NO YES
查看一个用户所有的权限及角色
select privilege from dba_sys_privs where grantee=‘WUTONG’
union
select privilege from dba_sys_privs where grantee in
(select granted_role from dba_role_privs where grantee=‘WUTONG’ );
8.用户操作
–创建用户
create user 用户 identified by pass1
default tablespace users
temporary tablespace temp;
–修改用户配额
alter user 用户 quota 20m on users;
–修改用户密码
alter user 用户 identified by pass2;
–解锁用户
alter user 用户 account unlock;
–删除用户
drop user 用户 cascade;
–授权用户
–grant sys_privi|role to user|role|public with admin option
grant connect to 用户;
grant resource to 用户;
grant dba to 用户;
grant exp_full_database to 用户;
grant imp_full_database to 用户;
–回收用户权限
–revoke sys_privi|role from user|role|public
revoke dba from用户;
–对象授权
grant select,insert,delete,update on BBS.BBSDETAIL to 用户;
–回收对象授权
–revoke obj_privi|all on schema.object from user|role|public cascade constraints
revoke delete,update,insert,select on BBS.BBSDETAIL from 用户;