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 :

IRecordReader is my optimized IDataReader object.

Regards,
Alexnaldo Santos