December 12, 2016 at 1:42 am
Hello all,
I have a question regarding security of SQL data in database.
I use:
- sql2008 express with sql server authentication
- front end is ADE access file.
If someone has full version of access he could delete data directly from db.
What db role should be set on sql side for sql user to avoid to change something in db?
Right now has sql user set role public and db_owner.
When I change role for user to public, db_datareader, db_datawriter tables can not be deleted, but data in tables can be deleted or changed.
How Could I solve this case?
Thanks.
December 12, 2016 at 11:09 am
Is this just tables or what else do they need to change?
If you want them to be able to execute any DDL in the database then you would grant db_ddladmin
Sue
December 13, 2016 at 1:08 am
Sue_H (12/12/2016)
Is this just tables or what else do they need to change?If you want them to be able to execute any DDL in the database then you would grant db_ddladmin
Sue
Hi, Generaly , every user can read, write and run procedures directly from an access form.
Every user also has installed access runtime, but if someone has full Ms Access, NOT runtime, data on sql server can be in danger.
December 13, 2016 at 11:00 am
Hi, Generaly , every user can read, write and run procedures directly from an access form.
Every user also has installed access runtime, but if someone has full Ms Access, NOT runtime, data on sql server can be in danger.
Then use only stored procedures and just grant execute permissions the stored procedures, revoke view any definition. You could also look at having the stored procedures in their own schema so the users would only have execute permissions on that schema. Not sure why users would need to write procedures but again you could consider something with schemas for that. Or better yet is to reevaluate the design so users aren't creating objects. That's generally not a good idea and is likely to cause problems with security implementations.
ADPs and application roles are also sometimes used to address this with Access.
Sue
December 13, 2016 at 11:19 am
If a user is a member of DB_OWNER, then they own your database and can do whatever they want with it. DB_DATAREADER grants a user permission to read from any table, and DB_WRITER grants them permission to delete rows from any table.
Ideally, none of the above are used and all select/insert/update/delete operations are done through stored procedures. In this scenario, the user a member of PUBLIC, and then they are granted EXEC permission on stored procedures. They would not be able to query or update tables directly.
Without changing the design of your database, one thing you can do at the application layer is obfuscate the SQL Server account password. For example, you can setup the account with a two part password like 'Apple$123h5d16'. When the user logs in to your application, they supply 'Apple$123', and then your application programatically appends the suffix 'h5d16' to the password prior to establishing the connection. The user only knows the first half of the account password, so if they attempt to connect to the database using MS Access or SQL Server Management Studio, the login attempt will fail. Also, the complete password is not embedded in the application code, so you don't have to worry about a 3rd party obtaining login credentials by examining the application source code.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
December 14, 2016 at 3:06 pm
In addition to the comments of others, I should point out that support for ADP projects was removed in Access 2013. If you have some clever user install 2013 or 2016, you will likely have a significant support issue. In the long term, I would be looking to port the app to an ODBC linked table approach - you will find maintenance and updating is a less complex task in that environment.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply