“Our end-user reporting solution needs to implement a security scheme to limit the data that is available to end-users.”
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
- The end-user uses the query tools to define a SQL query.
- The TdaSQL object maintains an object based description of the SQL.
- The TdaSQL object generates SQL Text
- 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:
FQuery.Connection := TADOConnection(lDatabase);
FQuery.SQL.Assign(SQL.MagicSQLText); // assign TStrings to TWideStrings
// add security code here to modify FQuery.SQL