Security restrictions and table linking

4 Sep 2006 3:46 AM

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.

4 Sep 2006 11:00 AM

Rick Brandt

"John" <noem***@noemails.mail> wrote in message
news:SkNKg.43929$5i3.31414@bgtnsc04-news.ops.worldnet.att.net...
> 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.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

4 Sep 2006 5:37 PM

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

Show quoteHide quote

"Rick Brandt" <rickbran***@hotmail.com> wrote in message
news:7HTKg.5426$tU.5173@newssvr21.news.prodigy.com...
> "John" <noem***@noemails.mail> wrote in message
> news:SkNKg.43929$5i3.31414@bgtnsc04-news.ops.worldnet.att.net...
>> 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.
>
> --
> Rick Brandt, Microsoft Access MVP
> Email (as appropriate) to...
> RBrandt at Hunter dot com
>

5 Sep 2006 12:54 PM

Rick Brandt

John wrote:

> 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.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

5 Sep 2006 9:11 PM

John

"Rick Brandt" wrote:
> John wrote:
>> 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.

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.

5 Sep 2006 9:17 PM

Rick Brandt

John wrote:
Show quoteHide quote

> "Rick Brandt" wrote:
>> John wrote:
>>> 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.
>
> 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.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

6 Sep 2006 12:07 AM

John

thanks Rick!

Show quoteHide quote

"Rick Brandt" wrote:
> John wrote:
>> "Rick Brandt" wrote:
>>> John wrote:
>>>> 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.
>>
>> 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.
>
>
> --
> Rick Brandt, Microsoft Access MVP
> Email (as appropriate) to...
> RBrandt at Hunter dot com
>