记录用户登录操作

记录登录成功的用户信息到一个测试表：

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