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 |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
9842f2d5f606eb8f641ecae9fbc5368b |
User & Date: | dan 2009-10-02 14:23:42.000 |
Context
2009-10-02
| ||
15:29 | Add one more authentication test to fkey2.test to cover an untested branch. (check-in: e4fa8be770 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: 9842f2d5f6 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: dca2a7f608 user: dan tags: trunk) | |
Changes
Changes to src/auth.c.
︙ | ︙ | |||
86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 | ** Write an error message into pParse->zErrMsg that explains that the ** user-supplied authorization function returned an illegal value. */ static void sqliteAuthBadReturnCode(Parse *pParse){ sqlite3ErrorMsg(pParse, "authorizer malfunction"); pParse->rc = SQLITE_ERROR; } /* ** The pExpr should be a TK_COLUMN expression. The table referred to ** is in pTabList or else it is the NEW or OLD table of a trigger. ** Check to see if it is OK to read this particular column. ** ** If the auth function returns SQLITE_IGNORE, change the TK_COLUMN ** instruction into a TK_NULL. If the auth function returns SQLITE_DENY, ** then generate an error. */ void sqlite3AuthRead( Parse *pParse, /* The parser context */ Expr *pExpr, /* The expression to check authorization on */ Schema *pSchema, /* The schema of the expression */ SrcList *pTabList /* All table that pExpr might refer to */ ){ sqlite3 *db = pParse->db; | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > < < | 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 | ** Write an error message into pParse->zErrMsg that explains that the ** user-supplied authorization function returned an illegal value. */ static void sqliteAuthBadReturnCode(Parse *pParse){ sqlite3ErrorMsg(pParse, "authorizer malfunction"); pParse->rc = SQLITE_ERROR; } /* ** Invoke the authorization callback for permission to read column zCol from ** table zTab in database zDb. This function assumes that an authorization ** callback has been registered (i.e. that sqlite3.xAuth is not NULL). ** ** If SQLITE_IGNORE is returned and pExpr is not NULL, then pExpr is changed ** to an SQL NULL expression. Otherwise, if pExpr is NULL, then SQLITE_IGNORE ** is treated as SQLITE_DENY. In this case an error is left in pParse. */ void sqlite3AuthReadCol( Parse *pParse, /* The parser context */ const char *zTab, /* Table name */ const char *zCol, /* Column name */ int iDb, /* Index of containing database. */ Expr *pExpr /* Optional expression */ ){ sqlite3 *db = pParse->db; /* Database handle */ char *zDb = db->aDb[iDb].zName; /* Name of attached database */ int rc; /* Auth callback return code */ rc = db->xAuth(db->pAuthArg, SQLITE_READ, zTab,zCol,zDb,pParse->zAuthContext); if( rc!=SQLITE_IGNORE && rc!=SQLITE_DENY && rc!=SQLITE_OK ){ sqliteAuthBadReturnCode(pParse); }else if( rc==SQLITE_IGNORE && pExpr ){ pExpr->op = TK_NULL; }else if( rc!=SQLITE_OK ){ if( db->nDb>2 || iDb!=0 ){ sqlite3ErrorMsg(pParse, "access to %s.%s.%s is prohibited",zDb,zTab,zCol); }else{ sqlite3ErrorMsg(pParse, "access to %s.%s is prohibited", zTab, zCol); } pParse->rc = SQLITE_AUTH; } } /* ** The pExpr should be a TK_COLUMN expression. The table referred to ** is in pTabList or else it is the NEW or OLD table of a trigger. ** Check to see if it is OK to read this particular column. ** ** If the auth function returns SQLITE_IGNORE, change the TK_COLUMN ** instruction into a TK_NULL. If the auth function returns SQLITE_DENY, ** then generate an error. */ void sqlite3AuthRead( Parse *pParse, /* The parser context */ Expr *pExpr, /* The expression to check authorization on */ Schema *pSchema, /* The schema of the expression */ SrcList *pTabList /* All table that pExpr might refer to */ ){ sqlite3 *db = pParse->db; Table *pTab = 0; /* The table being read */ const char *zCol; /* Name of the column of the table */ int iSrc; /* Index in pTabList->a[] of table being read */ int iDb; /* The index of the database the expression refers to */ int iCol; /* Index of column in table */ if( db->xAuth==0 ) return; iDb = sqlite3SchemaToIndex(pParse->db, pSchema); if( iDb<0 ){ /* An attempt to read a column out of a subquery or other |
︙ | ︙ | |||
144 145 146 147 148 149 150 | }else if( pTab->iPKey>=0 ){ assert( pTab->iPKey<pTab->nCol ); zCol = pTab->aCol[pTab->iPKey].zName; }else{ zCol = "ROWID"; } assert( iDb>=0 && iDb<db->nDb ); | < < < < < < < < | < < < < < < < | 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 | }else if( pTab->iPKey>=0 ){ assert( pTab->iPKey<pTab->nCol ); zCol = pTab->aCol[pTab->iPKey].zName; }else{ zCol = "ROWID"; } assert( iDb>=0 && iDb<db->nDb ); sqlite3AuthReadCol(pParse, pTab->zName, zCol, iDb, pExpr); } /* ** Do an authorization check using the code and arguments given. Return ** either SQLITE_OK (zero) or SQLITE_IGNORE or SQLITE_DENY. If SQLITE_DENY ** is returned, then the error count and error message in pParse are ** modified appropriately. |
︙ | ︙ |
Changes to src/fkey.c.
︙ | ︙ | |||
728 729 730 731 732 733 734 735 736 737 738 739 740 741 | iCol = pFKey->aCol[0].iFrom; aiCol = &iCol; } for(i=0; i<pFKey->nCol; i++){ if( aiCol[i]==pTab->iPKey ){ aiCol[i] = -1; } } /* Take a shared-cache advisory read-lock on the parent table. Allocate ** a cursor to use to search the unique index on the parent key columns ** in the parent table. */ sqlite3TableLock(pParse, iDb, pTo->tnum, 0, pTo->zName); pParse->nTab++; | > > > > > > > | 728 729 730 731 732 733 734 735 736 737 738 739 740 741 742 743 744 745 746 747 748 | iCol = pFKey->aCol[0].iFrom; aiCol = &iCol; } for(i=0; i<pFKey->nCol; i++){ if( aiCol[i]==pTab->iPKey ){ aiCol[i] = -1; } #ifndef SQLITE_OMIT_AUTHORIZATION /* Request permission to read the parent key columns. */ if( db->xAuth ){ char *zCol = pTo->aCol[pIdx ? pIdx->aiColumn[i] : pTo->iPKey].zName; sqlite3AuthReadCol(pParse, pTo->zName, zCol, iDb, 0); } #endif } /* Take a shared-cache advisory read-lock on the parent table. Allocate ** a cursor to use to search the unique index on the parent key columns ** in the parent table. */ sqlite3TableLock(pParse, iDb, pTo->tnum, 0, pTo->zName); pParse->nTab++; |
︙ | ︙ |
Changes to src/sqliteInt.h.
︙ | ︙ | |||
2730 2731 2732 2733 2734 2735 2736 2737 2738 2739 2740 2741 2742 2743 | void sqlite3CreateForeignKey(Parse*, ExprList*, Token*, ExprList*, int); void sqlite3DeferForeignKey(Parse*, int); #ifndef SQLITE_OMIT_AUTHORIZATION void sqlite3AuthRead(Parse*,Expr*,Schema*,SrcList*); int sqlite3AuthCheck(Parse*,int, const char*, const char*, const char*); void sqlite3AuthContextPush(Parse*, AuthContext*, const char*); void sqlite3AuthContextPop(AuthContext*); #else # define sqlite3AuthRead(a,b,c,d) # define sqlite3AuthCheck(a,b,c,d,e) SQLITE_OK # define sqlite3AuthContextPush(a,b,c) # define sqlite3AuthContextPop(a) ((void)(a)) #endif void sqlite3Attach(Parse*, Expr*, Expr*, Expr*); | > | 2730 2731 2732 2733 2734 2735 2736 2737 2738 2739 2740 2741 2742 2743 2744 | void sqlite3CreateForeignKey(Parse*, ExprList*, Token*, ExprList*, int); void sqlite3DeferForeignKey(Parse*, int); #ifndef SQLITE_OMIT_AUTHORIZATION void sqlite3AuthRead(Parse*,Expr*,Schema*,SrcList*); int sqlite3AuthCheck(Parse*,int, const char*, const char*, const char*); void sqlite3AuthContextPush(Parse*, AuthContext*, const char*); void sqlite3AuthContextPop(AuthContext*); void sqlite3AuthReadCol(Parse*, const char *, const char *, int, Expr *); #else # define sqlite3AuthRead(a,b,c,d) # define sqlite3AuthCheck(a,b,c,d,e) SQLITE_OK # define sqlite3AuthContextPush(a,b,c) # define sqlite3AuthContextPop(a) ((void)(a)) #endif void sqlite3Attach(Parse*, Expr*, Expr*, Expr*); |
︙ | ︙ |
Changes to test/fkey2.test.
︙ | ︙ | |||
1347 1348 1349 1350 1351 1352 1353 1354 1355 1356 1357 1358 | } {1} do_test fkey2-17.2.9 { expr [db total_changes] - $nTotal } {4} do_test fkey2-17.2.10 { execsql { SELECT * FROM high ; SELECT * FROM low } } {} #------------------------------------------------------------------------- # Test that the authorization callback works. # | > > > > > > > | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1347 1348 1349 1350 1351 1352 1353 1354 1355 1356 1357 1358 1359 1360 1361 1362 1363 1364 1365 1366 1367 1368 1369 1370 1371 1372 1373 1374 1375 1376 1377 1378 1379 1380 1381 1382 1383 1384 1385 1386 1387 1388 1389 1390 1391 1392 1393 1394 1395 1396 1397 1398 1399 1400 1401 1402 1403 1404 1405 1406 1407 1408 1409 1410 1411 1412 1413 1414 1415 1416 1417 1418 1419 1420 1421 1422 1423 1424 1425 1426 1427 1428 1429 1430 1431 1432 | } {1} do_test fkey2-17.2.9 { expr [db total_changes] - $nTotal } {4} do_test fkey2-17.2.10 { execsql { SELECT * FROM high ; SELECT * FROM low } } {} execsql { PRAGMA count_changes = 0 } #------------------------------------------------------------------------- # Test that the authorization callback works. # ifcapable auth { do_test fkey2-18.1 { execsql { CREATE TABLE long(a, b PRIMARY KEY, c); CREATE TABLE short(d, e, f REFERENCES long); CREATE TABLE mid(g, h, i REFERENCES long DEFERRABLE INITIALLY DEFERRED); } } {} proc auth {args} {eval lappend ::authargs $args ; return SQLITE_OK} db auth auth # An insert on the parent table must read the child key of any deferred # foreign key constraints. But not the child key of immediate constraints. set authargs {} do_test fkey2-18.2 { execsql { INSERT INTO long VALUES(1, 2, 3) } set authargs } {SQLITE_INSERT long {} main {} SQLITE_READ mid i main {}} # An insert on the child table of an immediate constraint must read the # parent key columns (to see if it is a violation or not). set authargs {} do_test fkey2-18.3 { execsql { INSERT INTO short VALUES(1, 3, 2) } set authargs } {SQLITE_INSERT short {} main {} SQLITE_READ long b main {}} # As must an insert on the child table of a deferred constraint. set authargs {} do_test fkey2-18.4 { execsql { INSERT INTO mid VALUES(1, 3, 2) } set authargs } {SQLITE_INSERT mid {} main {} SQLITE_READ long b main {}} do_test fkey2-18.5 { execsql { CREATE TABLE nought(a, b PRIMARY KEY, c); CREATE TABLE cross(d, e, f, FOREIGN KEY(e) REFERENCES nought(b) ON UPDATE CASCADE ); } execsql { INSERT INTO nought VALUES(2, 1, 2) } execsql { INSERT INTO cross VALUES(0, 1, 0) } set authargs [list] execsql { UPDATE nought SET b = 5 } set authargs } {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 {}} do_test fkey2-18.6 { execsql {SELECT * FROM cross} } {0 5 0} rename auth {} proc auth {args} { if {[lindex $args 1] == "long"} {return SQLITE_IGNORE} return SQLITE_OK } do_test fkey2-18.7 { catchsql { INSERT INTO short VALUES(1, 3, 2) } } {1 {access to long.b is prohibited}} db auth {} unset authargs } #------------------------------------------------------------------------- # The following block of tests, those prefixed with "fkey2-genfkey.", are # the same tests that were used to test the ".genfkey" command provided # by the shell tool. So these tests show that the built-in foreign key # implementation is more or less compatible with the triggers generated # by genfkey. # |
︙ | ︙ |