Using views to restrict access.
I have been reviewing the docs but I am still quite stuck.
So say I have a table called classroom that contains name, subject, and grade. If I wanted to give access to users to view their name and subject, but I wanted to order by grade. I could either
Separate the two, have a public table of ID, name, subject. And a private table linked with their ID and grade. Then use a view to combine and sort and that way even if they try and do select* they wont be able to see the grades because they don't have permission on the underlying table to see it right?
But in this case they have access to the underlying table of name and subject and so if i wanted to hide other columns in the table such as the created and updated date I wouldnt be able to? So instead could have the VIEW only in public and accessible via API, and have both tables in a schema that ISNT accessible via API but enable RLS but with access to anon/auth on the table with name/subject but no access to to the table with ID grades? So that way they couldn't even query other columns in either table?
If that was the case could I then just have one table that has all of their data of ID, name, subject, grade in one table that is in a private schema. Enable RLS access to anon,auth, then put the VIEW in public accessable via api but only with name, subject columns? Is that less secure?
Thank you in advance!
So say I have a table called classroom that contains name, subject, and grade. If I wanted to give access to users to view their name and subject, but I wanted to order by grade. I could either
Separate the two, have a public table of ID, name, subject. And a private table linked with their ID and grade. Then use a view to combine and sort and that way even if they try and do select* they wont be able to see the grades because they don't have permission on the underlying table to see it right?
But in this case they have access to the underlying table of name and subject and so if i wanted to hide other columns in the table such as the created and updated date I wouldnt be able to? So instead could have the VIEW only in public and accessible via API, and have both tables in a schema that ISNT accessible via API but enable RLS but with access to anon/auth on the table with name/subject but no access to to the table with ID grades? So that way they couldn't even query other columns in either table?
If that was the case could I then just have one table that has all of their data of ID, name, subject, grade in one table that is in a private schema. Enable RLS access to anon,auth, then put the VIEW in public accessable via api but only with name, subject columns? Is that less secure?
Thank you in advance!
