1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sql/TEST/t_sqlsecurity4.cpp Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,405 @@
1.4 +// Copyright (c) 2006-2009 Nokia Corporation and/or its subsidiary(-ies).
1.5 +// All rights reserved.
1.6 +// This component and the accompanying materials are made available
1.7 +// under the terms of "Eclipse Public License v1.0"
1.8 +// which accompanies this distribution, and is available
1.9 +// at the URL "http://www.eclipse.org/legal/epl-v10.html".
1.10 +//
1.11 +// Initial Contributors:
1.12 +// Nokia Corporation - initial contribution.
1.13 +//
1.14 +// Contributors:
1.15 +//
1.16 +// Description:
1.17 +// t_sqlsecurity4 application has capabilities allowing read/write access to the test database
1.18 +//
1.19 +//
1.20 +
1.21 +#include <e32test.h>
1.22 +#include <bautils.h>
1.23 +#include <sqldb.h>
1.24 +
1.25 +///////////////////////////////////////////////////////////////////////////////////////
1.26 +//The test database has:
1.27 +// SCHEMA database policy: ECapabilityReadDeviceData, ECapabilityWriteUserData, ECapabilityReadUserData
1.28 +// WRITE database policy: ECapabilityWriteUserData
1.29 +// READ database policy: ECapabilityReadUserData
1.30 +//
1.31 +//Database tables:
1.32 +// TABLE A(F1 INTEGER, B1 BLOB)
1.33 +// TABLE B(F2 INTEGER, F3 TEXT, B2 BLOB)
1.34 +//
1.35 +//Database data:
1.36 +// TABLE A: {1, x'41414141414141414141'}, {2, x'42424242424242424242'}, {3, x'43434343434343434343'}, {4, x'44444444444444444444'}
1.37 +// TABLE B: {2, "ABC", x'45454545454545454545'}, {4, "DEF", x'46464646464646464646'}
1.38 +
1.39 +///////////////////////////////////////////////////////////////////////////////////////
1.40 +
1.41 +#define UNUSED_VAR(a) (a) = (a)
1.42 +
1.43 +RSqlDatabase TheDb;
1.44 +RTest TheTest(_L("t_sqlsecurity4 test"));
1.45 +
1.46 +_LIT(KTestDbName, "c:[21212125]t_ab.db");
1.47 +_LIT(KTestDbName2, "c:\\test\\t_sqlsecurity4_2.db");
1.48 +
1.49 +///////////////////////////////////////////////////////////////////////////////////////
1.50 +//Restore original test database function
1.51 +void RestoreOriginalDb()
1.52 + {
1.53 + TheDb.Close();
1.54 + TheDb.Open(KTestDbName);
1.55 +
1.56 + // Delete and restore the content of table A (unconditional DELETE, no READ operations)
1.57 + TheDb.Exec(_L("DELETE FROM A"));
1.58 + TheDb.Exec(_L("INSERT INTO A(F1,B1) VALUES(1,x'41414141414141414141');INSERT INTO A(F1,B1) VALUES(2,x'42424242424242424242');INSERT INTO A(F1,B1) VALUES(3,x'43434343434343434343');INSERT INTO A(F1,B1) VALUES(4,x'44444444444444444444');"));
1.59 +
1.60 + // Delete and restore the content of table B (unconditional DELETE, no READ operations)
1.61 + TheDb.Exec(_L("DELETE FROM B"));
1.62 + TheDb.Exec(_L("INSERT INTO B(F2,F3,B2) VALUES(2, 'ABC',x'45454545454545454545');INSERT INTO B(F2,F3,B2) VALUES(4,'DEF',x'46464646464646464646');"));
1.63 +
1.64 + TheDb.Close();
1.65 + }
1.66 +
1.67 +///////////////////////////////////////////////////////////////////////////////////////
1.68 +//Test macros and functions
1.69 +void Check1(TInt aValue, TInt aLine)
1.70 + {
1.71 + if(!aValue)
1.72 + {
1.73 + RestoreOriginalDb();
1.74 + RDebug::Print(_L("*** Line %d\r\n"), aLine);
1.75 + TheTest(EFalse, aLine);
1.76 + }
1.77 + }
1.78 +void Check2(TInt aValue, TInt aExpected, TInt aLine)
1.79 + {
1.80 + if(aValue != aExpected)
1.81 + {
1.82 + RestoreOriginalDb();
1.83 + RDebug::Print(_L("*** Line %d, Expected error: %d, got: %d\r\n"), aLine, aExpected, aValue);
1.84 + TheTest(EFalse, aLine);
1.85 + }
1.86 + }
1.87 +#define TEST(arg) ::Check1((arg), __LINE__)
1.88 +#define TEST2(aValue, aExpected) ::Check2(aValue, aExpected, __LINE__)
1.89 +
1.90 +///////////////////////////////////////////////////////////////////////////////////////
1.91 +
1.92 +//This functnion is called while there is an open secure connection.
1.93 +//The function will create a new, non-secure connection and check that the non-secure database schema can be modified,
1.94 +//while there is another alive, secure database connection.
1.95 +void NonSecureDbTest()
1.96 + {
1.97 + (void)RSqlDatabase::Delete(KTestDbName2);
1.98 + RSqlDatabase db;
1.99 + TInt err = db.Create(KTestDbName2);
1.100 + TEST2(err, KErrNone);
1.101 +
1.102 + err = db.Exec(_L("CREATE TABLE A(I1 INTEGER, I2 INTEGER)"));
1.103 + TEST(err >= 0);
1.104 + err = db.Exec(_L("CREATE TEMP TABLE B(I1 INTEGER, I2 INTEGER)"));
1.105 + TEST(err >= 0);
1.106 +
1.107 + //"CREATE VIRTUAL TABLE" statement not supported
1.108 + err = db.Exec(_L("CREATE VIRTUAL TABLE V1 USING FTS3(ColOne TEXT, ColTwo DATETIME)"));
1.109 + TPtrC msg = db.LastErrorMessage();
1.110 + TheTest.Printf(_L("*** \"CREATE VIRTUAL TABLE\" expected failure, msg=\"%S\", err=%d\r\n"), &msg, err);
1.111 + TEST(err != KErrNone);
1.112 +
1.113 + err = db.Exec(_L("CREATE TRIGGER T1 AFTER INSERT ON A BEGIN INSERT INTO B VALUES(new.I1, new.I2); END;"));
1.114 + TEST(err >= 0);
1.115 + err = db.Exec(_L("DROP TRIGGER T1"));
1.116 + TEST(err >= 0);
1.117 + err = db.Exec(_L("CREATE TEMP TRIGGER T2 AFTER UPDATE OF I1 ON A BEGIN UPDATE B SET I1 = new.I1; END;"));
1.118 + TEST(err >= 0);
1.119 + err = db.Exec(_L("DROP TRIGGER T2"));
1.120 + TEST(err >= 0);
1.121 +
1.122 + err = db.Exec(_L("CREATE VIEW V1 AS SELECT * FROM A"));
1.123 + TEST(err >= 0);
1.124 + err = db.Exec(_L("DROP VIEW V1"));
1.125 + TEST(err >= 0);
1.126 + err = db.Exec(_L("CREATE TEMP VIEW V2 AS SELECT * FROM A"));
1.127 + TEST(err >= 0);
1.128 + err = db.Exec(_L("DROP VIEW V2"));
1.129 + TEST(err >= 0);
1.130 +
1.131 + err = db.Exec(_L("CREATE INDEX Idx1 ON A(I1)"));
1.132 + TEST(err >= 0);
1.133 + err = db.Exec(_L("ANALYZE A"));
1.134 + TEST(err >= 0);
1.135 + err = db.Exec(_L("DROP INDEX Idx1"));
1.136 + TEST(err >= 0);
1.137 + err = db.Exec(_L("CREATE INDEX Idx2 ON B(I1)"));
1.138 + TEST(err >= 0);
1.139 + err = db.Exec(_L("DROP INDEX Idx2"));
1.140 + TEST(err >= 0);
1.141 +
1.142 + err = db.Exec(_L("DROP TABLE B"));
1.143 + TEST(err >= 0);
1.144 + err = db.Exec(_L("DROP TABLE A"));
1.145 + TEST(err >= 0);
1.146 +
1.147 + db.Close();
1.148 + (void)RSqlDatabase::Delete(KTestDbName2);
1.149 + }
1.150 +
1.151 +/**
1.152 +@SYMTestCaseID SYSLIB-SQL-CT-1646
1.153 +@SYMTestCaseDesc Testing database operations on a secure database.
1.154 + The test application's capabilities allow read/write access to the test secure database.
1.155 + Verify that any other kind of a database operation will fail with KErrPermissionDenied error.
1.156 +@SYMTestPriority High
1.157 +@SYMTestActions Testing database operations on a secure database.
1.158 +@SYMTestExpectedResults Test must not fail
1.159 +@SYMREQ REQ5792
1.160 + REQ5793
1.161 +*/
1.162 +void ReadWriteDatabaseTest()
1.163 + {
1.164 + RSqlDatabase db;
1.165 + TInt err = TheDb.Open(KTestDbName);
1.166 + TEST2(err, KErrNone);
1.167 +
1.168 + //Attempt to modify the database schema
1.169 + err = TheDb.Exec(_L("CREATE TABLE C(FFF TEXT)"));
1.170 + TEST2(err, KErrPermissionDenied);
1.171 + err = TheDb.Exec(_L("CREATE TEMP TABLE TBL1(COL1 INTEGER, COL2 INTEGER)"));
1.172 + TEST(err >= 0);
1.173 + err = TheDb.Exec(_L("CREATE TEMP TRIGGER del1 AFTER DELETE ON TBL1 BEGIN DELETE FROM A; END;"));
1.174 + TEST(err >= 0);
1.175 + err = TheDb.Exec(_L("DROP TRIGGER del1"));
1.176 + TEST(err >= 0);
1.177 + err = TheDb.Exec(_L("CREATE TEMP VIEW V1 AS SELECT * FROM TBL1"));
1.178 + TEST(err >= 0);
1.179 + err = TheDb.Exec(_L("DROP VIEW V1"));
1.180 + TEST(err >= 0);
1.181 + err = TheDb.Exec(_L("CREATE INDEX I1 ON TBL1(COL2)"));
1.182 + TEST(err >= 0);
1.183 + err = TheDb.Exec(_L("DROP INDEX I1"));
1.184 + TEST(err >= 0);
1.185 + err = TheDb.Exec(_L("DROP TABLE TBL1"));
1.186 + TEST(err >= 0);
1.187 + err = TheDb.Exec(_L("ANALYZE A"));
1.188 + TEST2(err, KErrPermissionDenied);
1.189 + err = TheDb.Exec(_L("CREATE VIEW V2 AS SELECT * FROM A"));
1.190 + TEST2(err, KErrPermissionDenied);
1.191 + //Attempt to update the user data (but it includes a READ operation)
1.192 + err = TheDb.Exec(_L("UPDATE A SET F1 = 11 WHERE F1 = 1"));
1.193 + TEST(err >= 0);
1.194 + //Attempt to update the user data (unconditional UPDATE, no READ operations)
1.195 + err = TheDb.Exec(_L("UPDATE A SET F1 = 11"));
1.196 + TEST(err >= 0);
1.197 + //Attempt to delete the user data (but it includes a READ operation)
1.198 + err = TheDb.Exec(_L("DELETE FROM B WHERE F2 = 2"));
1.199 + TEST(err >= 0);
1.200 + //Attempt to delete the user data (unconditional DELETE, no READ operations)
1.201 + err = TheDb.Exec(_L("DELETE FROM A"));
1.202 + TEST(err >= 0);
1.203 + //Restore the deleted table A
1.204 + err = TheDb.Exec(_L("INSERT INTO A(F1,B1) VALUES(1,x'41414141414141414141');INSERT INTO A(F1,B1) VALUES(2,x'42424242424242424242');INSERT INTO A(F1,B1) VALUES(3,x'43434343434343434343');INSERT INTO A(F1,B1) VALUES(4,x'44444444444444444444');"));
1.205 + TEST(err >= 0);
1.206 + //Restore the deleted record in table B
1.207 + err = TheDb.Exec(_L("INSERT INTO B(F2, F3, B2) VALUES(2, 'ABC', x'45454545454545454545');"));
1.208 + TEST2(err, 1);
1.209 + //Attempt to insert new user data
1.210 + err = TheDb.Exec(_L("INSERT INTO B(F2, F3, B2) VALUES(6, 'GHI', x'47474747474747474747');"));
1.211 + TEST2(err, 1);
1.212 + //Attempt to read the user data
1.213 + RSqlStatement stmt;
1.214 + err = stmt.Prepare(TheDb, _L("SELECT A.F1 FROM B,A WHERE A.F1 = B.F2"));
1.215 + TEST2(err, KErrNone);
1.216 + //ColumnCount() has no capabilities assigned
1.217 + TInt colCnt = stmt.ColumnCount();
1.218 + TEST2(colCnt, 1);
1.219 + //DeclaredColumnType() has no capabilities assigned
1.220 + TSqlColumnType colType;
1.221 + err = stmt.DeclaredColumnType(0, colType);
1.222 + TEST2(err, KErrNone);
1.223 + TEST2(colType, ESqlInt);
1.224 + err = stmt.Next();
1.225 + TEST2(err, KSqlAtRow);
1.226 + RDebug::Print(_L("Value=%d\r\n"), stmt.ColumnInt(0));
1.227 + err = stmt.Next();
1.228 + TEST2(err, KSqlAtRow);
1.229 + RDebug::Print(_L("Value=%d\r\n"), stmt.ColumnInt(0));
1.230 + stmt.Close();
1.231 + //Attempt to read the system data
1.232 + err = stmt.Prepare(TheDb, _L("SELECT * FROM SQLITE_MASTER"));
1.233 + TEST2(err, KErrNone);
1.234 + err = stmt.Next();
1.235 + TEST2(err, KSqlAtRow);
1.236 + TPtrC p;
1.237 + err = stmt.ColumnText(0, p);
1.238 + TEST2(err, KErrNone);
1.239 + RDebug::Print(_L("Value=%S\r\n"), &p);
1.240 + stmt.Close();
1.241 +
1.242 + NonSecureDbTest();
1.243 +
1.244 + TheDb.Close();
1.245 + }
1.246 +
1.247 +/**
1.248 +@SYMTestCaseID SYSLIB-SQL-UT-4097
1.249 +@SYMTestCaseDesc Testing incremental blob reads and writes on a secure database.
1.250 + The test application's capabilities allow read and write access to the blobs.
1.251 + Verify that both reads and writes are allowed.
1.252 +@SYMTestPriority Medium
1.253 +@SYMTestActions Testing incremental blob reads and writes on a secure database.
1.254 +@SYMTestExpectedResults Test must not fail
1.255 +@SYMREQ REQ5794
1.256 +*/
1.257 +void ReadWriteBlobTestL()
1.258 + {
1.259 + // Current database data:
1.260 + // TABLE A: {1, x'41414141414141414141'}, {2, x'42424242424242424242'}, {3, x'43434343434343434343'}, {4, x'44444444444444444444'}
1.261 + // TABLE B: {4, "DEF", x'46464646464646464646'} <- ROWID = 2, {2, "ABC", x'45454545454545454545'} <- ROWID = 3, {6, "GHI", x'47474747474747474747'} <- ROWID = 4
1.262 +
1.263 + RSqlDatabase db;
1.264 + TInt err = TheDb.Open(KTestDbName);
1.265 + TEST2(err, KErrNone);
1.266 +
1.267 + // Attempt to read the blobs in tables A and B
1.268 + RSqlBlobReadStream rdStrm;
1.269 + CleanupClosePushL(rdStrm);
1.270 + TBuf8<20> data;
1.271 + TRAP(err, rdStrm.OpenL(TheDb, _L("A"), _L("B1"), 1));
1.272 + TEST2(err, KErrNone);
1.273 + TRAP(err, rdStrm.ReadL(data, 3));
1.274 + TEST2(err, KErrNone);
1.275 + TEST(data.Compare(_L8("AAA")) == 0);
1.276 + rdStrm.Close();
1.277 + TRAP(err, rdStrm.OpenL(TheDb, _L("B"), _L("B2"), 3));
1.278 + TEST2(err, KErrNone);
1.279 + TRAP(err, rdStrm.ReadL(data, 10));
1.280 + TEST2(err, KErrNone);
1.281 + TEST(data.Compare(_L8("EEEEEEEEEE")) == 0);
1.282 + CleanupStack::PopAndDestroy(&rdStrm);
1.283 +
1.284 + HBufC8* wholeBuf = TSqlBlob::GetLC(TheDb, _L("A"), _L("B1"), 4);
1.285 + TEST(wholeBuf->Des().Compare(_L8("DDDDDDDDDD")) == 0);
1.286 + CleanupStack::PopAndDestroy(wholeBuf);
1.287 + wholeBuf = TSqlBlob::GetLC(TheDb, _L("B"), _L("B2"), 2);
1.288 + TEST(wholeBuf->Des().Compare(_L8("FFFFFFFFFF")) == 0);
1.289 + CleanupStack::PopAndDestroy(wholeBuf);
1.290 +
1.291 + HBufC8* buf = HBufC8::NewLC(10);
1.292 + TPtr8 bufPtr(buf->Des());
1.293 + err = TSqlBlob::Get(TheDb, _L("A"), _L("B1"), bufPtr, 2);
1.294 + TEST2(err, KErrNone);
1.295 + TEST(bufPtr.Compare(_L8("BBBBBBBBBB")) == 0);
1.296 + err = TSqlBlob::Get(TheDb, _L("B"), _L("B2"), bufPtr, 4);
1.297 + TEST2(err, KErrNone);
1.298 + TEST(bufPtr.Compare(_L8("GGGGGGGGGG")) == 0);
1.299 + CleanupStack::PopAndDestroy(buf);
1.300 +
1.301 + // Attempt to write the blobs in tables A and B
1.302 + RSqlBlobWriteStream wrStrm;
1.303 + CleanupClosePushL(wrStrm);
1.304 + TRAP(err, wrStrm.OpenL(TheDb, _L("A"), _L("B1"), 1));
1.305 + TEST2(err, KErrNone);
1.306 + TRAP(err, wrStrm.WriteL(_L8("ZZZ")));
1.307 + TEST2(err, KErrNone);
1.308 + wrStrm.Close();
1.309 + TRAP(err, wrStrm.OpenL(TheDb, _L("B"), _L("B2"), 2));
1.310 + TEST2(err, KErrNone);
1.311 + TRAP(err, wrStrm.WriteL(_L8("XXXXXXXXX")));
1.312 + TEST2(err, KErrNone);
1.313 + CleanupStack::PopAndDestroy(&wrStrm);
1.314 +
1.315 + TRAP(err, TSqlBlob::SetL(TheDb, _L("A"), _L("B1"), _L8("UUUUUUUU"), 4));
1.316 + TEST2(err, KErrNone);
1.317 + TRAP(err, TSqlBlob::SetL(TheDb, _L("B"), _L("B2"), _L8("TT"), 2));
1.318 + TEST2(err, KErrNone);
1.319 +
1.320 + // SQLite and system tables
1.321 +
1.322 + // Attempt to read from and write to the SQLite master table -
1.323 + // reads should be permitted because read capability is enough for this,
1.324 + // writes should not be permitted because schema capability is required for this
1.325 + CleanupClosePushL(rdStrm);
1.326 + TRAP(err, rdStrm.OpenL(TheDb, _L("sqlite_master"), _L("tbl_name"), 1)); // TEXT column
1.327 + TEST2(err, KErrNone);
1.328 + TRAP(err, rdStrm.ReadL(data, 1));
1.329 + TEST2(err, KErrNone);
1.330 + CleanupStack::PopAndDestroy(&rdStrm);
1.331 +
1.332 + wholeBuf = TSqlBlob::GetLC(TheDb, _L("sqlite_master"), _L("tbl_name"), 1);
1.333 + TEST(wholeBuf->Length() > 0);
1.334 + CleanupStack::PopAndDestroy(wholeBuf);
1.335 +
1.336 + buf = HBufC8::NewLC(100);
1.337 + bufPtr.Set(buf->Des());
1.338 + err = TSqlBlob::Get(TheDb, _L("sqlite_master"), _L("tbl_name"), bufPtr, 1);
1.339 + TEST2(err, KErrNone);
1.340 + TEST(bufPtr.Length() > 0);
1.341 + CleanupStack::PopAndDestroy(buf);
1.342 +
1.343 + CleanupClosePushL(wrStrm);
1.344 + TRAP(err, wrStrm.OpenL(TheDb, _L("sqlite_master"), _L("tbl_name"), 1));
1.345 + TEST2(err, KErrPermissionDenied);
1.346 + CleanupStack::PopAndDestroy(&wrStrm);
1.347 +
1.348 + TRAP(err, TSqlBlob::SetL(TheDb, _L("sqlite_master"), _L("tbl_name"), _L8("VVVV"), 1));
1.349 + TEST2(err, KErrPermissionDenied);
1.350 +
1.351 + // Attempt to read from and write to the system tables - neither reads nor writes should be permitted
1.352 + CleanupClosePushL(rdStrm);
1.353 + TRAP(err, rdStrm.OpenL(TheDb, _L("symbian_security"), _L("PolicyData"), 1)); // BLOB column
1.354 + TEST2(err, KErrPermissionDenied);
1.355 + CleanupStack::PopAndDestroy(&rdStrm);
1.356 +
1.357 + TRAP(err, wholeBuf = TSqlBlob::GetLC(TheDb, _L("symbian_security"), _L("PolicyData"), 1));
1.358 + TEST2(err, KErrPermissionDenied);
1.359 +
1.360 + buf = HBufC8::NewLC(100);
1.361 + bufPtr.Set(buf->Des());
1.362 + err = TSqlBlob::Get(TheDb, _L("symbian_security"), _L("PolicyData"), bufPtr, 1);
1.363 + TEST2(err, KErrPermissionDenied);
1.364 + CleanupStack::PopAndDestroy(buf);
1.365 +
1.366 + CleanupClosePushL(wrStrm);
1.367 + TRAP(err, wrStrm.OpenL(TheDb, _L("symbian_security"), _L("PolicyData"), 1));
1.368 + TEST2(err, KErrPermissionDenied);
1.369 + CleanupStack::PopAndDestroy(&wrStrm);
1.370 +
1.371 + TRAP(err, TSqlBlob::SetL(TheDb, _L("symbian_security"), _L("PolicyData"), _L8("VVVV"), 1));
1.372 + TEST2(err, KErrPermissionDenied);
1.373 +
1.374 + TheDb.Close();
1.375 + }
1.376 +
1.377 +void DoTestsL()
1.378 + {
1.379 + TheTest.Start(_L(" @SYMTestCaseID:SYSLIB-SQL-CT-1646 Read-write database access test "));
1.380 + ReadWriteDatabaseTest();
1.381 +
1.382 + TheTest.Next(_L(" @SYMTestCaseID:SYSLIB-SQL-UT-4097 Read-write blob access test"));
1.383 + ReadWriteBlobTestL();
1.384 +
1.385 + RestoreOriginalDb(); // the same db is used by the other t_security test exe's
1.386 + }
1.387 +
1.388 +TInt E32Main()
1.389 + {
1.390 + TheTest.Title();
1.391 +
1.392 + CTrapCleanup* tc = CTrapCleanup::New();
1.393 +
1.394 + __UHEAP_MARK;
1.395 +
1.396 + TRAPD(err, DoTestsL());
1.397 + TEST2(err, KErrNone);
1.398 +
1.399 + __UHEAP_MARKEND;
1.400 +
1.401 + TheTest.End();
1.402 + TheTest.Close();
1.403 +
1.404 + delete tc;
1.405 +
1.406 + User::Heap().Check();
1.407 + return KErrNone;
1.408 + }