| View previous topic :: View next topic |
| Author |
Message |
Arthur Hoornweg
Guest
|
 Posted: Wed Mar 01, 2006 9:03 am Post subject: Now here's a nice gotcha (MSSQL2000) |
 |
|
I ran into the weirdest thing yesterday.
I added a new field to an existing table called "tracks" in my database:
ALTER TABLE tracks ADD nlfbcode NVARCHAR(50) NOT NULL Default 'No ID
entered'
The database already contained a view "tracks_view".
The purpose of this view is to implement row-level security, i.e. all users
only get to see a subset of the records depending on their credentials.
Table "tracks" itself is not accessible directly to normal database users.
It was defined as:
CREATE VIEW tracks_view WITH VIEW_METADATA AS
SELECT T.* FROM (tracks T inner join sec_rights R on t.tracknr=r.objectid)
WHERE r.uname=system_user WITH CHECK OPTION
To my amazement, the new column "nlfbcode" doesn't show up at all when I
perform "select * from tracks_view", but it does show up when I do a
"select * from tracks".
I had to delete the view and re-create it to get things working again!
--
Arthur Hoornweg
(In order to reply per e-mail, please just remove the ".net"
from my e-mail address. Leave the rest of the address intact
including the "antispam" part. I had to take this measure to
counteract unsollicited mail.) |
|
| Back to top |
|
 |
Oliver Townshend
Guest
|
| Posted: Wed Mar 01, 2006 9:03 am Post subject: Re: Now here's a nice gotcha (MSSQL2000) |
|
|
| Quote: | The purpose of this view is to implement row-level security, i.e. all
users
only get to see a subset of the records depending on their credentials.
Table "tracks" itself is not accessible directly to normal database users.
|
Well in this case I think you'd could say that SQL-Server was working the
way you wanted.
Oliver Townshend |
|
| Back to top |
|
|
Arthur Hoornweg
Guest
|
| Posted: Wed Mar 01, 2006 9:03 am Post subject: Re: Now here's a nice gotcha (MSSQL2000) |
|
|
Oliver Townshend wrote:
| Quote: | Well in this case I think you'd could say that SQL-Server was working the
way you wanted.
|
er, you mean by giving me column-level security when I wanted only row-level
security? Please explain...
--
Arthur Hoornweg
(In order to reply per e-mail, please just remove the ".net"
from my e-mail address. Leave the rest of the address intact
including the "antispam" part. I had to take this measure to
counteract unsollicited mail.) |
|
| Back to top |
|
|
Bob Dawson
Guest
|
| Posted: Wed Mar 01, 2006 10:03 am Post subject: Re: Now here's a nice gotcha (MSSQL2000) |
|
|
"Arthur Hoornweg" wrote
| Quote: |
I had to delete the view and re-create it to get things working again!
|
It would appear that SQLServer prepares and stores
| Quote: | SELECT T.* FROM [...]
|
as a set of literal field references, and that altering the table did not
trigger a reanalysis of (ie, did not invalidate) this stored evaluation of
T.*. I can see why that could be an issue for maintaining a database design
that makes extensive use of views. I can also see why silently updating the
view could be an issue.
bobD |
|
| Back to top |
|
|
Oliver Townshend
Guest
|
| Posted: Wed Mar 01, 2006 10:03 am Post subject: Re: Now here's a nice gotcha (MSSQL2000) |
|
|
| Quote: | er, you mean by giving me column-level security when I wanted only
row-level
security? Please explain...
|
Bonus! :)
Oliver Townshend |
|
| Back to top |
|
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
