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:Microsoft Windows (xp sp 2)
Assigned to: Assigned Account CPU Architecture:Any
Tags: odbc51_postga
Triage: D4 (Minor)

[24 Apr 2007 2:09] Erica Moss
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
[22 Oct 2007 14:39] Susanne Ebrecht
Bug #28063 is a duplicate of this bug here.
[14 Dec 2007 9:01] Erica Moss
Retested against MyODBC 5.51.22 and also 5.1.1  Problem is still present
[31 Jan 2008 19:33] Tonci Grgin
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...
[6 Feb 2008 8:20] Tonci Grgin
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?
[29 Feb 2008 21:20] Jim Winstead
Set correct SQLSTATE for permission errors (no test case)

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

[13 Mar 2008 16:05] Jim Winstead
Lawrin offered to take this over.
[3 Dec 2010 4:19] Bogdan Degtyariov
Lawrin,

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