?? pkg_sys_user_security.~bdy
字號:
'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;
/
?? 快捷鍵說明
復(fù)制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -