Oracle用户管理

锁用户

alter user UCR_CEN1 ACCOUNT LOCK;
alter user UCR_CEN1 ACCOUNT UNLOCK;

输错密码无限制

alter profile default limit failed_login_attempts unlimited;
select * from DBA_PROFILES where resource_name='FAILED_LOGIN_ATTEMPTS';

用户密码重试次数

select name, lcount from sys.user$ where name like 'U%';

重置密码

alter user scott identified by 123456;

查看当前用户具有哪些系统权限

select * from user_sys_privs;

查看当前用户具有哪些对象权限

select * frm user_tab_privs;

查看当前用户具有哪些控制到列的权限

select * from user_col_privs;

用户赋权

grant create table,unlimited tablespace to public
grant all on mywangwu to public

查看用户清单

select user_id,USERNAME,ACCOUNT_STATUS,CREATED from dba_users order by CREATED

DROP用户下所有对象

drop user UOP_UIF2 cascade;

用户视图

select * from all_users; --查看所用用户
select * from all_tables; --查看所有表
select * from user_tables; --查看当前用户下有那些 表
select * from user_views; --查看当前用户下有那些 试图
select constraint_name,table_name from user_constraints; --查看当前用户下有那些 约束，已经所对应的表
select * from dictionary where table_name like 'USER%';
select * from user_sys_privs; --查看当前用户具有哪些系统权限
select * from user_tab_privs; --查看当前用户具有哪些对象权限
select * from user_col_privs; --查看当前用户具有哪些控制到列的权限

查看表权限

select * from dba_tab_privs where owner='UCR_CEN1';