Join Now
Quality Rating:
  • Currently 0.0 / 5
(0.0 / 5 - 0 votes cast)
Expertise Level:
  • Currently 0.0 / 5
(0.0 / 5 - 0 votes cast)

This page was last modified 07:16, 2 May 2008.

CS000925 - Using SQL API for creating non-secure and secure databases

From Forum Nokia Wiki


ID CS000925 Creation date May 2, 2008
Platform S60 3rd Edition, FP2 Tested on devices Nokia 6220 Classic
Category Symbian C++ Subcategory Files/Data


Keywords (APIs, classes, methods, functions): RSqlDatabase, TSecurityPolicy, RSqlSecurityPolicy,RSqlDatabase::Create(), RSqlDatabase::Exec(), RSqlDatabase::Close(), RSqlSecurityPolicy::Create(), RSqlDatabase::Copy(),RSqlDatabase::Delete(), RSqlSecurityPolicy::SetDbPolicy(), RSqlSecurityPolicy::SetPolicy()

Overview

This code snippet shows how the SQL API's RSqlDatabase handle class allows the user to interact with the SQL server and create either a non-secure or secure database. Non-secure databases are databases that can be accessed and updated by any program. Secure databases have a defined static policy and can be accessed only by authorized clients with specific capabilities. To create a secure database, the UID of the application that creates the database and security policy are needed.

Another possibility to create a new database is to make a copy of an existing database with the method RSqlDatabase::Copy(). Databases can be deleted using the method RSqlDatabase::Delete().

This snippet can be self-signed.

MMP file

The following libraries are required:

LIBRARY  euser.lib
LIBRARY  sqldb.lib


The following capabilities are needed to test the secure database in this example:

CAPABILITY ReadUserData
CAPABILITY WriteUserData


Source file

#include <e32base.h>
#include <SqlDb.h>
 
void CreateDatabasesL()
  {
//== Create non-secure database ==
  RSqlDatabase database;
 
  _LIT(KNonSecureDbName, "\\nonsecure.db");
  TInt error = database.Create(KNonSecureDbName);
 
  if(error == KErrNone)
    {
    CleanupClosePushL(database);
 
    //Create MOVIES table
    _LIT(KSqlCreateTableMovies, "CREATE TABLE MOVIES(ID INTEGER, TITLE TEXT, 
YEAR SMALLINT, DURATION SMALLINT, COLOR BOOLEAN)");
    User::LeaveIfError(database.Exec(KSqlCreateTableMovies));
 
    _LIT(KSqlCreateTableMoviesIndex, "CREATE UNIQUE INDEX IDX ON MOVIES (ID)");
    User::LeaveIfError(database.Exec(KSqlCreateTableMoviesIndex));
 
    //Insert data into MOVIES table
    _LIT(KSqlInsertIntoTableMovies, "INSERT INTO MOVIES(ID, TITLE, YEAR, 
DURATION, COLOR) VALUES(1, 'numberOne', 1999, 120, 'true')");
    User::LeaveIfError(database.Exec(KSqlInsertIntoTableMovies));
 
    CleanupStack::PopAndDestroy(); //database
    }
  else
    {
    //creating database failed:
    //reason could be e.g. already exists, the disk full, bad database name...
    }
 
//== Create secure database ==
 
  TSecurityPolicy defaultPolicy;
  RSqlSecurityPolicy securityPolicy;
 
  // Create security policies container object using a default security policy.
  securityPolicy.Create(defaultPolicy);
  CleanupClosePushL(securityPolicy);
 
  // Set up policy to apply to database schema and assign it
  TSecurityPolicy schemaPolicy(ECapabilityReadUserData, ECapabilityWriteUserData);
  User::LeaveIfError(securityPolicy.SetDbPolicy(RSqlSecurityPolicy::ESchemaPolicy,
 schemaPolicy));
 
  // Set up policy to apply to write activity on the database and assign it
  TSecurityPolicy readPolicy(ECapabilityReadUserData);
  User::LeaveIfError(securityPolicy.SetDbPolicy(RSqlSecurityPolicy::EReadPolicy, readPolicy));
 
  // Set up policy to apply to write activity to the database table named
  // "MOVIES" and assign it
  TSecurityPolicy tablePolicy(ECapabilityWriteUserData);
 
  _LIT(KMoviesTableName, "MOVIES");
  User::LeaveIfError(securityPolicy.SetPolicy(RSqlSecurityPolicy::ETable, KMoviesTableName,
      RSqlSecurityPolicy::EWritePolicy, tablePolicy));
 
  // Create the database, passing the security policies
  _LIT(KSecureDbName, "[E80000AF]secure.db");  //Note: [UID3]
  error = database.Create(KSecureDbName, securityPolicy);
 
  //close RSqlSecurityPolicy object.
  CleanupStack::PopAndDestroy(); //securityPolicy
 
  if(error == KErrNone)
    {
    CleanupClosePushL(database);
    //Create BOOKS table
    _LIT(KSqlCreateTableBooks, "CREATE TABLE BOOKS(ID INTEGER NOT NULL, 
TITLE TEXT, AUTHOR TEXT)");
    User::LeaveIfError(database.Exec(KSqlCreateTableBooks));
 
    //insert data
    _LIT(KSqlInsertIntoTableBooks, "INSERT INTO BOOKS(ID, TITLE, AUTHOR) 
VALUES(1, 'numberOne', 'author1')");
    User::LeaveIfError(database.Exec(KSqlInsertIntoTableBooks));
 
    CleanupStack::PopAndDestroy(1); //database
    }
  else
    {
    //database creation failed:
    //reason could be e.g. already exists, the disk full, bad database name...
    }
 
  }

Postconditions

Two databases, nonsecure.db and secure.db, have been created and the example rows are inserted into the databases.

nonsecure.db after creation:

TABLE:MOVIES
ID|TITLE    |YEAR|DURATION|COLOR
1 |numberOne|1999|120     |true

[UID3]secure.db after creation:

TABLE:BOOKS
ID|TITLE    |AUTHOR
1 |numberOne|author1

See also

Related Discussions
Thread Thread Starter Forum Replies Last Post
Secure Connection in j2me !!! cvraiden Mobile Java General 9 2007-03-19 14:45
Secutity application implementation berroto Mobile Java Networking & Messaging & Security 0 2008-01-31 15:55
credit card payment midlet supanana Mobile Java General 0 2003-09-04 12:19
Jetz API on secure element Alezzz Near Field Communication 0 2007-09-11 10:13
Problem in sending mail harshalpatil Mobile Java General 2 2007-11-15 06:45
 
Powered by MediaWiki