Grant sur toutes les tables en même temps

[jer64]

Bonjour,

Je n'arrive pas à trouver l'instruction, si elle existe, qui permet de faire un grant pour un user sur toutes les tables de ma base de donnée.

J'utilise FIREBIRD 1.5

Merci d'avance.

Jer

[makowski]

ça n'existe pas
mais bon c'est pas difficile à faire.

[jer64]

Mais comment fait-on?

Est-ce plus facile à faire avec delphi 7?

jer

[makowski]

tu peux utiliser qqchose comme ça :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
SET TERM ^;
CREATE PROCEDURE GRANT_PERMS
  (CMD VARCHAR(6),
   PRIV CHAR(10),
   USR VARCHAR(31),
   ROLENAME VARCHAR(31),
   GRANTOPT SMALLINT,
   CREATE_ROLE SMALLINT)
AS
  DECLARE VARIABLE RELNAME VARCHAR(31);
  DECLARE VARIABLE EXESTRING VARCHAR(1024) = '';
  DECLARE VARIABLE EXESTUB VARCHAR(1024) = '';
BEGIN
    IF (ROLENAME = '') THEN ROLENAME = NULL;
    IF ((ROLENAME IS NOT NULL) AND (CREATE_ROLE = 1)) THEN
    BEGIN
      EXESTUB = 'CREATE ROLE '||ROLENAME;
      EXECUTE STATEMENT EXESTUB;
      EXESTUB = '';
      USR = ROLENAME;
      ROLENAME = NULL;
    END
    IF (USR = '') THEN USR = NULL;
    IF (PRIV = '') THEN PRIV = NULL;
    IF ((PRIV IS NULL AND ROLENAME IS NULL) OR USR IS NULL) THEN EXIT;
    IF (CMD STARTING WITH 'G') THEN
      EXESTUB = 'GRANT ';
    ELSE
      EXESTUB = 'REVOKE ';
    IF (ROLENAME IS NOT NULL) THEN
    BEGIN
      IF (EXESTUB = 'GRANT') THEN
      BEGIN
        EXESTUB = EXESTUB||ROLENAME||' TO '||USR;
        IF (GRANTOPT = 1) THEN
          EXESTUB = EXESTUB||' WITH ADMIN OPTION';
      END
      ELSE
        EXESTUB = EXESTUB||ROLENAME||' FROM '||USR;
      EXECUTE STATEMENT EXESTUB;
    END
    ELSE
    BEGIN
      IF (PRIV = 'ANY') THEN
        EXESTUB = EXESTUB||'SELECT,DELETE,INSERT,UPDATE,REFERENCES ON ';
      ELSE
        EXESTUB = EXESTUB||PRIV||' ON ';
      FOR SELECT RDB$RELATION_NAME FROM RDB$RELATIONS
      WHERE RDB$RELATION_NAME NOT STARTING WITH 'RDB$'
      INTO :RELNAME DO
      BEGIN
        EXESTRING = EXESTUB||:RELNAME||' ';
        IF (CMD STARTING WITH 'G') THEN
          EXESTRING = EXESTRING||'TO ';
        ELSE
          EXESTRING = EXESTRING||'FROM ';
        EXESTRING = EXESTRING||USR;
        IF (GRANTOPT = 1) THEN
          EXESTRING = EXESTRING||' WITH GRANT OPTION';
        EXECUTE STATEMENT EXESTRING;
      END
      FOR SELECT RDB$PROCEDURE_NAME FROM RDB$PROCEDURES
      INTO :RELNAME DO
      BEGIN
        IF (CMD STARTING WITH 'G') THEN
          EXESTRING = 'GRANT EXECUTE ON PROCEDURE '||:RELNAME||' TO ';
        ELSE
          EXESTRING = 'REVOKE EXECUTE ON PROCEDURE '||:RELNAME||' FROM ';
        EXESTRING = EXESTRING||USR;
        IF (GRANTOPT = 1) THEN
          EXESTRING = EXESTRING||' WITH GRANT OPTION';
        EXECUTE STATEMENT EXESTRING;
      END
    END
END ^
SET TERM ;^
COMMIT;
 
EXECUTE PROCEDURE GRANT_PERMS('GRANT','ALL','MONUSER','',0,0);
COMMIT;