with check option: bug or misunderstood definition

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

with check option: bug or misunderstood definition

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-06-21 : 08:22:58

Tommy writes "Hi there

Does anyone know a better definition of the "check option" for views, than the definition in Books Online. That definition seems a bit weak, as explained below.

Assuming we have a table T having the following definition:

CREATE TABLE T (pk int PRIMARY KEY, data varchar(50), locked bit NOT NULL)

Now create a view V as follows:

CREATE VIEW V WITH view_metadata AS
SELECT * FROM T
WHERE locked=0
WITH CHECK OPTION

When one inserts a row in the view, the check option ensures, that the (new) locked column holds a 0. And one also cannot alter the 0 to a 1 through the view. Okay, that works, but consider the following scenario.

A (power)user P can access the table T. Another user U can only access the view V. Now, U opens the view (through some application), and it contains one row:

43, 'bla', 0

P opens the table T, and alters the locked column to 1 for the row with pk=43. Remember that U have opened the view, before P altered 0 to 1.

If U now tries to alter the datacolumn from 'bla' to 'foo', the effect depends on the application used.

Most applications report, that such an update isn't possible, because the row doesn't exists in the view. But writing ones own application, using ADO, it is possible, with a little tweak. (I haven't been able to find a "fool-able" existing application)

In Visual Basic add a datagrid control to an exe-project/form, and make sure that the project references Microsoft ActiveX Data Objects 2.x Library. Add the following code:

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Private Sub Form_Load()
cn.Open "Provider=SQLOLEDB;SERVER=...;Database=...;Trusted_Connection=yes"
rs.Open "select * from V", cn, adOpenStatic, adLockOptimistic
Set DataGrid1.DataSource = rs
End Sub

If user U updates the pk column from 43 to 43 (yes, same number), it is possible to update the data column from 'bla' to 'foo'.

This little pre-update tweaks the ADO-connection/MDAC/?, so that an "illegal" update is possible. Or is the update legal?

According to Books Online: "WITH CHECK OPTION: Forces all data modification statements executed against the view to adhere to the criteria set within select_statement ..."

The value of locked in row 43 is 1 at the time when user U succesfully updates data in the same column through V. But locked=1 does not adhere to the criteria in the definition of V, and update is a data modification statement!!!

I originally understood the CHECK OPTION as a _virtual_ INSTEAD OF trigger on the view looking (in the case of updates) as follows:

CREATE trigger trg_update_V ON V
INSTEAD OF UPDATE
AS
UPDATE T
SET
data = inserted.data,
locked = inserted.locked
FROM inserted
WHERE T.pk=inserted.pk
AND T.locked=0

But considering it was possible to update the data column, this can't be right.

My question: Is this a bug, and if not, can anyone give me a more elaborating definition of the check options in views.

Regards,
Tommy

My specs: SQL Server 2000.080.0534, Windows NT, VB 6.0 (sp5)"

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-06-21 : 09:39:37

Tommy,
A technical reading of the definition of the WITH CHECK option differs slightly from your interpretation.

From BOL:

quote:

All data modification statements executed against the view must adhere to the criteria set within the SELECT statement defining the view if the WITH CHECK OPTION clause is used in the definition of the view. If the WITH CHECK OPTION clause is used, rows cannot be modified in a way that causes them to disappear from the view. Any modification that would cause this to happen is canceled and an error is displayed.

Note that it says rows cannot be modified in a way that causes them to disappear, but it does not say that rows cannot be added in a way that would cause them not to appear in the view. This is supposition on my part, but I suspect that ADO is issuing a DELETE and an INSERT when you "update" the primary key from 43 to 43.

setBasedIsTheTruepath
<O>

weisseb
Starting Member

8 Posts

Posted - 2002-07-03 : 05:04:50

Dear setBasedIsTheTruepath,

Thanks for your 'swift' answer.

If I understand you correctly, you're not sure whether it is possible to add/insert a row into a view, so that the row does not appear in the view (does not adhere to the where-clause). But if you execute the following in the Query Analyzer;

insert into V values (43, 'bla', 1)

you get

Server: Msg 550, Level 16, State 1, Line 1
The attempted insert or update failed because the target view either specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK OPTION and one or more rows resulting from the operation did not qualify under the CHECK OPTION constraint.
The statement has been terminated.

So an ADO-update cannot be a delete-insert pair, because the inserted row would contain a 1 in the locked column, which would cause the insert to fail.
Remember, that the view is defined with view_metadata option, so all modifications pass through the view (the basetable isn't used directly).

What I was/am really looking for, was/is a comprehensive and elaborating definition of the check option, which eliminates all the what-if's.

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-07-03 : 09:18:55

I would expect that insert to fail; it would not be included in the view.

Have you run a profiler trace to see exactly what DML is being passed to SQL Server? Having ADO as an abstraction layer is complicating the issue.

Jonathan Boott, MCDBA

weisseb
Starting Member

8 Posts

Posted - 2002-07-05 : 07:21:01

setBasedIsTheTruepath, hope you're up for another long reading :)

I've run a profiler trace according to the scenario, and I managed to reproduce the 'bug'(?) in the Query Analyzer (QA).

The ADO uses the (underdocumented) stored procedures which control cursors (see BOL "System Stored Procedures", last section).

The reproduction assumes, aside from having created T and V, and filled T with (43, 'bla', 0), that you run the following in QA, in the same connection.

Start by executing this:


declare @P1 int
set @P1=NULL
declare @P2 int
set @P2=98305
declare @P3 int
set @P3=311300
declare @P4 int
set @P4=NULL
exec sp_cursoropen @P1 output, N'select * from V', @P2 output, @P3 output, @P4 output
select @P1, @P2, @P3, @P4

The output ought to look like:


pk          data                                               locked 
----------- -------------------------------------------------- ------ 

                                                
----------- ----------- ----------- ----------- 
180150000   1           16388       1

(1 row(s) affected)

The first number, 180150000, is a handle to the cursor just opened. We are going to use this handle in subsequent statements, so if your handle is different from this, just substitute.

With the Enterprice Manager, change the locked column to 1 for row pk=43, through the table T. Now execute in QA in the same connection as before:


exec sp_cursorfetch 180150000, 16, 2, 1
exec sp_cursorfetch 180150000, 16, 1, 1
exec sp_cursorfetch 180150000, 16, 2, 1

The output ought to look like:


pk          data                                               locked 
----------- -------------------------------------------------- ------ 

(0 row(s) affected)

pk          data                                               locked 
----------- -------------------------------------------------- ------ 
43          bla                                                1

(1 row(s) affected)

pk          data                                               locked 
----------- -------------------------------------------------- ------ 

(0 row(s) affected)

Execute this in QA:


exec sp_cursorfetch 180150000, 1040, 1, 1

You get the following output


pk          data                                               locked 
----------- -------------------------------------------------- ------ 
43          bla                                                1

(1 row(s) affected)

And finally execute:


exec sp_cursor 180150000, 33, 1, N'V', @data = 'zzz'

which responds with


(1 row(s) affected)


(1 row(s) affected)

To clean up, one executes:


exec sp_cursorclose 180150000

and QA responds nicely with:


The command(s) completed successfully.

Now try


select * from T

which produces


pk          data                                               locked 
----------- -------------------------------------------------- ------ 
43          zzz                                                1

Exactly what is desired (or not!?)

The magic occurs with the three cursorfetch statements


exec sp_cursorfetch 180150000, 16, 2, 1
exec sp_cursorfetch 180150000, 16, 1, 1
exec sp_cursorfetch 180150000, 16, 2, 1

Without them, the update step


exec sp_cursor 180150000, 33, 1, N'V', @data = 'zzz'

responds with


Server: Msg 16934, Level 10, State 1, Line 1
Optimistic concurrency check failed. The row was modified outside of this cursor.

(0 row(s) affected)


(0 row(s) affected)

and no row is updated.

Note, that I only access the view V in QA, and yet I succeded in changing a value I could not see.

Again I restate my question; is this right, that sometimes, through one application you cannot update this row, and at other times, through another application (with a little cheating!?), you can update the row.

I need either a more detailled definition of this check option, or ...?

If you, or anyone else is interested, I'll be glad to mail the whole profiler trace (.trc file). Just let me know.

Tommy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources