記錄登錄oracle的人員信息

1.創建用戶inldba.授dba權限.
2.在inldba用戶下創建表:logon_log,logonlog,z_error.
3.用sys用戶登錄,授相應表的select權限給inldba.
4.創建procedure:logon和p_logon_log
5.創建触發器trigger:logon_trig
6.創建4小時執行一次procedure的job.
附件:
創建用戶
CREATE USER INLDBA
  IDENTIFIED BY VALUES '9FCEE25A4CF59DE3'
  DEFAULT TABLESPACE USERS
  TEMPORARY TABLESPACE TEMP
  PROFILE DEFAULT
  ACCOUNT UNLOCK;
  -- 3 Roles for INLDBA
  GRANT DBA TO INLDBA;
  GRANT RESOURCE TO INLDBA;
  GRANT SELECT_CATALOG_ROLE TO INLDBA;
  ALTER USER INLDBA DEFAULT ROLE DBA, SELECT_CATALOG_ROLE;
  -- 3 System Privileges for INLDBA
  GRANT CREATE SESSION TO INLDBA;
  GRANT SELECT ANY DICTIONARY TO INLDBA;
  GRANT UNLIMITED TABLESPACE TO INLDBA;
  -- 4 Object Privileges for INLDBA
    GRANT SELECT ON SYS.V_$INSTANCE TO INLDBA;
    GRANT SELECT ON SYS.V_$PARAMETER TO INLDBA;
    GRANT SELECT ON SYS.V_$PROCESS TO INLDBA;
    GRANT SELECT ON SYS.V_$SESSION TO INLDBA;

創建表:
表logon_log:
CREATE TABLE INLDBA.LOGON_LOG
(
  FETCH_TIME  VARCHAR2(16 BYTE),
  SID         NUMBER,
  SERIAL#     NUMBER,
  USERNAME    VARCHAR2(50 BYTE),
  SCHEMANAME  VARCHAR2(50 BYTE),
  OSUSER      VARCHAR2(50 BYTE),
  MACHINE     VARCHAR2(100 BYTE),
  PROGRAM     VARCHAR2(100 BYTE),
  LOGON_TIME  DATE
)
TABLESPACE USERDATA
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          80K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOLOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
NOMONITORING;

CREATE UNIQUE INDEX INLDBA.LOGON_LOG_PK ON INLDBA.LOGON_LOG
(SID, SERIAL#, LOGON_TIME)
LOGGING
TABLESPACE USERDATA
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          80K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;

ALTER TABLE INLDBA.LOGON_LOG ADD (
  CONSTRAINT LOGON_LOG_PK
 PRIMARY KEY
 (SID, SERIAL#, LOGON_TIME)
    USING INDEX
    TABLESPACE USERDATA
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          80K
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
               ));

表logonlog:
CREATE TABLE INLDBA.LOGONLOG
(
  SID              NUMBER,
  SERIAL#          NUMBER,
  USERNAME         VARCHAR2(30 BYTE),
  SCHEMANAME       VARCHAR2(30 BYTE),
  OSUSER           VARCHAR2(30 BYTE),
  MACHINE          VARCHAR2(64 BYTE),
  PROGRAM          VARCHAR2(48 BYTE),
  LOGON_TIME       DATE,
  TRACE_FILE_NAME  VARCHAR2(500 BYTE),
  LOGOUTFLAG       CHAR(1 BYTE)
)
TABLESPACE USERDATA
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          80K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
NOMONITORING;

GRANT SELECT ON INLDBA.LOGONLOG TO PUBLIC;

表z_error:
CREATE TABLE INLDBA.Z_ERROR
(
  TIMEKEY   VARCHAR2(20 BYTE)                   NOT NULL,
  PROCNAME  VARCHAR2(30 BYTE)                   NOT NULL,
  ERRNUM    NUMBER,
  ERRMSG    VARCHAR2(100 BYTE)
)
TABLESPACE USERDATA
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          80K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
NOMONITORING;

創建procedure:
###procedure:logon
CREATE OR REPLACE PROCEDURE INLDBA.LOGON
AS
   V_SID               v$session.sid%TYPE;
   V_SERIAL            v$session.SERIAL#%TYPE;
   V_USERNAME          v$session.USERNAME%TYPE;
   V_SCHEMANAME        v$session.SCHEMANAME%TYPE;
   V_OSUSER            v$session.OSUSER%TYPE;
   V_MACHINE           v$session.MACHINE%TYPE;
   V_LOGON_TIME        DATE;
   V_PROGRAM           v$session.PROGRAM%TYPE;
   v_TRACE_FILE_NAME   VARCHAR2 (500);
   V_MESSAGE           VARCHAR2 (1000);
   V_BAD_LOGON EXCEPTION;
BEGIN
   SELECT   SID,
            SERIAL#,
            USERNAME,
            SCHEMANAME,
            OSUSER,
            MACHINE,
            CASE
               WHEN UPPER (PROGRAM) LIKE '%TOAD%' THEN 'TOAD'
               WHEN UPPER (PROGRAM) LIKE '%SQLPLUS%' THEN 'SQLPLUS'
               WHEN UPPER (PROGRAM) LIKE '%PLSQLDEV%' THEN 'PLSQLDEV'
            END,
            LOGON_TIME
     INTO   v_SID,
            v_SERIAL,
            v_USERNAME,
            v_SCHEMANAME,
            v_OSUSER,
            v_MACHINE,
            v_PROGRAM,
            v_LOGON_TIME
     FROM   v$session
    WHERE   AUDSID = SYS_CONTEXT ('USERENV', 'SESSIONID') AND ROWNUM < 2;

   IF UPPER (v_PROGRAM) IN ('TOAD', 'SQLPLUS', 'PLSQLDEV')
      AND v_USERNAME NOT IN
               ('SYSTEM',
                'SYS',
                'INLDBA',
                'OUTLN',
                'DBSNMP',
                'WMSYS',
                'PERFSTAT',
                'PCBAPURGE')
       AND UPPER(V_OSUSER) NOT IN('SMMON')        
   THEN
      --SYS.DBMS_SYSTEM.set_sql_trace_in_session (v_sid, v_serial, TRUE);

      SELECT      a.VALUE
               || b.symbol
               || c.instance_name
               || '_ora_'
               || d.spid
               || '.trc'
                  trace_file_name
        INTO   v_trace_file_name
        FROM   (SELECT   VALUE
                  FROM   v$parameter
                 WHERE   NAME = 'user_dump_dest') a,
               (SELECT   SUBSTR (VALUE, -6, 1) symbol
                  FROM   v$parameter
                 WHERE   NAME = 'user_dump_dest') b,
               (SELECT   instance_name FROM v$instance) c,
               (SELECT   spid
                  FROM   v$session s, v$process p
                 WHERE   s.paddr = p.addr AND s.SID = v_sid) d;

      INSERT INTO LOGONLOG (SID,
                            SERIAL#,
                            USERNAME,
                            SCHEMANAME,
                            OSUSER,
                            MACHINE,
                            PROGRAM,
                            LOGON_TIME,
                            TRACE_FILE_NAME)
        VALUES   (v_SID,
                  v_SERIAL,
                  v_USERNAME,
                  v_SCHEMANAME,
                  v_OSUSER,
                  v_MACHINE,
                  v_PROGRAM,
                  v_LOGON_TIME,
                  v_trace_file_name);
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      v_message :=
            'FATAL ERROR - inldba$LOGON TRIGGER- Please Contact Your DBA!!'
         || CHR (10)
         || SQLERRM;
      RAISE_APPLICATION_ERROR (-20003, v_message);
END;
/

 

 

###procedure:p_logon_log
CREATE OR REPLACE PROCEDURE INLDBA.p_logon_log IS
proname    varchar2(30) := 'p_logon_log';
starttime    varchar2(14) := '';
err_num      number;
err_msg      varchar2(100);

BEGIN

SELECT to_char(SYSDATE, 'yyyymmddhh24miss') INTO starttime FROM dual;

insert into inldba.logon_log
SELECT TO_CHAR (TRUNC (SYSDATE, 'MI'), 'YYYY-MM-DD HH24:MI') fetch_time, SID,
       serial#, username, schemaname, osuser, machine, program, logon_time
  FROM v$session
 WHERE UPPER (program) = 'TOAD.EXE'
   AND schemaname NOT IN ('SYS', 'SYSTEM', 'INLDBA')
   AND (SID, serial#, logon_time) NOT IN (SELECT SID, serial#, logon_time
                                            FROM inldba.logon_log);
  

EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    err_num := sqlcode;
    err_msg := substr(sqlerrm, 1, 100);
 
    --Writ error Log
    INSERT INTO inldba.z_error
      (timekey, procname, errnum, errmsg)
    VALUES
      (starttime, proname, err_num, err_msg);

END p_logon_log;
/

創建触發器trigger:logon_trig

CREATE OR REPLACE TRIGGER INLDBA.LOGON_TRIG
AFTER LOGON
ON DATABASE
begin
logon;
end;
/

4小時執行一次的job

DECLARE
  X NUMBER;
BEGIN
  SYS.DBMS_JOB.SUBMIT
  ( job       => X
   ,what      => 'INLDBA.P_LOGON_LOG;'
   ,next_date => to_date('17/08/2010 21:49:07','dd/mm/yyyy hh24:mi:ss')
   ,interval  => 'SYSDATE+240/1440 '
   ,no_parse  => FALSE
  );
  SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
COMMIT;
END;
/

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25402324/viewspace-715410/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25402324/viewspace-715410/