Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Location
    LA, CA
    Posts
    18

    Unanswered: Newbie question: Access 2003 Security...

    I am designing a database in which each user must have full access to his/her own data, and read-only access to other people’s data.
    So, can I have multiple users use a single set of tables in a way that each one of them has full access to his/ her own data, but read-only access to other people’s?
    Or is it easier to have a set of tables for each user and allow all others to have read only access to them?
    Note that I will need to run reports that include all data in the system.
    Any help is much appreciated!!!
    Last edited by matatan; 01-09-04 at 02:35.

  2. #2
    Join Date
    Oct 2003
    Location
    US
    Posts
    343

    Re: Newbie question: Access 2003 Security...

    Using default security feature won't help you here. All these security features are applied on object not on data.

    But what you can do is add one extra field to all of your tables and call it as CREATED_BY. Store the name of the user who created that record. Now anyone can see it but if someone tries to update it you can put some code to validate the user and see if that is the same user who created this record. If yes let them update the record otherwise not.


    Originally posted by matatan
    I am designing a database in which each user must have full access to his/her own data, and read-only access to other people’s data.
    So, can I have multiple users use a single set of tables in a way that each one of them has full access to his/ her own data, but read-only access to other people’s?
    Or is it easier to have a set of tables for each user and allow all others to have read only access to them?
    Note that I will need to run reports that include all data in the system.
    Any help is much appreciated!!!

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1

    Re: Newbie question: Access 2003 Security...

    Originally posted by khan
    Using default security feature won't help you here. All these security features are applied on object not on data.

    But what you can do is add one extra field to all of your tables and call it as CREATED_BY. Store the name of the user who created that record. Now anyone can see it but if someone tries to update it you can put some code to validate the user and see if that is the same user who created this record. If yes let them update the record otherwise not.
    Well played. To elaborate on this method, you can pull the current user name by placing this code in a public module:

    Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" _
    (ByVal lpBuffer As String, nSize As Long) As Long

    Then call GetUserName whenever you need to verify credentials.

  4. #4
    Join Date
    Jan 2004
    Location
    LA, CA
    Posts
    18

    Thumbs up

    Perfect!!! This way I can have all the data together which will make dealing with reports etc. much easier...
    Thanks a lot guys!!!

Posting Permissions

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