By default a user which has report creation or modify rights can create a report which would ignore or override the security you established for reporting purposes. It is important that if a user creates or modifies a report that the report contains the lines necessary to implement your security restrictions.
To enforce your security each report must contain code in the main SQL parameters which checks for the authorization and security clearance of the user. Following are some examples of the security coding required to enforce the security parameters. The security is verified by calling a "stored procedure" with the appropriate parameters.
The SQL query must contain the From OFFICESMASTER table (see example below) and the where clause (see example below) which calls the stored procedure. The example below represents the base query to which you would add the fields and tables you require as well as the where clause and the order by clause.
Base Query:
select *
from OFFICESMASTER
where ((select HAS_ACCESS from P_EMPLOYEE_OFFICE_ACCESS(:EMPLOYEE_ID, OFFICESMASTER.OFFICE__ID))='T')
Example of a query without the security code and the same query with the security code.
1) Without Security Code
SELECT
PatientNo,
Lastname,
FirstName,
Address_1,
City,
PhoneHome,
FirstVisit
From Patients
Where (FirstVisit between :StartDate and :EndDate)
and PatientStatus = 'Active'
Order by FirstVisit
2) With the security Code
SELECT
PatientNo,
Lastname,
FirstName,
Address_1,
City,
PhoneHome,
FirstVisit
From Patients, OFFICESMASTER
Where (FirstVisit between :StartDate and :EndDate)
and PatientStatus = 'Active'
and ((select HAS_ACCESS from P_EMPLOYEE_OFFICE_ACCESS(:EMPLOYEE_ID, OFFICESMASTER.OFFICE__ID))='T')
Order by FirstVisit
The query with the security code will only display the information for the offices the user is authorized to access.
The above coding will automatically restrict the user to only see the data from the offices he is allowed to access. To activate the security parameter you must link it with the users logon. This is accomplished by:
1) Selecting the Params option in the report editor for the query which contains the security code (in this example the Office query).
2) Select the vUserEmployeeNo variable from the drop down field. This field will be automatically provided when the report is run. The field is a Data Type Integer.
