I am trying to write a pl/sql procedure that functions as a trojan horse program
ID: 3751901 • Letter: I
Question
I am trying to write a pl/sql procedure that functions as a trojan horse program between user A and user B.
User B cannot access the employee table. Only user A can. The idea is to write a procedure that finds a way to bypass this to retrieve the data for user B. So, for example, when user A is granted access to this procedure from user B, if user A passes the employee table into the procedure parameter, this will copy the data into an alternate table or some other way, perhaps using a cursor or refcursor to fetch the data, so that user B can access it. The table name for user A is employees, which is listed below. I just have to transfer the employee_id, not all of them.
CREATE TABLE EMPLOYEES (
EMPLOYEE_ID NUMBER(7) NOT NULL,
FIRST_NAME VARCHAR2(10) NULL,
LAST_NAME VARCHAR2(10) NULL,
JOB_ID NUMBER(4) NULL,
MANAGER_ID NUMBER(4) NULL,
HIRE_DATE DATE NULL,
SALARY NUMBER NULL,
DEPARTMENT_ID NUMBER(2) NULL
);
Explanation / Answer
As per what I understood from the given problem is that, User A has access to employees table but user B dosn't have access to the table. So in order to make employees table accessable to user B, we want to create a procedure which will have employees table as a parameter and the procedure copies the table to another table in order to make data of employees table accessable to user B. The procedure is as folllows :
First of all you need to create a package in order to create a type of the employees table so that this type can be passed as parameter in the procedure which wil take reference from the original employees table. The package declaration is as follows :
create or replace package copy as
type emp is table of employees%rowtype;
end copy;
/
Now you need to create a procedure ehich will take the type name as the parameter which will reference the original employees table. The procedure declaration is as follows:
create or replace procedure passing(emp_data in out copy.emp) as
BEGIN
select * bulk collect into emp_data from employees;
end passing;
/
Now you need to create a table with same colums as in original employee table say EMPCOPY.
CREATE TABLE EMPCOPY(
EMPLOYEE_ID NUMBER(7) NOT NULL,
FIRST_NAME VARCHAR2(10) NULL,
LAST_NAME VARCHAR2(10) NULL,
JOB_ID NUMBER(4) NULL,
MANAGER_ID NUMBER(4) NULL,
HIRE_DATE DATE NULL,
SALARY NUMBER NULL,
DEPARTMENT_ID NUMBER(2) NULL
);
Now finally you can call the procedure passing and store all the data of original employees table into EMPCOPY table. The idea is both table has primary key attributes, so if you try to copy all the data everytime into EMPCOPY table then primary key constraint will be violated. So everytime you need to truncate the table and insert all the data into the new table. This will increase the efficiency. The code is as follows:
declare
empd copy.emp;
begin
execute immediate 'truncate table EMPCOPY';
passing(empd) //Passing the employees table reference.
for i in empd.first..empd.last loop
insert into EMPCOPY values(empd(i).EMPLOYEE_ID, empd(i).FIRST_NAME, empd(i).LAST_NAME, empd(i).JOB_ID, empd(i).MANAGER_ID, empd(i).HIRE_DATE, empd(i).SALARY, empd(i).DEPARTMENT_ID);
end loop;
end;
/
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.