Variante para evitar la creación de sinonimos locales en usuarios de aplicación

Es una práctica recomendada y en general impuesta por el area de seguridad de las empresas, crear un esquema de datos (owner) y un esquema de acceso (usuario de explotación o usuario de aplicación) cuando se realiza el setup o deploy de una nueva aplicación o modulo en un base de datos. Este esquema imposibilita a los usuarios (excepto obviamente al los usuarios administradores o dba) realizar cambios sobre los objetos del esquema propietario. Las pautas mas importantes a seguir en dicho esquema de layout son:

• Los permisos sobre los objetos del owner se deben otorgar por medio de roles hacia los usuarios de explotación (nunca en forma directa).
• Los usuarios de explotación solo deben tener otorgado el rol de conexión y el rol de definido para la aplicación. No tendrán quota en ningun tablespace ni podrán crear ningun objeto.
• Los objetos otorgados desde el owner hacia el usuario de explotacion deberan ser referenciados mediante un sinonimo local en el usuario de explotación (no referenciar ningun objeto anteponiendo el esquema owner).
• Los esquema owner y de explotación y los roles deberán ser creados por el area de base de datos. En el deploy de las app se deberá otorgar los grants al rol y crear los sinonimos locales.
  

Una variante interesante para evitar la gestión de sinonimos locales es alterar la sesion (podría ser mediante un on-logon trigger) de forma tal de cambiar el esquema corriente (de explotación a owner) y asi no tener la necesidad de calificar el objeto anteponiendo el esquema. Les voy a mostrar como implementar esta variante:

Lo primero es crear un on logon trigger. En el ejemplo mi usuario de explotación de llama exp_rop

create or replace trigger SYSTEM.onLogon_trg
after logon on database
Begin                         
  if ( user in ('EXP_ROP')) then
      execute immediate 'alter session set current_schema=ROP';
  end if;
end;

Creo un esquema de explotación y el rol de aplicación (rol_rop). El objeto usado para la prueba
es la tabla ROP.T.

rop@DESA10G> create user exp_rop identified by exp_rop;

Usuario creado.

rop@DESA10G> grant create session to exp_rop;

Concesión terminada correctamente.

rop@DESA10G> desc t
Nombre                                                ¿Nulo?   Tipo
----------------------------------------------------- -------- ------------------------------------
C1                                                             VARCHAR2(40)
N1                                                             NUMBER
N2                                                             NUMBER

rop@DESA10G> create role rol_rop;

Rol creado.

rop@DESA10G> grant select on t to rol_rop;

Concesión terminada correctamente.

rop@DESA10G> grant rol_rop to exp_rop;

Concesión terminada correctamente.

Ahora pruebo de conectarme con el usuario de explotación para ver si se ejecuta el trigger y se altera la sesión:

rop@DESA10G> conn exp_rop/exp_rop@movi10d
Conectado.
exp_rop@DESA10G> desc t
Nombre                                                ¿Nulo?   Tipo
----------------------------------------------------- -------- ------------------------------------
C1                                                             VARCHAR2(40)
N1                                                             NUMBER
N2                                                             NUMBER

exp_rop@DESA10G> select count(1) from t;

COUNT(1)
----------
    5000

exp_rop@DESA10G>

Como se vió pude acceder la tabla T que esta en el esquema ROP sin la necesidad de utilizar un sinonimo local.