Hi,
I want to create a plsql stored procedure to create and validate user login for an application.The username and password will be stored in the login table.
The table creation syntax are
Create table svx_person
( person_id number,
name varchar2(30)
);
alter table svx_person add constraint PID_PK primary key (person_id);
Create table svx_role
(role_id number,
description (number)
);
alter table role add constraint rid_PK primary key (role_id);
create table svx_person_role
(svx_person_role_id number,
person_id number,
role_id number);
alter table SVX_PERSON_ROLE add constraint PERSON_ROLE_ID_PK primary key (SVX_PERSON_ROLE_ID)
alter table SVX_PERSON_ROLE add constraint PERSON_ID_FK7 foreign key (person_id) references SVX_PERSON (PERSON_ID);
alter table SVX_PERSON_ROLE add constraint ROLE_ID_FK3 foreign key (ROLE_ID) references SVX_ROLE (ROLE_ID);
create table svx_login
(login id number,
password varchar2(20),
svx_person_role_id number);
alter table svx_login add constraint LOGIN_ID_PK primary key (LOGIN_ID);
alter table svx_login add constraint PERSON_ROLE_ID_FK foreign key (SVX_PERSON_ROLE_ID) references SVX_PERSON_ROLE (SVX_PERSON_ROLE_ID);
The data in the tables are
insert into svx_person values (1,'abc');
insert into svx_person values (2,'def');
insert into svx_person values (3,'ijk');
insert into svx_role values (1,'admin');
insert into svx_role values (2,'individual');
insert into svx_person_role values (1,1,1);
insert into svx_person_role values (2,1,2);
insert into svx_person_role values (3,2,1);
insert into svx_person_role values (4,3,1);
The Pl/SQL procedure I wrote is
CREATE OR REPLACE PACKAGE BODY svx_user_security AS
PROCEDURE add_user (p_login_id IN SVX_LOGIN.LOGIN_ID%TYPE,
p_password IN SVX_LOGIN.PASSWORD%TYPE,
p_person_role_id IN SVX_LOGIN.SVX_PERSON_ROLE_ID%TYPE) IS
v_person_role_id number;
BEGIN
select pr.svx_person_role_id into v_person_role_id
from svx_person p,svx_person_role pr
where p.person_id=pr.svx_user_id;
-- populate the login table
INSERT INTO svx_login (login_id, password,svx_person_role_id )
VALUES
(
p_login_id,
svx_encrypt_decrypt.encrypt_val(p_password),
v_person_role_id
);
COMMIT;
END;
PROCEDURE valid_user (p_login_id IN SVX_LOGIN.LOGIN_ID%TYPE,
p_password IN SVX_LOGIN.PASSWORD%TYPE) AS
v_dummy VARCHAR2(1);
BEGIN
SELECT '1'
INTO v_dummy
FROM svx_login
WHERE login_id = p_login_id
AND password = svx_encrypt_decrypt.encrypt_val(p_password);
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20000, 'Invalid username/password.');
END;
FUNCTION valid_user (p_login_id IN SVX_LOGIN.LOGIN_ID%TYPE,
p_password IN SVX_LOGIN.PASSWORD%TYPE)
RETURN BOOLEAN AS
BEGIN
valid_user(p_login_id, p_password);
RETURN TRUE;
EXCEPTION
WHEN OTHERS THEN
RETURN FALSE;
END;
END;
I have created a procedure for password encryption and decryption which is working fine.
But this login procedure is not inserting any value in the svx_login table.I want a login_id to be created for every person who has a role in the database
Can you please provide some guidance?