/**
@SYMTestCaseID SYSLIB-SQL-UT-3508
@SYMTestCaseDesc Test for DEF109100: SQL, code coverage for TSqlDbSysSettings is low.
The test attempts to create a secure shared database with a security policy
specified for the system tables, which is not allowed.
@SYMTestPriority High
@SYMTestActions Test for DEF109100: SQL, code coverage for TSqlDbSysSettings is low.
@SYMTestExpectedResults Test must not fail
@SYMDEF DEF109100
*/
void StoreSysTableSecurityPolicyTest()
{
_LIT(KSecurityTable, "symbian_security");
_LIT(KSettingsTable, "symbian_settings");
//Case 1: "symbian_security" table
RSqlSecurityPolicy sp;
TInt err = sp.Create(KDefaultPolicy);
TEST2(err, KErrNone);
err = sp.SetDbPolicy(RSqlSecurityPolicy::ESchemaPolicy, KPolicy1);
TEST2(err, KErrNone);
err = sp.SetPolicy(RSqlSecurityPolicy::ETable, KSecurityTable, RSqlSecurityPolicy::EReadPolicy, KPolicy1);
TEST2(err, KErrNone);
(void)RSqlDatabase::Delete(KTestDbName);
RSqlDatabase db;
err = db.Create(KTestDbName, sp);
TEST2(err, KErrArgument);
sp.Close();
//Case 2: "symbian_settings" table
err = sp.Create(KDefaultPolicy);
TEST2(err, KErrNone);
err = sp.SetDbPolicy(RSqlSecurityPolicy::ESchemaPolicy, KPolicy1);
TEST2(err, KErrNone);
err = sp.SetPolicy(RSqlSecurityPolicy::ETable, KSettingsTable, RSqlSecurityPolicy::EReadPolicy, KPolicy1);
TEST2(err, KErrNone);
err = RSqlDatabase::Delete(KTestDbName);
TEST2(err, KErrNotFound);
err = db.Create(KTestDbName, sp);
TEST2(err, KErrArgument);
sp.Close();
err = RSqlDatabase::Delete(KTestDbName);
TEST2(err, KErrNotFound);
}
/**
@SYMTestCaseID SYSLIB-SQL-CT-1635
@SYMTestCaseDesc RSqlSecurityPolicy test.
Tests RSqlSecurityPolicy API.
@SYMTestPriority High
@SYMTestActions RSqlSecurityPolicy test.
@SYMTestExpectedResults Test must not fail
@SYMREQ REQ5792
REQ5793
*/
void SqlSecurityPolicyTest()
{
RSqlSecurityPolicy securityPolicy;
TInt err = securityPolicy.Create(KDefaultPolicy);
TEST2(err, KErrNone);
//Empty RSqlSecurityPolicy object tests
//Database policies
TSecurityPolicy policy = securityPolicy.DbPolicy(RSqlSecurityPolicy::ESchemaPolicy);
TEST(policy == KDefaultPolicy);
policy = securityPolicy.DbPolicy(RSqlSecurityPolicy::EReadPolicy);
TEST(policy == KDefaultPolicy);
policy = securityPolicy.DbPolicy(RSqlSecurityPolicy::EWritePolicy);
TEST(policy == KDefaultPolicy);
//Table policies
policy = securityPolicy.Policy(RSqlSecurityPolicy::ETable, _L("A-Tbl"), RSqlSecurityPolicy::EReadPolicy);
TEST(policy == KDefaultPolicy);
policy = securityPolicy.Policy(RSqlSecurityPolicy::ETable, _L("B-Tbl"), RSqlSecurityPolicy::EWritePolicy);
TEST(policy == KDefaultPolicy);
//
securityPolicy.Close();
//Tests with initialized RSqlSecurityPolicy object
CreateTestSecurityPolicy(securityPolicy);
//Check the created and initialized object
CheckTestSecurityPolicy(securityPolicy);
//Externalize, then internalize and check the security policy object
RFileWriteStream out;
err = out.Replace(TheFs, KTestFile, EFileRead | EFileWrite);
TEST2(err, KErrNone);
TRAP(err, securityPolicy.ExternalizeL(out));
TEST2(err, KErrNone);
TRAP(err, out.CommitL());
out.Close();
TEST2(err, KErrNone);
securityPolicy.Close();
err = securityPolicy.Create(KDefaultPolicy);
TEST2(err, KErrNone);
RFileReadStream in;
err = in.Open(TheFs, KTestFile, EFileRead);
TEST2(err, KErrNone);
TRAP(err, securityPolicy.InternalizeL(in));
in.Close();
TEST2(err, KErrNone);
CheckTestSecurityPolicy(securityPolicy);
//Change some security policies and check that the RqlSecurityPolicy content was properly updated
err = securityPolicy.SetPolicy(RSqlSecurityPolicy::ETable, KTableName11, RSqlSecurityPolicy::EReadPolicy, KPolicy3);
TEST2(err, KErrNone);
policy = securityPolicy.Policy(RSqlSecurityPolicy::ETable, KTableName1, RSqlSecurityPolicy::EReadPolicy);
TEST(policy == KPolicy3);
policy = securityPolicy.Policy(RSqlSecurityPolicy::ETable, KTableName11, RSqlSecurityPolicy::EWritePolicy);
TEST(policy == KPolicy2);
//
securityPolicy.Close();
}
void CreateTestSecurityPolicy(RSqlSecurityPolicy& aSecurityPolicy)
{
TInt err = aSecurityPolicy.Create(KDefaultPolicy);
TEST2(err, KErrNone);
err = aSecurityPolicy.SetDbPolicy(RSqlSecurityPolicy::ESchemaPolicy, KPolicy1);
TEST2(err, KErrNone);
err = aSecurityPolicy.SetDbPolicy(RSqlSecurityPolicy::EWritePolicy, KPolicy3);
TEST2(err, KErrNone);
err = aSecurityPolicy.SetPolicy(RSqlSecurityPolicy::ETable, KTableName1, RSqlSecurityPolicy::EReadPolicy, KPolicy1);
TEST2(err, KErrNone);
err = aSecurityPolicy.SetPolicy(RSqlSecurityPolicy::ETable, KTableName1, RSqlSecurityPolicy::EWritePolicy, KPolicy2);
TEST2(err, KErrNone);
err = aSecurityPolicy.SetPolicy(RSqlSecurityPolicy::ETable, KTableName2, RSqlSecurityPolicy::EReadPolicy, KPolicy1);
TEST2(err, KErrNone);
err = aSecurityPolicy.SetPolicy(RSqlSecurityPolicy::ETable, KTableName3, RSqlSecurityPolicy::EReadPolicy, KPolicy2);
TEST2(err, KErrNone);
err = aSecurityPolicy.SetPolicy(RSqlSecurityPolicy::ETable, KTableName2, RSqlSecurityPolicy::EWritePolicy, KPolicy1);
TEST2(err, KErrNone);
err = aSecurityPolicy.SetPolicy(RSqlSecurityPolicy::ETable, KTableName3, RSqlSecurityPolicy::EWritePolicy, KPolicy3);
TEST2(err, KErrNone);
}
RSqlSecurityPolicy CreateTestSecurityPolicy()
{
const TSecurityPolicy KDefaultPolicy(TSecurityPolicy::EAlwaysPass);
RSqlSecurityPolicy securityPolicy;
TInt err = securityPolicy.Create(KDefaultPolicy);
TEST2(err, KErrNone);
return securityPolicy;
}
void CreateDatabaseL(const TDesC& aDbName)
{
// create the database now
RSqlSecurityPolicy securityPolicy;
CleanupClosePushL(securityPolicy);
TSecurityPolicy policy(TSecurityPolicy::EAlwaysPass);
securityPolicy.Create(policy);
TSecurityPolicy schemaPolicy(TSecurityPolicy::EAlwaysPass);
TSecurityPolicy readPolicy(TSecurityPolicy::EAlwaysPass);
TSecurityPolicy writePolicy(TSecurityPolicy::EAlwaysPass);
User::LeaveIfError(securityPolicy.SetDbPolicy(RSqlSecurityPolicy::ESchemaPolicy, schemaPolicy));
User::LeaveIfError(securityPolicy.SetDbPolicy(RSqlSecurityPolicy::EReadPolicy, readPolicy));
User::LeaveIfError(securityPolicy.SetDbPolicy(RSqlSecurityPolicy::EWritePolicy, writePolicy));
TheTest.Printf(_L("Creating Database %S\n"), &aDbName);
TInt err = TheDbC.Create(aDbName, securityPolicy, &TheSqlConfigString);
if (KErrAlreadyExists == err)
{
// the file already exists
// make sure we delete the file
User::LeaveIfError(TheDbC.Delete(aDbName));
// try again
err = TheDbC.Create(aDbName, securityPolicy, &TheSqlConfigString);
}
User::LeaveIfError(err);
//Create tables
User::LeaveIfError(TheDbC.Exec(KMusicCreateTable));
User::LeaveIfError(TheDbC.Exec(KAuxiliaryCreateTable));
User::LeaveIfError(TheDbC.Exec(KAlbumCreateTable));
User::LeaveIfError(TheDbC.Exec(KArtistCreateTable));
User::LeaveIfError(TheDbC.Exec(KComposerCreateTable));
User::LeaveIfError(TheDbC.Exec(KGenreCreateTable));
User::LeaveIfError(TheDbC.Exec(KPlaylistCreateTable));
User::LeaveIfError(TheDbC.Exec(KPlaylistSongInfoCreateTable));
User::LeaveIfError(TheDbC.Exec(KPlaylistSongsCreateTable));
TheDbC.Close();
CleanupStack::PopAndDestroy(&securityPolicy);
}
/**
@SYMTestCaseID SYSLIB-SQL-UT-3440
@SYMTestCaseDesc Test for DEF105928 "SQL server ignores config string parameters".
The test creates a secure database with a configuration string. The test must not fail.
@SYMTestPriority High
@SYMTestActions Test for DEF105928 "SQL server ignores config string parameters".
@SYMTestExpectedResults The test must not fail
@SYMDEF DEF105928
*/
void CfgSecureDbTest()
{
//Create
TSecurityPolicy policy(TSecurityPolicy::EAlwaysPass);
RSqlSecurityPolicy dbPolicy;
TInt err = dbPolicy.Create(policy);
TEST2(err, KErrNone);
_LIT8(KCfgStr1, "cache_size = 80; page_size = 4096; encoding = UTF-8");
err = TheDb.Create(KTestDbName3, dbPolicy, &KCfgStr1);
TEST2(err, KErrNone);
//Since it is a secure database, PRAGMAs cannot be executed (in order to assert the parameter values)
//AssertConfigPrmValues(TheDb, 80, 4096, TSqlSrvConfig::EEncUtf8);
TheDb.Close();
dbPolicy.Close();
//Open
_LIT8(KCfgStr2, "cache_size = 100");
err = TheDb.Open(KTestDbName3, &KCfgStr2);
TEST2(err, KErrNone);
TheDb.Close();
(void)RSqlDatabase::Delete(KTestDbName3);
}
/*!
Opens the database connection using the given connection options. Returns true on success; otherwise returns false.
Error information can be retrieved using the lastError() function. Symbian SQL dbs have no \a user, \a password, \a host
or \a port just file names.
\a connOpts Connection options hold definition for security policies and all parameters that does not contain "POLICY_" will be
passed to RSqlDatabase. Policy will be filled according to parsed values.
Value in database wide parameters starts by definition which can be vendorId or secureId. These come directly from TSecurityPolicy class in Symbian.
POLICY_DB_DEFAULT
Default security policy which will be used for the database and all database objects. POLICY_DB_DEFAULT must be
defined before any other policy definitions can be used.
POLICY_DB_READ
Read database security policy. An application with read database security policy can read from database.
POLICY_DB_WRITE:
Write database security policy. An application with write database security policy can write to database.
POLICY_DB_SCHEMA:
Schema database security policy. An application with schema database security policy can modify
the database schema, write to database, read from database.
Format:
POLICY_DB_DEFAULT=cap1,cap2,cap3,cap4,cap5,cap6,cap7 (Up to 7 capabilities)
POLICY_DB_READ=cap1,cap2,cap3,cap4,cap5,cap6,cap7 (Up to 7 capabilities)
POLICY_DB_WRITE=vendorid,cap1,cap2,cap3 (Vendor ID and up to 3 capabilities)
POLICY_DB_SCHEMA=secureid,cap1,cap2,cap3 (Secure ID and up to 3 capabilities)
Table policies does not support schema policy as database level does.
Table specific parameters would be as:
POLICY_TABLE_WRITE=tablename,cap1,cap2,cap3,cap4,cap5,cap6,cap7
POLICY_TABLE_READ=tablename,cap1,cap2,cap3,cap4,cap5,cap6,cap7
Vendor Id and Secure id format:
vid[0x12345678] (Hex)
sid[0x12345678] (Hex)
Example:
\code
QSqlDatabase database = QSqlDatabase::addDatabase("QSYMSQL", "MyConnection");
database.setConnectOptions("POLICY_DB_DEFAULT=ReadDeviceData");
database.setDatabaseName("[12345678]myDatabase");
bool ok = database.open();
\encode
\code
QSqlDatabase database = QSqlDatabase::addDatabase("QSYMSQL", "MyConnection");
database.setConnectOptions("POLICY_DB_DEFAULT=None; POLICY_DB_WRITE=sid[0x12345678], WriteDeviceData");
database.setDatabaseName("[12345678]myDatabase");
bool ok = database.open();
\encode
FOREIGN KEY:
Enabling foreign key support from underlying SQLite
add: "foreign_keys = ON" to your connection options string. This will be passes to SQLite.
Foreign key Example:
\code
QSqlDatabase database = QSqlDatabase::addDatabase("QSYMSQL", "MyConnection");
database.setDatabaseName("[12345678]myDatabase");
database.setConnectOptions("foreign_keys = ON");
bool ok = database.open();
\encode
More information about Symbian Security Policy can be found from Symbian documentation.
*/
bool QSymSQLDriver::open(const QString & db, const QString &, const QString &, const QString &, int, const QString &conOpts)
{
if (isOpen())
close();
if (db.isEmpty())
return false;
//Separating our parameters from Symbian ones and construct new connection options
const QString itemSeparator(QLatin1String(";"));
QRegExp isOurOption(QLatin1String("POLICY_*"), Qt::CaseInsensitive, QRegExp::Wildcard);
QStringList optionList = conOpts.split(itemSeparator, QString::SkipEmptyParts);
QStringList symbianList;
for (int i = optionList.count() - 1; i >= 0; i--) {
if (!optionList[i].contains(isOurOption)) {
symbianList.append(optionList[i]);
optionList.removeAt(i);
} else {
//Removing whitespace
QString formatted = optionList[i];
formatted = formatted.remove(QLatin1Char(' '));
formatted = formatted.remove(QLatin1Char('\t'));
formatted = formatted.remove(QLatin1Char('\n'));
formatted = formatted.remove(QLatin1Char('\r'));
optionList[i] = formatted;
}
}
QString symbianOpt;
for (int i = 0; i < symbianList.count(); i++) {
symbianOpt += symbianList[i];
symbianOpt += itemSeparator;
}
TPtrC dbName(qt_QString2TPtrC(db));
QByteArray conOpts8 = symbianOpt.toUtf8();
const TPtrC8 config(reinterpret_cast<const TUint8*>(conOpts8.constData()), (conOpts8.length()));
TInt res = d->access.Open(dbName, &config);
if (res == KErrNotFound) {
QRegExp findDefault(QLatin1String("POLICY_DB_DEFAULT=*"), Qt::CaseInsensitive, QRegExp::Wildcard);
QRegExp findRead(QLatin1String("POLICY_DB_READ=*"), Qt::CaseInsensitive, QRegExp::Wildcard);
QRegExp findWrite(QLatin1String("POLICY_DB_WRITE=*"), Qt::CaseInsensitive, QRegExp::Wildcard);
QRegExp findSchema(QLatin1String("POLICY_DB_SCHEMA=*"), Qt::CaseInsensitive, QRegExp::Wildcard);
QRegExp findTableRead(QLatin1String("POLICY_TABLE_READ=*"), Qt::CaseInsensitive, QRegExp::Wildcard);
QRegExp findTableWrite(QLatin1String("POLICY_TABLE_WRITE=*"), Qt::CaseInsensitive, QRegExp::Wildcard);
int policyIndex = optionList.indexOf(findDefault);
if (policyIndex != -1) {
QString defaultPolicyString = optionList[policyIndex];
optionList.removeAt(policyIndex);
TSecurityPolicy policyItem;
if (qExtractSecurityPolicyFromString(defaultPolicyString, policyItem)) {
RSqlSecurityPolicy policy;
res = policy.Create(policyItem);
if (res == KErrNone) {
for (int i = 0; i < optionList.count(); i++) {
QString option = optionList[i];
if (option.contains(findRead)) {
if (qExtractSecurityPolicyFromString(option, policyItem)) {
res = policy.SetDbPolicy(RSqlSecurityPolicy::EReadPolicy, policyItem);
} else {
res = KErrArgument;
}
} else if (option.contains(findWrite)) {
if (qExtractSecurityPolicyFromString(option, policyItem)) {
res = policy.SetDbPolicy(RSqlSecurityPolicy::EWritePolicy, policyItem);
} else {
res = KErrArgument;
}
} else if (option.contains(findSchema)) {
if (qExtractSecurityPolicyFromString(option, policyItem)) {
res = policy.SetDbPolicy(RSqlSecurityPolicy::ESchemaPolicy, policyItem);
} else {
res = KErrArgument;
}
} else if (option.contains(findTableWrite)) {
QString tableOption = option.mid(option.indexOf(QLatin1Char('=')) + 1);
int firstComma = tableOption.indexOf(QLatin1Char(','));
if (firstComma != -1) {
QString tableName = tableOption.left(firstComma);
tableOption = tableOption.mid(firstComma + 1);
if (qExtractSecurityPolicyFromString(tableOption, policyItem)) {
TPtrC symTableName(qt_QString2TPtrC(tableName));
res = policy.SetPolicy(RSqlSecurityPolicy::ETable, symTableName,
RSqlSecurityPolicy::EWritePolicy, policyItem);
} else {
res = KErrArgument;
}
} else {
res = KErrArgument;
}
} else if (option.contains(findTableRead)) {
QString tableOption = option.mid(option.indexOf(QLatin1Char('=')) + 1);
int firstComma = tableOption.indexOf(QLatin1Char(','));
if (firstComma != -1) {
QString tableName = tableOption.left(firstComma);
tableOption = tableOption.mid(firstComma + 1);
if (qExtractSecurityPolicyFromString(tableOption, policyItem)) {
TPtrC symTableName(qt_QString2TPtrC(tableName));
res = policy.SetPolicy(RSqlSecurityPolicy::ETable, symTableName,
RSqlSecurityPolicy::EReadPolicy, policyItem);
} else {
res = KErrArgument;
}
} else {
res = KErrArgument;
}
} else {
res = KErrArgument;
}
if (res != KErrNone) {
setLastError(gMakeErrorOpen(tr("Invalid option: ") + option, QSqlError::ConnectionError, res));
break;
}
}
if (res == KErrNone) {
res = d->access.Create(dbName, policy, &config);
policy.Close();
if (res != KErrNone)
setLastError(gMakeErrorOpen(tr("Error opening database"), QSqlError::ConnectionError, res));
}
}
} else {
res = KErrArgument;
setLastError(gMakeErrorOpen(tr("Invalid option: ") + defaultPolicyString, QSqlError::ConnectionError, res));
}
} else {
//Check whether there is some of our options, fail if so.
policyIndex = optionList.indexOf(isOurOption);
if (policyIndex == -1) {
res = d->access.Create(dbName, &config);
if (res != KErrNone)
setLastError(gMakeErrorOpen(tr("Error opening database"), QSqlError::ConnectionError, res));
} else {
res = KErrArgument;
setLastError(gMakeErrorOpen(tr("POLICY_DB_DEFAULT must be defined before any other POLICY definitions can be used"), QSqlError::ConnectionError, res));
}
}
}
if (res == KErrNone) {
setOpen(true);
setOpenError(false);
return true;
} else {
setOpenError(true);
return false;
}
}
void CMdSSqLiteConnection::OpenDbL( const TDesC& aDbFileName )
{
_LIT8( KMdsSqlDbaConfig, "cache_size=2000; page_size=2048; encoding=\"UTF-16\";");
_LIT8( KBlacklistSqlDbaConfig, "cache_size=1500; page_size=1024; encoding=\"UTF-16\";");
delete iDbFileName;
iDbFileName = NULL; // in case AllocL leaves
iDbFileName = aDbFileName.AllocL();
TBool setupForMdsServer( EFalse );
// Check if it is MDS server DB that is accessed, otherwise setup will be for Blacklist Server
if( iDbFileName->Des().FindF( KMdsSqlDbDefaultName ) != KErrNotFound )
{
setupForMdsServer = ETrue;
}
TInt err = KErrNone;
// we need to set up policy, because we use secure database
TSecurityPolicy defaultPolicy(TSecurityPolicy::EAlwaysPass);
RSqlSecurityPolicy sqlSecurityPolicy;
CleanupClosePushL( sqlSecurityPolicy );
err = sqlSecurityPolicy.Create( defaultPolicy );
if ( err != KErrNone )
{
_LIT( KMdsSecurityCheckFail, "Security check fail" );
TraceAndLeaveL( KMdsSecurityCheckFail, err );
}
/**
* Open database:
* First we try to open db. If this fails because db not creater yer, then
* try to create it. Otherwise we cannot open it and we leave
*/
if( setupForMdsServer )
{
err = iMdeSqlDb.Open( *iDbFileName, &KMdsSqlDbaConfig );
}
else
{
err = iMdeSqlDb.Open( *iDbFileName, &KBlacklistSqlDbaConfig );
}
if ( err != KErrNone )
{
__LOG1( ELogDb, "Cannot open database %d", err );
if( err == KErrNotFound )
{
__LOG1( ELogDb, "Cannot find database %d", err );
if( setupForMdsServer )
{
err = iMdeSqlDb.Create( *iDbFileName, sqlSecurityPolicy, &KMdsSqlDbaConfig );
}
else
{
err = iMdeSqlDb.Create( *iDbFileName, sqlSecurityPolicy, &KBlacklistSqlDbaConfig );
}
if( err != KErrNone )
{
__LOG1( ELogDb, "Unknown error while creating %d", err );
User::LeaveIfError( err );
}
}
else if( err == KErrCorrupt ||
err == KSqlErrCorrupt )
{
__LOGLB( ELogDb, "Warning: Database is corrupted, will delete and re-create it." );
if( setupForMdsServer )
{
err = DeleteAndReCreateDB( iDbFileName, sqlSecurityPolicy, &KMdsSqlDbaConfig );
}
else
{
err = DeleteAndReCreateDB( iDbFileName, sqlSecurityPolicy, &KBlacklistSqlDbaConfig );
}
if ( KErrNone == err )
{
if( setupForMdsServer )
{
err = iMdeSqlDb.Open( *iDbFileName, &KMdsSqlDbaConfig );
}
else
{
err = iMdeSqlDb.Open( *iDbFileName, &KBlacklistSqlDbaConfig );
}
if ( err != KErrNone )
{
__LOG1( ELogDb, "Cannot open database again after delete and re-create %d", err );
User::LeaveIfError( err );
}
}
}
else
{
__LOG1( ELogDb, "Unknown error while accessing database %d", err );
User::LeaveIfError( err );
}
}
CleanupStack::PopAndDestroy( &sqlSecurityPolicy );
}
