Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
JTProg
Starting Member
24 Posts |
 Posted - 2006-08-21 : 14:57:45
|
| I'm working with an ADP/ADE File and I can't find a way of hiding my SQL Server tables from the database window other than naming tables Usys + table name. I know I'm protected with SQL Server Security, but I really want to add an extra layer of security by not allowing users to export all the data into another access application if attempted. The application will eventually be upgraded to vb.net, but in the interim what else can I do to protect the data? Please advice. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-08-21 : 15:13:53
|
| Grant the access only to stored procedures. Users won't be able to select from the tables or export the data.Tara Kizer |
 |
|
|
JTProg
Starting Member
24 Posts |
Posted - 2006-08-21 : 17:45:44
|
| Oh, I would love to do that. I have about 90% of my sql statements in stored procedures. The other 5-10 % of the SQL Statements are in Resync commands under the properties of the form. Almost a requirement when using stored procedures with access. Your solution is the best, I just have to figure out how to resync, or refresh, the procedure instead of the table itself since the user will lack the ability to select off the table. Do you have any suggestions on this? The main form is unbounded, so vba is refreshing the procedure. The subform utilizes the resync command.Thanks again. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-08-21 : 17:54:18
|
quote:
Originally posted by JTProg
Your solution is the best, I just have to figure out how to resync, or refresh, the procedure instead of the table itself since the user will lack the ability to select off the table. Do you have any suggestions on this? The main form is unbounded, so vba is refreshing the procedure. The subform utilizes the resync command.
I'm not an Access programmer, so I can't help you with that. Hopefully someone else will be able to help you with that. Perhaps you should post that question in the Access forum here.Tara Kizer |
|
|
|
JTProg
Starting Member
24 Posts |
Posted - 2006-08-23 : 09:57:45
|
| Will granting access to views and stored procedures have the same outcome as just granting access to stored procedures when it comes to restricting users from selecting from the tables or exporting the data. Or, do stored procedures work differently than views in this respect. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-08-23 : 13:49:38
|
| Stored procedures work differently than views. If you grant access to views, then the user will be able to perform everything that the view which will be a select with potentially a where clause, joins, etc... The user can then query everything that the view can with select * from viewName. You can't do that with a stored procedure. The user has to input the required parameters and the stored procedure will return a result set back. The user can not get it to do anything else. This is the security benefit of stored procedures. A view is better than granting access to a table though, it's just not as good as a stored procedure. Some people will challenge me here though. Views don't allow parameters in the view definition. You can't do fancy stuff with views either. You can do everything with stored procedures though. Tara Kizer |
|
|
|
|
|
|
|
|
Tables