How do I hide specified records from certain users in an Access database?
First, I’m nowhere near being a database wiz and need help. The database I manage is linked to SharePoint and requires a log in the SharePoint before accessing the database. Besides a the easily reversible filter, is there anyway to only let users see records they’ve created vs the seeing the entire list? Thanks.
You must add a user_name field to your database tables and build a query that select data using user_name as a criteria.
Besides using select * from table_products, you should use select * from table_produts where user_name = ‘xisto’ .
You can also use groups because they are easier to control/administer than users.
I have not used sharepoint but this is usually achieved by VPD (virtual private database) Oracle and MySQL come with VPD support. So if you have a VPD and you are loggedin as a user for example scott then if you do select * from tablename will return only the rows that scott is allowed to see. If that is your requirement readup on VPD and how to configure it.
References :
http://santhoshjohn78.blogspot.com
http://oracle.com
You must add a user_name field to your database tables and build a query that select data using user_name as a criteria.
Besides using select * from table_products, you should use select * from table_produts where user_name = ‘xisto’ .
You can also use groups because they are easier to control/administer than users.
References :
How do I hide specified records from certain users in an Access database?
There are various methods and techniques that could be implemented.
1. My approach is to build a table to hold user details and any other profile information including a password field. This table will link to other tables which you wish to restrict access with queries or filters can be applied using standard SQL/query statements for each calling Form, Report or any other output object.
It will ideally require some basic VBA or macro work to prompt a user for a password which is supplied to other reports in the current session.
This way, you as the developer have full control over the database security.
2. If working with version 2003 (or earlier), you could implement ‘Users & Permissions’ which is Access’s own security module. It requires some understanding of how this tool works as you need to join a workgroup and set objects (table, queries etc) to each workgroup which a user is a member.
In Access 2007 this no longer exists and has been dropped (at least for the time being).
3. The more longer-winded approach (and have seen in the past) is to split the database into smaller ‘front-ended’ units (one for each user) which is specific to an individual only (no password would necessarily be required) pointing to their own recordsets (which would still need a field to match the individual to).
Each Form, Report etc would be filtered (by design) to load only related records.
The downside to this however, is the maintenance overhead should you wish to add new objects at a later stage as you would have to deploy a new ‘front-end’ specifically designed for each user.
The data ‘back-end’ would remain in one central shared area maintaining data integrity but the local tables would be required to hold user profile information and threaded into the main linked tables.
As I say, this last approach is time consuming and I do have doubts about data security in general.
The final question you need to establish is how sensitive is this data which can lead to which method you should lean towards.
In all cases, you need to spend some time reviewing these options and then design and implement the objects as Access does not provide this sort of functionality as standard (via wizards).
A lot to think about – hope this helped.
Ben Beitler
References :
Expert Access database developer, Excel spreadsheet and VBA programmer.
http://www.about-access-databases.com
http://www.access-database.com
http://www.excel-spreadsheet.com
http://www.trainingpartnership.biz