记录登录成功的用户信息到一个测试表:
create table login (ip varchar(30),u_name varchar(30));
create or replace trigger logon_db_record
after logon on database
declare
ip STRING(30);
user STRING(30);
begin
SELECT SYS_CONTEXT('USERENV','SESSION_USER') into user from dual;
SELECT SYS_CONTEXT('USERENV','IP_ADDRESS') into ip from dual;
INSERT into login values(ip,user);
commit;
end;
/
只记录失败的登录到日志文件:
create or replace trigger logon_denied_to_alert
after servererror on database
declare
message varchar2(120);
IP varchar2(15);
v_os_user varchar2(80);
v_module varchar2(50);
v_action varchar2(50);
v_pid varchar2(10);
v_sid number;
begin
IF (ora_is_servererror(1017)) THEN
if sys_context('userenv', 'network_protocol') = 'tcp' then
IP := sys_context('userenv', 'ip_address');
else
select distinct sid into v_sid from sys.v_$mystat;
SELECT p.SPID
into v_pid
FROM V$PROCESS p, V$SESSION v
WHERE p.ADDR = v.PADDR
AND v.sid = v_sid;
end if;
v_os_user := sys_context('userenv', 'os_user');
dbms_application_info.READ_MODULE(v_module, v_action);
message := to_char(sysdate, 'Dy Mon dd HH24:MI:SS YYYY') ||
' logon denied from ' || nvl(IP, v_pid) || ' ' || v_os_user ||
' with ' || v_module || ' ' || v_action;
sys.dbms_system.ksdwrt(2, message);
end if;
end;
/
这往往用在有些用户程序频繁失败登陆,但不知情,进行检测用,下边看看 系统默认的一些配置信息
SQL> set pagesize 100
SQL> SELECT * FROM dba_profiles WHERE profile='DEFAULT';
PROFILE RESOURCE_NAME RESOURCE LIMIT
---------- -------------------------------- -------- ----------
DEFAULT COMPOSITE_LIMIT KERNEL UNLIMITED
DEFAULT SESSIONS_PER_USER KERNEL UNLIMITED
DEFAULT CPU_PER_SESSION KERNEL UNLIMITED
DEFAULT CPU_PER_CALL KERNEL UNLIMITED
DEFAULT LOGICAL_READS_PER_SESSION KERNEL UNLIMITED
DEFAULT LOGICAL_READS_PER_CALL KERNEL UNLIMITED
DEFAULT IDLE_TIME KERNEL UNLIMITED
DEFAULT CONNECT_TIME KERNEL UNLIMITED
DEFAULT PRIVATE_SGA KERNEL UNLIMITED
DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 10
DEFAULT PASSWORD_LIFE_TIME PASSWORD 180
DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED
DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED
DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL
DEFAULT PASSWORD_LOCK_TIME PASSWORD 1
DEFAULT PASSWORD_GRACE_TIME PASSWORD 7
16 rows selected.
-- 比如,登录多少次数后锁定,默认10:
ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED;
注,其他一些有用的信息查询:
select SYS_CONTEXT('USERENV', 'TERMINAL') terminal,
SYS_CONTEXT('USERENV', 'LANGUAGE') language,
SYS_CONTEXT('USERENV', 'SESSIONID') sessionid,
SYS_CONTEXT('USERENV', 'INSTANCE') instance,
SYS_CONTEXT('USERENV', 'ENTRYID') entryid,
SYS_CONTEXT('USERENV', 'ISDBA') isdba,
SYS_CONTEXT('USERENV', 'NLS_TERRITORY') nls_territory,
SYS_CONTEXT('USERENV', 'NLS_CURRENCY') nls_currency,
SYS_CONTEXT('USERENV', 'NLS_CALENDAR') nls_calendar,
SYS_CONTEXT('USERENV', 'NLS_DATE_FORMAT') nls_date_format,
SYS_CONTEXT('USERENV', 'NLS_DATE_LANGUAGE') nls_date_language,
SYS_CONTEXT('USERENV', 'NLS_SORT') nls_sort,
SYS_CONTEXT('USERENV', 'CURRENT_USER') current_user,
SYS_CONTEXT('USERENV', 'CURRENT_USERID') current_userid,
SYS_CONTEXT('USERENV', 'SESSION_USER') session_user,
SYS_CONTEXT('USERENV', 'SESSION_USERID') session_userid,
SYS_CONTEXT('USERENV', 'PROXY_USER') proxy_user,
SYS_CONTEXT('USERENV', 'PROXY_USERID') proxy_userid,
SYS_CONTEXT('USERENV', 'DB_DOMAIN') db_domain,
SYS_CONTEXT('USERENV', 'DB_NAME') db_name,
SYS_CONTEXT('USERENV', 'HOST') host,
SYS_CONTEXT('USERENV', 'OS_USER') os_user,
SYS_CONTEXT('USERENV', 'EXTERNAL_NAME') external_name,
SYS_CONTEXT('USERENV', 'IP_ADDRESS') ip_address,
SYS_CONTEXT('USERENV', 'NETWORK_PROTOCOL') network_protocol,
SYS_CONTEXT('USERENV', 'BG_JOB_ID') bg_job_id,
SYS_CONTEXT('USERENV', 'FG_JOB_ID') fg_job_id,
SYS_CONTEXT('USERENV', 'AUTHENTICATION_TYPE') authentication_type,
SYS_CONTEXT('USERENV', 'AUTHENTICATION_DATA') authentication_data
from dual