Discussion :

[xreBOT]

Bonjour,

Je débute en PL/SQL. J'ai à ma disposition une base Oracle et SQL*Plus.

Pour mon premier script, j'ai souhaité faire quelque chose qui me semble simple : récupérer l'ensemble des droits sur tous les objets de la base, pour tous les utilisateurs.

J'étais donc parti sur cette idée :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
Set SERVEROUTPUT On
 
Prompt ;
Prompt ------------------------------------- ;
Prompt - Liste des utilisateurs de la base - ;
Prompt ------------------------------------- ;
Prompt ;
 
DECLARE
        CURSOR object_cur IS
                SELECT OBJECT_NAME, OWNER
                FROM DBA_OBJECTS
                WHERE OBJECT_TYPE = 'TABLE'
                OR OBJECT_TYPE = 'INDEX'
                OR OBJECT_TYPE = 'VIEW';
        object_rec object_cur%ROWTYPE;
 
        vpriv VARCHAR2(64);
        vcount INTEGER;
 
        CURSOR user_cur IS
                SELECT USERNAME
                FROM DBA_USERS;
        user_rec user_cur%ROWTYPE;
 
PROCEDURE count_rights (object VARCHAR2, user VARCHAR2) IS
BEGIN
        SELECT COUNT(t.PRIVILEGE)
        INTO vcount
        FROM DBA_TAB_PRIVS t
        WHERE t.GRANTEE = user
        AND t.TABLE_NAME = object;
END;
 
PROCEDURE get_rights (object VARCHAR2, user VARCHAR2) IS
BEGIN
        SELECT t.PRIVILEGE
        INTO vpriv
        FROM DBA_TAB_PRIVS t
        WHERE t.GRANTEE = user
        AND t.TABLE_NAME = object;
END;
 
BEGIN
        OPEN object_cur;
        LOOP
                FETCH object_cur INTO object_rec;
                EXIT WHEN object_cur%NOTFOUND;
                OPEN user_cur;
                LOOP
                        FETCH user_cur INTO user_rec;
                        EXIT WHEN user_cur%NOTFOUND;
                        count_rights (object_rec.OBJECT_NAME, user_rec.USERNAME);
                      IF vcount <> 0 THEN
                              get_rights (object_rec.OBJECT_NAME, user_rec.USERNAME);
                      END IF;
                END LOOP;
                CLOSE user_cur;
        END LOOP;
        CLOSE object_cur;
END;
/

On a donc deux sortes de cursor for loops (ce n'est pas exactement ça) imbriquées. Le count_rights me sert à gérer l'exception NO_DATA_FOUND, je n'arrive pas à la gérer autrement.

Le get_rights ne fonctionne pas car il arrive qu'il retourne plusieurs rows, lorsque l'utilisateur a plusieurs droits sur un même objet. Et il ne m'est pas possible de faire le Select Into sur plusieurs lignes.

Or je suis obligé de faire ce Select Into, puisqu'un simple Select ne marche pas. Oracle se plaint qu'un "Into" est attendu à ce moment-là.

Je pense en fait que la façon dont j'ai conçu le script n'est pas bonne du tout. Et je suis de toute façon bloqué par ce dernier point du Select Into sur plusieurs rows.

Le problème est que je ne vois pas vraiment quelle autre direction prendre...

Pourriez-vous s'il vous plaît me suggérer une piste pour contourner ce problème ? Quitte à tout refaire depuis le début, tant pis !

Merci d'avance.

[xreBOT]

Bon en fait j'ai laissé tomber les curseurs et j'ai fait un truc tout con avec un script shell.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
#!/bin/sh
 
echo > gtr.log
 
sqlplus "****/**** as sysdba" @sql/gtr.sql
 
# traitement du fichier resultat
 
# suppression des titres des colonnes et de la ligne resultats
cat gtr.log | grep -v "USERNAME" | grep -v "rows selected" > gtr.log.tmp
mv gtr.log.tmp gtr.log
 
# suppression des lignes vides
/usr/bin/sed '/^$/d' gtr.log > gtr.log.tmp
mv gtr.log.tmp gtr.log
 
# suppression des lignes de tirets
/usr/bin/sed '/----/d' gtr.log > gtr.log.tmp
mv gtr.log.tmp gtr.log
 
# formatage vers CSV
cat gtr.log | tr -s " " | tr " " ";" | sort -u > gtr.log.tmp
mv gtr.log.tmp gtr.log
echo "Utilisateur;Objet;Type d'Objet;Droit Possede;" > gtr.csv
cat gtr.log >> gtr.csv
rm gtr.log
 
echo ""
echo "Les droits ont ete extraits vers le fichier gtr.csv."
echo ""

Et le SQL qui va avec :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
Set Line 512
 
Spool gtr.log
 
SELECT u.USERNAME, o.OBJECT_NAME, o.OBJECT_TYPE, t.PRIVILEGE
FROM DBA_USERS u, DBA_OBJECTS o, DBA_TAB_PRIVS t
WHERE t.GRANTEE = u.USERNAME
AND t.TABLE_NAME = o.OBJECT_NAME
AND u.USERNAME != ALL ('SYS', 'SYSTEM', 'DBSNMP', 'OUTLN',
                        'MDSYS', 'ORDSYS', 'ORDPLUGINS', 'CTXSYS', 'DSSYS', 'PERFSTAT',
                        'WKPROXY', 'WKSYS', 'WMSYS', 'XDB', 'ANONYMOUS', 'ODM', 'ODM_MTR',
                        'OLAPSYS', 'TRACESVR', 'REPADMIN', 'HR', 'OE', 'PM', 'SH', 'QS',
                        'QS_ES', 'QS_WS', 'QS_OS', 'QS_CB', 'QS_CS', 'QS_ADM', 'QS_CBADM',
                        'RMAN', 'SYSMAN', 'DIP', 'DMSYS', 'MDDATA', 'MGMT_VIEW', 'OLAPDBA',
                        'OLAPSVR', 'TSMSYS', 'EXFSYS', 'SI_INFORMTN_SCHEMA', 'ORACLE_OCM');
 
Spool off
 
Quit

Voilà, c'est pas génial mais ça fait l'affaire. En attendant d'apprendre petit à petit..!

[greek75]

c'est dommage que t'as pas pris le pl/sql

moi aussi je comprenais rien au début. J'ai lu les tutos sur ce site sur oracle et c'est pas mal expliqué. même si le site est un peu pateu

http://www.waanser.com/pl-sql/38-initiation-en-plsql.html

[brassouille]

Salut,
je n'ai pas regardé en détail ton code, mais le SELECT INTO ne marche effectivement que si il y a une seule ligne retournée.

Si tu as plusieurs lignes :
- soit tu crées un curseur que tu parcours, à chaque passe il lira une nouvelle ligne que tu devras traiter
- soit tu crées une variable de type tableau (la plus simple à utiliser est TABLE INDEX BY, qui se déclare du genre TYPE toto IS TABLE OF cequetuveux INDEX BY BINARY_INTEGER. Ensuite, tu as la même syntaxe que SELECT INTO, sauf que tu rajoutes le mot clé BULK : BULK COLLECT INTO variableDeTypeTableau. Ensuite tu devras parcourir ce tableau pour faire ce que tu veux de chaque ligne retournée.