4.1 CONSULTA VARIAS
Detalles para tener en cuenta sobre los usuarios en Oracle:- El nombre de usuario no debe superar 30 caracteres, no debe tener caracteres especiales y debe iniciar con una letra.
- Un método de autentificación. El más común es una clave o password, pero desde Oracle 10g soporta otros métodos (como biometric, certificado y autentificación por medio de token).
- Un Tablespace default, el cual es donde el usuario va a poder crear sus objetos por defecto, sin embargo, esto no significa que pueda crear objetos, o que tenga una cuota de espacio. Estos permisos se asignan de forma separada, salvo si utiliza el privilegio RESOURCE el que asigna una quota unlimited, incluso en el Tablespace SYSTEM. Sin embargo si esto ocurre, usted como DBA. puede posteriormente mover los objetos creados en el SYSTEM a otro Tablespace.
- Un Tablespace temporal, donde el usuario crea sus objetos temporales y hace los sort u ordenamientos.
- Un perfil o profile de usuario, que son las restricciones que puede tener su cuenta (opcional).
Las siguientes son las vistas asociadas a los perfiles, privilegios y roles de Oracle.
SQL> DESC DBA_PROFILES
SQL> DESC DBA_ROLES
SQL> DESC DBA_USERS
SQL> DESC DBA_ROLE_PRIVS
SQL> DESC DBA_TAB_PRIVS
SQL> DESC DBA_SYS_PRIVS
Podemos conceder diferentes permisos a nuestros usuarios y así controlar exactamente qué acciones queremos que puedan realizar y que acciones no van a ser permitidas.
GRANT privilegio1 [[,privilegio2, ...] | ALL]
[(columna1[,columna2,...])]
[ON usuario[.objeto] | ANY TABLE]
TO {nombreUsuario | rol | PUBLIC}
[WITH GRANT OPTION];
A lo anterior se debe complementar lo siguiente:
- ON: Objeto sobre el que aplico los privilegios
- TO: Usuario al que concedo los privilegios
- ALL: Permite asignar todos los permisos
- PUBLIC: Asigna el privilegio o privilegios a todos los usuarios del sistema (también a los futuros)
- WITH GRANT OPTION: Permite que el usuario que lo reciba pueda conceder permisos a otros usuarios
Para consultar cual es usuario de bases de datos con el cual actualmente se encuentra conectado se ejecuta la siguiente sentencia:
SQL>show user;
En muchas ocasiones es necesario ejecutar algunas sentencias sobre la base de datos con un propietario de los objetos o propiamente con un usuario especifico, y se tiene el acceso a la base de datos con el usuario SYS o SYSTEM, para tal proceso se ejecuta la siguiente sentencia:
SQL> ALTER SESSION SET CURRENT_SCHEMA=UXXIAC
La anterior sentencia permite que desde el usuario de SYS en la base de datos se ejecute un procedimiento o ejecución de cualquier sentencia con el usuario ‘UXXIAC’. Pero cabe resaltar que la conexión esta validada con el usuario original, en este caso SYS o SYSTEM
Para verificar cuales son las tablas del esquema/usuario que actualmente se encuentra logueado en la base de datos se puede ejecutar la siguiente sentencia:
SQL> select table_name from user_tables;
Para verificar cuales son los usuarios que tienen password por defecto en la base de datos se ejecuta la siguiente sentencia.
SQL>SELECT * FROM dba_users_with_defpwd;
Para verificar cual es el usuario actual del Sistema operativo o de la maquina desde donde se encuentra conectado a la base de datos se ejecuta la siguiente sentencia.
Para cambiar el profile de un usuario se ejecuta la siguiente sentencia.
SQL>ALTER USER sh PROFILE new_profile;
Para el bloqueo de un usuario en la instancia.
SQL>alter user <usuario> account lock
Para el desbloqueo de un usuario en la instancia.
SQL>alter user <usuario> account unlock;
Para consultar la dirección IP del servidor en la cual está asociada la base de datos se puede ejecutar el siguiente script.
4.2 SEGUIMIENTO A UN USUARIO (TRACE)
En ocasiones es necesario la hacer un seguimiento de todo el proceso transaccional que genera un usuario en la base de datos, el más sencillo y conocido es la ejecución de una traza, el cual nos detalla cada uno de los movimientos, procesos, ejecuciones que hace un usuario en un lapso de tiempo definido por el administrador o en su defecto, si se tiene identificado una ejecución de una sentencia de un usuario definido, se puede activar la traza en el momento especifico en que se ejecuta la sentencia.
Se debe definir el SID y el SERIAL del usuario a ejecutar la traza
SQL>select * from v$session where username ='*****'
Se ejecuta la siguiente sentencia para activar la traza.
begin
dbms_monitor.session_trace_enable(SID, SERIAL, TRUE, TRUE);
end;
Para encontrar la ruta donde se guarda el archivo de la traza se ejecuta la siguiente sentencia.
SQL>SELECT value FROM v$diag_info WHERE name = 'Default Trace File';
Para detener la ejecución de la traza se ejecuta la siguiente sentencia:
SQL>EXECUTE DBMS_MONITOR.SESSION_TRACE_DISABLE(id,serial);
4.2.1 Trace de la sesión actual
El usuario con el que estamos ejecutando el proceso, debe tener el privilegio de:
ALTER SESSION
Hay que ver el directorio que está asignado al parámetro de la base de datos user_dump_dest:
SQL>show parameter user_dump
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest string D:\ORACLE\PRODUCT\10.2.0\ADMIN\LAB\UDUMP
En dicho directorio, se generará nuestro archivo trace.
Tenemos que saber cuál es el spid de nuestra actual sesión. Esto lo hacemos con el siguiente query:
SQL> select pro.spid, ses.username, ses.machine, ses.osuser from v$session ses, v$process pro where ses.paddr = pro.addr and ses.username = upper ('&usuario');
Enter value for usuario: admaud
old 5: and a.username = upper ('&usuario')
new 5: and a.username = upper ('admaud')
SPID USERNAME MACHINE OSUSER
------------ -------------------- -------------------- -------------------------
5432 ADMAUD RESOURCEIT\OOOLAPTOP OOOLAPTOP\Administrador
Ya teniendo los datos anteriores, podemos ahora si lanzar el trace, para esto, usamos la instrucción, alter session como se muestra a continuación:
SQL>alter session set events '10046 trace name context forever, level no_nivel';
Una vez ejecutado el paso anterior, ya podemos ejecutar cualquier query o el proceso que se está demorando.
Ya que se ejecutaron todos los queries y procesos que se requieren, se tiene que detener el trace por medio de la siguiente instrucción:
SQL>alter session set events '10046 trace name context off';
Con esto, hay que ir al directorio que obtuvimos en el punto 2 y listar el archivo trace que contenga el spid que obtuvimos en el punto 3.
Ejemplo:
D:\Oracle\Product\10.2.0\db10g\BIN>cd D:\ORACLE\PRODUCT\10.2.0\ADMIN\LAB\UDUMP
D:\Oracle\Product\10.2.0\admin\lab\udump>dir *5432*
El volumen de la unidad D es MetalliOra El número de serie del volumen es: 9079-081A
Directorio de D:\Oracle\Product\10.2.0\admin\lab\udump
27/09/2008 02:10 p.m. 191,300 lab_ora_5432.trc
1 archivos 191,300 bytes
Ya teniendo el archivo, lo único que tenemos que hacer, es ejecutar el comando tkprof para convertir el texto a algo más legible. El formato del comando es:
tkprof nombre_archivo.trc nombre_arch_salida.extensión
Aplicando esto al ejemplo que estamos haciendo:
D:\Oracle\Product\10.2.0\admin\lab\udump>tkprof lab_ora_5432.trc salida.txt
TKPROF: Release 10.2.0.2.0 - Production on Lun Oct 6 16:44:23 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
4.3 REPLICAR UN USUARIO
ORACLE 10G, La siguiente sentencia solicitará en su ejecución el Esquema, con esto se generará la estructura necesaria para recrear dicho Esquema. Extracta la estructura de creación con los tablespaces, privilegios, roles, quotas y permisos asignados. Se debe ejecutar los siguientes pasos.
SQL>SELECT 'create user ' || username || ' identified by values ' || ''''
|| password || '''' || ' profile ' || profile || ' default tablespace '
|| default_tablespace || ' temporary tablespace ' || temporary_tablespace || ';'
FROM DBA_USERS
WHERE USERNAME = :1
UNION
select 'grant '|| privilege || ' to '|| grantee || decode(admin_option,'YES',' with admin option','')|| ';'
FROM DBA_SYS_PRIVS
WHERE GRANTEE=:1
UNION
select 'grant '|| privilege || ' on ' || owner || '.' || table_name ||' to '|| grantee || decode(grantable,'YES',' with grant option','')|| ';'
FROM DBA_TAB_PRIVS
WHERE GRANTEE=:1
UNION
select 'grant '|| granted_role || ' to '|| grantee || decode(admin_option,'YES',' with admin option','') || ';'
FROM DBA_ROLE_PRIVS
WHERE GRANTEE =:1
UNION
SELECT 'alter user ' || username || ' quota unlimited on ' || tablespace_name|| ';'
FROM DBA_TS_QUOTAS
WHERE USERNAME =:1;
Digitar el usuario a replicar.
ii) Si en la base destino el usuario ya existe, se debe borrar (drop – cascade)
iii)Con el resultado de la anterior consulta, se ejecuta en la nueva base de datos (destino).
Ejemplo:
SQL>create user UXXIAC identified by values '' profile DEFAULT default tablespace UXXIAC_DAT_128K temporary tablespace TEMPORARY_DATA;
grant ADM_GRAD to UXXIAC with admin option;
grant ADM_HOMO to UXXIAC with admin option;
grant ADM_RCUP to UXXIAC with admin option;
grant ADM_READ to UXXIAC with admin option;
grant ADM_TRAS to UXXIAC with admin option;
grant ADM_TRIB to UXXIAC with admin option;
grant AGOOCU to UXXIAC with admin option;
alter user UXXIAC quota unlimited on UXXIAC_DAT_128K;
alter user UXXIAC quota unlimited on UXXIAC_DAT_128M;
alter user UXXIAC quota unlimited on UXXIAC_DAT_4M;
alter user UXXIAC quota unlimited on UXXIAC_DAT_LOBS_4M;
alter user UXXIAC quota unlimited on UXXIAC_INX_128K;
alter user UXXIAC quota unlimited on UXXIAC_INX_128M;
alter user UXXIAC quota unlimited on UXXIAC_INX_4M;
grant AGO_EST_MCAS to UXXIAC with admin option;
grant AGO_E_RECIB to UXXIAC with admin option;
grant AGO_F_AGRONO to UXXIAC with admin option;
grant AGO_F_ARTES to UXXIAC with admin option;
grant AGO_F_CIENCI to UXXIAC with admin option;
grant AGO_F_DERECH to UXXIAC with admin option;
grant AGO_F_ECONO to UXXIAC with admin option;
grant AGO_F_ENFERM to UXXIAC with admin option;
grant AGO_F_HUMANA to UXXIAC with admin option;
grant AGO_F_INGENI to UXXIAC with admin option;
grant AGO_F_MEDICI to UXXIAC with admin option;
grant AGO_F_MVETER to UXXIAC with admin option;
Nota: Cabe resaltar que al replicar el usuario la contraseña que tiene en el ambiente origen pasaría al ambiente destino.
Para Oracle 11G existe una variación en la consulta de la tabla de los usuarios, de resto los pasos son los mismos.
SQL> SELECT 'create user ' || username || ' identified by values ' || ''''
|| T.password || '''' || ' profile ' || profile || ' default tablespace '
|| default_tablespace || ' temporary tablespace ' || temporary_tablespace || ';'
FROM DBA_USERS u, sys.user$ T
WHERE u.USERNAME =T.NAME
and USERNAME = :1
UNION
select 'grant '|| privilege || ' to '|| grantee || decode(admin_option,'YES',' with admin option','')|| ';'
FROM DBA_SYS_PRIVS
WHERE GRANTEE=:1
UNION
select 'grant '|| privilege || ' on ' || owner || '.' || table_name ||' to '|| grantee || decode(grantable,'YES',' with grant option','')|| ';'
FROM DBA_TAB_PRIVS
WHERE GRANTEE=:1
UNION
select 'grant '|| granted_role || ' to '|| grantee || decode(admin_option,'YES',' with admin option','') || ';'
FROM DBA_ROLE_PRIVS
WHERE GRANTEE =:1
UNION
SELECT 'alter user ' || username || ' quota unlimited on ' || tablespace_name|| ';'
FROM DBA_TS_QUOTAS
WHERE USERNAME =:1;
4.4 SOBRE PERMISOS
La siguiente sentencia dinámica, permite generar el script para otorgar permisos sobre las tablas de un esquema a otro usuario de la base de datos. Se debe digitar el usuario al cual se le darán los permisos y de igual manera el usuario propietario de las tablas.
Para proporcionar o actualizar una contraseña para varios usuarios, o poder colocar como contraseña el mismo usuario se puede usar el siguiente pl/sql
DECLARE
CLAVE VARCHAR2(30);
CURSOR USUARIO IS
select usrusu
from tuib_usuari, all_users
where usrusu not in ('UXXIAC', 'SYSTEM','SYS','AGOMIGRA','GEN_INF','TECNICO1', 'W3SIADNA5', 'CAMPUS','SIA_US_DEU', 'SIA_US_WEB', 'SIA_US_FORE', 'SIA_USA_PAG', 'SIA_US_CAL', 'HORWEB', 'USRWS', 'BC4J_TT', 'AUTOMAT', 'ACTASWEB')
and username= usrusu
order by 1;
BEGIN
FOR USUARIO_R IN USUARIO LOOP
begin
--clave:= uib$uclv.ENCRYP('prueba01'); --clave para todos los usuarios manual
clave:= uib$uclv.ENCRYP(USUARIO_R.USRUSU); --coloca como clave el nombre del usuario
DBMS_OUTPUT.PUT_LINE(USUARIO_R.USRUSU||'/'||CLAVE);
EXECUTE IMMEDIATE('ALTER USER ' || USUARIO_R.USRUSU || ' IDENTIFIED BY ' || clave);
end;
END LOOP;
END;
/
Para proporcionar o actualizar las contraseñas masiva-mente con una contraseña aleatoria para cada usuario se ejecuta el siguiente cursor.
declare
cursor c_sus is select 'alter user '||username||' identified by ' ints from dba_users where username='prueba';
v_pw varchar2(20);
v_result varchar2(2000);
begin
for i in c_sus loop
SELECT CONCAT(dbms_random.string('L', 5),dbms_random.string('X', 5))
into v_pw
FROM dual;
dbms_output.put_line(i.ints||v_pw||';'); (Genera el listado)
--insert into nombre tabla (campo) values(i.ints||v_pw||';'); (inserta sobre una table temporal)
end loop;
commit;
end;
Conceder privilegios a un usuario sobre los tablespace.
SQL>GRANT UNLIMITED TABLESPACE TO <user>;
Para limpiar las sesiones que están conectadas en la base de datos y su estado es INACTIVO se ejecuta la siguiente sentencia (PL/SQL) Ejecutar con el usuario SYS
BEGIN
FOR R IN (SELECT SID, SERIAL# FROM SYS.V_$SESSION WHERE STATUS = 'INACTIVE' AND SCHEMANAME='AUTOMAT' )
LOOP
execute IMMEDIATE 'ALTER SYSTEM KILL SESSION ''' ||
R.SID || ',' || R.SERIAL# || '''';
END LOOP;
END;
/
Para validar que se mataron las sesiones se puede ejecutar la siguiente sentencia:
SQL> Select username, sid, serial#, status from v$session where STATUS = 'INACTIVE';
De igual manera, la siguiente sentencia dinámica, nos permite crear el script para matar todas las sesiones abiertas diferentes de el usuario SYS.
SQL>select 'ALTER SYSTEM KILL SESSION ''' || SID || ',' || SERIAL# ||''' IMMEDIATE;' FROM SYS.V_$SESSION WHERE SCHEMANAME NOT IN ('SYS') ;
La siguiente sentencia dinámica genera los scripts para eliminar o cancelar la sesiones desde el sistema operativo que presentan más de tres hora de ejecución y cuyo estado es “INACTIVO”
SQL>select 'kill -9 ' || P.SPID from v$session s, v$process p where paddr = addr and S.STATUS = 'INACTIVE' and S.TYPE not like 'BACKGROUND' and S.LOGON_TIME <= (SYSDATE - (240/1450)) ;
Para validar las últimas consultas SQL ejecutadas en Oracle y usuario que las ejecutó:
SQL>select distinct vs.sql_text, vs.sharable_mem, vs.persistent_mem, vs.runtime_mem, vs.sorts, vs.executions, vs.parse_calls, vs.module, vs.buffer_gets, vs.disk_reads, vs.version_count, vs.users_opening, vs.loads, to_char(to_date(vs.first_load_time, 'YYYY-MM-DD/HH24:MI:SS'),'MM/DD HH24:MI:SS') first_load_time, rawtohex(vs.address) address, vs.hash_value hash_value, rows_processed , vs.command_type, vs.parsing_user_id , OPTIMIZER_MODE , au.USERNAME parseuser from v$sqlarea vs , all_users au where (parsing_user_id != 0) AND (au.user_id(+)=vs.parsing_user_id) and (executions >= 1) order by buffer_gets/executions desc
4.5 CAMBIAR EL TABLESPACE POR DEFAULT Y TEMPORAL PARA UN USUARIO
Para cambiar el tablespace por Default de un usuario ya existente se ejecuta la siguiente sentencia.
SQL>ALTER USER "TECNICO1" DEFAULT TABLESPACE "SIA_DATOS";
Para cambiar el tablespace temporal de un usuario ya existente se ejecuta la siguiente sentencia.
SQL>ALTER USER "TECNICO1" TEMPORARY TABLESPACE "TEMPORARY_DATA";
Ejemplo de usuarios con tablespace temporal TEMP y no son propios del motor de Oracle y están en estado OPEN.
SQL>select 'ALTER USER "' || USERNAME ||'" TEMPORARY TABLESPACE "TEMPORARY_DATA";'from dba_users where TEMPORARY_TABLESPACE ='TEMP' AND ACCOUNT_STATUS ='OPEN' AND DEFAULT_TABLESPACE NOT IN ('SYSAUX');
Muchas gracias Mundo Oracle. Excelente material para DBA de Oracle, para conocer las tablas de control y monitoreo de instancias.
ResponderEliminarExcelente articulo muchas gracias
ResponderEliminar