| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-07-10 : 09:05:31
|
| John writes "Background: I am attempting to use a View in SQL server to implement row level security in several SQL databases. Essentially I am locking the users out of the base tables and giving them access to a view that limits the result-set based on who the current user accessing the database is. Since I am using this technique in multiple databases and multiple tables, I would like to simplify matters by having the view select all columns(*) from the base table so that when new columns are added to the table, they need not also be added to the view. Because the consumers of the view are using it instead of the base table for queries, the columns returned are going to be limited in the final query anyway.Problem: When I add, remove columns from the base table. The view does not reflect the change and must be refreshed using sp_refreshview. Apparently it is not storing Select * from MyTable, but rather expanding out the * when I save the view, thus defeating the entire purpose of using the * specifier. Like I said before, using sp_RefreshView fixes the problem once you have updated the base table, however I would love to know if there is a way to short circuit this behavior or at least make the view refresh itself whenever the underlying table is changed automatically.BTW: My Server is SQL 7.0 running on Win2k." |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-07-10 : 09:23:30
|
| Short answer...no, there is no way to short-circuit this.As a general rule, in a production environment, I never user select * as it leads to endless problems.<O> |
 |
|
|
MakeYourDaddyProud
184 Posts |
Posted - 2002-07-10 : 09:29:05
|
I think you may have contradicted yourself since you said earlier:quote:
...I would like to simplify matters by having the view select all columns(*) from the base table so that when new columns are added to the table, they need not also be added to the view. Because the consumers of the view are using it instead of the base table for queries
But you say later when you add columns it's not to be reflected in the view already established, since you want to implement (row or col level) security???I agree in the fact that if you stipulate the columns in the view rather than relying on (*), you will have less probbos and no need to refresh views.Daniel Small MIAPwww.danielsmall.com IT Factoring |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-07-10 : 09:52:18
|
| I know of no way around the need to recompile views defined with a select * (and I agree with Page that generally this is not a construct of production robustness).There is an undocumented sp that enumerates table objects and lets you perform actions on each one, sp_msforeachtable. At first glance I don't see an analog for views but you might modify that procedure yourself to work with views.Jonathan Boott, MCDBA |
|
|
|
jfuex
Starting Member
29 Posts |
Posted - 2002-07-10 : 09:57:12
|
| I have to disagree. The "Select * " syntax is perfectly appropriate when used correctly to mean "Return all ". Where you run into trouble with this syntax is when you opposed to using it as a shorcut for returning specific fields needed by an application. I had a feeling that I was going to get responses like these that didn't even attempt to address the issue at hand.Let me make the problem simpler and see if I get any better results...1) In roughly 40 databases (and growing daily) I have a table called "Documents" the fields contained in this table vary from database to database and are added/removed from time to time. 2) In an application that is used against all of these databases I need to query whatever fields happen to be in the documents table for that particular database. 3) I need to implement ROW level security based on a field that is present in this table in all databases. In essence: Select * FROM documents where (securityfield=SomeValue)4) The problem is that if I, for example, add a field to the Documents table in any of the databases, it is not returned by the view described in step 3 until I open and save the view or refresh it. |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-07-10 : 10:04:16
|
quote:
I have to disagree. The "Select * " syntax is perfectly appropriate when used correctly to mean "Return all ". Where you run into trouble with this syntax is when you opposed to using it as a shorcut for returning specific fields needed by an application. I had a feeling that I was going to get responses like these that didn't even attempt to address the issue at hand.
That's puzzling, i don't see why you feel that way. I certainly did attempt to address the issue at hand, which is that SQL Server does not recompile views when the base tables it depends on change. I expressly said I didn't see a way to force SQL Server to perform that recompilation automatically, and I went as far as to suggest a workaround.Bear in mind that this is a free problem/discussion forum. We're certainly not paid to answer questions so you're not entitled to feel indignant when responses don't suit you.Jonathan Boott, MCDBA |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-07-10 : 10:12:23
|
quote:
I had a feeling that I was going to get responses like these that didn't even attempt to address the issue at hand.
Hold on there, cowboy. Did I sound abstract? The issue at hand is that you have a view defined using 'select *' dml and ddl changes to your base tables don't expose themselves in your poorly created view. The issue at hand has been directly addressed in triple redundancy. The issue at hand can only be resolved by saying 'tough, you are screwed, you made your bed, now sleep in it'. You will have to run sp_refreshview for all you dependant views. Either by hand, or using the automated method provided by setbased.quote:
The "Select * " syntax is perfectly appropriate when used correctly to mean "Return all ".
I 100% disagree, and quite frankly can't believe you would stand by that statement considering the the trouble you are currently having. 'Select *' means "I am too lazy to type out a column list". It has nothing to do with 'Return all'. And you will find, time and time again, that this kind of lazyness will lead to bug after bug after bug....Best of luck.<O> |
|
|
|
MakeYourDaddyProud
184 Posts |
Posted - 2002-07-10 : 10:20:08
|
| I can feel a heated argument brewing... I'd better not post my opinion!Daniel Small MIAPwww.danielsmall.com IT Factoring |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-07-10 : 10:21:35
|
quote:
1) In roughly 40 databases (and growing daily) I have a table called "Documents" the fields contained in this table vary from database to database and are added/removed from time to time.
To be honest I think that might be your larger problem. Having so many different databases with the same table name is a very ISAM-oriented structure, in the Dbase or Clipper vein.You might be better off having just one database and adding a column to "Documents" to specify which logical "database" you are talking about. If the Documents table has different columns in each of the databases currently, then that would be a subtype and more properly modeled as another table, say DocumentProperties.Jonathan Boott, MCDBA |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-07-10 : 10:52:33
|
quote:
To be honest I think that might be your larger problem. Having so many different databases with the same table name is a very ISAM-oriented structure, in the Dbase or Clipper vein.
Ahhhhhh, Clipper...  Jonathan is right, if that's how you're setting up your databases you really can't avoid problems like you're having.I think the larger issue is that the views and tables are still in the process of being developed. Yeah, using SELECT * is a timesaver, but there's no excuse to constantly update a table or view every week to add a column to it. Lord knows I've been in that position myself a number of times, but I've pretty much eliminated this situation. Get the design nailed down and created before people start using it. If that sounds stupid or unrealistic, tough. If you can't get around that, then get used to recompiling your views every week and stop looking for shortcuts around completely avoidable problems.quote:
I can feel a heated argument brewing... I'd better not post my opinion!
C'mon Danny, let it out! You'll explode if you keep it in... |
|
|
|
|
Selecting all columns in a view.