Workflow

  • Report is run.
  • User is noted.
  • All groups for the user are checked and any access group entry for that group and table is used.
  • The SQL is processed and any additional where clause for that group, user and table are used.
  • When a group or table that is not found for a user 1=0, by default, is used.

Examples

SQL

select * from example where true and {access_group.example}

Access group records

Admin
  • table = example
  • group = admin
  • where = true
Restricted
  • table = example
  • group = restricted
  • where = restricted=’Yes’

User has groups *,admin

select * from example where true and true

Any row shown

User has group *,normal

select * from example where true and 1=0

No records are shown.

User has group *,restricted

select * from example where true and restricted='Yes'

Only restricted records are shown.

Fragments

Each fragment for a group is defined to be used with a corresponding table. Define group where clauses for each table the group may access. Format is:

table group where clause
name group-name SQL

The default group access is 1=0 and will be used if no specific group access list is defined. Create a group entry of * to change the default for a table entry.

Each access group can have as many group entries as you like. Click on the + button to add a group. Use group * to change the default SQL value for an access group.

Example Scenario

For example, you have a system that records details of games. Each host_id records details for a certain game manager. You want to setup a report that restricts each game manager to seeing only game details for their host_id.

Users that manage host_id=2 have a group of host_2. They should not be able to see other games.

Example table

game table

game_id host_id name
1 1 poker
2 1 bingo
3 2 black jack
4 2 fish

group list definition

table name group query value
game host_2 host_id=2
game host_1 host_id=1

Report SQL

select game_id, name from game
where 1=1
and {{access_group.game}}

Report SQL with user with matching group of host_2

select game_id, name from game
where 1=1
and host_id=2

A user with group of host_2 will only be able to see those game rows where host_id=2.

Report SQL with user with no matching group

select game_id, name from game
where 1=1
and  1=0

When there is no matching group no records from any game will be returned.

| NOTE: when processing the groups of a user the first group of the user to be matched with the group in the Access Group will be used. Groups will be sorted alphabetically.

Testing

The administration page allows you to test each Access Group.

Test Click this button to test shown table fields using the Test SQL as shown at the top. Test Reports Click to test shown table fields against all reports that have a matching {{{{access_group.#table#}}}

When the test is run #table# is replaced by the table of the tested Access Group. Store a custom test SQL for each Access Group. Use group field to test using a specific group. Use email field to test with groups of that user.