MySQL Bugs: #28062: MyODBC 3/ ADO record set status - adRecPermissionDenied not working correctly

Bug #28062	MyODBC 3/ ADO record set status - adRecPermissionDenied not working correctly
Submitted:	24 Apr 2007 2:09	Modified:	13 Mar 2008 16:05
Reporter:	Erica Moss	Email Updates:
Status:	Verified	Impact on me:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:	3.51.22 / 5.1.1 / 5.2.2	OS:	Windows (xp sp 2)
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	odbc51_postga

Description:
In this test a user who only has a SELECT grant is attempting to update the datasource.  After this attempt, the recordset status should have adRecPermissionDenied added to it, but it doesn't.  

When adUseClient cursor is used, the rs.status is set to adRecModified.
When adUseServer cursor is used, the status is set to adRecModified + adRecIntegrityViolation.

The behavior in this test is identical for MyODBC 5.0 but a different bug is entered for tracking purposes

How to repeat:
SETUP:
DROP TABLE IF EXISTS ado_test;
CREATE TABLE ado_test (C1 INT PRIMARY KEY, C2 CHAR(20));
INSERT INTO ado_test VALUES (1, 'A'), (2, 'B');
CREATE USER foo@localhost IDENTIFIED BY 'bar';
GRANT SELECT ON ado.* TO foo@localhost;

VB Code:
Private Sub Test()
    On Error GoTo EH
    Dim strSQL, strConn As String
    Dim connection1 As ADODB.Connection
    Dim recset1 As ADODB.Recordset
    Dim fld As Field
    Set connection1 = New ADODB.Connection
    Set recset1 = New ADODB.Recordset
    Set recset2 = New ADODB.Recordset
    
    strSQL = "SELECT * FROM ado_test"
        
'   strConn = "DRIVER={MySQL ODBC 3.51 Driver};" & _
        "SERVER=localhost;DATABASE=ado" & _
        ";USER=foo;PASSWORD=bar;OPTION=3;"

   ' open connection
    connection1.ConnectionString = strConn
    connection1.CursorLocation = adUseClient
   ' connection1.CursorLocation = adUseServer
    connection1.Open
    
    strSQL = "SELECT * FROM ado_test"
    recset1.Open strSQL, connection1, adOpenStatic, _
                            adLockBatchOptimistic, adCmdText
    recset1.MoveFirst

    recset1.Fields(0).Value = 111
    recset1.Fields(1).Value = "test"
    recset1.UpdateBatch
    Debug.Print "New Status: " & recset1.Status
    
    recset1.Close
    Set recset1 = Nothing
    connection1.Close
    Set connection1 = Nothing
    Exit Sub
EH:
    Debug.Print Err.Description & " " & Err.Number & vbNewLine
    Resume Next
End Sub

Bug #28063 is a duplicate of this bug here.

Retested against MyODBC 5.51.22 and also 5.1.1  Problem is still present

As the server doesn't provide us with any information to be able to indicate that the user doesn't have update/insert/delete permissions on a particular result set this is almost impossible to fix. It's even hard to figure that out for a table without actually trying to insert/update/delete...

I must revert my ruling after making test:
--<cut>--
+----+--------------------+------+
| ID | Name | File |
+----+--------------------+------+
| 1 | blah | NULL |
| 2 | blahblahblah | NULL |
--<cut>--
mysql> update tst1 set Name = "tst again" where ID = 2;
ERROR 1142 (42000): UPDATE command denied to user 'bug28062'@'localhost' for table 'tst1'

#Error: 1142 SQLSTATE: 42000 (ER_TABLEACCESS_DENIED_ERROR)
Message: %s command denied to user '%s'@'%s' for table '%s'

Seemingly it is possible to track this and set proper recordset status. Jim?

Set correct SQLSTATE for permission errors (no test case)

Attachment: bug28062.diff (text/plain), 913 bytes.

Lawrin offered to take this over.

Lawrin,

you have the patch for this bug. Do you think it is still incomplete?