/**
@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);
}
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);
}
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);
}
/*!
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;
}
}
