SQL Problem / CopyCat

[manfred_h]

Hallo zusammen

mit der CopyCat Komponente greife ich über http auf einen Mysql-Server im Internet zu.
Dabei ist in eine Zugriffsverwaltung "in" der MySQL Db integriert.
Diese Komponente wurde neu entwicklet und deshalb ist die Doku noch nicht fertig.
Vom Entwickler habe ich folgende Hilfe bekommen:

Zitat:

Group Funktion: >> Mail vom 16.10.2009 15:18 Re: CopyCat Group funktion

- Members of the camp group only receive lines of their own camp.
- Members of the admin group receive lines from all camps.
- Members of the "coadmin" group receive only a few chosen camps.

If so, then here is how I would do it :

- Create a GROUPS table, with a group number and a description. Fill it with at least "ADMIN", "CAMP" and "COADMIN".

- Create a GROUPS_RIGHTS table, with columns : CAMP_NO and GROUP_NO, with a double primary key (CAMP_NO, GROUP_NO).

- Fill this table with one row for each camp that you want each group to be able to see.
For example, if you want the COADMIN group to be able to see camps I47710 and I47711 and I47712,
then put three records in the table : ('COADMIN', 'I47710'), ('COADMIN', 'I47711') and ('COADMIN', 'I47712').
You don't need to put anything for the ADMIN or CAMP groups, because those are special groups, that we can handle separately.

- Note that this structure means you can create several co-admin groups if you want (for example, one for each region),
and you define how much each group can see.

- Create a field in the MEMBERS table called MEMBER_GROUP. This field should hold a group number from the GROUPS table.

- In RPL$USERS, put the member number of each member in the CONDITION_VALUE field.

- In RPL$TABLES, put the following SQL into the CONDITION field :

((select m.member_group from members m where m.id = u.condition_value) = 'ADMIN')
or (((select m.member_group from members m where m.id = u.condition_value) = 'CAMP') and ((select m.c_no from members m where m.id = u.condition_value) = new.c_no))
or (new.c_no in (select gr.camp_no from members m join groups g on g.group = m.member_group join groups_rights gr on gr.group_no = g.group_no where m.id = u.condition_value)))

This has to go in the RPL$TABLES.CONDITION field for all the tables that you want to replicate in this way.
Better yet, you could put this code in a stored procedure so that you don't have to duplicate it many times, but this should work.
It's not tested of course, but I'll leave that to you. Let me know if you have any questions about the expression, I can explain it for you.

Mein Problem ist nun die Umsetzung / Anpassung an meine DB. Das heisst der Inhalt des RPL$TABLES.CONDITION Field.

Bis jetzt habe ich folgende Tabellen und Felder erstellt.
Tabelle: RPL$GROUPS
Feld: GROUP_NR
Feld: DESCRIPTION

Tabelle: RPL$GROUPS_RIGHTS
Feld: GROUP_NO
Feld: CAMP_NO

Tabelle: RPL$USERS
Feld: LOGIN
Feld: PASSWRD
Feld: DESC
Feld: CONDITION_VALUE
Feld: MEMBER_GROUP

Währe dankbar wenn mir jemand mit dem SQL-Code helfen könnte.
Manfred