Restricting access to database content by user?

Restricting access to database content by user?

by Stewart Brouillette -
Number of replies: 19

Well, I have successfully created my database template and it is working. My next task is to restrict access. The database I have built is designed to hold employee training records. There are categories which indicate the training they are supposed to take part in, and their scores on any tests.

I need to be able to restrict access to the database such that: Individual employees can view their own record, but not that of other employees. First-level managers, on the other hand, need to be able to view records of all employees under their responsibility. Senior managers need to be able to view all records. Both I and my instructors need to be able to view and edit all records.

I have done a test run with a test student account and a test guest account, and both are able to see all records - I can't have that. If I "hide" the database, then only I as an admin have access. It's like it's one extreme or the other.

Would appreciate any insight on how to do what I am trying to do. If I can't do this, then I have to restrict viewing to only managers and make them admins (which I REALLY don't want to do).

Thanks.

Average of ratings: -
In reply to Stewart Brouillette

Re: Restricting access to database content by user?

by Jason Hollowell -
Picture of Particularly helpful Moodlers
Stewart,

I'm currently battling with the database myself and haven't made much progress so I may not be the most qualified person to give advice but...

Have you considered/tried using a combination of groups (maybe separate groups - one for each person) and role overrides to accomplish what you want to do?

I think "separate groups" will allow you to do what you want to for "individual employees" and you can probably set up an override for "senior managers" so they can see all records but can't edit them. I think the best thing to do would be to create a new role...you could call it senior managers smile and then tweak the permissions so those assigned to that role are allowed what you want them to be able to do but no more.

I'm not sure how to make the "first-level managers" work though.....thoughtful If you create groups of employees that they (the first-level managers) are in charge of then all of those employees will be able to see each other's records... Anybody have any creative ideas for the "first-level managers"??


Jason
Average of ratings: -
In reply to Stewart Brouillette

Re: Restricting access to database content by user?

by David Bogner -
Picture of Core developers Picture of Plugin developers

Hi Stewart,

the solution provided by Jason seems already to be the key for the problem. If you combine the group technique and use two different databases, link the databases and entries with an appropriate link field.

Create for each field of responsibility (department) a database, then assign the roles "individual", that the person can only view his record in the database of his department.

Create (overwrite) a role for the department responsible and the respective database, so he has the right to see the database and edit it.

Create a role for the senior who can edit and see all databases.

Perhaps this is complicated, but perhaps it could work.

I don't know if this response could be understood, but perhaps it could lead to some mor reflections.... Perhaps combining groups and roles could lead to the same result?

Good luck,

David

Average of ratings: -
In reply to David Bogner

Re: Restricting access to database content by user?

by Jit San Chia -

Hi,

Another variable you can play with here is the "Require approval" setting. Setting it to "yes" will make individual entries invisible to others (except managers) Then you save the trouble of having to set a group for each individual. But managers have to remember not to "approve" the entries : ) To be absolutely safe, you may go to the templates and remove the "approve" link altogether.

You will probably still need to have multiple databases, one for each department, as suggested by David.

And of course you need to set the permissions right for each role.

Average of ratings: -
In reply to Jit San Chia

Re: Restricting access to database content by user?

by Stewart Brouillette -

Thanks folks - all good suggestions that merit further investigation.

Couple of talking points, as-it-were: The project for which I'm doing this is the largest of our company - employs about 250- and although we are the "guinea pigs", this is the project most in need of something like this. As far as breaking it down by department, the breakdown is thus: "Engineering", "Logistics", "Software/Hardware Training", and "Hardware Installation". The first focus group are the engineers, of which there are about 100.

I'm musing over breaking it down by department. That may be a good approach.

I've already started building the records into a single database, so that's going to have to be first generation. Maybe I can explore the other options for the next generation.

Thanks again...

Stew

Average of ratings: -
In reply to Stewart Brouillette

Re: Restricting access to database content by user?

by Helen Foster -
Picture of Core developers Picture of Documentation writers Picture of Moodle HQ Picture of Particularly helpful Moodlers Picture of Plugin developers Picture of Testers Picture of Translators
Hi Stewart,

Another method of restricting access, available in Moodle 1.9 onwards, is by assigning a database activity to a particular grouping. Please see the groupings documentation for further information.
Average of ratings: -
In reply to Jit San Chia

Re: Restricting access to database content by user?

by Lee Cosner -

Hi,

I've been  trying this based on an earlier suggestion to me however I can't seem to figure out the Approve portion.  When I build the database I was told to set it Approve Entries, but then to keep it private, do not approve.  Somehow I'm missing something.  I have the database set up, but when photos are uploaded (my database is for photos that must be taken by the student) everyone can still see them.  In fact, I don't even see a place to Approve or Not Approve.  The only place I've seen that is when I built the original template.  What am I missing?

Thx,  Lee

Average of ratings: -
In reply to Lee Cosner

Re: Restricting access to database content by user?

by Helen Foster -
Picture of Core developers Picture of Documentation writers Picture of Moodle HQ Picture of Particularly helpful Moodlers Picture of Plugin developers Picture of Testers Picture of Translators
Hi Lee,

Please note that database entries added by teachers or administrators never require approval - only entries added by students. You may wish to create a test student account for checking things out.
Average of ratings: Useful (2)
In reply to Helen Foster

Re: Restricting access to database content by user?

by Mich P -
This 'automatic approval' for teachers and administrators, does this also apply to entries uploaded by cvs file?

We would like to start off the database by importing our paper-based records, so that students can continue editing their 'submissions' in Moodle.

But we definitely don't want to approve the entries because they would be visible by all students and we need to keep them private.

There are too many users to create individual groups for them.
Average of ratings: -
In reply to Stewart Brouillette

Re: Restricting access to database content by user?

by Paul Fynn -

Hi Stewart,

I've just been through this, and its' not particularly clear even through the documentation. In my view it would be far more straightforward to have a capability defined at individual level for activities which can currently be set by group - any activity could then be private to teachers and individual students.

There are two issues in trying to use the **approve** function.

  1. **approve** does not appear in all of the templates (eg 'add' template) and I found putting it into templates idiosynchratic - perhaps I needed to reload pages to get the code to function, or maybe a tecchie could identify if it is to do with another setting. It actually doesn't matter because the **approve** requirement, if selected, appears to operate even without being visible on a template and (as noted on a thread) its safer to leave it off.
  2. The approve function does not seem to apply to admin and teacher roles (this was quietly noted earlier in this thread?). If you are testing using teacher logons, the contributions will immediately be public, and you won't see the approve function taking effect. You need student test accounts to see it work.

I have a preset which I am happy to load up which is set up for student research logs/diaries - can anyone advise how I get a .zip file into this area of moodle discussion??

Thanks, Paul

Average of ratings: -
In reply to Paul Fynn

Re: Restricting access to database content by user?

by Ben Davis -
Maybe database is not the right module for this function.


It sounds like the gradebook might be a better fit for what you want to do. Create a new course called employee training, and create assignments for 'course completion'. This way, you don't need to create all sorts of work arounds, users can only see their 'grades' , section instructors can see their section grades, and course creators can see all grades.


Average of ratings: Useful (1)
In reply to Paul Fynn

Re: Restricting access to database content by user?

by Helen Foster -
Picture of Core developers Picture of Documentation writers Picture of Moodle HQ Picture of Particularly helpful Moodlers Picture of Plugin developers Picture of Testers Picture of Translators
Paul, thanks for offering to share your student research logs/diaries database preset. If the zip file is less than 100KB you can attach it to a forum post, otherwise please attach it to tracker issue MDLSITE-303 to give us a nudge about creating a database activity for sharing database presets. wink
Average of ratings: -
In reply to Helen Foster

Re: Restricting access to database content by user?

by Paul Fynn -

Thanks Helen,

The preset above is a first attempt at encouraging students to 'plan, do, review' and offers four different record types - Research Diary, Tutorial Record, Action Plan, or Reflective Record.

If Database settings is set to  'approval required'  only tutors can see all records, unless the post is Teacher or Admin.

As a side point, it would be useful to have a link into the database presets function which did not involve going through a course?

Paul

Average of ratings: Useful (1)
In reply to Paul Fynn

Re: Restricting access to database content by user?

by Allison Soo -
I am creating a database for students to enter a particular project information. Students have to log into the course before they can enter the information.

First question:
When students adding the entry, is there any way or somewhere that I can get the identity of signed-in student and put it into one of database field, for teacher to grade their work later on ? I thought of letting students to put in their name manually in a text field but not favourable this might leads to data confusion or potential errors.

Second question:
Am I allow to restrict the viewing of certain fields in the database ?

Appreciate any advice or guildance. Thank you.

Average of ratings: -
In reply to Allison Soo

Re: Restricting access to database content by user?

by Paul Fynn -

Hi Alison,

Question 1 - you may find it easier to take upload and try out a database preset at http://moodle.org/mod/data/view.php?id=7303  There are various fields for name which appear in the format ##user## etc, rather than within the menus, but this is not (in my view) intuitive.

Question 2 - I don't know - I haven't noted one, but it would be useful to be able to define different views for different people. Generally speaking you could just not include a field after 'add data' (ie not in list or single view templates) but that doesn't give the people that you want to give access to the data.

Average of ratings: -
In reply to Stewart Brouillette

Re: Restricting access to database content by user?

by Brian Piltin -
I am new to Moodle and this is the first activity I tried to add and I am having this same problem. It seems as if the database activity has no concept of individual record "owners". Ideally, each record in the database would have an "OwnerID" and there could be an option set within the db permissions to allow full access to owners, but restricted access to everyone else, and that being for individual records...
Average of ratings: -
In reply to Brian Piltin

Re: Restricting access to database content by user?

by Mary Cooch -
Picture of Documentation writers Picture of Moodle HQ Picture of Particularly helpful Moodlers Picture of Testers Picture of Translators
HI Brian. What exactly is it that you want to do with the database? Maybe there is an easier option. I am not sure if this is useful to you but if you set the database so that entries always have to be approved before they appear -and you never approve them -then students have their own private entries that you and they can see and nobody else can.
Average of ratings: -
In reply to Mary Cooch

Re: Restricting access to database content by user?

by Brian Piltin -
Thanks Mary. I tried that and realized that it works fine. Now if only I could find an easy way to re-order the fields in the database record. I'm assuming the only way to do it is with the templates. Either way, thanks for your response.
Average of ratings: -