Hi,
I can run this code in ANY BO.
public void GetAllDatabaseTables()
{string TABLE_SQL = @"SELECT TABLE_CATALOG AS [Database], TABLE_SCHEMA AS Owner, TABLE_NAME AS Name, TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES WHERE (TABLE_TYPE = 'BASE TABLE') AND (TABLE_NAME <> N'sysdiagrams') AND (TABLE_NAME <> N'dtproperties')";
this.FillDataTable(TABLE_SQL);
}Any BO user that don't have access for table X, can access it easy...
I don't think direct SQL statement secure !
One malicious user can do :
1)Connection to my ES using the SDK. Remember! He is an user, so he have a login and password.
2)Execute ANY direct SQL in the database, because, even using ES as database access, ES will uses the same database user for all connection.
How to solve it :
Don't execute SQL statement. Send a command like "QueryCommand" and before execute it on ES/DataLayer context, pass it for the "Secure Layer", so we can do like it :
public void Authorization( QueryCommand cmd )
{
string tableName = cmd.SchemaName;
string affectedFields = cmd.Fields;
QueryType queryType = cmd.QueryType;
AuthorizationLayer.Deman( tableName, affectedFields, queryType );
}
Rules :
1)Don't accept direct SQL Statement;
2)Join must be from a View and not direct from table;
Note: This approach isn't limitation for Stored Procedures.
The main key of it is :
1) My BO only access DataLayer(..Get, GetAll, Save, Update etc.. )
2) My QueryLayer only access my CommandLayer
3) My CommandLayer only acess my DataAccess Layer
Here's my test case code :
[
TestMethod]
public void QuerySelectTest()
{
ISelectQuery<Customer> query = (ISelectQuery<Customer>
DataContext.CreateQuery<Customer>(QueryType.Select);
query.AddWhere(Customer.ColumnNames.CustomerID, "ALFKI");Customer customer = query.Get();Assert.IsNotNull(customer);
Assert.AreEqual(customer.CustomerID,"ALFKI");
Assert.AreEqual(customer.CompanyName, "Alfreds Futterkiste");
Assert.AreEqual(customer.ContactName, "Maria Anders");
Assert.IsNull(customer.Region);
}
/* Now the flow */
//DataContext
public static IQuery<T> CreateQuery<T>(QueryType queryType) where T : AbstractRecord<T>, new()
{
return Default.DataLayer.CreateQuery<T>(queryType);
}
//DataLayer ( SqlServerProvider )
public override IQuery<T> CreateQuery<T>(QueryType queryType)
{
return SqlQueryFactory.Create<T>(this, queryType);
}
//Factory
switch (queryType)
{
case QueryType.Select: return new SelectDataQuery<T>(provider, provider, queryType);
case QueryType.Insert:
case QueryType.Update: return new SqlDataQuery<T>(provider, provider, queryType);
default: throw new NotImplementedException();
}
}
//Now, Query.Get()
#region ISelectQuery<T> Members
public T Get()
{
return DataLayer.Get<T>(this);
}
#endregion
//DataLayer ( SqlServerProvider )
public virtual T Get<T>(IQuery query) where T : AbstractRecord<T>, new()
{
ExceptionHelper.ArgumentNullException(query, "query");
IRecordReader reader = query.ExecuteReader();
if (!reader.IsEmpty)
{
T obj = CreateObject<T>();
FetchObject<T>(obj, reader);
return obj;
}
else
{
return null;
}
}
//QueryLayer
public virtual IRecordReader ExecuteReader()
{
QueryCommand command = CreateCommand();
return _commandLayer.ExecuteReader(command);
}
//DataLayer(SqlProvider) implement ICommandLayer
public virtual IRecordReader ExecuteReader(QueryCommand command)
{
ExceptionHelper.ArgumentNullException(command, "command");
command.Prepare();
return command.ExecuteReader(_dataAccess);
}
//CommandLayer
public virtual IRecordReader ExecuteReader(IDataAccess dataAccess)
{
ExceptionHelper.ArgumentNullException(dataAccess, "dataAccess");
return dataAccess.ExecuteReader(this);
}
As you can see, my Command ONLY can access my Database when I pass it, so to turn it remotable easy, we can create a new provider WebServiceProvider that does it :
public override IRecordReader ExecuteReader(QueryCommand command)
{
/*the command must prepare all your data(i.e. table name, Efftected Fields and QueryType */
command.Prepare();
/* simple serialization of Command */
byte[] bytes = Nano.Mapper.IO.Serialization.SerializeBinary(command);
using (WCFMapperProviderProxy proxy = new WCFMapperProviderProxy())
{
bytes = proxy.ExecuteCommand(bytes);
}
return Nano.Mapper.IO.Serialization.DeserializeBinary<IRecordReader>(bytes);
}// MyWebService method ONLY does it :
[Transaction(TransactionOption.Required)]
public byte[] ExecuteCommand(byte[] command)
{
QueryCommand queryCommand = Serialization.DeserializeBinary<QueryCommand>(command);
IRecordReader reader = queryCommand.ExecuteReader(DataContext.DefaultDataLayer.DataAccess);
return Serialization.SerializeBinary(reader);
}1)As you can see, the Commands are executed on the Server DataAccess context.
2)You can easy encrypt, compress or create a custom serialization.
3)In the "ExecuteReader" of the command you can protect all access.
4)You can uses the TransactionScope class of .NET 2.0 without problem.
IRecordReader is my optimized IDataReader object.
Regards,
Alexnaldo Santos