CREATE SCHEMA ORACLE.

C'est quoi un SCHEMA Oracle. ?
Comment créér plusieurs Tables, Vues et Grant en une transaction sur un SCHEMA Oracle. ?

La base de données ORACLE comporte des schémas dans lesquels on retrouve des objets tel que Tables, Vues, Index, Procédures, ...
Un SCHEMA permet de segmenter la base, de mieux gérer les privilèges, le SCHEMA appartient toujours à l'utilisateur qui l'a créé.

La commande CREATE SCHEMA comporte des commandes CREATE TABLE, CREATE VIEW et GRANT et donc permet de créer des tables, des vues ainsi que des autorisations en une seule transaction. Si une commande est erronée alors la transaction est annulée.
Le nom du SCHEMA doit être celui d'un utilisateur Oracle existant.

Description de la commande CREATE SCHEMA.

Syntaxe CREATE SCHEMA.

CREATE SCHEMA AUTHORIZATION schema

   { create_table_statement

   | create_view_statement

   | grant_statement

   }

     [ create_table_statement

     | create_view_statement

     | grant_statement

     ]... ;

CREATE SCHEMA.

- Connexion au schema SYSADM existant.
- Création d'un User.
- Création Tables, Vues et Grant pour le user DBA_ORA dans le SCHEMA SYSADM.

 
C:\>SET ORACLE_SID=DBTEST

C:\>SQLPLUS /NOLOG

SQL*Plus: Release 10.2.0.1.0 - Production on Sam. Janv. 21 18:21:47 2012
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> CONNECT SYSADM/mdp
Connecté.

SQL> CREATE USER DBA_ORA
       IDENTIFIED BY VALUES 'DBA_ORA'
       DEFAULT TABLESPACE USERS
       TEMPORARY TABLESPACE TEMP
       PROFILE DEFAULT
       ACCOUNT UNLOCK;

Utilisateur créé.


SQL> CREATE SCHEMA AUTHORIZATION "SYSADM"
       CREATE TABLE ora
          (Champs1 VARCHAR2(10)  PRIMARY KEY, Champs2 NUMBER)
       CREATE TABLE ora2
          (Champs1 VARCHAR2(10)  PRIMARY KEY, Champs2 NUMBER)
       CREATE VIEW ora_view
          AS SELECT Champs1, Champs2 FROM ora WHERE Champs1 = 'DBA'
       CREATE VIEW ora_view2
          AS SELECT soc_code FROM societe WHERE soc_code = 10
       GRANT select ON ora_view TO dba_ora
       GRANT select ON ora_view2 TO dba_ora;

Schéma créé.

SQL>

GRANT SYSTEM PRIVILEGES.

Comment attribuer des droits à un Utilisateur, un rôle. ?
Comment attribuer des privilèges systemes à un USER dans Oracle. ?
Comment accorder des privilèges systemes avec la commande GRANT dans Oracle. ?

La commande GRANT permet d’attribuer un ou plusieurs privilèges à un ou plusieurs bénéficiaires.
Lorsque vous voulez attribuer un ou plusieurs privilèges à un utilisateur ou à un rôle, on utilise la commande sql GRANT.

Lorsque les GRANT privilèges systèmes sont accordées, ils prennent effet immédiatement, il n'est pas nécessaire pour l'utilisateur de se déconnecter.
Les Privilèges Systèmes doivent être attribués avec une grande attention, synonyme de Pouvoir et donc de Danger. Normalement, ce n'est jamais un utilisateur final qui contrôle et attribue ces privilèges mais plutôt des administrateurs (DBA) et développeurs.

Sachez que pour attribuer un privilège système il faut soit :
- Avoir reçu le privilège en question avec la clause WITH ADMIN OPTION.
- Avoir reçu le privilège système GRANT ANY PRIVILEGE.

Pour pouvoir créer une Table dans votre schéma il vous faut par exemple le privilège système CREATE TABLE.
Par contre pour pouvoir créer une Table dans n'importe quel schéma il vous faut le privilège système CREATE ANY TABLE. Le mot clef ANY est utilisé pour chaque objet appartenant à un schéma, par exemple le privilège CREATE ANY TABLESPACE n'existe pas car un TABLESPACE n'appartient pas à un schéma.

Description de la commande Sql GRANT.

Nous voyons dans cette partie uniquement l'attribution de Privilèges Systèmes avec la commande GRANT.

Syntaxe GRANT.(system privileges)

GRANT { { system_privilege_name
        | role
        | ALL PRIVILEGES 
        }
TO      { user [ IDENTIFIED BY password ]
        | role
        | PUBLIC
        }
[ WITH ADMIN OPTION ]
      };

• system_privilege_name.
  Indique le privilège système à attribuer. Vous pouvez les consulter dans la vue du dictionnaire de données : SYSTEM_PRIVILEGE_MAP
• ALL PRIVILEGES.
  Indique que tous les privilèges systèmes seront accordés.
• PUBLIC.
  Indique que l'on attribue le(s) privilège(s) à tous les utilisateurs.
• IDENTIFIED BY.
  Indique et désigne un utilisateur inexistant dans la base, il sera créé à l’exécution du GRANT.
• WITH ADMIN OPTION.
  Indique que le bénéficiaire peut :
  - Retransmettre les privilèges reçus à un rôle ou à un autre utilisateur.
  - Révoquer les privilèges reçus d'un autre utilisateur ou rôle.
  - Supprimer le rôle avec DROP ROLE.
  - Conserver son privilège système ou rôle même si le User qui a cédé ce droit est supprimé. (Contrairement à un Privilège Objet)

GRANT SYSTEM PRIVILEGES TO ROLE.

Vous devez avoir le privilège système GRANT ANY ROLE ou avoir reçu ce rôle en question avec la clause WITH ADMIN OPTION, ou alors avoir créer ce rôle.

 
 
SQL> GRANT CREATE SESSION TO r_role;
 
Autorisation de privilèges (GRANT) acceptée.
 
SQL> GRANT CREATE TABLE TO r_role;
 
Autorisation de privilèges (GRANT) acceptée.
 
SQL> GRANT INSERT ANY TABLE TO r_role;
 
Autorisation de privilèges (GRANT) acceptée.
 
SQL> GRANT create session, create table, insert any table TO r_role;

Autorisation de privilèges (GRANT) acceptée.

SQL>


 

GRANT SYSTEM PRIVILEGES TO USER.

 
 
SQL> GRANT grant any role TO scott;

Autorisation de privilèges (GRANT) acceptée.

SQL> GRANT create session TO scott;

Autorisation de privilèges (GRANT) acceptée.

SQL> GRANT drop any role, insert any table TO scott;

Autorisation de privilèges (GRANT) acceptée.

SQL> GRANT select any dictionary TO scott;

Autorisation de privilèges (GRANT) acceptée.

SQL>