Securely passing parameters from tabular reports.

[oliverk]

Hello

I have a question about passing parameters from report datapages. I have a gallery datapage that allows users to select a project to work on. There is a link attached to each project, which passes the project ID as a query string. It works. The gallery is filtered so users only see projects that they have permission to access.

However, this means that the project ID is visible. I think this hampers security - if someone else saw this project ID then they could potentially use it to log into a project that they don't have permission to access.

I think my options are:

1. Have the gallery datapage pass through to a new submission datapage, and use a secondary code as the query string (not the main project ID). The submission datapage then ensures that the user has permission to access the project (checking on a Match table), and if so passes them through to the main project page, passing the Project ID through as an internal parameter. This way the project ID is kept secure, and only users with correct permissions can access the project. However, it's a bit fiddly, uses an extra data page, and I still reveal the secondary code to users.
2. Convert my gallery datapage to a submission page, so that the project ID is passed internally. However I then lose all the benefits of having a gallery report... I can use a cascading dropdown for users to select projects, but I can't show additional project details for each of the projects.

Does anyone have any thoughts?

Many thanks!

[Jodie]

Is the main project page not checking user credentials?

What type of information are you looking to share with the user through the link or does this move into a new workflow step? If you're just sharing info, would a tool tip instead of a link provide the user with all the info and without the hassle of logging back and forth between pages?

Also for some reason, I keep wondering whether a bootstrap modal might bring the page up without exposing the id. I've deleted all my modals because my Wordpress template butchers the display so I can't check for you.

good luck

[oliverk]

Hi Jodie, many thanks for the response.

The main project page checks credentials in so much as you have to be logged in. But just because a user is logged in, it doesn't mean they have access to all projects. The projects they have access to are determined through a match table. So to check whether a user is allowed access to a specific project (the one they entered the external parameter too) then I believe I'd need a separate data page - i.e. option 1 above.

Unless there's another way to do it?

Thanks!

[DrSimi]

Hi @oliverk,

I believe what you are looking for is Record Level Security, so that only authorized users can see records. This is different from authentication in that if a user is not authorized to see a specific record, they wouldn't be able to see it, but they can still log in.

See Caspio's HowTo - Record Level Security

[JoeyBrannon]

I have this same issue and unfortunately I cannot figure out how to get record level security to address it. The problem in the above instance is that the authentication needs to be based on three parameters....

username

password

project

And this would require users to re-authenticate when they switch projects. Not a workable solution.

[DrSimi]

Hi @JoeyBrannon,

RLS can be used in a way that you have the Authentication fields unchanged. In your scenario, you would leave the user login as normal (just username and password), but in your Projects table there should be a field such as "assigned_to", where their username would be stored. Then, RLS would be linked with the username from the auth to the username in the Projects table.

If multiple users have access to the same project, then I suggest creating access "groups" - this would be a new field in the Authentication, but would not be input by the user on login, it should be maintained by whoever assigns access levels through the table or a specific screen to update the user profile. Then, the Project "assigned_to" field would store the Group name instead of the username, and all users with the same Group name assigned would be able to see that Project.

Hope this helps.

[futurist]

Hi, 

Just wanna share this customization solution I came up with if you want to make gallery report datapages look more appealing:

 

[JoeyBrannon]

The solution I landed on seems to be working pretty well. It requires a third table to establish a many-many relationship between users and projects. Here is the format

USER table is used for authentication and user has a unique id called userID

USERASSIGNMENTS table contains two fields; projectID and userID

PROJECTS field contains projectID

A view relates PROJECTS and USERASSIGNMENTS using the fields PROJECTS.projectID and USERASSIGNMENTS.projectID in a one-to-many relationship

Now all records returned by the passed parameter can be subject to RLS by checking the field USERASSIGNMENTS.userID against the authentication field USER.userID.

The only downside is that if there are 10 users assigned to a project the view will return 10 records related to that project. RLS ensures that only one of those records shows up in the results, but it may cause undesired overhead as the database grows. But it does solve the problem of unwanted access if a user who is not assigned to the project gets hold of the projectID through a copied url or if they were to piece it together themselves.

[oliverk]

Hi everyone, thanks for all of the input into this.

I will check out the solution that @JoeyBrannon gave using RLS. I think the previous RLS example given by @DrSimi would not work for me as users can be assigned to multiple 'groups' on by multiple people across different accounts. (Unless I have misunderstood it).

Separately, I have found a way to hide the Project ID when using the gallery view. I now have two IDs for a project - a public ID and a private ID. The public ID is the one that is used in the query string. This takes the user to a datapage used specifically to check their credentials. This datapage checks whether they have access to the project - according to the match table, and then if so it passes the private ID as an internal parameter.

I have put the formula below. I'd welcome any feedback on this - particularly on how secure it is! I believe there is no way to see the private_ID through inspecting the page if you don't have access according to the Match table. [In the formula, "User_Project_ID_Match" is just the record ID from the match table ("Match_User_Project"). "Project_ID" is the private ID... the rest is hopefully self explanatory.]

One thing I would like to know is if it's possible to prevent people overriding the internal Caspio parameter through a query string. As in - on a datapage, can you prevent the use of query strings?

Thanks!

Oliver

CASE
WHEN
(
SELECT COUNT (User_Project_ID_Match)
FROM Match_User_Project
WHERE Project_ID = (SELECT Project_ID FROM List_Projects WHERE Public_ID = target.[@field:Project_ID_Public])
AND
User_ID = '[@User_ID]'
)
= '1'
THEN (SELECT Project_ID FROM List_Projects WHERE Public_ID = target.[@field:Project_ID_Public])
ELSE ''
END