- View Vulnerabilities
- Changes to Underlying Objects
- Breaks In Chains
- Performance
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Changes to Underlying Objects
Be on the alert for issues arising from modifications of the tables or views on which views are constructed.
SELECT * View
One special problem is the view based on a SELECT * statement. A query of the view runs without generating error messages, but does not display columns added to underlying objects after the view was created. This is because the asterisk (meaning "show all columns") is expanded at view creation time, not at execution time. In the following example, the addon table starts out with two columns.
create table addon
(name char(5),
num int)
insert into addon
values ('one' , 1)
After a view is created, someone adds a column to the table.
create view addonv as select * from addon alter table addon add status char(1) null
A query of the table shows three columns.
select * from addon name num status ----- ----------- ------ one 1 NULL (1 row(s) affected)
A query of the view shows only two columns—the two that existed when the view was created.
select * from addonv name num ----- ----------- one 1 (1 row(s) affected)
Column Names
Another issue to look out for is changes in column names. Let's say you have a view based on two tables. It gets the publisher name from the publisher table and uses the titles table to calculate the number of books each publisher has.
create view pubvu as select pub_name as Publisher, count(title_id) as Titles from publishers, titles where publishers.pub_id = titles.pub_id group by pub_name select * from pubvu Publisher Titles ---------------------------------------- ----------- Algodata Infosystems 6 Binnet & Hardley 6 New Age Books 6 (3 row(s) affected)
Now consider what happens when you add a pub_name column to the titles table.
alter table titles add pub_name varchar(4) The command(s) completed successfully.
The change is without apparent effect on pubvu until you query the view, when you get an error message telling you the pub_name column in the view (which used to run fine!) is ambiguous.
select * from pubvu Server: Msg 209, Level 16, State 1, Procedure pubvu, Line 3 Ambiguous column name 'pub_name'.
You can prevent this by giving the full name of all columns in queries.
drop view pubvu The command(s) completed successfully. create view pubvu as select publishers.pub_name as Publisher, count(titles.title_id) as Titles from publishers, titles where publishers.pub_id = titles.pub_id group by publishers.pub_name The command(s) completed successfully. select * from pubvu Publisher Titles ---------------------------------------- ----------- Algodata Infosystems 6 Binnet & Hardley 6 New Age Books 6 (3 row(s) affected)