How To...Control Security

Question

"Our end-user reporting solution needs to implement a security scheme to limit the data that is available to end-users."

Solution

Use the DataDictionary to limit which tables and fields are available to end-users.

To limit specific table records that are accessible requires that you modify the SQL statement submitted to the database. The best approach is to customize the DADE plug-in so that you can modify the SQLText that is assigned to the Query object. That way it is transparent to the end-user.

The flow looks something like this.  

Query Tools --> TdaSQL object --> SQL Text --> Query DataSet

1. The end-user uses the query tools to define a SQL query.
2. The TdaSQL object maintains an object based description of the SQL.
3. The TdaSQL object generates SQL Text
4. The SQL Text is assigned to a SQL based TDataSet descendant. This last step is handled by the DADE plug-in. For the BDE, TQuery is used, for ADO, TADOQuery is used, etc.

As an example, have a look at the DADE plug-in for ADO. Open RBuilder\Source\daADO.pas and search for the TdaADOQueryDataView.SQLChanged method. You can modify the DADE plug-in code directly or create your own descendants. The registration appears in the initialization section at the bottom of the unit.

Delphi code sample:

procedure TdaADOQueryDataView.SQLChanged;
var
  lDatabase: TComponent;
begin

  ....

  FQuery.Connection := TADOConnection(lDatabase);
  FQuery.SQL.Assign(SQL.MagicSQLText); // assign TStrings to TWideStrings

  // add security code here to modify FQuery.SQL

end;