Gestion de base de données Oracle

Document Sample
Gestion de base de données Oracle Powered By Docstoc
					                                                      (                    #   )


   ++++++++++++++++++++++++++++++++++++
    ++++++++++++++++++++++++++++++++++++
* +++++++++++++++++++++++++++++++++++++                                                     )
                                                                                            )        ,
   +++++++++++++++++++++++++++++
    +++++++++++++++++++++++++++++
*!-+ + + + + + + + + + + + + + + + + + + + + + + + + + + + + (



$          .                                     (        )   )                    )
                                    )                 )
                                                      )     +     )                 )     /)
               (                       1
                                      0)                  # )+& (
                                                          1                             . 2          )
                                   ) )   )                 . 3 )                   )     ))      )
     )                 2       +




    4 3      .
           15 )            6
    7       8      9           !       2 :
    8     9 ;                      +
                                   )




  !"       #
 , $ =
' ' < #$ #    /   >  /                  >        ?:
     > # 7' #
 & ' % < = $ %"/ ( .)>                      ?:
&      &#
 # * '= $ &   /   > /
                   )                   >          /           /       ?:
  <    =      # )> 2
 ' " *' # $ ' / (                      ?:
 # ' = "$       /                      ?:
@     &<#
     &' = $ &  ( "$                    ?:




                                                                  !                     "        $ %'
                                                                                                # %&
    *                    &'      *'6
*               1           &'   *'A '     # ># A 2
                                            @&                     2            B7 .          )(                +

CREATE TABLESPACE GESTION_LOG
DATAFILE 'gest_log'
SIZE 60 M;

    *               1
                    )                      )(      6
 C                  .        1
                             )                     )(       /    )
                                                                 A >        +
                                                                            A

CREATE USER log_admin IDENTIFIED BY log_admin
DEFAULT TABLESPACE GESTION_LOG
QUOTA UNLIMITED ON GESTION_LOG;

GRANT DBA TO log_admin WITH ADMIN OPTION;

DEFAULT TABLESPACE ...                                  &'       *'.                 (     1
                                                                                          0) )        +
QUOTA UNLIMITED ON ...                                       )                                 &'    *'+
GRANT DBA TO ...                                        (                   1                   4 3        )(       +
... WITH ADMIN OPTION                              *         .  .          0) .                14     2     (
                                                              . 2          (                   4 3    +
    *                   ()            .)       6
&                                D&            )                   D&                           .)           )
                                                                                                            ( +

START 'C:\create_table.sql';

    *               )2       A@       &&    @
                                           # A6
*               1       2 A                        /
                                                   A         2         )    )       ()           )
                                                                                               )( .   )     )
        .   3            )              E A
                                         )          )             A=            )         ?+

CREATE VIEW INSTALLATIONS AS
SELECT nom_log AS logiciel, nom_edit AS editeur, COUNT(INSTALLER.id_log) AS
installations
FROM LOGICIEL, INSTALLER, EDITEUR
WHERE LOGICIEL.id_log=INSTALLER.id_log
AND LOGICIEL.id_edit=EDITEUR.id_edit
GROUP BY nom_log, nom_edit
ORDER BY nom_log;

    *                   4    4        .() 6
)                                          4    4  .()                     . 2                  5    () 0.
              .         1 )                    2 0    ))           )             )       ()             . .
        )
        )    +

CREATE      PUBLIC      SYNONYM   PLATE_FORME FOR log_admin.PLATE_FORME;
CREATE      PUBLIC      SYNONYM   EDITEUR FOR log_admin.EDITEUR;
CREATE      PUBLIC      SYNONYM   SERVICE FOR log_admin.SERVICE;
CREATE      PUBLIC      SYNONYM   LOGICIEL FOR log_admin.LOGICIEL;
CREATE      PUBLIC      SYNONYM   INSTALLER FOR log_admin.INSTALLER;
CREATE      PUBLIC      SYNONYM   INSTALLATIONS FOR log_admin.INSTALLATIONS;




                                                                   !                            "           $ %'
                                                                                                           # %&
 *            )
             E 6
)        )                   E) 2          .         +*    E)                 3
     0               .         +
  @ &
*# $      @
         # 6 * E )         3  1 ) ( A@
                                   )     &    @ / )
                                           & # A (                                )
   )   )    . 3 )            )    (   1  ))   +
  *F   @
 ' @* ' 6 *   E)   .)   . 2       0      )    )
                                             ( /.
   )  )     ))          0C ) ( A@
                               )     &<
                                     &' A )     4 )                           1
       0C            )
                    ( +

CREATE ROLE CONSULTATION;
GRANT CREATE SESSION TO CONSULTATION;
GRANT SELECT ON INSTALLATIONS TO CONSULTATION;

CREATE ROLE TECHNICIEN;
GRANT CREATE SESSION TO TECHNICIEN;
GRANT SELECT ON PLATE_FORME TO TECHNICIEN;
GRANT SELECT ON EDITEUR TO TECHNICIEN;
GRANT SELECT ON SERVICE TO TECHNICIEN;
GRANT SELECT ON LOGICIEL TO TECHNICIEN;
GRANT SELECT, INSERT, UPDATE ON INSTALLER TO TECHNICIEN;
GRANT SELECT ON INSTALLATIONS TO TECHNICIEN;

 *               )       6
&     3      .                      0)         )          )     (        )
                                                                        E +

;7 '
'' & 6

CREATE USER gengis IDENTIFIED BY gengis;
GRANT CONSULTATION TO gengis;

CREATE USER gege IDENTIFIED BY gege;
GRANT TECHNICIEN TO gege;




                                           !     !                  "          $ %'
                                                                              # %&