Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Dec 2005
    Posts
    7

    Unanswered: Restrict access to tables but not individual records

    hi

    I am creating a database that will be accessed by many people for checking data. However, I only want them to be able to see their own records without being able to update, delete or create data. Is it possible to do so by creating separate user accounts? Or is there any other way?

    btw, I'm still a beginner at this, so it would be much appreciated if any response be stated in simple terms. Thanks alot!

  2. #2
    Join Date
    Nov 2003
    Location
    Sussex, England
    Posts
    404
    Before replying in detail, can I ask a simple question. How familiar are you with Access security? (.mdw files)

  3. #3
    Join Date
    Dec 2005
    Posts
    7
    i'm afraid to say....minimal knowledge...perhaps near to nothing.

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    This can be a deceptively complicated question. I can offer one of an array of approaches:

    Since many people will be using the database, a split db is probably a viable choice. Actually, there are those that say even a single user database should be split for various reasons.

    Compile the front end into an mde. This locks all of the code in the project. If you set up forms for the user to interface with and the data is in a seperate location, you can be reasonably assured that your users will only see the data you have explicitly given them a means to view.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by momo22peach
    I only want them to be able to see their own records without being able to update, delete or create data.
    Hi momo22peach - welcome to the forum
    I have one question - how is data attributed to them if they cannot create or edit records? Or is it attributed to, say, a department and they can access the data based on this?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Dec 2005
    Posts
    7
    Quote Originally Posted by Teddy
    Since many people will be using the database, a split db is probably a viable choice. Actually, there are those that say even a single user database should be split for various reasons.

    Compile the front end into an mde. This locks all of the code in the project. If you set up forms for the user to interface with and the data is in a seperate location, you can be reasonably assured that your users will only see the data you have explicitly given them a means to view.
    erm...i don't quite understand some of ur terms. For eg, 'front end' and 'code'. Are forms ussed to only create/update data? I only want other users to view their own data. and what do u mean by 'the data is in a separate location'??

    pardon me for my ignorance.

  7. #7
    Join Date
    Dec 2005
    Posts
    7
    Quote Originally Posted by pootle flump
    Hi momo22peach - welcome to the forum
    I have one question - how is data attributed to them if they cannot create or edit records? Or is it attributed to, say, a department and they can access the data based on this?
    Thank u for welcoming me!

    Don't exactly understand ur qn, but hopefully what i say below will give u ur ans.

    I will be the one creating, updating etc the data. Anyone else will only be allowed to see what they are allowed to by giving them a special code(which will act as the primary key in the table). btw, I only have one single table.
    Did i answer ur qn??

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by momo22peach
    I will be the one creating, updating etc the data. Anyone else will only be allowed to see what they are allowed to by giving them a special code(which will act as the primary key in the table). btw, I only have one single table.
    Did i answer ur qn??
    Cheers momo
    Giving the user the primary key code for each records will really slow them down assuming that each user will be able to access several rows.
    Why not create a second table that has two columns - the primary key of the first table and then the user that is allowed access to that row. These two columns would be the primary key of your new table (assuming only one person can view any single record) with the first column the foreign key.
    If security is not a major issue (and if it is then it sounds a bit unfair to give you the project as you are a self professed noobee) the user can simply enter their name as a parameter when the report loads and they will see all the records they are entitled to.
    For more "robust" security, you could enter a code in the table instead of their name or you could use programming code to read their NT login.

    Idea?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    is their anything in the data that uniquely identifes which data 'belongs' to which user(s), such as a department code, or their userid?

    Use a form which open in read only mode, set the from properties to allow edits=no, allow deletions=no, allow additions=no

    agree with pootleflump, that you could pull the userid off from the network, test that userid and see if it was yours and set the edits / deletion / additions accordingly. that way round you can do anyhting you want. A word of caution its very hard to stop users doing anything directly to a table in access. to a certain extent it depends on trust and integrity of the people using the db. if neither of these apply then you have potentailly a hard road ahead to fully secure the data.

    if you want to share the data, so that many users can view the same data / same application then you need to consider soplitting the Access file into two, a front end which contains the forms , reports and user interface, and a back end which contains the data.

  10. #10
    Join Date
    Dec 2005
    Posts
    7
    thank u everyone for replying my posts!!

    I read and re-read all ur comments and i finally got an inkling about what all of u are saying. I'm gonna try out the read-only form first, to see if it's what i need. I am also very interested in healdem's suggestion of splitting the file into 2 as it sounds perfect/ just what i need. However, I have no idea how to go about it. Perhaps someone can guide me??

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by momo22peach
    I am also very interested in healdem's suggestion of splitting the file into 2 as it sounds perfect/ just what i need. However, I have no idea how to go about it. Perhaps someone can guide me??
    Hi
    Easiest is to start with a split database. The principle is to keep the data (BE or back end) seperate from the presentation (FE or front end). The BE will typically be accessible to everyone (on a network share for example) while each user would have their own copy of the front end

    To demonstrate - create a db with a table. This is your back end. Create a second database. Link to your back end (open the tables section of your database--> Right click the window and select "Link Tables"--> Browse to you BE--> Select the table). You can now treat that linked table as though it were local in your FE.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Dec 2005
    Posts
    7
    Quote Originally Posted by pootle flump
    Hi
    Easiest is to start with a split database. The principle is to keep the data (BE or back end) seperate from the presentation (FE or front end). The BE will typically be accessible to everyone (on a network share for example) while each user would have their own copy of the front end
    You mean everyone still can access the BE? Can i restrict the access to me myself only? This problem goes back to the original one, whereby I want people to be able to see the forms and query but not the table itself. Parameters are set on the forms and queries to make sure that people can only see what they are allowed to. Is there anyway to set parameters on the table that is similar to the forms/queries, such that a password is needed to open up the table?

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    The short answer is no. You can put together some fairly sophisticated bespoke security but it is quite time consuming and complicated. The sort of thing you are looking for is quite simple with a server based database (such as SQL Server) but is not what Access was really designed for.

    I am not sure whether it was on this thread or another but Healdem mentioned that with access there is a requirement of a little trust of users - to replace this trust with a technical fix is not a simple matter unfortunately.

    To switch things around and play with the question - your problem is that you want to maintain a database and have certain users view (but not alter) certain rows. Have you considered not providing access to the db at all and emailing the data to the users? This can be virtually automated with some relatively somple code.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Dec 2005
    Posts
    7
    Quote Originally Posted by pootle flump
    To switch things around and play with the question - your problem is that you want to maintain a database and have certain users view (but not alter) certain rows. Have you considered not providing access to the db at all and emailing the data to the users? This can be virtually automated with some relatively somple code.
    Hmm...How does this emailing 'thing' work? Will the email be sent automatically when the individuals ask for the data via (???) ? Because the current situation is, I have all the data. If someone needs to know his own record, he will come to me and i will tell him what he wants to know. I thought a database can eliminate this procedure, as in, the individuals will look up their own records in the database instead of coming to me everytime. If the 'email solution' doesn't improve the current situation, then I guess MS Access is really not suitable for my problem.

  15. #15
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    we are drifting away from your original question, but i use pootle's mail idea for distributing regularly updated data that users of my applications are not authorised to edit.

    the data source runs a user-specific query to .CSV and e-mails to the users at intervals (to discuss later)

    each user has an outlook rule that auto-deletes the inbound mail from Xsender with Ysubject.

    each user starts his Access client application whenever he likes with the option to update data if he chooses (else run on previously imported data).

    to update, the client Access programme roots around in Outlook's deleted box, locates the most recent msg from Xsender with Ysubject, and imports the data from the .CSV attachment.

    the user can then report/play/trash his data anyway he likes.
    it is HARD-CORE read only as far as the data source is concerned - the user's .CSV is totally disconnected from the source db.

    i do this with mail from a db server sent when certain trigger events occur.

    you could do something identical from an Access db on your machine. an automailer sends query returns in .CSV as mail attachments to each user...

    ...the pending question is WHEN.

    that's your choice.
    - manually whenever you decide to click a button
    - windows scheduler runs the mailer every hour/day/week/whatever as long as the datasource (your) machine is running

    why Access for the clients if i'm sending a .CSV ?????
    - access is way more powerful for reports
    - i'm much more comfortable with Access than Excel
    - it's easy to build an application that really works for the clients so they don't even notice the "workaround" e-mail data distribution


    it's not Access-101, but all the bits you need are on this site (correction: were on this site before it got trashed). if you try this approach and get stuck. post.

    izy
    currently using SS 2008R2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •