| Author |
Topic |
|
steelkilt
Constraint Violating Yak Guru
255 Posts |
 Posted - 2004-04-23 : 12:28:02
|
| I want to facilitate access to my SQL Server 7.0 database for users who will have limited access and I'm trying to decide between a VIEW and a stored procedure. Right now I've set up an SPROC, which I like b/c it's very secure. Users are getting the data via a web page, which launches Excel, where an embedded macro launches the sproc. This setup enables me to push a warning banner to the web page and, again, is very secure.However, these users would like to query the data set that is being returned. The easiest way to do this, it seems to me, is to use MS Access instead of Excel and do away with the web page access altogether. Questions arise, however:1. can I have them launch the sproc from within MS Access? Ideally the sproc would be launched each time the user entered the MDB, therefore ensuring that that table object in MS Access was always the latest data. With a VIEW, this is easy, just do an Access link table and you're done, but...2. If another DBA "accidentally" gives them Write access to the VIEW, that write access would override more restrictive access on the base tables, correct? This is the main reason I've stayed away from the view to date.3. Another reason for the view: users could use Excel Query to write their own queries to the VIEW. They can't do that with an SPROC.Any help or guidance is appreciated. |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2004-04-23 : 12:47:53
|
| If you want to run it into Access, why not just run the results of the view into a work table when you launch access? Then the users can do what they want with the data and you don't have to worry about them changing anything... |
 |
|
|
cas_o
Posting Yak Master
154 Posts |
Posted - 2004-04-23 : 12:51:20
|
| You can have Access run a stored proc. You could have Access put the returned recordset into a local table, which the user can destroy freely.;-] |
|
|
|
steelkilt
Constraint Violating Yak Guru
255 Posts |
Posted - 2004-04-23 : 12:59:17
|
| thanks all. each of these solutions is attractive. I'd like to explore further. Can you point me to a link, or links, that might give me some background on how to implement.thx |
|
|
|
scotchbroom
Starting Member
17 Posts |
Posted - 2004-04-23 : 15:56:01
|
| You can also use ODBC to link to the tables on the SQL server. If the user logs in and only has read-only permissions then they will not be able to make changes to the data. This also allows more advanced users to make their own queries. You can either use the Windows authentication or SQL logins to open the tables or views. If the user has write permissions than you can even build Access forms for data entry. I like this because I'm used to Access and its toolbars like filter, find, etc. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-04-23 : 16:01:12
|
| i say go with the view, if performance won't be a problem. if the users know how to use access and know how to query, they can used linked tables to the view and they will be very self sufficient. however, make sure there are no performance concerns if the users query the db directly -- i.e., if the server has a heavy workload and suddenly users might start doing cross joins accidentally and returning back millions of rows .... but if you are reasonably sure that scenerio won't occur, then let them at the views. and lock down all of the tables while you're at it, to be sure they can't link directly to tables. and make the views read only.to link to any ODBC datasource in Access, you create a linked table. check out Access help for info. very easy to do.- Jeff |
|
|
|
steelkilt
Constraint Violating Yak Guru
255 Posts |
Posted - 2004-04-24 : 08:53:37
|
| jsmith,I'm inclined to go with your recommendation re: views b/c this is a low-use application/database and the users are somewhat comfortable with db queries.However, could you clarify re: inherited permissions: 1. I assume a user must have at least read only permissions to a base table in order to run a VIEW?2. If a user's VIEW permissions are bumped up to INSERT/UPDATE, then the VIEW permissions would replace the more restrictive read only permissions on the base tables, correct? i.e. the user could edit the base tables via the VIEW.thx. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-04-24 : 09:23:06
|
| 1. NO -- as long as the owner of the view (usually DBO) has permissions on the base tables. that is why they work well -- you can hide the base tables from all users, and only allow them to use the VIEWS. as long as they have access to the view, they can open all tables the view refers to.2. YES -- assuming the view is writable at all. if it combines more than 1 table or does aggregate calculations, regardless of permissions it will not be writable. but as long as the owner of the view has write permissions on the table, whoever has write permissions on the view will be able to write to the tables using the view. of course, i hope you don't think point #2 is a drawback. simply maintain proper permissions on your views and you are all set. just like any table or any other database object.- Jeff |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-04-24 : 09:23:20
|
| 1. NO -- as long as the owner of the view (usually DBO) has permissions on the base tables. that is why they work well -- you can hide the base tables from all users, and only allow them to use the VIEWS. as long as they have access to the view, they can open all tables the view refers to.2. YES -- assuming the view is writable at all. if it combines more than 1 table or does aggregate calculations, regardless of permissions it will not be writable. but as long as the owner of the view has write permissions on the table, whoever has write permissions on the view will be able to write to the tables using the view. of course, i hope you don't think point #2 is a drawback. simply maintain proper permissions on your views and you are all set. just like any table or any other database object.- Jeff |
|
|
|
steelkilt
Constraint Violating Yak Guru
255 Posts |
Posted - 2004-04-26 : 15:58:27
|
| re: your YES response to #2, my understanding has always been in line with this passage from an old SAMS sql server 7.0 book:"modifications cannot affect more than one underlying table. If your view joins information from one or more tables, you can modify data in only one of the base tables."The question that comes to mind: which base table is updated if a user changes data in several VIEW cells that map back to several base tables?also, SAMS declares a view non-updatable "if the view contains a UNION." |
|
|
|
steelkilt
Constraint Violating Yak Guru
255 Posts |
Posted - 2004-04-29 : 12:29:45
|
| I went with a VIEW, read-only, and then just for an extra bit of security set up a secured MS Access app, allowing Read-Only permissions to the VIEW object within the mdb. thx |
|
|
|
|
Sproc is secure, users want a VIEW, which one?