Board index » delphi » IB6:Problem with stored procedure

IB6:Problem with stored procedure

Hello,

to you all. I have a problem with writing a stored procedure. I'll to
explain you the problem. I'm writing a NNTP-server as personal project in
Delphi with an IB6-db, and i want to use access-rights within the db.
Because they are access-rights to specific records, I can't use the built-
in rights-structure.
The questions I have, are at the end of the text.

I use a table with the access-rights to the groups:
=====================
== Pasted code
=====================
/* Table: WELKRECHT, Owner: SYSDBA */
CREATE TABLE "WELKRECHT"
(
  "WELKRECHTID"       INTEGER NOT NULL,
  "RECHT_ID"  INTEGER,
  "VOLGORDE"  INTEGER,
  "RECHTEN"   CHAR(1),
  "GROEPEN"   CHAR(32),
CONSTRAINT "PK_WELKRECHT" PRIMARY KEY ("WELKRECHTID")
);

INSERT INTO "WELKRECHT" ("WELKRECHTID", "RECHT_ID", "VOLGORDE", "RECHTEN", "GROEPEN") VALUES (1, 3, 1, 'G', '%');
INSERT INTO "WELKRECHT" ("WELKRECHTID", "RECHT_ID", "VOLGORDE", "RECHTEN", "GROEPEN") VALUES (2, 9, 0, 'G', 'NL.%');
INSERT INTO "WELKRECHT" ("WELKRECHTID", "RECHT_ID", "VOLGORDE", "RECHTEN", "GROEPEN") VALUES (3, 9, 2, 'S', '%.TEST');
INSERT INTO "WELKRECHT" ("WELKRECHTID", "RECHT_ID", "VOLGORDE", "RECHTEN", "GROEPEN") VALUES (5, 13, 1, 'G', '%');
INSERT INTO "WELKRECHT" ("WELKRECHTID", "RECHT_ID", "VOLGORDE", "RECHTEN", "GROEPEN") VALUES (7, 13, 2, 'L', 'NL.%');
INSERT INTO "WELKRECHT" ("WELKRECHTID", "RECHT_ID", "VOLGORDE", "RECHTEN", "GROEPEN") VALUES (21, 13, 6, 'S', 'NL.TEST');
INSERT INTO "WELKRECHT" ("WELKRECHTID", "RECHT_ID", "VOLGORDE", "RECHTEN", "GROEPEN") VALUES (23, 11, 1, 'S', '%TEST');
INSERT INTO "WELKRECHT" ("WELKRECHTID", "RECHT_ID", "VOLGORDE", "RECHTEN", "GROEPEN") VALUES (17, 9, 1, 'L', 'ALT.%');
INSERT INTO "WELKRECHT" ("WELKRECHTID", "RECHT_ID", "VOLGORDE", "RECHTEN", "GROEPEN") VALUES (19, 11, 0, 'G', 'NL.%');

=====================
== End Pasted code
=====================

Contents:
WELKRECHTID RECHT_ID VOLGORDE RECHTEN GROEPEN
          1        3        1     'G' '%'
          2        9        0     'G' 'NL.%'
         17        9        1     'L' 'ALT.%'
          3        9        2     'S' '%.TEST'
         19       11        0     'G' 'NL.%'
         23       11        1     'S' '%TEST'
          5       13        1     'G' '%'
          7       13        2     'L' 'NL.%'
         21       13        6     'S' 'NL.TEST'

In this table:
RECHT_ID: is the identifier that identifies just one client.
VOLGORDE: means order
RECHTEN: 'G' means No Rights, 'L' Read-rights and 'S' Write-rights

You have to read this table with this meaning:
Client with Recht-id 9 has No-rights in the groups starting with 'NL.', except:
Read-rights in the groups starting with 'ALT.', except
Write-rights in the groups ending with '.TEST'.

To know the names of the groups, there is another table. In this example 5 testgroups:
=====================
== Pasted code
=====================
/* Table: GROEPEN, Owner: SYSDBA */

CREATE TABLE "GROEPEN"
(
  "GROUPID"   INTEGER NOT NULL,
  "GROUPNAME" CHAR(32) NOT NULL,
  "LASTMESSAGEID"     INTEGER NOT NULL,
  "FIRSTMESSAGEID"    INTEGER NOT NULL,
  "MODE"      CHAR(10),
  "DATETIME"  TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  "DESCRIPTION"       CHAR(80),
  "DATUMTIJD" CHAR(13),
CONSTRAINT "PK_GROEPEN" PRIMARY KEY ("GROUPID")
);
INSERT INTO "GROEPEN" ("GROUPID", "GROUPNAME", "LASTMESSAGEID", "FIRSTMESSAGEID", "MODE", "DATETIME", "DESCRIPTION", "DATUMTIJD") VALUES (1, 'INTERN.TEST', 3, 1, NULL, '25-8-00 20:45:59', 'Zomaar een testgroepje...', '000825 204559');
INSERT INTO "GROEPEN" ("GROUPID", "GROUPNAME", "LASTMESSAGEID", "FIRSTMESSAGEID", "MODE", "DATETIME", "DESCRIPTION", "DATUMTIJD") VALUES (2, 'INTERN.ROTZOOI', 2, 1, NULL, '25-8-00 20:46:21', 'Ook al een testgroepje...', '000825 204621');
INSERT INTO "GROEPEN" ("GROUPID", "GROUPNAME", "LASTMESSAGEID", "FIRSTMESSAGEID", "MODE", "DATETIME", "DESCRIPTION", "DATUMTIJD") VALUES (3, 'INTERN.VERVOLG', 1, 1, NULL, '25-8-02 20:47:50', 'Eentje uit de toekomst', '020825 204750');
INSERT INTO "GROEPEN" ("GROUPID", "GROUPNAME", "LASTMESSAGEID", "FIRSTMESSAGEID", "MODE", "DATETIME", "DESCRIPTION", "DATUMTIJD") VALUES (4, 'NL.LEZEN', 0, 0, NULL, '27-12-00 17:15:44', 'Alleen maar leesrecht....', '001227 171544');
INSERT INTO "GROEPEN" ("GROUPID", "GROUPNAME", "LASTMESSAGEID", "FIRSTMESSAGEID", "MODE", "DATETIME", "DESCRIPTION", "DATUMTIJD") VALUES (5, 'NL.TEST', 0, 0, NULL, '27-12-00 17:16:24', 'Ook schrijfrecht', '001227 171624');
=====================
== End Pasted code
=====================

So far, so clear. At least for myself. To determine in a quick way the
rights of a specific user in a specific group, I use third table. This table
is generated everytime, the list of groups, or the list with access-rights
is changed. I do this from within Deplhi.

The structure of this table with is:
=====================
== Pasted code
=====================
/* Table: EFF_RECHT, Owner: SYSDBA */

CREATE TABLE "EFF_RECHT"
(
  "RECHT_ID"  INTEGER,
  "GROUP_ID"  INTEGER,
  "RECHT"     CHAR(1)
);
=====================
== End Pasted code
=====================

After the Deplhi procedure has done its job, the following records are
available for the user with recht_id 9:

RECHT_ID GROUP_ID RECHT
       9        1 'S'
       9        4 'L'
       9        5 'S'

Or in english the user with recht_id 9 may write in group 1(Intern.test)
and 5(nl.test) and has Read-rights in group 4(nl.lezen).

The SQL-procedure I use in Delphi is:
  Delete everything from EFF_RECHT

  For all records in WELKRECHT
    Add Records in EFF_RECHT
      for all groups are meeting the criteria
        except the records already in EFF_RECHT.

or in SQL:
=====================
== Pasted code
=====================
CREATE PROCEDURE "NEW_RIGHTS"
AS
    DECLARE VARIABLE WR_id INTEGER;
    DECLARE VARIABLE WR_recht CHAR(1);
    DECLARE VARIABLE WR_groepen CHAR(32);
  BEGIN
    DELETE FROM EFF_RECHT;
    FOR SELECT RECHT_ID,RECHTEN,GROEPEN
      FROM WELKRECHT
      ORDER BY RECHT_ID ASC, VOLGORDE DESC
      INTO :WR_id, :WR_recht, :WR_groepen
     DO
      BEGIN
        INSERT INTO EFF_RECHT(RECHT_ID,GROUP_ID,RECHT)
          SELECT :WR_id , GROUPID, :WR_recht FROM GROEPEN
            WHERE Upper(Groupname) LIKE ':WR_groepen'
              AND GROUPID NOT IN
                (SELECT GROUP_ID FROM EFF_RECHT WHERE RECHT_ID=:WR_id);
  END
END
=====================
== End Pasted code
=====================

If I do this procedure by hand in Delphi, everyhting looks right, but as
soon as I use the stored procedure (the beauty-solution), I don't get the
right records in EFF_RECHT.
On MS SQL 7 I can use the next line to accomplish this result:

INSERT EFF_RECHT
  SELECT NEWSGROUPID, RECHT_ID, RECHT = MAX(RECHT)
  FROM GROEPEN, WELKRECHT
  WHERE GROEPEN.GROUPNAME LIKE WELKRECHT.BEREIK
  GROUP BY GROUPID, RECHT_ID

The questions I have:
- Why doesn't my stored procedure do what I want?
- How can I correct this procedure?
- Why can't I use the SQl-statement in IB6 as I can in MS SQL7? I get an
  error on the MAX-function.

Thanks for your time,

Jeroen van der Meer
--
X-Posting is not allowed to Borland, so I multipost this post to the correct groups:
- comp.lang.pascal.delphi.databases
- borland.public.delphi.database.interbaseexpress

 

Re:IB6:Problem with stored procedure


On 02 Apr 2001 19:24:05 GMT, Jer...@vd-Meer.net (Jeroen) wrote:

Quote
>Hello,

>to you all. I have a problem with writing a stored procedure. I'll to
>explain you the problem. I'm writing a NNTP-server as personal project in
>Delphi with an IB6-db, and i want to use access-rights within the db.
>Because they are access-rights to specific records, I can't use the built-
>in rights-structure.
>...

Jeroen,

Why can't you use the built in structure? I'm not familiar with IB6,
but i assume it is possible to grant rights to views. If so, it is
possible to add a column to each record to indicate the type of
record. You can build views on the different record-types.

Just an idea.

Re:IB6:Problem with stored procedure


hvan...@worldonline.nl (Hans van Dam) schreef in
<3ac99900.4074...@news.worldonline.nl>:

Quote
>On 02 Apr 2001 19:24:05 GMT, Jer...@vd-Meer.net (Jeroen) wrote:

>>Hello,

>>to you all. I have a problem with writing a stored procedure. I'll to
>>explain you the problem. I'm writing a NNTP-server as personal project
>>in Delphi with an IB6-db, and i want to use access-rights within the
>>db. Because they are access-rights to specific records, I can't use the
>>built- in rights-structure.
>>...

>Jeroen,

>Why can't you use the built in structure? I'm not familiar with IB6,
>but i assume it is possible to grant rights to views. If so, it is
>possible to add a column to each record to indicate the type of
>record. You can build views on the different record-types.

The rights structure is for every user on a different basis. If I add a
field with the kind of record, I need a field for every user. And that's a
situation I don't want. I wanted the problem was so simple :(
But thanks reading and thinking,

Jeroen

Re:IB6:Problem with stored procedure


perhaps you need to consider hardcoding the access rights in the application
its-self or Creating a table for acess rights which woll link to the record
as well as the user, a bit messy but it can be made dynamic so it can grow
per record

I.E. the table has the user id, record id and specific rights for that user
and record.

Not a very well thought out Idea but something to work on

Other Threads