ORACLE 11g: User Authentication System with Stored Procedure and SQL*Plus Integration

Oracle 11g PL/SQL: User Authentication System with Stored Procedure and SQL*Plus Integration

Creating the tblUsers and tblRoles tables with sequences and 6 records, creating a stored procedure sp_UserNameAndPassword that returns user details as an OUT parameter (using a record type), and modifying the login.sql script to accept username and password, call the procedure, and display the results in SQL*Plus. 

The solution will include "what," "why," "how" explanations, comments, and adhere to coding standards with exception handling.

Step-by-Step Solution
1. Create Tables and Insert Records
Create the tables with sequences for primary keys and insert 6 records.

-- Create tblRoles table with sequence for RoleId
CREATE TABLE tblRoles (
    RoleId NUMBER(10) PRIMARY KEY,
    roleName VARCHAR2(50) NOT NULL
);

CREATE SEQUENCE role_seq START WITH 1 INCREMENT BY 1;

-- Insert 4 roles
INSERT INTO tblRoles (RoleId, roleName) VALUES (role_seq.NEXTVAL, 'Administrator');
INSERT INTO tblRoles (RoleId, roleName) VALUES (role_seq.NEXTVAL, 'Doctor');
INSERT INTO tblRoles (RoleId, roleName) VALUES (role_seq.NEXTVAL, 'Receptionist');
INSERT INTO tblRoles (RoleId, roleName) VALUES (role_seq.NEXTVAL, 'LabTech');
COMMIT;

-- Create tblUsers table with sequence for userId and foreign key to tblRoles
CREATE TABLE tblUsers (
    userId NUMBER(10) PRIMARY KEY,
    userName VARCHAR2(50) NOT NULL,
    password VARCHAR2(50) NOT NULL,
    fullName VARCHAR2(100) NOT NULL,
    roleId NUMBER(10),
    FOREIGN KEY (roleId) REFERENCES tblRoles(RoleId)
);

CREATE SEQUENCE user_seq START WITH 1001 INCREMENT BY 1;

-- Insert 6 users
INSERT INTO tblUsers (userId, userName, password, fullName, roleId) 
VALUES (user_seq.NEXTVAL, 'admin1', 'pass123', 'Admin User One', (SELECT RoleId FROM tblRoles WHERE roleName = 'Administrator'));

INSERT INTO tblUsers (userId, userName, password, fullName, roleId) 
VALUES (user_seq.NEXTVAL, 'doc1', 'docpass', 'Dr. John Smith', (SELECT RoleId FROM tblRoles WHERE roleName = 'Doctor'));

INSERT INTO tblUsers (userId, userName, password, fullName, roleId) 
VALUES (user_seq.NEXTVAL, 'rec1', 'recpass', 'Receptionist Jane', (SELECT RoleId FROM tblRoles WHERE roleName = 'Receptionist'));

INSERT INTO tblUsers (userId, userName, password, fullName, roleId) 
VALUES (user_seq.NEXTVAL, 'lab1', 'labpass', 'Lab Tech Mike', (SELECT RoleId FROM tblRoles WHERE roleName = 'LabTech'));

INSERT INTO tblUsers (userId, userName, password, fullName, roleId) 
VALUES (user_seq.NEXTVAL, 'doc2', 'docpass2', 'Dr. Alice Brown', (SELECT RoleId FROM tblRoles WHERE roleName = 'Doctor'));

INSERT INTO tblUsers (userId, userName, password, fullName, roleId) 
VALUES (user_seq.NEXTVAL, 'rec2', 'recpass2', 'Receptionist Tom', (SELECT RoleId FROM tblRoles WHERE roleName = 'Receptionist'));

COMMIT;

-- Verify data
SELECT * FROM tblRoles;
SELECT * FROM tblUsers;

What: Defines tblRoles and tblUsers with sequences for RoleId and userId, establishing a foreign key relationship.
Why: Ensures unique IDs and referential integrity for a user management system.
How: Use CREATE TABLE, CREATE SEQUENCE, and INSERT statements.
2. Create Stored Procedure sp_UserNameAndPassword
The procedure will return user details as an OUT parameter using a record type.

-- Create a record type to hold user details
CREATE OR REPLACE TYPE user_details_rec AS OBJECT (
    userId NUMBER(10),
    userName VARCHAR2(50),
    password VARCHAR2(50),
    fullName VARCHAR2(100),
    roleName VARCHAR2(50)
);
/

-- Create procedure to fetch user details as OUT parameter
CREATE OR REPLACE PROCEDURE sp_UserNameAndPassword (
    p_userName IN VARCHAR2,
    p_password IN VARCHAR2,
    p_userDetails OUT user_details_rec
) AS
BEGIN
    -- Fetch user details including roleName
    SELECT user_details_rec(u.userId, u.userName, u.password, u.fullName, r.roleName)
    INTO p_userDetails
    FROM tblUsers u
    JOIN tblRoles r ON u.roleId = r.RoleId
    WHERE u.userName = p_userName AND u.password = p_password;

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RAISE_APPLICATION_ERROR(-20001, 'No user found with Username ' || p_userName || ' and Password ' || p_password);
    WHEN TOO_MANY_ROWS THEN
        RAISE_APPLICATION_ERROR(-20002, 'Multiple users found with given credentials');
    WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR(-20003, 'Error: ' || SQLERRM);
END sp_UserNameAndPassword;
/

-- Enable DBMS_OUTPUT for testing (optional)
SET SERVEROUTPUT ON;

What: A procedure that takes username and password as IN parameters and returns a user_details_rec object as an OUT parameter.
Why: Allows flexible return of multiple columns as a single structured output, useful for client applications or scripts.
How: Define a TYPE for the record, use INTO with the object constructor, and handle exceptions with custom error messages.
3. Create SQL Script File (login.sql)
This script will prompt for username and password, call the procedure, and display the results in SQL*Plus.

-- login.sql
-- Prompt for user input
ACCEPT username PROMPT 'Enter Username: '
ACCEPT password PROMPT 'Enter Password: '

-- Set server output to display results
SET SERVEROUTPUT ON

-- Declare variable to hold the record
VARIABLE user_details user_details_rec

-- Call the procedure with user input
BEGIN
    sp_UserNameAndPassword('&username', '&password', :user_details);
    -- Display results
    DBMS_OUTPUT.PUT_LINE('User ID: ' || :user_details.userId);
    DBMS_OUTPUT.PUT_LINE('Username: ' || :user_details.userName);
    DBMS_OUTPUT.PUT_LINE('Password: ' || :user_details.password);
    DBMS_OUTPUT.PUT_LINE('Full Name: ' || :user_details.fullName);
    DBMS_OUTPUT.PUT_LINE('Role: ' || :user_details.roleName);
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/
What: A SQL*Plus script that accepts user input, calls sp_UserNameAndPassword, and displays the returned record.
Why: Provides an interactive way to test the procedure and view results directly in the console.
How: Use ACCEPT for input, VARIABLE for the record, and DBMS_OUTPUT to print fields.
4. Execution and Output in SQL*Plus
Steps to Run:
  1. Log in to SQL*Plus (e.g., sqlplus username/password@service).
  2. Run the table creation, sequence, and procedure scripts.
  3. Execute the login.sql script using: @login.sql
Example Session:

SQL> @login.sql
Enter Username: admin1
Enter Password: pass123

User ID: 1001
Username: admin1
Password: pass123
Full Name: Admin User One
Role: Administrator

SQL>
Another Example (Invalid Credentials):

SQL> @login.sql
Enter Username: admin2
Enter Password: wrongpass

ORA-20001: No user found with Username admin2 and Password wrongpass

SQL>

(Ensure userName uniqueness in a real system.)
Note: It is possible to solve this without using the user_details_rec object type. Instead, we can use individual OUT parameters for each field (userId, userName, password, fullName, roleName) in the stored procedure sp_UserNameAndPassword. The login.sql script can then accept these parameters and display them directly. This approach avoids creating a custom object type while achieving the same functionality.

Eg:
CREATE OR REPLACE PROCEDURE sp_UserNameAndPassword (
    p_userName IN VARCHAR2,
    p_password IN VARCHAR2,
    p_userId OUT NUMBER,
    p_userNameOut OUT VARCHAR2,
    p_passwordOut OUT VARCHAR2,
    p_fullName OUT VARCHAR2,
    p_roleName OUT VARCHAR2
) AS
BEGIN
    -- Fetch user details including roleName
    SELECT u.userId, u.userName, u.password, u.fullName, r.roleName
    INTO p_userId, p_userNameOut, p_passwordOut, p_fullName, p_roleName
    FROM tblUsers u
    JOIN tblRoles r ON u.roleId = r.RoleId
    WHERE u.userName = p_userName AND u.password = p_password;

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RAISE_APPLICATION_ERROR(-20001, 'No user found with Username ' || p_userName || ' and Password ' || p_password);
    WHEN TOO_MANY_ROWS THEN
        RAISE_APPLICATION_ERROR(-20002, 'Multiple users found with given credentials');
    WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR(-20003, 'Error: ' || SQLERRM);
END sp_UserNameAndPassword;
/

-- Enable DBMS_OUTPUT for testing (optional)
SET SERVEROUTPUT ON;
-- login.sql
-- Prompt for user input
ACCEPT username PROMPT 'Enter Username: '
ACCEPT password PROMPT 'Enter Password: '

-- Set server output to display results
SET SERVEROUTPUT ON

-- Declare variables to hold OUT parameters
VARIABLE v_userId NUMBER
VARIABLE v_userName VARCHAR2(50)
VARIABLE v_password VARCHAR2(50)
VARIABLE v_fullName VARCHAR2(100)
VARIABLE v_roleName VARCHAR2(50)

-- Call the procedure with user input
BEGIN
    sp_UserNameAndPassword('&username', '&password', :v_userId, :v_userName, :v_password, :v_fullName, :v_roleName);
    -- Display results
    DBMS_OUTPUT.PUT_LINE('User ID: ' || NVL(:v_userId, 'N/A'));
    DBMS_OUTPUT.PUT_LINE('Username: ' || NVL(:v_userName, 'N/A'));
    DBMS_OUTPUT.PUT_LINE('Password: ' || NVL(:v_password, 'N/A'));
    DBMS_OUTPUT.PUT_LINE('Full Name: ' || NVL(:v_fullName, 'N/A'));
    DBMS_OUTPUT.PUT_LINE('Role: ' || NVL(:v_roleName, 'N/A'));
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/

5. Questions and Answers

For Tables and Sequences:
  1. Q: Why use sequences for RoleId and userId?
    • A: Sequences ensure unique, auto-incrementing primary keys, simplifying data management.
  2. Q: What happens if a roleId in tblUsers doesn’t exist in tblRoles?
    • A: The foreign key constraint prevents insertion, raising an error.
For Stored Procedure:
  1. Q: What is the purpose of the user_details_rec type?
    • A: It defines a structured object to return multiple user details as a single OUT parameter.
  2. Q: What does RAISE_APPLICATION_ERROR do?
    • A: It raises a custom error with a specific code and message (e.g., -20001), handled by the calling script.
  3. Q: How to add a password length check?
    • A: Add IF LENGTH(p_password) < 6 THEN RAISE_APPLICATION_ERROR(-20004, 'Password must be at least 6 characters'); END IF;
For login.sql:
  1. Q: Why use :user_details in the BEGIN block?
    • A: It’s a bind variable that holds the OUT parameter returned by the procedure, accessible in SQL*Plus.
  2. Q: What if the user enters an empty password?
    • A: The query will execute, and if no match is found, NO_DATA_FOUND will trigger the error message.
  3. Q: How to hide password input?
    • A: Use ACCEPT password PROMPT 'Enter Password: ' HIDE (hides input but stores as plain text).
Coding Standards
  • Uppercase keywords, lowercase variables/parameters.
  • -- comments, indent blocks.
  • Prefixes: v_ (variables), p_ (parameters), _seq (sequences).
  • Robust exception handling.
Additional Notes
  • Security: Use hashed passwords (e.g., DBMS_CRYPTO) in a real system.
  • Testing: Verify with SELECT * FROM tblUsers JOIN tblRoles ON tblUsers.roleId = tblRoles.RoleId;

Comments

Post a Comment

Popular posts from this blog

Interview Tips: Dot NET Framework vs Net CORE

Image
Interview Tips: Dot NET Framework vs Net CORE .NET Framework What :  It's a software development framework created by Microsoft, mainly used for building Windows applications. Why :  It has been around for a long time and is very reliable for building large, complex applications especially for enterprises. How :  You use it to build applications that run exclusively on Windows, making use of a vast array of built-in libraries and features. Why Not :  It can't be used for cross-platform applications, so if you need your application to run on other operating systems like macOS or Linux, .NET Framework isn't the right choice. .NET Core What :  It's a newer, open-source, and cross-platform framework from Microsoft, designed for modern app development. Why :  It's lightweight, high-performance, and can run on multiple operating systems (Windows, macOS, Linux). How :  You use it to build modern, scalable, and quickly deployable applications, especially cloud...
Read more

FREE Webinar: Run Your Own Independent DeepSeek LLM

Image
Faith Infotech Academy, Technopark invites you to an exclusive FREE webinar on "Run Your Own Independent DeepSeek LLM" , where you’ll learn how to deploy and manage DeepSeek LLM effectively. Speaker:  Abhilash Nelson  Trainer & Researcher in AI & Machine Learning  An expert in AI and machine learning, Abhilash will guide you through the process of setting up and running your own independent DeepSeek LLM, empowering you with insights into model deployment, fine-tuning, and real-world applications. What You’ll Learn: ✅ Understanding DeepSeek LLM and its capabilities ✅ Setting up DeepSeek LLM on your own infrastructure ✅ Fine-tuning and optimizing the model for various applications ✅ Use cases and practical demonstrations Key Takeaways: ✅ Mastering DeepSeek LLM for Developers ✅ Boost Your VS Code workflow ✅ Learn to Run DeepSeek locally for AI assistance 📅 Date: 6 February 2025, Thursday 🕒 Time:  10.00 to 11.00 AM 📍 Mode: Online (Webinar link will be shar...
Read more

Delegates and Events

1. What are Delegates in C#? Explain: Delegates are type-safe function pointers. They act as a placeholder for methods that have a specific signature (return type and parameters). Analogy: Imagine a delegate as a contract. It specifies the "shape" of the method it can hold. Any method that fits that shape (same return type and parameters) can be assigned to the delegate. Example: public delegate int MathOperation(int x, int y);  // Define a delegate // Methods that match the delegate signature public int Add(int x, int y) { return x + y; } public int Subtract(int x, int y) { return x - y; } // Use the delegate MathOperation operation = Add; // Assign the Add method to the delegate int result = operation(5, 3); // Call the method through the delegate 2. What are Events in C#? Explain: Events are a mechanism for broadcasting notifications to interested parties (subscribers). They are built upon delegates. Analogy: Imagine an event as a doorbell. When someone presses the d...
Read more