How To Solve Ora-01219 Database Or Pluggable Database Not Open

Make Custom User Authorization Schemes. Oracle Apex. Part-1

 

Today I will try to show you how to create a custom Authorization scheme in Oracle Apex. Today's post is the 1st part of user authorization. Hope to see all the parts.

Some discussion about Oracle Apex Authorization.

"User Authorization" is an important issue for any web application. Without the use of "User Authorization", no application is complete. I will try to show all the important things in the Oracle Apex application through a few parts, including user login according to "User Authorization", user role, user log, user menu usage. Hopefully, if you see all the parts related to "User Authorization", you can easily use "User Authorization" in Oracle Apex.

Steps to create Oracle Apex User Authentication, Part-1.

In today's part, I will try to show you how to create user authentication related tables, sequences, packages, trigger keys. Hopefully you can easily use User Authentication for your Oracle Apex application using the SQL code provided by me.

1. CREATE All table................ 

CREATE TABLE "MY_USERS"
(
    "USER_ID"          NUMBER,
    "USERNAME"         VARCHAR2 (50),
    "FULL_NAME"        VARCHAR2 (100),
    "PHONE_NUMBER"     NUMBER (11, 0),
    "EMAIL_ADDRESS"    VARCHAR2 (50),
    "IMAGE"            BLOB,
    "IS_ACTIVE"        VARCHAR2 (10),
    "PIN"              VARCHAR2 (100),
    CONSTRAINT "MY_USERS_PK" PRIMARY KEY ("USER_ID") USING INDEX ENABLE
)

CREATE TABLE "USERS_LOG"
(
    "LOG_ID"        NUMBER,
    "IP_ADDRESS"    VARCHAR2 (30),
    "LOGIN_USER"    VARCHAR2 (30),
    "LOGIN_TIME"    TIMESTAMP (6),
    "LOG_DATE"      DATE
)

CREATE TABLE "E_LOG"
(
    "PID"         NUMBER,
    "MSG"         NVARCHAR2 (300),
    "ERR_DATE"    DATE DEFAULT SYSDATE
)
ENABLE ROW MOVEMENT

CREATE TABLE "USER_GROUP"
(
    "OID"                  NUMBER,
    "PID_PARENT"           NUMBER,
    "GROUP_TITLE"          NVARCHAR2 (30),
    "GROUP_DESCRIPTION"    NVARCHAR2 (250)
)

CREATE TABLE "USER_GROUP_MY_STOCK"
(
    "PID"          NUMBER NOT NULL ENABLE,
    "PID_USER"     NUMBER NOT NULL ENABLE,
    "PID_GROUP"    NUMBER NOT NULL ENABLE
)

CREATE TABLE "IS_ACTIVATE"
(
    "OID"       NUMBER,
    "STATUS"    VARCHAR2 (30),
    CONSTRAINT "IS_ACTIVATE_PK" PRIMARY KEY ("OID") USING INDEX ENABLE
)

CREATE All SEQUENCE............... 

CREATE SEQUENCE   "MY_USERS_SEQ"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 361 CACHE 20 NOORDER  NOCYCLE  NOKEEP  NOSCALE  GLOBAL
                CREATE SEQUENCE   "USERS_LOG_SEQ"  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 248 NOCACHE  NOORDER  NOCYCLE  NOKEEP  NOSCALE  GLOBAL
                CREATE SEQUENCE   "USERS_SEQ"  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE  NOORDER  NOCYCLE  NOKEEP  NOSCALE  GLOBAL
                CREATE SEQUENCE   "USER_GROUP_MY_STOCK_SEQ"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 61 CACHE 20 NOORDER  NOCYCLE  NOKEEP  NOSCALE  GLOBAL
                CREATE SEQUENCE   "LOGIN_LOG_ID_SEC"  MINVALUE 0 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 8692285 NOCACHE  NOORDER  NOCYCLE  NOKEEP  NOSCALE  GLOBAL

CREATE All Package.................. 

CREATE OR REPLACE PACKAGE USER_AUTH_IP
AS
    FUNCTION CUSTOM_AUTH (P_USERNAME VARCHAR2, P_PASSWORD VARCHAR2)
        RETURN BOOLEAN;

    FUNCTION CUSTOM_HASH (P_USERNAME VARCHAR2, P_PASSWORD VARCHAR2)
        RETURN VARCHAR2;
END;

CREATE OR REPLACE PACKAGE BODY USER_AUTH_IP AS FUNCTION CUSTOM_AUTH (P_USERNAME VARCHAR2, P_PASSWORD VARCHAR2)
                   RETURN BOOLEAN
                IS
                   L_PASSWORD          VARCHAR2 (4000);
                   L_STORED_PASSWORD   VARCHAR2 (4000);
                   v_Time VARCHAR2 (6);
                   v_ADDR VARCHAR2 (100);
                   v_LOGIN_ID VARCHAR2 (100);
                BEGIN
                  IF P_USERNAME IS NULL AND P_PASSWORD IS NULL THEN
                     APEX_UTIL.Set_Session_State('LOGIN_MESSAGE','Please enter Username & password.');
                     RETURN FALSE;      END IF;
                 SELECT
            NVL(ROUND(TO_NUMBER(TO_CHAR(MAX(SESSION_IDLE_TIMEOUT_ON),'SSSSS')/60)),0) - ROUND( TO_NUMBER(TO_CHAR(SYSDATE,'SSSSS')/60)) INTO v_Time
                FROM apex_workspace_sessions WHERE USER_NAME=(SELECT DISTINCT USER_NAME FROM apex_workspace_sessions WHERE USER_NAME=UPPER(P_USERNAME));
                       IF v_Time > 1  THEN
                       SELECT REMOTE_ADDR INTO v_ADDR  FROM apex_workspace_sessions WHERE USER_NAME=UPPER(P_USERNAME) AND ROWNUM=1;
                       ELSE NULL;
                       END IF;
                       SELECT PIN
                        INTO L_STORED_PASSWORD
                         Return False;     END If;

                   IF L_PASSWORD                     = L_STORED_PASSWORD
                   THEN
                     insert into USERS_LOG (LOG_ID,IP_ADDRESS,LOGIN_USER,LOGIN_TIME)
                RETURN TRUE;
                END IF;
                END;

FUNCTION CUSTOM_HASH (P_USERNAME VARCHAR2, P_PASSWORD VARCHAR2)
    RETURN VARCHAR2
IS
    L_PASSWORD   VARCHAR2 (4000);
    L_SALT       VARCHAR2 (4000) := 'D9GE4CORSJZVKADPOJ5C1PERC704WB';
BEGIN
    L_PASSWORD :=
        UTL_RAW.CAST_TO_RAW (
            DBMS_OBFUSCATION_TOOLKIT.MD5 (INPUT_STRING => P_PASSWORD)) RETURN       L_PASSWORD;
END;

                END;

2nd Pakge............................


CREATE OR REPLACE PACKAGE "ACL"
AS
    FUNCTION CUSTOM_AUTH (P_USERNAME VARCHAR2, P_PASSWORD VARCHAR2)
        RETURN BOOLEAN;

    FUNCTION CUSTOM_HASH (P_USERNAME VARCHAR2, P_PASSWORD VARCHAR2)
        RETURN VARCHAR2;
END;

            ====================================================

CREATE OR REPLACE PACKAGE BODY "ACL"
AS
    FUNCTION CUSTOM_AUTH (P_USERNAME VARCHAR2, P_PASSWORD VARCHAR2)
        RETURN BOOLEAN
    IS
        L_PASSWORD          VARCHAR2 (4000);
        L_STORED_PASSWORD   VARCHAR2 (4000);
    BEGIN
        SELECT PIN
          INTO L_STORED_PASSWORD
          FROM MY_USERS
         WHERE     is_active = '1'
               AND LOWER (USERNAME) = LOWER (P_USERNAME)
               AND ROWNUM <= 1;

        L_PASSWORD := CUSTOM_HASH (P_USERNAME, P_PASSWORD);

        IF L_PASSWORD = L_STORED_PASSWORD
        THEN
            INSERT INTO USERS_LOG (LOG_ID,
                                   IP_ADDRESS,
                                   LOGIN_USER,
                                   LOGIN_TIME)                                                               RETURN           TRUE        ;
ELSE
                INSERT INTO e_log (pid, msg)
                         VALUES (
                                    (SELECT NVL (MAX (pid), 0) + 1 FROM e_log),
                                       'not match - user: '
                                    || P_USERNAME
                                    || '   pass: '
                                    || P_PASSWORD);

                RETURN FALSE;
        END IF;
    EXCEPTION
        WHEN NO_DATA_FOUND
        THEN
            INSERT INTO e_log (pid, msg)
                     VALUES (
                                (SELECT NVL (MAX (pid), 0) + 1 FROM e_log),
                                   'No User Found - user: '
                                || P_USERNAME
                                || '   pass: '
                                || P_PASSWORD);

            RETURN FALSE;
    END;

    FUNCTION CUSTOM_HASH (P_USERNAME VARCHAR2, P_PASSWORD VARCHAR2)
        RETURN VARCHAR2
    IS
        L_PASSWORD   VARCHAR2 (4000);
        L_SALT       VARCHAR2 (4000) := 'D9GE4CORSJZVKADPOJ5C1PERC704WB';
    BEGIN
        L_PASSWORD :=
            UTL_RAW.CAST_TO_RAW (
                DBMS_OBFUSCATION_TOOLKIT.MD5 (INPUT_STRING => P_PASSWORD));
        RETURN L_PASSWORD;
    END;
END;

CREATE All TRIGGER.................. 

CREATE OR REPLACE EDITIONABLE TRIGGER  "AUTO_USER_GROUP_MY_STOCK" 
              after INSERT
              ON MY_USERS   REFERENCING NEW AS NEW OLD AS OLD
              FOR EACH ROW
            DECLARe
            begin
             INSERT INTO USER_GROUP_MY_STOCK (PID, PID_USER, PID_GROUP, IS_ACTIVE, INSERT_BY, INSERT_DATE, UPDATE_BY, UPDATE_DATE)
            EXCEPTION
              WHEN OTHERS
              THEN
                 RAISE;
            END;
            ALTER TRIGGER  "AUTO_USER_GROUP_MY_STOCK" ENABLE
            CREATE OR REPLACE EDITIONABLE TRIGGER  "BI_MY_USERS" 
              before insert on "MY_USERS"               
              for each row  
            begin   
              if :NEW."USER_ID" is null then 
                select "MY_USERS_SEQ".nextval into :NEW."USER_ID" from sys.dual; 
              end if; 
            end; 
            ALTER TRIGGER  "BI_MY_USERS" ENABLE
            CREATE OR REPLACE EDITIONABLE TRIGGER  "LOGIN_ID_ACL_USER" 
            BEFORE INSERT OR UPDATE of USER_ID
            ON MY_USERS 
            REFERENCING NEW AS New OLD AS Old
            FOR EACH ROW
            DECLARE
            BEGIN
            :NEW.USER_ID := lower(:NEW.USER_ID);
               EXCEPTION
                 WHEN OTHERS THEN
                   -- Consider logging the error and then re-raise
                   RAISE;
            END ;
            ALTER TRIGGER  "LOGIN_ID_ACL_USER" ENABLE

            CREATE OR REPLACE EDITIONABLE TRIGGER  "PIN_MY_USER" 
            BEFORE INSERT OR UPDATE of PIN 
            ON MY_USERS
            DECLARE
            BEGIN
            :new.PIN:=USER_AUTH_IP.CUSTOM_HASH ( :new.USER_ID, :new.PIN); 
            IF INSERTING
              THEN
                 :NEW.ADDED_DATE := SYSDATE;
              END IF;
               EXCEPTION
                 WHEN OTHERS THEN
                   RAISE;
            END ;
            ALTER TRIGGER  "PIN_MY_USER" ENABLE
            ===========
            CREATE OR REPLACE EDITIONABLE TRIGGER  "USER_UPDATE_LOGS" 
              before insert or update on USERS_LOG 
              for each row 
            begin 
              if :new.LOG_ID is null then 
                :new.LOG_ID := to_number(sys_guid(), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'); 
              end if; 
            end user_update_logs;
            ALTER TRIGGER  "USER_UPDATE_LOGS" ENABLE

If there is any problem with the SQL code provided by me then you can definitely let me know by mail or mobile number and comment. I must give you any SQL code. User Authorization

2. Once all tables, sequences, packages, triggers have been created, we will create a new application.

Application Name - User Authentication 

3. Create new forms and report pages for each table. 

4. First create two active and inactive statuses, 

We will complete the task from the database to make the task easier without creating a form for the two statuses.

5. At this stage we will create some user groups according to the needs of the application. 

6. Create some users at this stage 

7. Go To Shared Components Authentication Schemes -> Create New Authentication Schemes
Name-Custom Auth, -> Scheme Type- Custom, -> Authentication Function Name- ACL.CUSTOM_AUTH

Links to all the parts related to User Authorization will be given below.

🔗 1. User Authorization Schemes, Part-1 Video Url--
            URL-https://youtu.be/vSdzwnkFDRs
 

🔗 2. Add Create New Account button on the login page, User Authorization, Part-2 Video Url--
            URL-https://youtu.be/IajwZ5dp6Qc
 

🔗 3. Create a ChangePassword Page, User Authorization, Part-3 Video Url--
            URL-https://youtu.be/iRXsO0MTOuM
 

🔗 4. Reset Password, User Authorization, Part-4 Video Url--
            URL-https://youtu.be/IK3PjyWGQIA
 

🔗 5. Dynamic Navigation Menu, User Authorization, Part-5 Video Url--
            URL-https://youtu.be/Ozoc4cpjBKY
 

🔗 6. Set Menu According To User Access Role, User Authorization Part-6 Video URL--
            URL-https://youtu.be/X407N_N2HNM
 

🔗 7. Add Edit Button in Report According to User Access Role || Oracle APEX? User Authentication, Part-7. Video URL--
            URL-https://youtu.be/n9W64qUoS1E
 

🔗 8. Access google authentication in Oracle Apex | Google Authentication. User Authorization Video URL--
            URL-https://youtu.be/kGMmvuG5Qs8
 

🔗 9. Change Password in Email Verification if you Forget Your Password. User Authorization Video URL--
            URL-https://youtu.be/MPZQuraig7w
 

Hope you find this post helpful User Authorization. To see more about Oracle Apex User Authorization, you can visit my website. You can also visit my youtube channel. I try my best to share my education with everyone. I will always try to give you something new. Please help by subscribing to my youtube channel.

 

🔗 Demo Application-
            URL- Demo Application
            Username - demo, Pass- demo
 

I hope everyone will like it. Please watch the full video,
Comment on any of your problems, I will try my best to solve the problem, In-Shah Allah. Everyone's cooperation is desirable. Visit my blog site, new technology related videos, you will get different types of tutorials of Oracle Apex, and hopefully, you can use them in your daily work.
Please stay tuned by subscribing to the YouTube channel, and encourages new videos to be uploaded.
=================
Visit my site to get more collaborative posts about Oracle Apex and subscribe to my YouTube channel. Thanks.
Comment on any of your issues, I will try my best to solve the problem, In-Shah Allah. Everyone's cooperation is desirable.
Visit my blog site, new technology-related videos, you will get different types of tutorials of Oracle Apex, and hopefully, you can use them in your daily work.
==============================

🙍🏾‍ Md jABER HOSSEN
📲 Mobile- +8801760688286
📨 Email- jaberit786@gmail.com
🌐 FB- facebook.com/mdjaber.hossen1
Please Subscribe to My Channel

Many thanks for visiting the site.

Then Enjoy.........................