pkg_sys_user_security.bdy

用C＃开发的一套数据仓库的代码。（C＃＋ORACLE）

                               'SYSINFO',
                               'SUCCESS',
                               'CHANGE_PW',
                               P_OUT_MSG,
                               P_OUT_NUM);
    -------------------------------------- 
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      ROLLBACK;
      P_OUT_NUM := -20000;
      P_OUT_MSG := 'INVALID USERNAME OR PASSWORD!';
      PKG_SYS_LOG.SP_LOG_ACTIVITY(P_UPDATEBYUSER_CODE,
                                 'SYSINFO',
                                 'ERROR',
                                 'CHANGE_PW',
                                 P_OUT_MSG,
                                 P_OUT_NUM);
    WHEN OTHERS THEN
      ROLLBACK;
      P_OUT_NUM := SQLCODE;
      P_OUT_MSG := SUBSTR(SQLERRM, 1, 500);
      PKG_SYS_LOG.SP_LOG_ACTIVITY(P_UPDATEBYUSER_CODE,
                                 'SYSINFO',
                                 'ERROR',
                                 'CHANGE_PW',
                                 P_OUT_MSG,
                                 P_OUT_NUM);
  END;

  /* ******************************************************************************** */
  PROCEDURE SP_RESET_PASSWORD(P_OUT_NUM           OUT NUMBER,
                              P_OUT_MSG           OUT VARCHAR2,
                              P_USER_ID         IN NUMBER,
                              P_NEW_PASSWORD      IN VARCHAR2,
                              P_UPDATEBYUSER_CODE IN VARCHAR2) AS
    V_ROWID ROWID;
    V_USER_CODE VARCHAR2(20);
  BEGIN
    SELECT ROWID,USER_CODE INTO V_ROWID,V_USER_CODE 
    FROM SYS_USER 
    WHERE USER_ID = P_USER_ID FOR UPDATE;
  
    UPDATE SYS_USER
       SET PASSWORD = GET_HASH(V_USER_CODE, P_NEW_PASSWORD),
           UPDATEDATE = SYSDATE,
           UPDATEBYUSER_CODE = UPPER(P_UPDATEBYUSER_CODE)
     WHERE ROWID = V_ROWID;
    COMMIT;
  
    ------------------------------------- Standard Return Block   - Success   
    P_OUT_NUM := 0;
    P_OUT_MSG := 'RESET PASSWOR SUCCESS:' || V_USER_CODE;
  
    PKG_SYS_LOG.SP_LOG_ACTIVITY(P_UPDATEBYUSER_CODE,
                               'SYSINFO',
                               'SUCCESS',
                               'RESET_PW',
                               P_OUT_MSG,
                               P_OUT_NUM);
    --------------------------------------    
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      ROLLBACK;
      P_OUT_NUM := -20000;
      P_OUT_MSG := 'INVALUD USER NAME';
      PKG_SYS_LOG.SP_LOG_ACTIVITY(P_UPDATEBYUSER_CODE,
                                 'SYSINFO',
                                 'ERROR',
                                 'RESET_PW',
                                 P_OUT_MSG,
                                 P_OUT_NUM);
    WHEN OTHERS THEN
      ROLLBACK;
      P_OUT_NUM := SQLCODE;
      P_OUT_MSG := SUBSTR(SQLERRM, 1, 500);
      PKG_SYS_LOG.SP_LOG_ACTIVITY(P_UPDATEBYUSER_CODE,
                                 'SYSINFO',
                                 'ERROR',
                                 'RESET_PW',
                                 P_OUT_MSG,
                                 P_OUT_NUM);
  END;

  /* ******************************************************************************** */
  PROCEDURE SP_FEATURE_ACTION(P_OUT_NUM       OUT NUMBER,
                              P_OUT_MSG       OUT VARCHAR2,
                              P_OUT_ACTION    OUT VARCHAR2,
                              P_USER_CODE     IN VARCHAR2,
                              P_APPFEATURE_ID IN NUMBER) AS
  BEGIN
    SELECT (CASE WHEN APPF.VIEW_ENABLED = 'Y' THEN '1' ELSE '0' END)||
           (CASE WHEN APPF.APPEND_ENABLED = 'Y' THEN '1' ELSE '0' END)||
           (CASE WHEN APPF.UPDATE_ENABLED = 'Y' THEN '1' ELSE '0' END)||           
           (CASE WHEN APPF.DELETE_ENABLED = 'Y' THEN '1' ELSE '0' END)||           
           (CASE WHEN APPF.EXPORT_ENABLED = 'Y' THEN '1' ELSE '0' END) INTO P_OUT_ACTION
    FROM SYS_USER USR, 
         SYS_APPGROUP_FEATURE APPF,
         SYS_APPFEATURE FEAT
    WHERE USR.USERSTATUS_CODE = 'ACTIVE'
      AND USR.APPGROUP_ID = APPF.APPGROUP_ID
      AND APPF.ISVALID = 'Y'
      AND APPF.APPFEATURE_CODE = FEAT.APPFEATURE_CODE
      AND USR.USER_CODE = P_USER_CODE
      AND FEAT.APPFEATURE_ID = P_APPFEATURE_ID;
      
      P_OUT_MSG := 'GET FEATURE ACTION SUCCESS!';
  
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
      P_OUT_ACTION := '00000';
      P_OUT_NUM := -10000;
      P_OUT_MSG := 'USER:' || P_USER_CODE || '- NOT ASSIGNED THEN FEATURE: ' || P_APPFEATURE_ID;
      PKG_SYS_LOG.SP_LOG_ACTIVITY(P_USER_CODE,
                                 'SYSADMIN',
                                 'ERROR',
                                 'GET FEATURE ACTION',
                                 P_OUT_MSG,
                                 P_OUT_NUM);
    WHEN OTHERS THEN
      ROLLBACK;
      P_OUT_NUM := SQLCODE;
      P_OUT_MSG := SUBSTR(SQLERRM, 1, 500);
      PKG_SYS_LOG.SP_LOG_ACTIVITY(P_USER_CODE,
                                 'SYSADMIN',
                                 'ERROR',
                                 'GET FEATURE ACTION',
                                 P_OUT_MSG,
                                 P_OUT_NUM);
    
  END;

 PROCEDURE SP_AUTHENTICATE_USER(P_OUT_NUM      OUT NUMBER,
                                 P_OUT_MSG      OUT VARCHAR2,
                                 P_OUT_USERNAME OUT VARCHAR2,
                                 P_USER_CODE    IN VARCHAR2,
                                 P_PASSWORD     IN VARCHAR2,
                                 P_OUT_USERID   OUT NUMBER) AS
    V_EXPIRYDATE   DATE;
    V_DAYSTOEXPIRY NUMBER;
  
  BEGIN
  
    SELECT '0', (A.PWLASTCHANGEDDATE + B.PARAMETERVALUE), A.USER_ID, A.FIRSTNAME
      INTO P_OUT_NUM, V_EXPIRYDATE, P_OUT_USERID, P_OUT_USERNAME
      FROM SYS_USER A, SYS_SYSPARAMETER B
     WHERE A.USER_CODE = UPPER(P_USER_CODE)
       AND A.PASSWORD = GET_HASH(P_USER_CODE, P_PASSWORD)
       AND B.PARAMETER_CODE = 'PASSWORD_EXPIRE_DAYS';
  
    V_DAYSTOEXPIRY := ROUND(TO_NUMBER(V_EXPIRYDATE - SYSDATE), 0);
  
    IF (V_DAYSTOEXPIRY <= 0) THEN
      P_OUT_NUM      := -1;
      P_OUT_MSG      := 'Password Has Expired - Contact Tech Admin';
      P_OUT_USERNAME := ' ';
      PKG_SYS_LOG.SP_LOG_ACTIVITY(P_USER_CODE,
                                 'SYSINFO',
                                 'WARNING',
                                 'AUTHENTICATE_USER',
                                 P_OUT_MSG,
                                 P_OUT_NUM);
    ELSIF (V_DAYSTOEXPIRY < 10) THEN
      P_OUT_NUM := V_DAYSTOEXPIRY;
      P_OUT_MSG := 'Successful User Authentication - Password Due to expire in' || V_DAYSTOEXPIRY ||
                   ' Days';
      PKG_SYS_LOG.SP_LOG_ACTIVITY(P_USER_CODE,
                                 'SYSINFO',
                                 'SUCCESS',
                                 'AUTHENTICATE_USER',
                                 P_OUT_MSG,
                                 P_OUT_NUM);
    ELSE
      P_OUT_NUM := 0;
      P_OUT_MSG := 'SUCCESSFUL USER AUTHENTICATION';
      PKG_SYS_LOG.SP_LOG_ACTIVITY(P_USER_CODE,
                                 'SYSIFNO',
                                 'SUCCESS',
                                 'AUTHENTICATE_USER',
                                 P_OUT_MSG,
                                 P_OUT_NUM);
    END IF;
    COMMIT;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      ROLLBACK;
      P_OUT_NUM := -20000;
      P_OUT_MSG := ' INVALID USER NAME OR PASSWORD!';
      PKG_SYS_LOG.SP_LOG_ACTIVITY(P_USER_CODE,
                                 'SYSINFO',
                                 'ERROR',
                                 'AUTHENTICATE_USER',
                                 P_OUT_MSG,
                                 P_OUT_NUM);
    WHEN OTHERS THEN
      ROLLBACK;
      P_OUT_NUM := SQLCODE;
      P_OUT_MSG := SUBSTR(SQLERRM, 1, 500);
      PKG_SYS_LOG.SP_LOG_ACTIVITY(P_USER_CODE,
                                 'SYSINFO',
                                 'ERROR',
                                 'AUTHENTICATE_USER',
                                 P_OUT_MSG,
                                 P_OUT_NUM);
  END;  
end PKG_SYS_USER_SECURITY;
/