记录用户登录操作

来源：互联网发布：淘宝在台湾编辑：程序博客网时间：2024/05/18 02:28

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

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_alertafter servererror on databasedeclare  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 100SQL> SELECT * FROM dba_profiles WHERE profile='DEFAULT';PROFILE    RESOURCE_NAME                    RESOURCE LIMIT---------- -------------------------------- -------- ----------DEFAULT    COMPOSITE_LIMIT                  KERNEL   UNLIMITEDDEFAULT    SESSIONS_PER_USER                KERNEL   UNLIMITEDDEFAULT    CPU_PER_SESSION                  KERNEL   UNLIMITEDDEFAULT    CPU_PER_CALL                     KERNEL   UNLIMITEDDEFAULT    LOGICAL_READS_PER_SESSION        KERNEL   UNLIMITEDDEFAULT    LOGICAL_READS_PER_CALL           KERNEL   UNLIMITEDDEFAULT    IDLE_TIME                        KERNEL   UNLIMITEDDEFAULT    CONNECT_TIME                     KERNEL   UNLIMITEDDEFAULT    PRIVATE_SGA                      KERNEL   UNLIMITEDDEFAULT    FAILED_LOGIN_ATTEMPTS            PASSWORD 10DEFAULT    PASSWORD_LIFE_TIME               PASSWORD 180DEFAULT    PASSWORD_REUSE_TIME              PASSWORD UNLIMITEDDEFAULT    PASSWORD_REUSE_MAX               PASSWORD UNLIMITEDDEFAULT    PASSWORD_VERIFY_FUNCTION         PASSWORD NULLDEFAULT    PASSWORD_LOCK_TIME               PASSWORD 1DEFAULT    PASSWORD_GRACE_TIME              PASSWORD 716 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

阅读全文

0 0