Multiple be for large file attachments (1 Viewer)

[SLeach]

Hey guys, I’m in a unique situation.
I want to develop a new access db program for my work. I have some random limitations and concerns. Not sure which is best and was hoping for some feedback.

We have a all Dept shared folder that everyone has access to, and each Dept has their own shared folder only people from that Dept has access to.

I want to save and access file attachments, some of which can be sensitive and don’t want anyone to them.
A. If I have the be saved on the all Dept shared drive and the folders for each attachment with a link to the file path, anyone can open them. I don’t want someone to surf the folder and circumnavigate any viewing controls I build in. Is there a way to encrypt the file so only access can open them? So they are saved externally but only access can open it?

B. If I store them in the db, I can control viewing permission. I can keep them safe and put a password on the be so no one can surf attachments in the all Dept shared folder. I know it would then run slower and I’m worried if in a few years, it may get too big. I was thinking of saving each large attachment in its own password protected be db, if the file does not need to be accessed, would the fe run fast as if it wasn’t connected to the be? Would it only slow down when trying to open one of the be db (or run a query)? Or would it run just as slow as one large db when connected to 5-6 be db.

C. if I have a query built on side going to multiple be, will it run if no open form needs the info?
random question I always wondered that may affect this, when you have the queries built Are they running in the background or do they only run when called or opened?
For example I have two fe db. Both connected to the same be. One has 50 queries, one has 1 query. Will they both perform at same speed to open the same form? I was thinking of only using the vba run sql to fill forms. Is it worth the effort? Would it make the db faster or would I only be able to brag about coding all the queries? Cuz I don’t need to brag, and noone would understand cuz noone knows how to code hahaha.

 

[Jon]

Welcome to the forums! We are the most active Microsoft Access community on the internet by far, with posts going back over 20 years!

To get started, I highly recommend you read the post below. It contains important information for all new users to this forum.

https://www.access-programmers.co.uk/forums/threads/new-member-read-me-first.223250/

We look forward to having you around here, learning stuff and having fun!

 

[arnelgp]

you can create a Shared folder with a Suffix of $. then it is hidden in the prying eyes.

 

[The_Doc_Man]

I think your solution (A) is closest, just needs modification.

In your (C) discussion, your question about queries: Queries that are not opened are just text strings stored statically as the SQL of the query (even if you used the query grid to build it). A query, to contain data, has to be passed to a DB SQL engine in order to be associated with actual data from the query data source. In this (C) solution, you imply use of multiple BE files, but I think that is overkill. Your question of speed when you break things apart into multiple BE files depends on the question of location. If the two BE files are on the same disk, just different folders, they will likely operate at exactly the same speed. Ditto if on two different partitions of the same disk. Not so clear if on physically different disks, particularly if either is network-attached storage.

In your (B) discussion, your thought that this would lead to bloat. Yes, that is correct. Your (C) solution with mutliple BE files has the same problem, just putting off the inevitable. You also mentioned storing the files in the DB itself. You never want to store objects in a DB that are best opened by some other utility program. OLE works but gets big fast.

For your (A) discussion, you look to Access as a protector but you have a better friend in Access security.

If you have one back-end file available to all users and everyone has their own front-end file (as a copy of the master FE), you can then use distinct folders for your department files - which will NOT be embedded in anything at all. Just keep links or fully-qualified file specs to them.

You might need the assistance of your IT group to do this, perhaps, but here is what you do.

1. Create several Group Identifiers - 1 for "all users of the database", 1 for each department.

2. Put access control lists on each folder (and allow the folder's permissions to be inherited). The files that are ONLY for a department go to the departmental folders. The files that can be seen by any DB user can go in the DB folder. You COULD have one other separate folder that bears the "all users of the database" identifier to hold the external files that are not department-restricted (but not the BE file). Or you could store the shared external files in the same folder as the BE. Your choice there. The folder that contains the BE file must have MODIFY access rights. You can, if you wish, create a list of Access Control Entries which will almost certainly include SYSTEM and one or more system operators such as the Backup Operator, but these Access Control Lists end with:

2.a ONE of the identifiers created for #1 above
2.b An Access Control Entry that says DENY ACCESS for "Everyone"

Access Control Entries are evaluated in order of appearance, so the "Deny Access" ACE is evaluated last. Further specific notes:
- The rights required for Access FE or BE files is always MODIFY.
- The rights required for these other files will depend on how they will be used.

3. Grant the department group ID only to members of the given department. Grant the "all users of the database" group ID to ... all users of the database. IF there is a person who will have access to all files in all folders, transcending the departmental protections, that person CAN have more than one group identifier (i.e. be a member of more than one group.) OR, your superuser could have a separate ID that gets into the ACL for each department folder, again appearing before the final "Deny All" that ends the ACL.

4. If your database would attempt to open the files itself, include error checking so that you can detect a "Permissions Denied" error on the OPEN operation, whatever it might be. This way, WINDOWS will stop users from doing an end-run around Access to get to the file.