数据库用户账号管理及权限管理

/查询所有用户登录记录/
SELECT sid,serial#,A.USERNAME 数据库账号,A.STATUS 状态,A.MACHINE 登录设备号,A.OSUSER 电脑用户,B.EMPNAME 姓名,
B.STATUS 状态,A.PROGRAM 使用程序,A.MODULE ,A.ACTION,A.LOGON_TIME 登录时间 FROM v$session a
LEFT JOIN LWPRO.V_EHR_INFO B ON A.OSUSER=B.EMPID
where (A.PROGRAM=‘w3wp.exe’) ORDER BY A.MACHINE;

/查询所有用户登录记录/
SELECT sid,serial#,A.USERNAME 数据库账号,A.STATUS 状态,A.MACHINE 登录设备号,A.OSUSER 电脑用户,B.EMPNAME 姓名,
B.STATUS 状态,A.PROGRAM 使用程序,A.MODULE ,A.ACTION,A.LOGON_TIME 登录时间 FROM v$session a
LEFT JOIN LWPRO.V_EHR_INFO B ON A.OSUSER=B.EMPID
where (A.PROGRAM=‘plsqldev.exe’ or A.PROGRAM=‘navicat.exe’) ORDER BY A.MACHINE;

/*查询lwpro登录记录*/

SELECT sid,serial#,A.USERNAME 数据库账号,A.STATUS 状态,A.MACHINE 登录设备号,A.OSUSER 电脑用户,B.EMPNAME 姓名,
B.STATUS 状态,A.PROGRAM 使用程序,A.MODULE ,A.ACTION,A.LOGON_TIME 登录时间 FROM v$session a
LEFT JOIN LWPRO.V_EHR_INFO B ON A.OSUSER=B.EMPID
where (A.PROGRAM=‘plsqldev.exe’ or A.PROGRAM=‘navicat.exe’) AND A.USERNAME=‘LWPRO’ ORDER BY A.MACHINE;

 /*查询lwpro登录记录*/

SELECT sid,serial#,A.USERNAME 数据库账号,A.STATUS 状态,A.MACHINE 登录设备号,A.OSUSER 电脑用户,B.REALNAME,
A.PROGRAM 使用程序,A.MODULE ,A.ACTION,A.LOGON_TIME 登录时间 FROM v$session a
LEFT JOIN LWPRO.S_USER B ON A.OSUSER=B.NAME
where A.PROGRAM=‘navicat.exe’ ORDER BY A.MACHINE;

select T.SQL_ID,T.SQL_TEXT,PARSING_SCHEMA_NAME,T.MODULE from v$sqlarea t WHERE T.LAST_ACTIVE_TIME >= TRUNC(SYSDATE) ;–GROUP BY PARSING_SCHEMA_NAME

/*查询lwpro登录记录*/

SELECT A.USERNAME 数据库账号,A.PROGRAM 使用程序,COUNT(distinct A.MACHINE) 客户端使用数
FROM v$session a
LEFT JOIN LWPRO.S_USER B ON A.OSUSER=B.NAME
where a.USERNAME IS NOT NULL
GROUP BY A.USERNAME ,A.PROGRAM ORDER BY A.USERNAME;

SELECT COUNT(distinct A.MACHINE) 客户端使用数 FROM v$session a;

-----4、kill对应的session;
alter system kill session ‘9591,63901’;

alter system kill session ‘15802,55282’;

alter system kill session ‘23657,61022’;

–命令：grant xxx权限 on Table to USER
grant select,insert,update,delete,all on 表名 to 用户名

–例如：将test表的查询权限赋予tom这个用户
grant select on test to tom;

–1.查看所有用户：

SELECT * FROM sys.user$ u;

select * from dba_users;

select A.USERNAME,B.EMPNAME,A.CREATED from all_users A
LEFT JOIN lwdw.HREmployeexk@mes2lwdw B ON A.USERNAME=‘MS’||B.EMPID WHERE USERNAME LIKE ‘MS%’;

select * from user_users;

–2.查看用户或角色系统权限(直接赋值给用户或角色的系统权限)：

select * from dba_sys_privs where grantee=‘MS2105258004’;

select * from user_sys_privs; --(查看当前用户所拥有的权限)

–3.查看角色(只能查看登陆用户拥有的角色)所包含的权限

select * from role_sys_privs;

–4.查看用户对象权限：

select * from dba_tab_privs where GRANTEE=‘LWPRO’;

select * from all_tab_privs;

select * from user_tab_privs;

–5.查看所有角色：
select * from dba_roles;

–6.查看用户或角色所拥有的角色：

select * from dba_role_privs WHERE GRANTEE=‘LWPRO’;

select * from user_role_privs;

–7.查看哪些用户有sysdba或sysoper系统权限(查询时需要相应权限)

select * from V$PWFILE_USERS

–8.SqlPlus中查看一个用户所拥有权限

select * from dba_sys_privs where grantee=‘LWPRO’; --其中的username即用户名要大写才行
–9.修改指定用户密码
alter user AA IDENTIFIED BY lw610824;