User Level Security question

[jmccullough]

I have a Student Disciplinary database that consists of many schools with a primary key on SchoolID. The database will be accessed by all of the schools. Is there a way to restrict/allow a user(school district) to view only his/her school data.

Thank you

 

[Access_guy49]

I have yet to play with this, but what your looking for is the User level security feature in access. if you open the help up and search for user level secuirty there is an article called "About user-level security (MDB)"

which had some great information about how to get started. from looking at the user settings you can create users and then get right down to specify which tables and queries they have read privilages for. I assume you would need to make queries that pull data for each school, create user accounts for each school to log on with, and then be sure that each school user account had privilages to only view the queries that pulled data for their respective schools.

 

[Banana]

That sounds more like a row level security than table level security, actually.

One way to do this is to write a query that filters records based on district. You would then use User Level Security to lock up access, and make that query Run With Owner's Permissions so they cannot open the table directly but only that query which will filter the data for you.

To get more details on set up of ULS the right way, I recommend this excellent guide.

 

[jmccullough]

Thanks very much for the quick reply. I'll check into this.

 

[Access_guy49]

I assume you would need to make queries that pull data for each school, create user accounts for each school to log on with, and then be sure that each school user account had privilages to only view the queries that pulled data for their respective schools.

Banana - what you stated in your reply is the same as what I said isn't it?
Not trying to come accross as a jerk or anything. lol. I just want to know if what you said is different in any way? (i always like to know if there are better ways to do things, and i also want to make sure i didn't give bad advice!)

 

[Banana]

Actually, I missed that bit, and only wanted to emphasize that because what he is really after is a row level security, which isn't supported per se via ULS, but rather by creating queries with RWOP. If RWOP isn't implemented, then they would be either locked out totally, even from data that they should have had access to, or would be able to read the whole things as ULS deals at objects level, not at row level.

The other reason is that this let us get away with making just one query that uses criteria to check the logged user, match it to the district and filter data based on the district, so it works for anyone without needing to have query for each user. With RWOP permission and no permissions to edit the query, they can't alter the query definition and always will get their own data.

Nothing about your advice is bad, actually.

 

[Access_guy49]

OHHHH ok i get what your saying now. OK awsome, i quite like that! (This is why i love forums! there are always people out there with different and sometimes more efficient ways of doing things!) Thanks banana!