Vous voulez contrôler l’accès à votre base de données.
Les utilisateurs doivent se connecter à la base au travers des applications pour apporter des modifications.
Si vous voulez que l’utilisateur accède à la base et aux données seulement à travers l’application, vous n’êtes pas obligés de donner des privilèges ou des rôles de façon permanente à l’utilisateur.
Des privilèges peuvent lui être attribués via l’application, ils peuvent être modifiés tout au long de l’application.
Cela se fait en quelques petites étapes :

• 1- Créer le package qui va manager le rôle :

SQL> create or replace package MANAGE_ROLE_DANS_APPLI
  2  authid current_user
  3  as
  4   procedure SET_ROLE (role_name in varchar2);
  5  end MANAGE_ROLE_DANS_APPLI;
  6  /
Package créé.
SQL> create or replace package body MANAGE_ROLE_DANS_APPLI
  2  as
  3   procedure SET_ROLE(role_name in varchar2) is
  4   begin
  5     --Un test simple pour l'exemple : véririfier le nom de l'application et nom d'utilisateur
  6     if sys_context('USERENV','MODULE') ='Mon application'
  7       and user='MY_USER1'
  8     then
  9   -- active le role
 10       dbms_session.set_role(role_name);
 11     else
 12  --ne rien faire, on active pas le role pour le user, ou lever une erreur
 13      null;
 14     end if;
 15   end SET_ROLE;
 16  end MANAGE_ROLE_DANS_APPLI;
 17  /
Corps de package créé.

• 2- créer le rôle applicatif :

SQL> create role MY_APP_ROLE1 identified using MANAGE_ROLE_DANS_APPLI;
Rôle créé.

• 3- Créer le user applicatif :

SQL> create user my_user1 identified by app_user;
Utilisateur créé.
SQL>
SQL> grant create session to my_user1;
Autorisation de privilÞges (GRANT) acceptée.
SQL> --Privilège d'exécution du package
SQL> grant execute  on MANAGE_ROLE_DANS_APPLI to my_user1;
Autorisation de privilÞges (GRANT) acceptée.

• 4- Tester :

Se connecter avec le compte applicatif, lister les rôles et privilèges

SQL>
SQL> connect my_user1/app_user
Connecté.
SQL>
SQL> select * from session_roles;
ROLE
--------------------------------------------------------------------------------
PLUSTRACE
SQL>
SQL> select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION

Simuler l’application, activer le rôle et vérifier que qu’il est bien activé :

SQL> exec dbms_application_info.set_module('Mon application','');
ProcÚdure PL/SQL terminée avec succÞs.
SQL> execute SYS.MANAGE_ROLE_DANS_APPLI.SET_ROLE('MY_APP_ROLE1');
Procédure PL/SQL terminée avec succÞs.
SQL> select * from session_roles;
ROLE
--------------------------------------------------------------------------------
MY_APP_ROLE1

Le user peux travailler maintenant : il ne peux accéder à la base qu’au travers de l’application.
D’autres rôles peuvent lui être attribués ou retirés selon les besoins tout au long de l’application.
Aucune information sur les rôles du compte n’est enregistrée dans la base.