Create a user and assign the responsibility in Oracle

Can i get the password of the user, if i forget.

Find the steps to create an Oracle User and assign the responsibility.

1) Create Table

CREATE TABLE CUST_USER_RESP

(

LINE_ID                 NUMBER,

USER_NAME               VARCHAR2(200),

UNENCRYPTED_PASSWORD    VARCHAR2(200),

START_DATE              DATE,

END_DATE                DATE,

DESCRIPTION             VARCHAR2(200),

PASSWORD_DATE           DATE,

EMPLOYEE_NAME           VARCHAR2(200),

EMAIL_ADDRESS           VARCHAR2(200),

ERROR_DESCRIPTION       VARCHAR2(200),

USER_ID                 VARCHAR2(200)

)

2) Create Control File.


LOAD DATA
        INFILE 'Users.csv'
INSERT
        INTO TABLE CUST_USER_RESP
        REPLACE FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
LINE_ID                 ,
USER_NAME               ,
UNENCRYPTED_PASSWORD    ,
START_DATE              ,
END_DATE                ,
DESCRIPTION             ,
PASSWORD_DATE           ,
EMPLOYEE_NAME           ,  
EMAIL_ADDRESS           ,
ERROR_DESCRIPTION       ,
USER_ID
)

3) Create the Procedure

CREATE OR REPLACE PROCEDURE APPS.CUST_CREATE_USER_RESP

IS

   CURSOR C_USER

   IS

      SELECT LINE_ID, USER_NAME, UNENCRYPTED_PASSWORD, START_DATE, END_DATE,

             DESCRIPTION, EMAIL_ADDRESS, EMPLOYEE_NAME

        FROM CUST_USER_RESP;

   LC_C_USER                      C_USER%ROWTYPE;

   P_OWNER                        VARCHAR2 (200)   := NULL;

   P_SESSION_NUMBER               NUMBER           := USERENV ('SESSIONID');

   P_LAST_LOGON_DATE              DATE             := NULL;

   P_PASSWORD_DATE                DATE             := SYSDATE;

   P_PASSWORD_ACCESSES_LEFT       NUMBER           := NULL;

   P_PASSWORD_LIFESPAN_ACCESSES   NUMBER           := NULL;

   P_PASSWORD_LIFESPAN_DAYS       NUMBER           := NULL;

   P_FAX                          VARCHAR2 (200)   := NULL;

   P_CUSTOMER_ID                  NUMBER           := NULL;

   P_SUPPLIER_ID                  NUMBER           := NULL;

   V_EMPLOYEE_ID                  NUMBER;

   V_USER_ID                      NUMBER;

   V_USER_NAME                    VARCHAR2 (200)   := NULL;

   RESULT                         BOOLEAN;

   ERROR_DESC                     VARCHAR2 (240);

BEGIN

   OPEN C_USER;

   LOOP

      FETCH C_USER

       INTO LC_C_USER;

      EXIT WHEN C_USER%NOTFOUND;

      SELECT PPF.PERSON_ID

        INTO V_EMPLOYEE_ID

        FROM PER_PEOPLE_F PPF

       WHERE UPPER (PPF.FULL_NAME) = UPPER (LC_C_USER.EMPLOYEE_NAME)

         AND SYSDATE BETWEEN PPF.EFFECTIVE_START_DATE AND PPF.EFFECTIVE_END_DATE;

      BEGIN

         FND_USER_PKG.CREATEUSER

               (X_USER_NAME                       => LC_C_USER.USER_NAME,

                X_OWNER                           => P_OWNER,

                X_UNENCRYPTED_PASSWORD            => LC_C_USER.UNENCRYPTED_PASSWORD,

                X_SESSION_NUMBER                  => P_SESSION_NUMBER,

                X_START_DATE                      => LC_C_USER.START_DATE,

                X_END_DATE                        => LC_C_USER.END_DATE,

                X_LAST_LOGON_DATE                 => P_LAST_LOGON_DATE,

                X_DESCRIPTION                     => LC_C_USER.DESCRIPTION,

                X_PASSWORD_DATE                   => P_PASSWORD_DATE,

                X_PASSWORD_ACCESSES_LEFT          => P_PASSWORD_ACCESSES_LEFT,

                X_PASSWORD_LIFESPAN_ACCESSES      => P_PASSWORD_LIFESPAN_ACCESSES,

                X_PASSWORD_LIFESPAN_DAYS          => P_PASSWORD_LIFESPAN_DAYS,

                X_EMPLOYEE_ID                     => V_EMPLOYEE_ID,

                X_EMAIL_ADDRESS                   => LC_C_USER.EMAIL_ADDRESS,

                X_FAX                             => P_FAX,

                X_CUSTOMER_ID                     => P_CUSTOMER_ID,

                X_SUPPLIER_ID                     => P_SUPPLIER_ID

               );

         BEGIN

            SELECT USER_ID, USER_NAME

              INTO V_USER_ID, V_USER_NAME

              FROM FND_USER

             WHERE UPPER (USER_NAME) = UPPER (LC_C_USER.USER_NAME);

         EXCEPTION

            WHEN OTHERS

            THEN

               DBMS_OUTPUT.PUT_LINE ('USER NOT FOUND');

         END;

         FND_USER_PKG.ADDRESP (USERNAME            => V_USER_NAME,

                               RESP_APP            => 'PER',

                               RESP_KEY            => 'EMPLOYEE_DIRECT_ACCESS_V4.0',

                               SECURITY_GROUP      => 'STANDARD',

                               DESCRIPTION         => 'EMPLOYEE SELF SERVICE',

                               START_DATE          => SYSDATE - 1,

                               END_DATE            => NULL

                              );

         --     FND_USER_PKG.ADDRESP(USERNAME       => V_USER_NAME

         --                      ,RESP_APP       => 'SYSADMIN'

         --                      ,RESP_KEY       => 'SYSTEM_ADMINISTRATOR'

         --                      ,SECURITY_GROUP => 'STANDARD'

         --                      ,DESCRIPTION    => 'EXD'

         --                      ,START_DATE     => SYSDATE - 1

         --                      ,END_DATE       => SYSDATE + 10000);

         --      FND_USER_PKG.ADDRESP(USERNAME       => V_USER_NAME

         --                          ,RESP_APP       => 'FND'

         --                          ,RESP_KEY       => 'FNDWF_ADMIN_WEB'

         --                          ,SECURITY_GROUP => 'STANDARD'

         --                          ,DESCRIPTION    => 'EXD'

         --                          ,START_DATE     => SYSDATE - 1

         --                          ,END_DATE       => SYSDATE + 10000);

         --          FND_USER_PKG.ADDRESP(USERNAME       => V_USER_NAME

         --                          ,RESP_APP       => 'FND'

         --                          ,RESP_KEY       => 'APPLICATION_DEVELOPER'

         --                          ,SECURITY_GROUP => 'STANDARD'

         --                          ,DESCRIPTION    => 'EXD'

         --                          ,START_DATE     => SYSDATE - 1

         --                          ,END_DATE       => SYSDATE + 10000);

         --

         --        FND_USER_PKG.ADDRESP(USERNAME       => V_USER_NAME,

         --                           RESP_APP       => 'ICX',

         --                           RESP_KEY       => 'PREFERENCES',

         --                           SECURITY_GROUP => 'STANDARD',

         --                           DESCRIPTION    => 'EXD',

         --                           START_DATE     => SYSDATE - 1,

         --                           END_DATE       => NULL);

         RESULT :=

            FND_PROFILE.SAVE (X_NAME             => 'APPS_SSO_LOCAL_LOGIN',

                              X_VALUE            => 'BOTH',

                              X_LEVEL_NAME       => 'USER',

                              X_LEVEL_VALUE      => V_USER_ID

                             );

         RESULT :=

            FND_PROFILE.SAVE (X_NAME             => 'FND_CUSTOM_OA_DEFINTION',

                              X_VALUE            => 'Y',

                              X_LEVEL_NAME       => 'USER',

                              X_LEVEL_VALUE      => V_USER_ID

                             );

         RESULT :=

            FND_PROFILE.SAVE (X_NAME             => 'FND_DIAGNOSTICS',

                              X_VALUE            => 'Y',

                              X_LEVEL_NAME       => 'USER',

                              X_LEVEL_VALUE      => V_USER_ID

                             );

         RESULT :=

            FND_PROFILE.SAVE (X_NAME             => 'DIAGNOSTICS',

                              X_VALUE            => 'Y',

                              X_LEVEL_NAME       => 'USER',

                              X_LEVEL_VALUE      => V_USER_ID

                             );

         RESULT :=

            FND_PROFILE.SAVE (X_NAME             => 'FND_HIDE_DIAGNOSTICS',

                              X_VALUE            => 'N',

                              X_LEVEL_NAME       => 'USER',

                              X_LEVEL_VALUE      => V_USER_ID

                             );

         IF V_USER_ID IS NOT NULL

         THEN

            ERROR_DESC := 'NO ERROR';

            UPDATE CUST_USER_RESP

               SET ERROR_DESCRIPTION = ERROR_DESC

             WHERE LINE_ID = LC_C_USER.LINE_ID;

         END IF;

         IF V_USER_ID IS NULL

         THEN

            ERROR_DESC := SQLERRM;

            UPDATE CUST_USER_RESP

               SET ERROR_DESCRIPTION = ERROR_DESC

             WHERE LINE_ID = LC_C_USER.LINE_ID;

         END IF;

         DBMS_OUTPUT.PUT_LINE ('USER_ID : ' || V_USER_ID);

      EXCEPTION

         WHEN OTHERS

         THEN

            ERROR_DESC := SQLERRM;

            UPDATE CUST_USER_RESP

               SET ERROR_DESCRIPTION = ERROR_DESC

             WHERE LINE_ID = LC_C_USER.LINE_ID;

      END;

   END LOOP;

   CLOSE C_USER;

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.PUT_LINE (SQLERRM);

END;

   

Feedback is much appreciated. Comment for any query.