ORACLE User Management

Check account status：

select username from dba_users;

 

Check account privileges：
select * from dba_role_privs;
select * from dba_sys_privs;
select * from role_sys_privs;

 

Create role：

create role READ_ONLY;

grant connect to READ_ONLY;

grant select any table to READ_ONLY;

 

Use role：

grant read_only to user;

 

Create account：

connect system/manager
Create user user50 identified by user50 default tablespace users temporary tablespace temp;
grant connect, resource to user50;

 

Create read-only account：

create user readonly identified by readonly default tablespace users temporary tablespace temp;

grant connect, select any table to readonly;

 

Delete account：

drop user <account> cascade;  //加上cascade则将用户连同其创建的东西全部删除

 

Modify

Modify password：
Alter user acc01 identified by "12345";

 

Modify the default tablespace：
Alter user acc01 default tablespace users;

Modify the temporary tablespace：
Alter user acc01 temporary tablespace temp_data;

Require accout to modify password：
Alter user acc01 password expire;

Modify lock status of account：
Alter user acc01 account lock;  // 加锁
Alter user acc01 account unlock;  // 解锁

 

Monitor

Check the session of account：
select username, sid, serial#, machine from v$session;

Terminate the session of account：
Alter system kill session 'sid, serial#';

Check SQL script of account：
select user_name, sql_text from v$open_cursor;

Properties of account：

Default is unlimited

Change by running sceipt: /$ORACLE_HOME/rdbms/admin/utlpwdmg.sql

quota setting：

alter user <account> quota 20M on <tablespace>;

role setting：

Admin Option：whether can grant the privilege to others

default：是否登录马上生效；否则需要命令生效：set role role名;

Priviliege setting：system：针对全局；不递归收回权限注意：select any table 权限，有的用户仍旧不能看字典表原因：O7_DICTIONARY_ACCESSIBILITY=falseobject：针对对象；递归收回权限

Grant select on v$ view to normal userExample: hr user want to view v$databaseLogin with sysgrant select on v_$database to hr;