Trigger compilé mais qui ne fonctionne pas

Version imprimable

Bonjour,

je tente de donner des droits à un rôle et à un utilisateur sur un objet (table ou vue) d'un schéma tiers dès que celui-ci est créé. J'ai tenté le trigger ci-dessous (créé à partir d'un post trouvé sur developpez.com) mais ça ne fonctionne pas quand je crée une table de TEST dans le schéma DEPLACEMENTS.
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 CREATE TRIGGER deplacements_tgr AFTER create ON SCHEMA DECLARE CURSOR C_TABLE IS SELECT TABLE_NAME FROM ALL_TABLES WHERE OWNER = 'DEPLACEMENTS'; CURSOR C_VIEW IS SELECT VIEW_NAME FROM ALL_VIEWS WHERE OWNER = 'DEPLACEMENTS'; req STRING(200); req2 STRING(200); BEGIN FOR CUR IN C_TABLE LOOP req := 'GRANT SELECT ON DEPLACEMENTS.' || CUR.TABLE_NAME || ' TO DEPLACEMENTS_CONSULT'; req2 := 'GRANT SELECT,INSERT,UPDATE,DELETE ON DEPLACEMENTS.' || CUR.TABLE_NAME || ' TO DEPLACEMENTS_MAJ'; execute immediate (req); execute immediate (req2); FOR CUR IN C_VIEW LOOP req := 'GRANT SELECT ON DEPLACEMENTS.' || CUR.VIEW_NAME || ' TO DEPLACEMENTS_CONSULT'; req2 := 'GRANT SELECT ON DEPLACEMENTS.' || CUR.VIEW_NAME || ' TO DEPLACEMENTS_MAJ'; execute immediate (req); execute immediate (req2); END LOOP; END; /
Autre question : comment peut-on contrôler l'exécution ou non du trigger (un mode debug en quelque sorte svp) ?

EDIT : le trigger est créé dans le schéma DEPLACEMENTS

Merci d'avance pour vos réponses

(ORACLE 10.2.0.3)

25/08/2011, 13h02
ORA-007
Bonjour,

Ton User DEPLACEMENTS a t il les droits pour donner les droits ?
Code:

1 2 select * from user_role_privs
cordialement.

Merci de m'aider.

Je pense que oui :

Code:

1
2
3
4
5
 
USERNAME                       GRANTED_ROLE                   ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
DEPLACEMENTS                   CONNECT                        NO  YES NO
DEPLACEMENTS                   RESOURCE                       NO  YES NO

D'ailleurs quand je suis connecté avec mon user DEPLACEMENTS, je peux le faire :

Code:

1
2
3
SQL> GRANT SELECT,INSERT,UPDATE,DELETE ON DEPLACEMENTS.TEST TO DEPLACEMENTS_MAJ;
 
Autorisation de privilèges (GRANT) acceptée.

25/08/2011, 14h40
skuatamad

Tu ne peux pas grant directement dans le trigger, il faut passer par un job.
Regarde Workaround for "Select Any Table" privilege

Merci pour le lien.

J'ai fait ç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
CREATE OR REPLACE TRIGGER deplacements_tgr
AFTER create
ON SCHEMA
 
DECLARE
  	CURSOR C_TABLE IS
    		SELECT TABLE_NAME
    		FROM ALL_TABLES
    		WHERE OWNER = 'DEPLACEMENTS';
	CURSOR C_VIEW IS
    		SELECT VIEW_NAME
    		FROM ALL_VIEWS
    		WHERE OWNER = 'DEPLACEMENTS';
 	req varchar2(255);
	req2 varchar2(255);
	l_req number;
	l_req2 number;
BEGIN
 
  	FOR CUR IN C_TABLE LOOP
		req := 'execute immediate "GRANT SELECT ON DEPLACEMENTS.' || CUR.TABLE_NAME || ' TO DEPLACEMENTS_CONSULT";';
		dbms_job.submit( l_req, replace(req,'"','''') );
		req2 := 'execute immediate "GRANT SELECT,INSERT,UPDATE,DELETE ON DEPLACEMENTS.' || CUR.TABLE_NAME || ' TO DEPLACEMENTS_MAJ";';
		dbms_job.submit( l_req2, replace(req2,'"','''') );
	END LOOP;
	FOR CUR IN C_VIEW LOOP
		req := 'execute immediate "GRANT SELECT ON DEPLACEMENTS.' || CUR.VIEW_NAME || ' TO DEPLACEMENTS_CONSULT";';
		dbms_job.submit( l_req, replace(req,'"','''') );
		req2 := 'execute immediate "GRANT SELECT ON DEPLACEMENTS.' || CUR.VIEW_NAME || ' TO DEPLACEMENTS_MAJ";';
		dbms_job.submit( l_req2, replace(req2,'"','''') );
  	END LOOP;
END;
/

Il se crée bien mais toujours rien (et je ne connais pas grand chose en jobs Oracle).

Pourquoi vouloir réinventer ce que propose tom kyte, adapte à ton besoin mais pas besoin d'utiliser de curseurs :

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
create or replace trigger do_grant
after CREATE on schema
declare
    l_str varchar2(255);
    l_str2 varchar2(255);
    l_job number;
    l_job2 number;
begin
    if ( ora_dict_obj_type = 'TABLE' )
    then
        l_str := 'execute immediate "grant select on ' ||
                                     ora_dict_obj_name ||
                                    ' to DEPLACEMENTS_CONSULT";';
	l_str2 := 'execute immediate "grant select, insert, update, delete on ' ||
                                     ora_dict_obj_name ||
                                    ' to DEPLACEMENTS_MAJ";';
        dbms_job.submit( l_job, replace(l_str,'"','''') );
	dbms_job.submit( l_job2, replace(l_str2,'"','''') );
    elsif ( ora_dict_obj_type = 'VIEW' )
    then
	l_str := 'execute immediate "grant select on ' ||
                                     ora_dict_obj_name ||
                                    ' to DEPLACEMENTS_CONSULT";';
	l_str2 := 'execute immediate "grant select on ' ||
                                     ora_dict_obj_name ||
                                    ' to DEPLACEMENTS_MAJ";';
        dbms_job.submit( l_job, replace(l_str,'"','''') );
	dbms_job.submit( l_job2, replace(l_str2,'"','''') );
    end if;
end;
/

Et un jeu de test, tu noteras que je "dors" pendant 2 min le temps que les jobs s'executent :

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
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
SQL> create user deplacement identified by d
  2  /
 
User created.
 
Elapsed: 00:00:00.06
SQL> grant connect, resource to deplacement
  2  /
 
Grant succeeded.
 
Elapsed: 00:00:00.03
SQL> grant create view to deplacement
  2  /
 
Grant succeeded.
 
Elapsed: 00:00:00.02
SQL> GRANT execute ON dbms_lock TO deplacement
  2  /
 
Grant succeeded.
 
Elapsed: 00:00:00.02
SQL> create user DEPLACEMENTS_MAJ identified by d
  2  /
 
User created.
 
Elapsed: 00:00:00.05
SQL> grant connect to DEPLACEMENTS_MAJ
  2  /
 
Grant succeeded.
 
Elapsed: 00:00:00.03
SQL> create role DEPLACEMENTS_CONSULT
  2  /
 
Role created.
 
Elapsed: 00:00:00.02
SQL> select GRANTEE, OWNER, TABLE_NAME, PRIVILEGE from DBA_TAB_PRIVS where grantee='DEPLACEMENTS_CONSULT'
  2  /
 
no rows selected
 
Elapsed: 00:00:00.02
SQL> disconnect
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> conn deplacement/d
Connected.
SQL> create or replace trigger do_grant
  2  after CREATE on schema
  3  declare
  4      l_str varchar2(255);
  5      l_str2 varchar2(255);
  6      l_job number;
  7      l_job2 number;
  8  begin
  9      if ( ora_dict_obj_type = 'TABLE' )
 10      then
 11          l_str := 'execute immediate "grant select on ' ||
 12                                       ora_dict_obj_name ||
 13                                      ' to DEPLACEMENTS_CONSULT";';
 14          l_str2 := 'execute immediate "grant select, insert, update, delete on ' ||
 15                                       ora_dict_obj_name ||
 16                                      ' to DEPLACEMENTS_MAJ";';
 17          dbms_job.submit( l_job, replace(l_str,'"','''') );
 18          dbms_job.submit( l_job2, replace(l_str2,'"','''') );
 19      elsif ( ora_dict_obj_type = 'VIEW' )
 20      then
 21          l_str := 'execute immediate "grant select on ' ||
 22                                       ora_dict_obj_name ||
 23                                      ' to DEPLACEMENTS_CONSULT";';
 24          l_str2 := 'execute immediate "grant select on ' ||
 25                                       ora_dict_obj_name ||
 26                                      ' to DEPLACEMENTS_MAJ";';
 27          dbms_job.submit( l_job, replace(l_str,'"','''') );
 28          dbms_job.submit( l_job2, replace(l_str2,'"','''') );
 29      end if;
 30  end;
 31  /
 
Trigger created.
 
Elapsed: 00:00:00.05
SQL> create table t (x number)
  2  /
 
Table created.
 
Elapsed: 00:00:00.06
SQL> create view v as select x from t
  2  /
 
View created.
 
Elapsed: 00:00:00.13
SQL> begin dbms_lock.sleep(120); end;
  2  /
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:02:00.03
SQL> disconnect
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> conn DEPLACEMENTS_MAJ/d
Connected.
SQL> select * from deplacement.t
  2  /
 
no rows selected
 
Elapsed: 00:00:00.01
SQL> insert into deplacement.t values (1)
  2  /
 
1 row created.
 
Elapsed: 00:00:00.03
SQL> commit
  2  /
 
Commit complete.
 
Elapsed: 00:00:00.01
SQL> select * from deplacement.v
  2  /
 
         X
----------
         1
 
Elapsed: 00:00:00.01
SQL> disconnect
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> conn skuatamad/mdp as sysdba
Connected.
SQL> select GRANTEE, OWNER, TABLE_NAME, PRIVILEGE from DBA_TAB_PRIVS where grantee='DEPLACEMENTS_CONSULT'
  2  /
 
GRANTEE                        OWNER                          TABLE_NAME                     PRIVILEGE
------------------------------ ------------------------------ ------------------------------ ----------------------------------------
DEPLACEMENTS_CONSULT           DEPLACEMENT                    T                              SELECT
DEPLACEMENTS_CONSULT           DEPLACEMENT                    V                              SELECT
 
Elapsed: 00:00:00.03
SQL>

Attention à la syntaxe create OR REPLACE view que je n'ai pas testée.

26/08/2011, 09h57
tck-lt

Citation:

Envoyé par skuatamad

Pourquoi vouloir réinventer ce que propose tom kyte, adapte à ton besoin mais pas besoin d'utiliser de curseurs :

Je voulais justement l'adapter et non le réinventer, encore fallait-il connaitre les variables ora_dict_obj_type et ora_dict_obj_name :lol:
C'est ton message qui m'a fait comprendre l'utilité de ces variables car du coup je m'y suis penché d'un peu plus près (sans tomber !).

Dernière question, pour la commande ci-dessous, permet-elle juste l'exécution du sleep pour le schema DEPLACEMENTS ou est-elle indispensable à l'exécution dbms_jobs.submit ?

Code:

GRANT execute ON dbms_lock TO deplacements

En tout cas merci, ça marche nickel.

(Je mettrai résolu après la réponse à ma dernière question)
26/08/2011, 12h08
skuatamad

Le GRANT n'est necessaire que pour le sleep, donc tu n'en as pas besoin pour ton cas reel.
Quand le job aura tourne (execution asynchrone par rapport au trigger) les GRANT seront donne.
Moi il ne m'etait utile que pour rendre le test explicite.

PS : desole pour les accents...clavier QWERTY.
26/08/2011, 12h42
tck-lt

Merci pour tes réponses !

(En plus ça va me permettre de le généraliser sur d'autres bases en l'adaptant)