Security restrictions and table linking

[John]

Hi all! I have a backend that is secured and a frontend (mde) that has some
vba code that re-links the tables to the backend. My question: Is there any
way I can stop a read-only users to open the backend directly? I tried
removing the "Open/Run" permission to the database object for this user in
the backend, but doing that does not allow the user to do the re-linking
when he/she logs on through the frontend.

What I'm trying to do is to avoid the read-only user from seeing the flat
data, as he/she might copy the bulk data outside the application.

Thanks for any ideas or suggestions.

 

[Rick Brandt]

Hi all! I have a backend that is secured and a frontend (mde) that has some
vba code that re-links the tables to the backend. My question: Is there any
way I can stop a read-only users to open the backend directly? I tried
removing the "Open/Run" permission to the database object for this user in the
backend, but doing that does not allow the user to do the re-linking when
he/she logs on through the frontend.

What I'm trying to do is to avoid the read-only user from seeing the flat
data, as he/she might copy the bulk data outside the application.

Thanks for any ideas or suggestions.

Your code for re-linking can open a new workspace where you specify a different
user (one with more permissions). You have to provide the username and password
in your code to do this so you would want to distribute only an MDE so the code
cannot be viewed.

I'm sorry, but off-hand I don't know the specific code for doing this. You
should be able to find it searching the help file or the web though.

 

[John]

Rick, That's an excellent idea; Never crossed through my mind...Below is the
code I came up with (works beautifully). The account I'm using for the
RefreshLinkAs function is a specific account that has 'Open/Run'
permissions. However I didn't realize that by removing the 'Open/Run'
permissions for the standard user I'm also removing the ability to run SQL
statements or open the linked tables ;-( (I'm a missing/doing something
else wrong)

Public Function RefreshLinksAs(strFileName As String, AsUsername As String,
Password As String) As Boolean
Dim wrk As DAO.Workspace
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef

Set wrk = DAO.DBEngine.CreateWorkspace("", AsUsername , Password,
dbUseJet)
Set dbs = wrk.OpenDatabase(CurrentDb.Name)

For Each tdf In dbs.TableDefs
If Len(tdf.Connect) > 0 Then
tdf.Connect = ";DATABASE=" & strFileName
Err = 0
On Error Resume Next
tdf.RefreshLink ' Relink the table.
If Err <> 0 Then
RefreshLinksAs = False
Exit Function
End If
End If
Next tdf

RefreshLinksAs = True ' Relinking complete.
dbs.Close
wrk.Close
Set dbs = Nothing
Set wrk = Nothing
End Function

 

[Rick Brandt]

Rick, That's an excellent idea; Never crossed through my mind...Below
is the code I came up with (works beautifully). The account I'm using
for the RefreshLinkAs function is a specific account that has
'Open/Run' permissions. However I didn't realize that by removing the
'Open/Run' permissions for the standard user I'm also removing the
ability to run SQL statements or open the linked tables ;-( (I'm a
missing/doing something else wrong)

Do you mena Open/Run on the back end file? For sure they need that, but you
can still limit access to the tables even though they have Open/Run perms on
the file.

 

[John]

Do you mena Open/Run on the back end file? For sure they need that, but
you can still limit access to the tables even though they have Open/Run
perms on the file.

Oh... I see. I wanted the database users to have access to run SQL
statements and/or open linked tables, but I wanted to stop them from opening
the database directly from GUI. Hmm...I see that the Open/Run permission
controls both permissions as one.

 

[Rick Brandt]

Oh... I see. I wanted the database users to have access to run SQL
statements and/or open linked tables, but I wanted to stop them from
opening the database directly from GUI. Hmm...I see that the Open/Run
permission controls both permissions as one.

When you use a link you are "opening" the back end file. It creates an LDB
file just the same as if you had opened the file directly.

Some people will put the back end file in a shared folder that is
accessible, but hidden. Unless a user examines the Connect property of your
links they would be very unlikely to be able to even find the back end file
much less open it. As always these barriers depend on the knowledge level
of the user the barrier is attempting to block.

 

[John]

thanks Rick!

When you use a link you are "opening" the back end file. It creates an
LDB file just the same as if you had opened the file directly.

Some people will put the back end file in a shared folder that is
accessible, but hidden. Unless a user examines the Connect property of
your links they would be very unlikely to be able to even find the back
end file much less open it. As always these barriers depend on the
knowledge level of the user the barrier is attempting to block.