Symaptic: os/persistentdata/persistentstorage/sql/TEST/t

     1 // Copyright (c) 2006-2009 Nokia Corporation and/or its subsidiary(-ies).

     2 // All rights reserved.

     3 // This component and the accompanying materials are made available

     4 // under the terms of "Eclipse Public License v1.0"

     5 // which accompanies this distribution, and is available

     6 // at the URL "http://www.eclipse.org/legal/epl-v10.html".

     7 //

     8 // Initial Contributors:

     9 // Nokia Corporation - initial contribution.

    10 //

    11 // Contributors:

    12 //

    13 // Description:

    14 // t_sqlsecurity3 application has capabilities allowing write-only access to the test database

    15 //

    16 //

    18 #include <e32test.h>

    19 #include <bautils.h>

    20 #include <sqldb.h>

    22 ///////////////////////////////////////////////////////////////////////////////////////

    23 //The test database has:

    24 //  SCHEMA database policy: ECapabilityReadDeviceData, ECapabilityWriteUserData, ECapabilityReadUserData

    25 //  WRITE database policy:  ECapabilityWriteUserData

    26 //  READ database policy:   ECapabilityReadUserData

    27 //

    28 //Database tables:

    29 //  TABLE A(F1 INTEGER, B1 BLOB)

    30 //  TABLE B(F2 INTEGER, F3 TEXT, B2 BLOB)

    31 //

    32 //Database data:

    33 //  TABLE A: {1, x'41414141414141414141'}, {2, x'42424242424242424242'}, {3, x'43434343434343434343'}, {4, x'44444444444444444444'}

    34 //  TABLE B: {2, "ABC", x'45454545454545454545'}, {4, "DEF", x'46464646464646464646'}

    36 ///////////////////////////////////////////////////////////////////////////////////////

    38 #define UNUSED_VAR(a) (a) = (a)

    40 RSqlDatabase TheDb;

    41 RTest TheTest(_L("t_sqlsecurity3 test"));

    43 _LIT(KTestDbName, "c:[21212125]t_ab.db");

    45 ///////////////////////////////////////////////////////////////////////////////////////

    46 //Restore original test database function

    47 void RestoreOriginalDb()

    48 	{

    49 	TheDb.Close();

    50 	TheDb.Open(KTestDbName);

    52 	// Delete and restore the content of table A (unconditional DELETE, no READ operations)

    53 	TheDb.Exec(_L("DELETE FROM A"));

    54 	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');"));

    56 	// Delete and restore the content of table B (unconditional DELETE, no READ operations)

    57 	TheDb.Exec(_L("DELETE FROM B"));

    58 	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');"));

    60 	TheDb.Close();

    61 	}

    63 ///////////////////////////////////////////////////////////////////////////////////////

    64 //Test macros and functions

    65 void Check1(TInt aValue, TInt aLine)

    66 	{

    67 	if(!aValue)

    68 		{

    69 		RestoreOriginalDb();

    70 		RDebug::Print(_L("*** Line %d\r\n"), aLine);

    71 		TheTest(EFalse, aLine);

    72 		}

    73 	}

    74 void Check2(TInt aValue, TInt aExpected, TInt aLine)

    75 	{

    76 	if(aValue != aExpected)

    77 		{

    78 		RestoreOriginalDb();

    79 		RDebug::Print(_L("*** Line %d, Expected error: %d, got: %d\r\n"), aLine, aExpected, aValue);

    80 		TheTest(EFalse, aLine);

    81 		}

    82 	}

    83 #define TEST(arg) ::Check1((arg), __LINE__)

    84 #define TEST2(aValue, aExpected) ::Check2(aValue, aExpected, __LINE__)

    86 ///////////////////////////////////////////////////////////////////////////////////////

    88 /**

    89 @SYMTestCaseID			SYSLIB-SQL-CT-1645

    90 @SYMTestCaseDesc		Testing database operations on a secure database.

    91 						The test application's capabilities allow write-only access to the test secure database.

    92 						Verify that any other kind of a database operation will fail with KErrPermissionDenied error.

    93 @SYMTestPriority		High

    94 @SYMTestActions			Testing database operations on a secure database.

    95 @SYMTestExpectedResults Test must not fail

    96 @SYMREQ					REQ5792

    97                         REQ5793

    98 */

    99 void WriteOnlyDatabaseTest()

   100 	{

   101 	TInt err = TheDb.Open(KTestDbName);

   102 	TEST2(err, KErrNone);

   104 	//Attempt to modify the database schema

   105 	err = TheDb.Exec(_L("CREATE TABLE C(FFF TEXT)"));

   106 	TEST2(err, KErrPermissionDenied);

   107     err = TheDb.Exec(_L("CREATE TRIGGER upd_a_b1 UPDATE OF B1 ON A BEGIN UPDATE B SET F3 = 'AAAA' WHERE F2 = A.F1; END;"));

   108     TEST2(err, KErrPermissionDenied);

   109     err = TheDb.Exec(_L("CREATE TEMP TRIGGER upd_a_b1 UPDATE OF B1 ON A BEGIN UPDATE B SET F3 = 'AAAA' WHERE F2 = A.F1; END;"));

   110     TEST2(err, KErrPermissionDenied);//Temp trigger which attempts to update one of the tables.

   111     err = TheDb.Exec(_L("CREATE VIEW V1 AS SELECT * FROM A"));

   112     TEST2(err, KErrPermissionDenied);

   113     err = TheDb.Exec(_L("CREATE TEMP VIEW V1 AS SELECT * FROM A"));

   114     TEST(err >= 0);

   115     err = TheDb.Exec(_L("DROP VIEW V1"));

   116     TEST(err >= 0);

   117 	//Attempt to update the user data (but it includes a READ operation)

   118 	err = TheDb.Exec(_L("UPDATE A SET F1 = 11 WHERE F1 = 1"));

   119 	TEST2(err, KErrPermissionDenied);

   120 	//Attempt to update the user data (unconditional UPDATE, no READ operations)

   121 	err = TheDb.Exec(_L("UPDATE A SET F1 = 11"));

   122 	TEST(err >= 0);

   123 	//Attempt to delete the user data (but it includes a READ operation)

   124 	err = TheDb.Exec(_L("DELETE FROM B WHERE F2 = 2"));

   125 	TEST2(err, KErrPermissionDenied);

   126 	//Attempt to delete the user data (unconditional DELETE, no READ operations)

   127 	err = TheDb.Exec(_L("DELETE FROM A"));

   128 	TEST(err >= 0);

   129 	//Restore the deleted table A

   130 	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');"));

   131 	TEST(err >= 0);

   132 	//Attempt to insert new user data

   133 	err = TheDb.Exec(_L("INSERT INTO B(F2, F3, B2) VALUES(22, 'AAA', x'47474747474747474747')"));

   134 	TEST2(err, 1);

   135 	//Attempt to change the isolation level.

   136 	err = TheDb.SetIsolationLevel(RSqlDatabase::ESerializable);

   137 	TEST2(err, KErrNone);

   138 	err = TheDb.SetIsolationLevel(RSqlDatabase::EReadUncommitted);

   139 	TEST2(err, KErrNone);

   140 	//Attempt to read the user data

   141 	RSqlStatement stmt;

   142 	err = stmt.Prepare(TheDb, _L("SELECT A.F1 FROM B,A WHERE A.F1 = B.F2"));

   143 	TEST2(err, KErrPermissionDenied);

   144 	//Attempt to read the system data

   145 	err = stmt.Prepare(TheDb, _L("SELECT * FROM SQLITE_MASTER"));

   146 	TEST2(err, KErrNone);

   147 	err = stmt.Next();

   148 	TEST2(err, KSqlAtRow);

   149 	TPtrC p;

   150 	err = stmt.ColumnText(0, p);

   151 	TEST2(err, KErrNone);

   152 	RDebug::Print(_L("Value=%S\r\n"), &p);

   153 	stmt.Close();

   155 	TheDb.Close();

   156 	}

   158 /**

   159 @SYMTestCaseID			SYSLIB-SQL-UT-4096

   160 @SYMTestCaseDesc		Testing incremental blob writes on a secure database.

   161 						The test application's capabilities allow write-only access to the blobs.

   162 						Verify that any attempt to read a blob will fail with KErrPermissionDenied.

   163 @SYMTestPriority		High

   164 @SYMTestActions			Testing incremental blob writes on a secure database.

   165 @SYMTestExpectedResults Test must not fail

   166 @SYMREQ					REQ5794

   167 */

   168 void WriteOnlyBlobTestL()

   169 	{

   170 	TInt err = TheDb.Open(KTestDbName);

   171 	TEST2(err, KErrNone);

   173 	// Attempt to write the blobs in tables A and B

   174 	RSqlBlobWriteStream wrStrm;

   175 	CleanupClosePushL(wrStrm);

   176 	TRAP(err, wrStrm.OpenL(TheDb, _L("A"), _L("B1"), 2));

   177 	TEST2(err, KErrNone);

   178 	TRAP(err, wrStrm.WriteL(_L8("YYYYYYY")));

   179 	TEST2(err, KErrNone);

   180 	wrStrm.Close();

   181 	TRAP(err, wrStrm.OpenL(TheDb, _L("B"), _L("B2"), 1));

   182 	TEST2(err, KErrNone);

   183 	TRAP(err, wrStrm.WriteL(_L8("XXXXXXXXX")));

   184 	TEST2(err, KErrNone);

   185 	CleanupStack::PopAndDestroy(&wrStrm);

   187 	TRAP(err, TSqlBlob::SetL(TheDb, _L("A"), _L("B1"), _L8("UUUUUUUU"), 4));

   188 	TEST2(err, KErrNone);

   189 	TRAP(err, TSqlBlob::SetL(TheDb, _L("B"), _L("B2"), _L8("SSS"), 2));

   190 	TEST2(err, KErrNone);

   192 	// Attempt to read from the blobs in tables A and B

   193 	RSqlBlobReadStream rdStrm;

   194 	CleanupClosePushL(rdStrm);

   195 	TRAP(err, rdStrm.OpenL(TheDb, _L("A"), _L("B1"), 1));

   196 	TEST2(err, KErrPermissionDenied);

   197 	rdStrm.Close();

   198 	TRAP(err, rdStrm.OpenL(TheDb, _L("B"), _L("B2"), 1));

   199 	TEST2(err, KErrPermissionDenied);

   200 	CleanupStack::PopAndDestroy(&rdStrm);

   202 	HBufC8* wholeBuf = NULL;

   203 	TRAP(err, wholeBuf = TSqlBlob::GetLC(TheDb, _L("A"), _L("B1"), 1));

   204 	TEST2(err, KErrPermissionDenied);

   205 	TRAP(err, wholeBuf = TSqlBlob::GetLC(TheDb, _L("B"), _L("B2"), 1));

   206 	TEST2(err, KErrPermissionDenied);

   208 	HBufC8* buf = HBufC8::NewLC(10);

   209 	TPtr8 bufPtr(buf->Des());

   210 	err = TSqlBlob::Get(TheDb, _L("A"), _L("B1"), bufPtr, 2);

   211 	TEST2(err, KErrPermissionDenied);

   212 	err = TSqlBlob::Get(TheDb, _L("B"), _L("B2"), bufPtr, 1);

   213 	TEST2(err, KErrPermissionDenied);

   214 	CleanupStack::PopAndDestroy(buf);

   216 	// SQLite and system tables

   218 	// Attempt to read from and write to the SQLite master table -

   219 	// reads should be permitted because write capability is enough for this,

   220 	// writes should not be permitted because schema capability is required for this

   221 	CleanupClosePushL(rdStrm);

   222 	TRAP(err, rdStrm.OpenL(TheDb, _L("sqlite_master"), _L("tbl_name"), 1)); // TEXT column

   223 	TEST2(err, KErrNone);

   224 	TBuf8<20> data;

   225 	TRAP(err, rdStrm.ReadL(data, 1));

   226 	TEST2(err, KErrNone);

   227 	CleanupStack::PopAndDestroy(&rdStrm);

   229 	wholeBuf = TSqlBlob::GetLC(TheDb, _L("sqlite_master"), _L("tbl_name"), 1);

   230 	TEST(wholeBuf->Length() > 0);

   231 	CleanupStack::PopAndDestroy(wholeBuf);

   233 	buf = HBufC8::NewLC(100);

   234 	bufPtr.Set(buf->Des());

   235 	err = TSqlBlob::Get(TheDb, _L("sqlite_master"), _L("tbl_name"), bufPtr, 1);

   236 	TEST2(err, KErrNone);

   237 	TEST(bufPtr.Length() > 0);

   238 	CleanupStack::PopAndDestroy(buf);

   240 	CleanupClosePushL(wrStrm);

   241 	TRAP(err, wrStrm.OpenL(TheDb, _L("sqlite_master"), _L("tbl_name"), 1));

   242 	TEST2(err, KErrPermissionDenied);

   243 	CleanupStack::PopAndDestroy(&wrStrm);

   245 	TRAP(err, TSqlBlob::SetL(TheDb, _L("sqlite_master"), _L("tbl_name"), _L8("VVVV"), 1));

   246 	TEST2(err, KErrPermissionDenied);

   248 	// Attempt to read from and write to the system tables - neither reads nor writes should be permitted

   249 	CleanupClosePushL(rdStrm);

   250 	TRAP(err, rdStrm.OpenL(TheDb, _L("symbian_security"), _L("PolicyData"), 1)); // BLOB column

   251 	TEST2(err, KErrPermissionDenied);

   252 	CleanupStack::PopAndDestroy(&rdStrm);

   254 	TRAP(err, wholeBuf = TSqlBlob::GetLC(TheDb, _L("symbian_security"), _L("PolicyData"), 1));

   255 	TEST2(err, KErrPermissionDenied);

   257 	buf = HBufC8::NewLC(100);

   258 	bufPtr.Set(buf->Des());

   259 	err = TSqlBlob::Get(TheDb, _L("symbian_security"), _L("PolicyData"), bufPtr, 1);

   260 	TEST2(err, KErrPermissionDenied);

   261 	CleanupStack::PopAndDestroy(buf);

   263 	CleanupClosePushL(wrStrm);

   264 	TRAP(err, wrStrm.OpenL(TheDb, _L("symbian_security"), _L("PolicyData"), 1));

   265 	TEST2(err, KErrPermissionDenied);

   266 	CleanupStack::PopAndDestroy(&wrStrm);

   268 	TRAP(err, TSqlBlob::SetL(TheDb, _L("symbian_security"), _L("PolicyData"), _L8("VVVV"), 1));

   269 	TEST2(err, KErrPermissionDenied);

   271 	TheDb.Close();

   272 	}

   274 void DoTestsL()

   275 	{

   276 	TheTest.Start(_L(" @SYMTestCaseID:SYSLIB-SQL-CT-1645 Write-only database access test "));

   277 	WriteOnlyDatabaseTest();

   279 	TheTest.Next(_L(" @SYMTestCaseID:SYSLIB-SQL-UT-4096 Write-only blob access test"));

   280 	WriteOnlyBlobTestL();

   282 	RestoreOriginalDb(); // the same db is used by the other t_security test exe's

   283 	}

   285 TInt E32Main()

   286 	{

   287 	TheTest.Title();

   289 	CTrapCleanup* tc = CTrapCleanup::New();

   291 	__UHEAP_MARK;

   293 	TRAPD(err, DoTestsL());

   294 	TEST2(err, KErrNone);

   296 	__UHEAP_MARKEND;

   298 	TheTest.End();

   299 	TheTest.Close();

   301 	delete tc;

   303 	User::Heap().Check();

   304 	return KErrNone;

   305 	}

author	sl
	Tue, 10 Jun 2014 14:32:02 +0200
changeset 1	260cb5ec6c19
permissions	-rw-r--r--