Rem Rem $Header: emcore/source/oracle/sysman/emdrep/sql/core/latest/user_model/user_view_pkgbody.sql /st_emcore_10.2.0.4.2db11.2/1 2009/06/17 22:17:24 nmathuri Exp $ Rem Rem user_view_pkgbody.sql Rem Rem Copyright (c) 2002, 2009, Oracle and/or its affiliates. Rem All rights reserved. Rem Rem NAME Rem user_view_pkgbody.sql - Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem nmathuri 06/12/09 - Fix the bug 8564295 Rem shianand 07/21/06 - Backport shianand_bug-5346292 from main Rem shianand 07/12/06 - fix bug 5346292, fix encrypt, decrypt from Rem rootkit attacks Rem shianand 07/06/06 - fix bug 5346292, fix encrypt, decrypt from Rem rootkit attacks Rem kmanicka 08/25/05 - add init_view_user_out_of_box Rem pmodi 08/23/05 - Bug:4543987 - Add comment on testing of change Rem password Rem jsadras 06/01/05 - add api to change view user passwd Rem skini 09/12/03 - Fix view user credentials Rem skini 08/18/03 - Use MGMT_VIEW_USER_CREDENTIALS table Rem rpinnama 02/27/03 - Remove hardcoded password Rem rpinnama 01/21/03 - Provide an API to reset view user's password Rem rpinnama 05/15/02 - rpinnama_reorg_rep_scripts Rem rpinnama 05/15/02 - Restructured. Rem mbhoopat 04/18/02 - Fix bug 2330078.. Rem rpinnama 04/16/02 - Add MGMT_VIEW user to created users. Rem mbhoopat 04/12/02 - mbhoopat_view_user_support Rem mbhoopat 04/09/02 - Created Rem CREATE OR REPLACE PACKAGE BODY MGMT_VIEW_PRIV AS PROCEDURE SET_VIEW_USER_CONTEXT(em_client_in IN VARCHAR2, op_in IN INTEGER, view_user IN VARCHAR2, view_pwd IN VARCHAR2) IS s_view_pwd VARCHAR2(256); s_view_user VARCHAR2(256); BEGIN get_view_user_creds(s_view_user, s_view_pwd); IF view_user != s_view_user THEN raise_application_error(MGMT_GLOBAL.INSUFFICIENT_PRIVILEGES_ERR, 'Invalid '|| MGMT_VIEW_PRIV.MGMT_VIEW_USER_KEY); END IF; IF view_pwd != s_view_pwd THEN raise_application_error(MGMT_GLOBAL.INSUFFICIENT_PRIVILEGES_ERR, 'Invalid '|| MGMT_VIEW_PRIV.MGMT_VIEW_PWD_KEY); END IF; SETEMUSERCONTEXT(em_client_in, op_in); END SET_VIEW_USER_CONTEXT; PROCEDURE GET_VIEW_USER_CREDS(view_user OUT VARCHAR2, view_pwd OUT VARCHAR2) IS BEGIN SELECT view_username, decrypt(view_password) INTO view_user, view_pwd FROM MGMT_VIEW_USER_CREDENTIALS; END GET_VIEW_USER_CREDS; PROCEDURE GET_VIEW_USER(view_user OUT VARCHAR2) IS BEGIN SELECT view_username INTO view_user FROM MGMT_VIEW_USER_CREDENTIALS; END GET_VIEW_USER; PROCEDURE SET_VIEW_USER_CREDS(v_secret IN VARCHAR2) IS l_view_pwd VARCHAR2(30); l_view_creds SMP_EMD_NVPAIR_ARRAY := SMP_EMD_NVPAIR_ARRAY(); BEGIN l_view_pwd := random_password(v_secret) ; DELETE FROM MGMT_VIEW_USER_CREDENTIALS; INSERT INTO MGMT_VIEW_USER_CREDENTIALS(view_username, view_password) VALUES (MGMT_VIEW_USER, encrypt(l_view_pwd)); END SET_VIEW_USER_CREDS; -- -- PURPOSE -- -- To get a Random password from a given seed -- FUNCTION random_password(seed_in IN VARCHAR2) RETURN VARCHAR2 IS BEGIN RETURN('A'||SUBSTR(RAWTOHEX(SYS.DBMS_OBFUSCATION_TOOLKIT.md5( input => SYS.UTL_RAW.cast_to_raw( seed_in||RAWTOHEX(sys_guid())) )), 1, 29)) ; END random_password ; -- -- PURPOSE -- -- To change the password for mgmt_view user -- -- PARAMETERS -- -- view_pwd: The new password to be set -- -- Checks: Only SYSMAN can change password -- -- Note: ** THERE IS A IMPLICIT COMMIT IN THIS PROCEDURE DUE TO -- ALTER USER -- -- Please make sure that NO ONE is accessing user MGMT_VIEW_USER -- while executing change_view_user_password. -- -- Test case tvmru050 has been commented from regression as it was running into user a/c lock issue. -- One should test tvmru050 manually. -- PROCEDURE change_view_user_password(view_pwd IN VARCHAR2) IS l_view_user mgmt_view_user_credentials.view_username%type ; l_sqlcode NUMBER ; BEGIN -- -- Check that the caller is the repository owner IF MGMT_USER.get_current_em_user NOT IN (MGMT_USER.get_repository_owner, G_SYS_USER) THEN RAISE mgmt_global.insufficient_privileges ; END IF; -- Check for null password EM_CHECK.check_not_null(view_pwd,'view password') ; -- Check the view schema has been created l_view_user := EM_CHECK.schema_name(MGMT_VIEW_USER) ; SAVEPOINT change_view_password ; UPDATE MGMT_VIEW_USER_CREDENTIALS SET view_password = encrypt(view_pwd) ; -- Will get into sql%notfound only for first time IF SQL%NOTFOUND THEN INSERT INTO MGMT_VIEW_USER_CREDENTIALS (view_username, view_password) VALUES (MGMT_VIEW_USER, encrypt(view_pwd)); END IF ; EXECUTE IMMEDIATE 'ALTER USER '||EM_CHECK.ENQUOTE_NAME(l_view_user)|| ' IDENTIFIED BY '|| EM_CHECK.ENQUOTE_NAME(view_pwd); EXCEPTION WHEN mgmt_global.insufficient_privileges THEN raise_application_error(MGMT_GLOBAL.INSUFFICIENT_PRIVILEGES_ERR, MGMT_GLOBAL.INSUFFICIENT_PRIVILEGES_ERR_M) ; WHEN mgmt_global.user_does_not_exist THEN raise_application_error(MGMT_GLOBAL.USER_DOES_NOT_EXIST_ERR, MGMT_GLOBAL.USER_DOES_NOT_EXIST_ERR_M) ; WHEN mgmt_global.invalid_params THEN RAISE ; WHEN OTHERS THEN l_sqlcode := sqlcode ; BEGIN ROLLBACK TO SAVEPOINT change_view_password ; EXCEPTION WHEN OTHERS THEN NULL ; END ; raise_application_error(MGMT_GLOBAL.MODIFY_FAILED_ERR, MGMT_GLOBAL.MODIFY_FAILED_ERR_M|| ' Error='||l_sqlcode); END change_view_user_password ; -- -- PURPOSE -- -- Called during out of box -- It replaces the View user password in the seed db to a new random password. -- -- PARAMETERS -- -- Checks: Only SYSMAN can call this function -- -- Note: ** THERE IS A IMPLICIT COMMIT IN THIS PROCEDURE DUE TO -- ALTER USER -- PROCEDURE init_view_user_out_of_box IS l_random_view_user_password mgmt_view_user_credentials.view_password%type; BEGIN -- create a random view user password. l_random_view_user_password := SYS.DBMS_OBFUSCATION_TOOLKIT.md5( input => SYS.UTL_RAW.cast_to_raw( rawtohex(sys_guid())||rawtohex(sys_guid()))); l_random_view_user_password := MGMT_VIEW_PRIV.random_password(l_random_view_user_password) ; -- change the existing password created in seed DB to the new random password. change_view_user_password(l_random_view_user_password); END; END MGMT_VIEW_PRIV; / show errors;