Changing generated SQL before it is executed

rbuser

Hi,

We develop HRM systems and use a simple form of security at this moment.
Basically each created report can either be executed or not. So far this
has served us fine, but our customers are getting bigger and bigger and
we are developing a next version that has to handle tens of users.
Needless to say that security is getting an overhaul as well.

The biggest issue is that users, most of the time, are only allowed to
view a subset of the data.

We are thinking about creating securtity filters that will limit the
number of records a person can view and attach these security filters to
reports. When a report is executed the content of the security filter
has to be merged with the sql contained in the report.

Has anybody done anything like this before and would this be a feasible
option?

regards
Paul Sjoerdsma

nardmoseley

----------------------------------------------------------
Tech Tip: Modify DADE SQL prior to execution
----------------------------------------------------------

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

Solution:
----------

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

2. 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.


example:

{assign the connection object}
FQuery.Connection := TADOConnection(lDatabase);

{assign the SQL Text}
FQuery.SQL := SQL.MagicSQLText;

{add custom code here to modify the Query.SQL}

--