Oracle 用户管理

--查看所有用户
select username,user_id,account_status,default_tablespace from dba_users where account_status='OPEN' order by user_id;
select * from dba_users;

--查看哪些用户是sysdba
select * from V$PWFILE_USERS;

--查看哪些用户是dba
select * from dba_role_privs where granted_role='DBA';

--查看用户授予的权限
select * from dba_role_privs where grantee='用户名大写';

--查看用户现有权限
SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE='ANONYMOUS';

--新建用户
create user 用户名 identified by 密码;

--授予用户普通权限
grant connect,resource to XXX;

--授予DBA
grant dba to 用户名;

--查看所有角色
select * from dba_roles;

--查看指定用的角色
SELECT GRANTED_ROLE, DEFAULT_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE='用户名大写';

--查看dba角色有哪些系统权限
select * from role_sys_privs where role='DBA';
--查看rosource角色有哪些权限，system才能查
select * from role_sys_privs where role='RESOURCE';

--删除用户相关权限
revoke connect from XXX;

--删除用户
drop user XXXX cascade;

查询用户自己的权限：
select * from user_sys_privs;

--查看当前用户有哪些表：
select * from user_tab_comments;

--查看所有用户对应的表
select * from all_tables;

--查看指定用户对应的表
select * from all_tables where owner = 'SCOTT';
select t.TABLE_NAME from dba_tables t where t.OWNER='SCOTT';

--用户解锁
alter user scott account unlock;

--用户改口令
alter user scott identified by "tiger";

--切换用户
conn scott/tiger@orcl;

--查看当前用户会话
select user#,username,status,terminal from v$session where user#!=0;

--查一个用户所有表信息
column table_name format a30
column tablespace_name format a25
column owner format a15
select table_name,tablespace_name,owner from dba_tables where owner='SCOTT';

--查看库启动时间
select instance_number,instance_name,host_name,status,startup_time from v$instance;

--查所有使用中的用户对哪些表有哪些权限
select a.role,a.owner,a.table_name,a.privilege,b.default_tablespace from role_tab_privs a
left join dba_users b
on a.owner=b.username
and b.account_status = 'OPEN'
order by a.owner;