--查询帐户的状态
select username,account_status from dba_users
where username='SCOTT';
--创建用户
create user john identified by john
default tablespace users
quota 5m | unlimited on users
--为新用户授予权限
grant connect to john
grant resouce to john
alter user john quota 10m on users; --用户配额
select * from dba_ts_quotas; --查询配额
alter user john password expire; --帐户状态过期
alter user john account lock; --锁定账户
alter user john account unlock; --解锁账户
--授予用户权限
grant create session to john;
grant create session to john with admin option;
grant select,insert,update on scott.bmb to john;
grant select on scott.gzb to public;
--查询用户角色权限
SQL> select tt.granted_role from dba_role_privs tt
2 where tt.grantee='SCOTT';
GRANTED_ROLE
------------------------------
RESOURCE
CONNECT
--查询用户系统权限
SQL> select tt.privilege from dba_sys_privs tt
2 where tt.grantee='SCOTT';
PRIVILEGE
----------------------------------------
UNLIMITED TABLESPACE
--删掉用户(用户在连接状态)
select sid,serial#,username from v$session; --查询用户会话进程
alter system kill session '150,974'; --杀掉该进程
drop user john cascade； --用户底下有对象的时候要加cascade
--查询用户对象
select owner,object_name from dba_objects
where owner like 'JOHN';
--查询会话
select username from v$session
where type='USER';
--创建角色
create role role1;
create role role2 identified by role;
create role role3 identified globally;
create role role4 identified externally;
--授权给角色
grant create table,create view,create session to role5;
grant select,insert,update on scott.bmb to role5;
--删除角色
drop role role1;
--查询角色的权限
select * from dba_sys_privs
where grantee='ROLE5';
--查看概要文件
select username,profile from dba_users;
col resource_name format a30
col limit format a10
select resource_name,limit from dba_profiles
where profile='DEFAULT' and resource_type='PASSWORD';