Protecting data (1 Viewer)

AnthonyGerrard · Jun 17, 2015

I have sql logins, and Database users to give permissions on my sql db. Which is all fine, these are based on windows accounts. And my app uses ODBC to connect to the db.

How do I stop users connecting to the data outside of my app?

I cant use application roles for mdbs?

Thanks?!

Ranman256 · Jun 17, 2015

in the startup macro, autoexec,
hide the db,
open a login screen, id,password,
if it authenticates, open the main screen, else close the app.
(to prevent outside the app you must password the db)

AnthonyGerrard · Jun 17, 2015

Ranman256 said:
in the startup macro, autoexec,
hide the db,
open a login screen, id,password,
if it authenticates, open the main screen, else close the app.
(to prevent outside the app you must password the db)

Ta

You saying I must go for sql server authentification? The password will still be in the ODBC details though - so if anyone can use the ODBC they are still in?

If I used sql logins would I still get windows usernames etc via the sql server?

SQL_Hell · Jun 17, 2015

The best way to do this is to use windows active directory groups, so add all application users to AD groups and give the group permissions in SQL server, then you never have to worry about setting permissions for individual users, you only have to worry about adding new users to the group.

Do you also have the issue where users have access to tables in the database that are not part of the application? (it's not clear in your OP), then you should use SQL server schemas, which are bit like namespaces.

So for example you could create schema called "app" and move all objects that are part of the application (tables,views blah blah blah) into the app schema, so a table called dbo.orders would become app.orders. Then you can assign the AD group you created above permissions to the "app" schema, that would mean the users can only query objects in the "app" schema.

AnthonyGerrard · Jun 17, 2015

SQL_Hell said:
The best way to do this is to use windows active directory groups, so add all application users to AD groups and give the group permissions in SQL server, then you never have to worry about setting permissions for individual users, you only have to worry about adding new users to the group.

Do you also have the issue where users have access to tables in the database that are not part of the application? (it's not clear in your OP), then you should use SQL server schemas, which are bit like namespaces.

So for example you could create schema called "app" and move all objects that are part of the application (tables,views blah blah blah) into the app schema, so a table called dbo.orders would become app.orders. Then you can assign the AD group you created above permissions to the "app" schema, that would mean the users can only query objects in the "app" schema.

Yes - I think that's what I have. But my problem is any user can create a new access FE and link through the ODBC and then they have say permission to delete on the company table.

My FE stops them deleting as it can only be done via a button - if the create their own FE app to the db - they can delete all records with a simple DELETE FROM COMPANY query.

That's what I need to stop, that possibility.

SQL_Hell · Jun 17, 2015

Ok, so what permissions do they currently have against the database?

Can you get a AD group created? something like domain\AppUsers

And get the users' AD accounts added into this group?

What kind of access do you have to the SQL server? can you RDP? are you sysadmin?

AnthonyGerrard · Jun 17, 2015

SQL_Hell said:
Ok, so what permissions do they currently have against the database?

Can you get a AD group created? something like domain\AppUsers

And get the users' AD accounts added into this group?

What kind of access do you have to the SQL server? can you RDP? are you sysadmin?

Yes we have a AD, and users are all added. For the most part the user have datareader/writer permissions on tables.

(this was upsized from access db, so access is not limited through sps, func, they should be using bound access forms)

So if they can connect using their windows login - they can update data as they please. (which they cant do in the correct access app, as they are tied to forms, queries, sps, func and cant get at the table directly ).

The problem is if they connect a new access app - (maybe bind a new form to these tables) they are into the tables data directly.

I am a sysadmin on the sql server.

AnthonyGerrard · Jun 17, 2015

This maybe explains my problem better

http://sqlmag.com/business-intelligence/mastering-application-roles

https://voiceofthedba.wordpress.com/2012/11/26/how-application-roles-work-in-sql-server/

but application role solution is not seemingly possible using an mdb?

SQL_Hell · Jun 17, 2015

Ok... so using schemas would be the best way but they means re-coding everything and re-linking the tables, so whilst I think it's the best thing for you to do it's not going to be the quick an easy fix I suspect you're looking for.

So we are (as a temporary fix) going to remove the permission they currently have and give them the permissions to only see the tables that are part of the app.

What's the name of your AD group that all these users are a member of?

I assume you have the AD group as the SQL server login rather than each individual domain account? so there's only 1 sql server login?

SQL_Hell · Jun 17, 2015

AnthonyGerrard said:
This maybe explains my problem better

http://sqlmag.com/business-intelligence/mastering-application-roles

https://voiceofthedba.wordpress.com/2012/11/26/how-application-roles-work-in-sql-server/

but application role solution is not seemingly possible using an mdb?

Yes you don't need to use application roles, use AD groups

AnthonyGerrard · Jun 17, 2015

SQL_Hell said:
Ok... so using schemas would be the best way but they means re-coding everything and re-linking the tables, so whilst I think it's the best thing for you to do it's not going to be the quick an easy fix I suspect you're looking for.

So we are (as a temporary fix) going to remove the permission they currently have and give them the permissions to only see the tables that are part of the app.

What's the name of your AD group that all these users are a member of?

I assume you have the AD group as the SQL server login rather than each individual domain account? so there's only 1 sql server login?

All above is correct - except the users only have access to the app tables already?

But they can access them outside of the app.

SQL_Hell · Jun 17, 2015

Oh I see, I understand now. Sorry went off on the wrong track.

Would your users honestly create a new access front end and hack things like that?

One way round is to transfer over to 1 SQL server account for the application, and never give them the password and delete all their domain accounts from SQL server or only give them select permissions.

AnthonyGerrard · Jun 17, 2015

SQL_Hell said:
Oh I see, I understand now. Sorry went off on the wrong track.

Would your users honestly create a new access front end and hack things like that?

One way round is to transfer over to 1 SQL server account for the application, and never give them the password and delete all their domain accounts from SQL server or only give them select permissions.

I'll look into whether that's ore secure - cant they just use the ODBC created with the password. So they still are into the data with their own app.

I have to look at this tomorrow. Thanks so far.

SQL_Hell · Jun 17, 2015

actually yes, they probably would still be able to do the same.

Someone had the same issue here and used a server trigger

http://www.codeproject.com/Articles/80903/Restricting-Access-to-Database-by-Application-Name

Be very careful when using a trigger like this, you don't want to make a mistake and lock yourself out of the server

SQL_Hell · Jun 17, 2015

Personally I would just setup some auditing and tell the users not to create new access apps under any situation.

Surely management can help enforce this rule?

Also a good backup strategy can help you mitigate any data loss by people executing delete statements. Do you have hourly transaction log backups running?

AnthonyGerrard · Jun 18, 2015

SQL_Hell said:
Personally I would just setup some auditing and tell the users not to create new access apps under any situation.

Surely management can help enforce this rule?

Also a good backup strategy can help you mitigate any data loss by people executing delete statements. Do you have hourly transaction log backups running?

Yes we have backups running, and transactional .

I'll have a think about what we can do.

Seems like a massive hole in access/sql security.
Not allowing other access apps wont work. Some users will swear black is white, even when multiple audit logs tell them differently.

CJ_London · Jun 18, 2015

involves more work but have you considered using DSN less connections?

http://www.accessmvp.com/djsteele/DSNLessLinks.html

Build them in your code so there are no linked tables for users to find the connection string and remove the ODBC driver for your DB from windows, then providing your front end is converted to mde/accde users will not have access except through your application.

SQL_Hell · Jun 18, 2015

I don't agree actually it's not a massive hole in access / sql security, that hole exists which ever database you use and the flaw is more to do with the use of ODBC connections in general.

I have worked with many applications over the years that use ODBC connections and I have never heard of users creating new applications and hacking data by using the existing ODBC connections and if they did they would be up for disciplinary action.

Do the management not have any kind of control over their employees at your place?

You could consider:

Using unbound forms, with stored procedures to update, insert, delete data and only give the users access to run the stored procedures with no direct table access.

But given you have transaction log backups and can repair any data that gets accidentally deleted, what's the actual issue here?

AnthonyGerrard · Jun 18, 2015

SQL_Hell said:
I don't agree actually it's not a massive hole in access / sql security, that hole exists which ever database you use and the flaw is more to do with the use of ODBC connections in general.

I have worked with many applications over the years that use ODBC connections and I have never heard of users creating new applications and hacking data by using the existing ODBC connections and if they did they would be up for disciplinary action.

Do the management not have any kind of control over their employees at your place?

You could consider:

Using unbound forms, with stored procedures to update, insert, delete data and only give the users access to run the stored procedures with no direct table access.

But given you have transaction log backups and can repair any data that gets accidentally deleted, what's the actual issue here?

Thanks have had a quick try of connecting though DSN less connection, seems ok. Also have set up a sql authenticated login. Which seems to still pass windows username etc correctly. So a fair bit of testing ahead to see if it throws up any unexpected problems. and then maybe can remove the windows based authenticated login ,and life's a goodun.

I hope its that easy!

Ta so far.

Protecting data (1 Viewer)

Registered User.

Well-known member

Registered User.

SQL Server DBA

Registered User.

SQL Server DBA

Registered User.

Registered User.

SQL Server DBA

SQL Server DBA

Registered User.

SQL Server DBA

Registered User.

SQL Server DBA

SQL Server DBA

Registered User.

Super Moderator

SQL Server DBA

Registered User.

Similar threads

Users who are viewing this thread