/ Check-in [9842f2d5]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:When inserting a row into a child table, invoke the authorization callback to request permission to read the parent key columns.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 9842f2d5f606eb8f641ecae9fbc5368b8d7e4286
User & Date: dan 2009-10-02 14:23:42
Context
2009-10-02
15:29
Add one more authentication test to fkey2.test to cover an untested branch. check-in: e4fa8be7 user: dan tags: trunk
14:23
When inserting a row into a child table, invoke the authorization callback to request permission to read the parent key columns. check-in: 9842f2d5 user: dan tags: trunk
06:35
Add a test to check that the incrblob API cannot be used to write to an IPK column. Also a comment to explain why the incrblob code does not need to check if a column is part of a parent key before writing to it. check-in: dca2a7f6 user: dan tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/auth.c.

    86     86   ** Write an error message into pParse->zErrMsg that explains that the
    87     87   ** user-supplied authorization function returned an illegal value.
    88     88   */
    89     89   static void sqliteAuthBadReturnCode(Parse *pParse){
    90     90     sqlite3ErrorMsg(pParse, "authorizer malfunction");
    91     91     pParse->rc = SQLITE_ERROR;
    92     92   }
           93  +
           94  +/*
           95  +** Invoke the authorization callback for permission to read column zCol from
           96  +** table zTab in database zDb. This function assumes that an authorization
           97  +** callback has been registered (i.e. that sqlite3.xAuth is not NULL).
           98  +**
           99  +** If SQLITE_IGNORE is returned and pExpr is not NULL, then pExpr is changed
          100  +** to an SQL NULL expression. Otherwise, if pExpr is NULL, then SQLITE_IGNORE
          101  +** is treated as SQLITE_DENY. In this case an error is left in pParse.
          102  +*/
          103  +void sqlite3AuthReadCol(
          104  +  Parse *pParse,                  /* The parser context */
          105  +  const char *zTab,               /* Table name */
          106  +  const char *zCol,               /* Column name */
          107  +  int iDb,                        /* Index of containing database. */
          108  +  Expr *pExpr                     /* Optional expression */
          109  +){
          110  +  sqlite3 *db = pParse->db;       /* Database handle */
          111  +  char *zDb = db->aDb[iDb].zName; /* Name of attached database */
          112  +  int rc;                         /* Auth callback return code */
          113  +
          114  +  rc = db->xAuth(db->pAuthArg, SQLITE_READ, zTab,zCol,zDb,pParse->zAuthContext);
          115  +  if( rc!=SQLITE_IGNORE && rc!=SQLITE_DENY && rc!=SQLITE_OK ){
          116  +    sqliteAuthBadReturnCode(pParse);
          117  +  }else if( rc==SQLITE_IGNORE && pExpr ){
          118  +    pExpr->op = TK_NULL;
          119  +  }else if( rc!=SQLITE_OK ){
          120  +    if( db->nDb>2 || iDb!=0 ){
          121  +      sqlite3ErrorMsg(pParse, "access to %s.%s.%s is prohibited",zDb,zTab,zCol);
          122  +    }else{
          123  +      sqlite3ErrorMsg(pParse, "access to %s.%s is prohibited", zTab, zCol);
          124  +    }
          125  +    pParse->rc = SQLITE_AUTH;
          126  +  }
          127  +}
    93    128   
    94    129   /*
    95    130   ** The pExpr should be a TK_COLUMN expression.  The table referred to
    96    131   ** is in pTabList or else it is the NEW or OLD table of a trigger.  
    97    132   ** Check to see if it is OK to read this particular column.
    98    133   **
    99    134   ** If the auth function returns SQLITE_IGNORE, change the TK_COLUMN 
................................................................................
   103    138   void sqlite3AuthRead(
   104    139     Parse *pParse,        /* The parser context */
   105    140     Expr *pExpr,          /* The expression to check authorization on */
   106    141     Schema *pSchema,      /* The schema of the expression */
   107    142     SrcList *pTabList     /* All table that pExpr might refer to */
   108    143   ){
   109    144     sqlite3 *db = pParse->db;
   110         -  int rc;
   111    145     Table *pTab = 0;      /* The table being read */
   112    146     const char *zCol;     /* Name of the column of the table */
   113    147     int iSrc;             /* Index in pTabList->a[] of table being read */
   114         -  const char *zDBase;   /* Name of database being accessed */
   115    148     int iDb;              /* The index of the database the expression refers to */
   116    149     int iCol;             /* Index of column in table */
   117    150   
   118    151     if( db->xAuth==0 ) return;
   119    152     iDb = sqlite3SchemaToIndex(pParse->db, pSchema);
   120    153     if( iDb<0 ){
   121    154       /* An attempt to read a column out of a subquery or other
................................................................................
   144    177     }else if( pTab->iPKey>=0 ){
   145    178       assert( pTab->iPKey<pTab->nCol );
   146    179       zCol = pTab->aCol[pTab->iPKey].zName;
   147    180     }else{
   148    181       zCol = "ROWID";
   149    182     }
   150    183     assert( iDb>=0 && iDb<db->nDb );
   151         -  zDBase = db->aDb[iDb].zName;
   152         -  rc = db->xAuth(db->pAuthArg, SQLITE_READ, pTab->zName, zCol, zDBase, 
   153         -                 pParse->zAuthContext);
   154         -  if( rc==SQLITE_IGNORE ){
   155         -    pExpr->op = TK_NULL;
   156         -  }else if( rc==SQLITE_DENY ){
   157         -    if( db->nDb>2 || iDb!=0 ){
   158         -      sqlite3ErrorMsg(pParse, "access to %s.%s.%s is prohibited", 
   159         -         zDBase, pTab->zName, zCol);
   160         -    }else{
   161         -      sqlite3ErrorMsg(pParse, "access to %s.%s is prohibited",pTab->zName,zCol);
   162         -    }
   163         -    pParse->rc = SQLITE_AUTH;
   164         -  }else if( rc!=SQLITE_OK ){
   165         -    sqliteAuthBadReturnCode(pParse);
   166         -  }
          184  +  sqlite3AuthReadCol(pParse, pTab->zName, zCol, iDb, pExpr);
   167    185   }
   168    186   
   169    187   /*
   170    188   ** Do an authorization check using the code and arguments given.  Return
   171    189   ** either SQLITE_OK (zero) or SQLITE_IGNORE or SQLITE_DENY.  If SQLITE_DENY
   172    190   ** is returned, then the error count and error message in pParse are
   173    191   ** modified appropriately.

Changes to src/fkey.c.

   728    728         iCol = pFKey->aCol[0].iFrom;
   729    729         aiCol = &iCol;
   730    730       }
   731    731       for(i=0; i<pFKey->nCol; i++){
   732    732         if( aiCol[i]==pTab->iPKey ){
   733    733           aiCol[i] = -1;
   734    734         }
          735  +#ifndef SQLITE_OMIT_AUTHORIZATION
          736  +      /* Request permission to read the parent key columns. */
          737  +      if( db->xAuth ){
          738  +        char *zCol = pTo->aCol[pIdx ? pIdx->aiColumn[i] : pTo->iPKey].zName;
          739  +        sqlite3AuthReadCol(pParse, pTo->zName, zCol, iDb, 0);
          740  +      }
          741  +#endif
   735    742       }
   736    743   
   737    744       /* Take a shared-cache advisory read-lock on the parent table. Allocate 
   738    745       ** a cursor to use to search the unique index on the parent key columns 
   739    746       ** in the parent table.  */
   740    747       sqlite3TableLock(pParse, iDb, pTo->tnum, 0, pTo->zName);
   741    748       pParse->nTab++;

Changes to src/sqliteInt.h.

  2730   2730   void sqlite3CreateForeignKey(Parse*, ExprList*, Token*, ExprList*, int);
  2731   2731   void sqlite3DeferForeignKey(Parse*, int);
  2732   2732   #ifndef SQLITE_OMIT_AUTHORIZATION
  2733   2733     void sqlite3AuthRead(Parse*,Expr*,Schema*,SrcList*);
  2734   2734     int sqlite3AuthCheck(Parse*,int, const char*, const char*, const char*);
  2735   2735     void sqlite3AuthContextPush(Parse*, AuthContext*, const char*);
  2736   2736     void sqlite3AuthContextPop(AuthContext*);
         2737  +  void sqlite3AuthReadCol(Parse*, const char *, const char *, int, Expr *);
  2737   2738   #else
  2738   2739   # define sqlite3AuthRead(a,b,c,d)
  2739   2740   # define sqlite3AuthCheck(a,b,c,d,e)    SQLITE_OK
  2740   2741   # define sqlite3AuthContextPush(a,b,c)
  2741   2742   # define sqlite3AuthContextPop(a)  ((void)(a))
  2742   2743   #endif
  2743   2744   void sqlite3Attach(Parse*, Expr*, Expr*, Expr*);

Changes to test/fkey2.test.

  1347   1347   } {1}
  1348   1348   do_test fkey2-17.2.9 {
  1349   1349     expr [db total_changes] - $nTotal
  1350   1350   } {4}
  1351   1351   do_test fkey2-17.2.10 {
  1352   1352     execsql { SELECT * FROM high ; SELECT * FROM low }
  1353   1353   } {}
         1354  +execsql { PRAGMA count_changes = 0 }
  1354   1355   
  1355   1356   #-------------------------------------------------------------------------
  1356   1357   # Test that the authorization callback works.
  1357   1358   #
  1358   1359   
         1360  +ifcapable auth {
         1361  +  do_test fkey2-18.1 {
         1362  +    execsql {
         1363  +      CREATE TABLE long(a, b PRIMARY KEY, c);
         1364  +      CREATE TABLE short(d, e, f REFERENCES long);
         1365  +      CREATE TABLE mid(g, h, i REFERENCES long DEFERRABLE INITIALLY DEFERRED);
         1366  +    }
         1367  +  } {}
         1368  +
         1369  +  proc auth {args} {eval lappend ::authargs $args ; return SQLITE_OK}
         1370  +  db auth auth
         1371  +
         1372  +  # An insert on the parent table must read the child key of any deferred
         1373  +  # foreign key constraints. But not the child key of immediate constraints.
         1374  +  set authargs {}
         1375  +  do_test fkey2-18.2 {
         1376  +    execsql { INSERT INTO long VALUES(1, 2, 3) }
         1377  +    set authargs
         1378  +  } {SQLITE_INSERT long {} main {} SQLITE_READ mid i main {}}
         1379  +
         1380  +  # An insert on the child table of an immediate constraint must read the
         1381  +  # parent key columns (to see if it is a violation or not).
         1382  +  set authargs {}
         1383  +  do_test fkey2-18.3 {
         1384  +    execsql { INSERT INTO short VALUES(1, 3, 2) }
         1385  +    set authargs
         1386  +  } {SQLITE_INSERT short {} main {} SQLITE_READ long b main {}}
         1387  +  
         1388  +  # As must an insert on the child table of a deferred constraint.
         1389  +  set authargs {}
         1390  +  do_test fkey2-18.4 {
         1391  +    execsql { INSERT INTO mid VALUES(1, 3, 2) }
         1392  +    set authargs
         1393  +  } {SQLITE_INSERT mid {} main {} SQLITE_READ long b main {}}
         1394  +
         1395  +  do_test fkey2-18.5 {
         1396  +    execsql {
         1397  +      CREATE TABLE nought(a, b PRIMARY KEY, c);
         1398  +      CREATE TABLE cross(d, e, f,
         1399  +        FOREIGN KEY(e) REFERENCES nought(b) ON UPDATE CASCADE
         1400  +      );
         1401  +    }
         1402  +    execsql { INSERT INTO nought VALUES(2, 1, 2) }
         1403  +    execsql { INSERT INTO cross VALUES(0, 1, 0) }
         1404  +    set authargs [list]
         1405  +    execsql { UPDATE nought SET b = 5 }
         1406  +    set authargs
         1407  +  } {SQLITE_UPDATE nought b main {} SQLITE_READ cross e main {} SQLITE_READ cross e main {} SQLITE_READ nought b main {} SQLITE_READ nought b main {} SQLITE_READ nought b main {} SQLITE_UPDATE cross e main {} SQLITE_READ nought b main {} SQLITE_READ cross e main {} SQLITE_READ nought b main {} SQLITE_READ nought b main {}}
         1408  +
         1409  +  do_test fkey2-18.6 {
         1410  +    execsql {SELECT * FROM cross}
         1411  +  } {0 5 0}
         1412  +
         1413  +  rename auth {}
         1414  +  proc auth {args} {
         1415  +    if {[lindex $args 1] == "long"} {return SQLITE_IGNORE}
         1416  +    return SQLITE_OK
         1417  +  }
         1418  +  do_test fkey2-18.7 {
         1419  +    catchsql { INSERT INTO short VALUES(1, 3, 2) }
         1420  +  } {1 {access to long.b is prohibited}}
  1359   1421   
  1360         -execsql { PRAGMA count_changes = 0 }
         1422  +  db auth {}
         1423  +  unset authargs
         1424  +}
         1425  +
  1361   1426   #-------------------------------------------------------------------------
  1362   1427   # The following block of tests, those prefixed with "fkey2-genfkey.", are 
  1363   1428   # the same tests that were used to test the ".genfkey" command provided 
  1364   1429   # by the shell tool. So these tests show that the built-in foreign key 
  1365   1430   # implementation is more or less compatible with the triggers generated 
  1366   1431   # by genfkey.
  1367   1432   #