Record-Level Security with more than one person assigned to one record

[CJSweatt]

Hi Everyone,

I work in higher ed, and I've got degree lists that need to be verified each term.  In my instance, we have program administrators who can view just their own programs.  Some program administrators have more than one program (curric), and some programs have more than one admin.  This creates a bit of confusion for me as I am trying to figure out how to set up the degree list (list of students who are graduating each term) so that each program admin can see only his/her students.  And in some cases, two program admins must view the same set of students.

I have the following tables set up:

• Master degree list with student names and their currics and GPA.
• Program Admin (names, email, phone)
• Curricula (curriculum name and associated curriculum number)

I'm a bit stumped when I'm trying to put all of this together.  Any suggestions on which piece I am missing?

Thanks so much!

[MayMusic]

So basically what you need to have are the following tables:

1- Programs (Program_ID [Unique])

2- Admins (AdminID [Unique])

4- Program_Admin (PAID [Unique])  // this is to know the programs that are offered by each admin as they can have more than one

3- Students (StudentID [Unique])

4- Student_Programs (StudentID, PAID)  From PAID you can get the name of the program and admin from the corresponding tables.

Now if you create a view to join table1, 2 and 3 and create your authentication based on it, you can restrict access to programs for Admins and more ...

[CJSweatt]

Hi @MayMusic - Thanks so much for the quick reply!!

I've got a few follow-up questions/comments:

1. What do you mean by "(PAID [Unique])"?  What does PAID stand for?
2. The Admins table has a unique email address for each admin.  Will this work?
3. Program_Admins - what data is in this table? Admin IDs (email) and Program IDs?  Would Admins be listed twice here for multiple programs?
4. Students all have a unique student ID number. So that existing table should be fine.
5. Student_Programs - maybe I'm not familiar with PAID and don't know how to work with this

Maybe I'm making this more difficult that it really is...

Thanks again!

 

[MayMusic]

1- A unique ID for each row in Program_Admin table in which we can select Programs for Admins

2- Yes that works 

3- Correct, for each program you select for an  admin you have to reselect the Admin ID /Email and then Program

4- Correct

5- So what I mean in here is that since there is a unique ID for a program from an admin, you can use this ID for students so you do not have to store the admin email and Program ID both. By just selecting the PAID (ProgramAdminID), you can then get the Admin Email and Program ID from  Program_Admins  and then from there you can get the Admin name from Admins table and Program details from Program Table. 

 

Look at the screenshot: http://joxi.ru/LmGLRbzUeqn032

[TsiBiRu]

Hi @CJSweatt,

Just to add up to the previous comments, you can check the links below, as it contains useful information on how to implement Record Level Security to restrict access to Data by User or Role. As well the best practice in designing your table.

https://howto.caspio.com/getting-started/record-level-security-stamping-new-records/

https://howto.caspio.com/tables-and-views/table-and-database-design/

I hope this helps.

Regards,

TsiBiRu