Show mapping based on student access-CodePudding

There is a table which has following data:

student       subject      code
student1      maths        312
student1      physics      785
student2      english      900
student3      geography    317

I am trying to restrict access to each student in the table to view data specific to their chosen subject, but there is one restriction to show maths data to student2, thereby both student1 and student2 both would be able to see maths data, and this mapping has to be done without altering the master data, so only while displaying the table, student2 should be mapped to both english and maths

Thanks for the help here!

CodePudding user response：

One option is to - as you said - temporarily use UNION set operator. Something like this:

SQL> WITH
  2     test (student, subject, code)
  3     AS
  4        (SELECT 'student1', 'maths', 312 FROM DUAL
  5         UNION ALL
  6         SELECT 'student1', 'physics', 785 FROM DUAL
  7         UNION ALL
  8         SELECT 'student2', 'english', 900 FROM DUAL
  9         UNION ALL
 10         SELECT 'student3', 'geography', 317 FROM DUAL)
 11  SELECT *
 12    FROM test
 13   WHERE student = '&&par_student'
 14  -- add this to your query
 15  UNION
 16  SELECT 'student2', 'maths', NULL
 17    FROM DUAL
 18   WHERE '&&par_student' = 'student2';

Enter value for par_student: student1  --> student1 is OK, it has two subjects

STUDENT  SUBJECT         CODE
-------- --------- ----------
student1 maths            312
student1 physics          785

SQL> undefine par_student
SQL> /
Enter value for par_student: student2  --> for student2, UNION is used

STUDENT  SUBJECT         CODE
-------- --------- ----------
student2 english          900
student2 maths

SQL> undefine par_student
SQL> /
Enter value for par_student: student3  --> nothing new for student3

STUDENT  SUBJECT         CODE
-------- --------- ----------
student3 geography        317

SQL>

Depending on tool you use, parameter might look as this (in e.g. TOAD):

WHERE student = :par_student

or any other way parameters are used in that tool of yours.

CodePudding user response：

Something like:

SELECT student, subject, code
FROM   (
  SELECT t.*,
         COUNT(
           CASE
           WHEN student = :your_student
           OR   (:your_student, subject) IN (('student2', 'maths'))
           THEN 1
           END
         ) OVER (PARTITION BY subject) AS has_access
  FROM   table_name t
)
WHERE  has_access > 0

Then, for the sample data:

CREATE TABLE table_name (student, subject, code) AS
SELECT 'student1', 'maths',     312 FROM DUAL UNION ALL
SELECT 'student1', 'physics',   785 FROM DUAL UNION ALL
SELECT 'student2', 'english',   900 FROM DUAL UNION ALL
SELECT 'student3', 'geography', 317 FROM DUAL;

If :your_student is student1 then the output is:

STUDENT SUBJECT CODE

student1 maths 312

student1 physics 785

STUDENT	SUBJECT	CODE
student1	maths	312
student1	physics	785

and if :your_student is student2 then the output is:

STUDENT SUBJECT CODE

student2 english 900

student1 maths 312

STUDENT	SUBJECT	CODE
student2	english	900
student1	maths	312

db<>fiddle here