Solved Secure table from being imported into excel (1 Viewer)

[bthomsin]

Hello,

I have a question on how to make my tables hidden from excel imports.

My situation:
I have a front end accde for the users that is not protected with a password. I have a script that checks the windows username and if its not in the backend it will close the database.
The backend is password protected.
I have hidden my tables even when i open the front end and then show the navigation pane. Even when i click show hidden tables.

The only problem is when i open a new excel file. Then i go to the data tab. Press new query then from database then from microsoft access database.
Then i go and select the front end. There i can see every linked table and import this into excel.

Is there a way to prevent this? I have searched all my books and i have searched for a while on the internet but i cannot seem to find a sollution anywhere.

I hope someone will be able to help.
Sorry for my bad english. Its not my native language.

 

[HiTechCoach]

The reason you are not finding a solution during your searching is there is not a very reliable way to secure an Access (accdb/accde) front end

Access (accdb/accde) front ends are not secure. The best you can hope for is to put up enough roadblocks that the person trying to crack it gives up.

If protecting the data is an issue, you will probably need to switch the front end to .NET or some other front end.

 

[bthomsin]

Hello Boyd,

Thank you for your fast respone.
Any suggestion to make it harder? The data is on a private server owned by the company.
So only a few users have access to it. But i would like to make sure they cannot read everything only the things they should.

Would it work if i keep using the same front-end but i move all the data to microsoft SQL server?

 

[HiTechCoach]

The issue is not with securing the back end. Switching to an SQL server will not fix the issue with the front end not being secure.

The issue is the front end is a database that Excel can read. You would still have the same issue with linked tables to any back end, like an SQL Server

Who is really concerned about the data being protected Is it you or the company management?

.

 

[bthomsin]

I am concerned the company isnt, even is the data is leaked it still isnt a real problem since we do not allow high importance data on the servers at all. I am just trying the best i can do to prevent the easy way to see all the data. I would mind less if it wasnt so easy to get to the data.

 

[Micron]

I have never tried this exactly, but what if your fe's were designed to be opened exclusively, i.e. will that prevent the same user from getting into the db from Excel? If so, then only set the links via code on fe open and destroy them on close. My thinking is that the links are present if the fe is open but you can't connect from Excel because the fe was opened exclusively. If it's not open, there are no established links to be seen from Excel. I don't know if opening a db exclusively prevents one from connecting from Excel.

It is pretty much impossible to prevent the most dedicated and knowledgeable hackers from thwarting whatever you throw at them when it comes to Access. The balance comes from weighing their skill level against the sensitivity of the data. If it's super sensitive, Access isn't the right tool for the job.

 

[isladogs]

Whilst I agree with all the cautionary comments above, perhaps it is possible after all

The attached database is split and for the purposes of this demo, the FE / BE both need to be in the same folder
The ACCDE FE file is encrypted with password isladogs.
The BE file is also encrypted with a different password that isn't needed for this to work.
The BE data is also separately encrypted using RC4 encryption

The data can be viewed and edited from the FE but AFAIK cannot be exported to Excel or any other external app.

I created it several months ago but haven't checked it recently for errors or security weaknesses
Please try it out and let me know whether or not you are able to export the data to Excel
Please send me a PM if you are able to break the security and explaining how you did so

 

[HiTechCoach]

I am concerned the company isnt, even is the data is leaked it still isnt a real problem since we do not allow high importance data on the servers at all. I am just trying the best i can do to prevent the easy way to see all the data. I would mind less if it wasnt so easy to get to the data.

Microsoft Office is all about letting the end-user use the data however they need it.

The great thing about building applications with Access is that it works so well with Excel. This allows users to pull the data to get exetly what they need.

Do you know if the users will actually try to use Excel to pull out the data? In 30+ years of building apps with Access, every time a user asks for data in Excel was to do something that I had into build into the application.

My goal is to put the user's needs first. Now I build into my application that ability to explore data to Excel.

 

[CJ_London]

There i can see every linked table and import this into excel.

Is there a way to prevent this?

there is with a bit of a rewrite of your FE to have all your queries written in VBA or in the BE and assign recordsets to forms and reports - Even that is not 100% secure from someone who knows their way around access.

Very simply:

At the moment you have a form with a recordsource of 'Table1' or 'SELECT * FROM Table1' or similar

or perhaps you have vba code along the lines of

sqlStr="SELECT * FROM Table1"
me.recordsource=sqlstr


Instead, what you do is as follows

When the FE is first opened, create a private db variable in a module with a couple of functions - something like this

Option Compare Database
Option Explicit

Private BEdb as dao.database

Private function openBE() as boolean

    if BEdb is nothing then 'open the backend
        set BEdb=dbengine.opendatabase("C:\databases\BE.accdb",0,0,";PWD=password")
    end if

end function

Public Function BErst(ByVal sqlStr as string) as dao.recordset

    OpenBE 'in case not already open
    set BErst=BEdb.openrecordset(sqlstr, dbfailonerror)

End Function

then in your form open or load event or however you are doing it

sqlStr="SELECT * FROM Table1"
set me.recordset=BErst(sqlstr)

now you can delete all the linked tables from your front end

the above is freetyped so check for typo's and you will no doubt want to add in error handling

Note as mentioned above with this method you can put queries in the BE that can be treated as views or action queries - providing the queries do not have parameters referencing forms. So you might have in the BE a query called 'vwTable1' with the sql "SELECT * FROM Table1"

and in your form you might have

sqlStr="SELECT * FROM vwTable1 WHERE ID=" & me.ID
set me.recordset=BErst(sqlstr)

For action queries you can just use for example

BEdb.Execute "DELETE * FROM Table1 WHERE ID=" & me.ID

which also gives you the benefit of being able to use the recordsaffected value
BEdb.Execute "DELETE * FROM Table1 WHERE ID=" & me.ID
msgbox BEDb.recordsaffected & " records deleted"

It still leaves the password accessible to those who know what they are doing but I see Colin has just posted something so he is probably covering that detail

And as others have said, Access cannot be made 100% secure, but there is a lot you can do to discourage 99% of potential hackers

 

[arnelgp]

there is one limit, you cannot set the Recordset of a Report.

 

[isladogs]

there is one limit, you cannot set the Recordset of a Report.

True but you can leave setting the record source until the Report_Open event which is secure enough

 

[bthomsin]

Thank you all for helping out, I will try the options above and report back what worked for me! Very helpfull and good idea's

 

[Micron]

It turns out that if the fe is opened exclusively you cannot connect from Excel so the tables cannot be seen.

 

[isladogs]

It turns out that if the fe is opened exclusively you cannot connect from Excel so the tables cannot be seen.

I'm not sure I agree with that statement. What method were you using?
Anyway I know for a fact that the tables can still be seen from another database even if the FE is opened exclusively.
The reason I know that is that I tried using that approach in an earlier version of the app attached to post #7.
I thought that it had added an extra layer of security. I was wrong.
I now use a different approach

I see the app from post #7 has been downloaded twice but have had no feedback so far.
I'd also be interested to hear from anyone who has tried and failed to crack its security.

 

[bthomsin]

Whilst I agree with all the cautionary comments above, perhaps it is possible after all

The attached database is split and for the purposes of this demo, the FE / BE both need to be in the same folder
The ACCDE FE file is encrypted with password isladogs.
The BE file is also encrypted with a different password that isn't needed for this to work.
The BE data is also separately encrypted using RC4 encryption

The data can be viewed and edited from the FE but AFAIK cannot be exported to Excel or any other external app.

I created it several months ago but haven't checked it recently for errors or security weaknesses
Please try it out and let me know whether or not you are able to export the data to Excel
Please send me a PM if you are able to break the security and explaining how you did so

So i have checked your file and it works. Even when i remove the password. When i try to see the hidden tables i just see 2. One with ur info and the ribbon one.
Do you have thread where u explain how this works?

 

[bthomsin]

there is with a bit of a rewrite of your FE to have all your queries written in VBA or in the BE and assign recordsets to forms and reports - Even that is not 100% secure from someone who knows their way around access.

Very simply:

At the moment you have a form with a recordsource of 'Table1' or 'SELECT * FROM Table1' or similar

or perhaps you have vba code along the lines of

sqlStr="SELECT * FROM Table1"
me.recordsource=sqlstr


Instead, what you do is as follows

When the FE is first opened, create a private db variable in a module with a couple of functions - something like this

Option Compare Database
Option Explicit

Private BEdb as dao.database

Private function openBE() as boolean

    if BEdb is nothing then 'open the backend
        set BEdb=dbengine.opendatabase("C:\databases\BE.accdb",0,0,";PWD=password")
    end if

end function

Public Function BErst(ByVal sqlStr as string) as dao.recordset

    OpenBE 'in case not already open
    set BErst=BEdb.openrecordset(sqlstr, dbfailonerror)

End Function

then in your form open or load event or however you are doing it

sqlStr="SELECT * FROM Table1"
set me.recordset=BErst(sqlstr)

now you can delete all the linked tables from your front end

the above is freetyped so check for typo's and you will no doubt want to add in error handling

Note as mentioned above with this method you can put queries in the BE that can be treated as views or action queries - providing the queries do not have parameters referencing forms. So you might have in the BE a query called 'vwTable1' with the sql "SELECT * FROM Table1"

and in your form you might have

sqlStr="SELECT * FROM vwTable1 WHERE ID=" & me.ID
set me.recordset=BErst(sqlstr)

For action queries you can just use for example

BEdb.Execute "DELETE * FROM Table1 WHERE ID=" & me.ID

which also gives you the benefit of being able to use the recordsaffected value
BEdb.Execute "DELETE * FROM Table1 WHERE ID=" & me.ID
msgbox BEDb.recordsaffected & " records deleted"

It still leaves the password accessible to those who know what they are doing but I see Colin has just posted something so he is probably covering that detail

And as others have said, Access cannot be made 100% secure, but there is a lot you can do to discourage 99% of potential hackers

I am going to test this now. I dont need it on all my tables just two or 3 so i hope it wont take me to long to test.
I will give back a awnser if this helped for my problem in 1 or 2 days. Thank you

 

[bthomsin]

It turns out that if the fe is opened exclusively you cannot connect from Excel so the tables cannot be seen.

This worked for me, but since everyone has there own front-end this alone has no use, or am i mistaken?

 

[isladogs]

So i have checked your file and it works. Even when i remove the password. When i try to see the hidden tables i just see 2. One with ur info and the ribbon one.
Do you have thread where u explain how this works?

I assume you mean you were unable to export to Excel which I'm obviously pleased about.
However, it wasn't intended that you would be able to remove the password.
Was that the ACCDE FE password or the ACCDB BE password?

Even if you've managed to somehow remove the BE password without knowing it, the data is separately encrypted so it won't help at all.
Similarly as the FE is an ACCDE file, you still can't view the code with the password removed
Even so, could you please PM or email me to explain how you managed to remove either password!
Please do NOT do so in this thread. Thanks

There is a detailed explanation of the ideas behind this app on my website: http://www.mendipdatasystems.co.uk/encrypted-split-no-strings-db/4594566347
It refers to an earlier version which I later found out contained some unintentional 'back doors'.
However in that example the FE is an ACCDB file so the code can be viewed

The newer version has several extra layers of security which I will explain at a later date.
In the meantime I have contacted several people including several highly skilled, determined & knowledgeable 'hackers' to try & break it.
I want to see how they get on first so it may be a few weeks before I post any updated explanations

 

[Micron]

What method were you using?

From Excel Get Data > From Database which is what I thought the users would be doing. If you're saying it can be done via code, I'm not sure if that's a fair comparison given the info in the first post. I'm saying that the likelyhood that users are going to write their own code to circumnavigate the lack of ribbon function is probably nil.

 

[bthomsin]

From Excel Get Data > From Database which is what I thought the users would be doing. If you're saying it can be done via code, I'm not sure if that's a fair comparison given the info in the first post. I'm saying that the likelyhood that users are going to write their own code to circumnavigate the lack of ribbon function is probably nil.

It is true if the database is openend exclusive, users could not load it, in excel from the option you say. But everyone has a frontend only for them. Since they can close it and then do the loading in excel it wont work right? But i will keep this option in mind, since i can probably use it in other situations.

While my own protection seems to work when go from excel get data > from database. Here it hides the tables.
But when i do the same thing but select new query, it doesnt hide the tables i want.

thank you