Friday, 20 de November del 2009
Publicando un blog en Orafaq
El proyecto en el que actualmente trabajo dedico una pequeña parte a la administración de Oracle. Además este año he finalizado la certificación a OCP 10g (estaba certificado en OCA 9 desde 2004), y todas estas experiencias me están generando contenido suficiente para publicarlo en un blog. Lo estoy haciendo en www.orafaq.com, por ser una web especializada en Oracle. El blog se puede leer aquí: http://www.orafaq.com/blog/16208.
Procedimientos almacenados y extendidos no documentados en SQL Server 2005
XP_FileExist
Su uso:
Devuelve 3 columnas con 0 ó 1: File Exists, File is a Directory, Parent Directory Exists.
Ejemplo:
SP_MSForEachDb
Ejecuta una cadena de texto como un comando por cada base de datos en el servidor. Puede ser útil para buscar una tabla de la que no sabes en qué base de datos está, por ejemplo:
También es útil para lanzar la misma tarea de mantenimiento en todas las bases de datos:
SP_MSForEachTable
Funcionamiento parecido a sp_msforeachdb, pero por cada tabla. Con este ejemplo vaciamos todas las tablas dejando sólo la estructura.
SP_who2
Algunos datos iguales que los de sp_who, con otros nombres de columnas, sin columna ecid y con las columnas cputime, diskio, lastbatch y programname añadidas.
sp_MSdependencies
Sirve para determinar las dependencias de un objeto en la base de datos. Su uso:
Todas las variables son opcionales, con:
obtenemos una descripción breve de las opciones.
Su uso:
EXEC xp_fileexist[, OUTPUT]
Devuelve 3 columnas con 0 ó 1: File Exists, File is a Directory, Parent Directory Exists.
Ejemplo:
exec master.dbo.xp_fileexist 'C:\temp'
SP_MSForEachDb
Ejecuta una cadena de texto como un comando por cada base de datos en el servidor. Puede ser útil para buscar una tabla de la que no sabes en qué base de datos está, por ejemplo:
exec dbo.sp_MSforeachdb 'select ''?'', * from [?].INFORMATION_SCHEMA.TABLES where table_name like ''autores'' '
También es útil para lanzar la misma tarea de mantenimiento en todas las bases de datos:
exec dbo.sp_MSforeachdb 'DBCC SHRINKDATABASE(N''?'' )'
SP_MSForEachTable
Funcionamiento parecido a sp_msforeachdb, pero por cada tabla. Con este ejemplo vaciamos todas las tablas dejando sólo la estructura.
exec dbo.sp_msforeachtable 'delete test.dbo.[?]'
SP_who2
Algunos datos iguales que los de sp_who, con otros nombres de columnas, sin columna ecid y con las columnas cputime, diskio, lastbatch y programname añadidas.
sp_MSdependencies
Sirve para determinar las dependencias de un objeto en la base de datos. Su uso:
exec dbo.sp_MSdependencies [
Todas las variables son opcionales, con:
exec sp_msdependencies '?'
obtenemos una descripción breve de las opciones.
Friday, 6 de November del 2009
Prevenir el uso de "SELECT * FROM..."
Con esta técnica evitamos que los desarrolladores utilicen SELECT * FROM en su código.
Por cada tabla, añadir un campo "nousesasterisco", al que más adelante le denegaremos el acceso de lectura:
Denegamos el SELECT en esa columna por cada tabla:
Tras asignar el rol "rol_deniega" a los usuarios de la aplicación, les aparecerá este mensaje:
Por cada tabla, añadir un campo "nousesasterisco", al que más adelante le denegaremos el acceso de lectura:
ALTER TABLE dbo.[tabla] ADD
nousesasterisco char(1) NULL
GO
[...]
Denegamos el SELECT en esa columna por cada tabla:
DENY SELECT ON OBJECT:: dbo.[tabla](nousesasterisco) TO [rol_deniega];
Tras asignar el rol "rol_deniega" a los usuarios de la aplicación, les aparecerá este mensaje:
SELECT * FROM dbo.tabla;
--Result
Msg 230, Level 14, State 1, Line 1
The SELECT permission was denied on the column 'nousesasterisco' of the object 'tabla', database 'Pruebas', schema 'dbo'.
Tuesday, 13 de October del 2009
Monitorizar SQL Server en 8 pasos (Paso 8/8)
Paso 8: Consolidación de los datos
Los scripts del 8.1 al 8.3 consolidan los resultados del PerfMon en un esquema de estrella. Luego, se puede construir un cubo OLAP sobre esto.
Para limitar las líneas de código, aquí sólo están las dimensiones de objeto y máquina. Una configuración similar hará falta para Fecha, Instancia y dimensiones del contador.
Script 8.1 crea las tabals
Script 8.2 refresca todas las dimensiones
Para recuperar espacio en disco, puedes truncar la tabla CounterData después de haber hecho la carga.
Los scripts del 8.1 al 8.3 consolidan los resultados del PerfMon en un esquema de estrella. Luego, se puede construir un cubo OLAP sobre esto.
Para limitar las líneas de código, aquí sólo están las dimensiones de objeto y máquina. Una configuración similar hará falta para Fecha, Instancia y dimensiones del contador.
Script 8.1 crea las tabals
--Script 8.1
CREATE TABLE dbo.Fact_Perfmon (
PerfMonID int IDENTITY (1, 1) NOT NULL ,
DateID int NULL ,
MachineID int NULL ,
ObjectID int NULL ,
InstanceID int NULL ,
CounterID int NULL ,
CounterValue float NULL
) ON [PRIMARY]
GO
CREATE TABLE dbo.Dim_Machine (
MachineID int IDENTITY (1, 1) NOT NULL ,
MachineName varchar (100) NULL
) ON [PRIMARY]
GO
CREATE TABLE dbo.Dim_Object (
ObjectID int IDENTITY (1, 1) NOT NULL ,
ObjectName varchar (100) NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.Fact_Perfmon ADD
CONSTRAINT PK_Fact_Perfmon PRIMARY KEY CLUSTERED
(
PerfMonID
) ON [PRIMARY]
GO
ALTER TABLE dbo.Dim_Machine ADD
CONSTRAINT PK_Dim_Machine PRIMARY KEY CLUSTERED
(
MachineID
) ON [PRIMARY]
GO
ALTER TABLE dbo.Dim_Object ADD
CONSTRAINT PK_Dim_Object PRIMARY KEY CLUSTERED
(
ObjectID
) ON [PRIMARY]
GO
ALTER TABLE dbo.Fact_Perfmon ADD
CONSTRAINT FK_Fact_Perfmon_Dim_Machine FOREIGN KEY
(
MachineID
) REFERENCES dbo.Dim_Machine (
MachineID
),
CONSTRAINT FK_Fact_Perfmon_Dim_Object FOREIGN KEY
(
ObjectID
) REFERENCES dbo.Dim_Object (
ObjectID
)
GO
Script 8.2 refresca todas las dimensiones
--Script 8.2
INSERT Dim_Machine (MachineName) SELECT DISTINCT MachineName FROM MonitoringDB.DBO.CounterDetails WHERE MachineName not in (SELECT DISTINCT MachineName from Dim_Machine)
INSERT Dim_Object (ObjectName) SELECT DISTINCT ObjectName FROM MonitoringDB.DBO.CounterDetails WHERE ObjectName not in (SELECT DISTINCT ObjectName from Dim_Object)
Script 8.3 refreshes your fact table
--Script 8.3
SELECT dd.DateID
, dm.MachineID
, do.ObjectID
, dc.CounterID
, di.InstanceID
, dat.CounterValue
FROM MonitoringDB.DBO.CounterData Dat
INNER JOIN MonitoringDB.DBO.CounterDetails Det ON Dat.CounterID = Det.CounterID
INNER JOIN Dim_Date dd ON DateValue = Dat.CounterDateTime
INNER JOIN Dim_Machine dm ON dm.MachineName = Det.MachineName
INNER JOIN Dim_Object do ON do.ObjectName=Det.ObjectName
INNER JOIN Dim_Counter dc ON dc.CounterName= Det.CounterName
INNER JOIN Dim_Instance di ON di.InstanceName = Det.InstanceName
Para recuperar espacio en disco, puedes truncar la tabla CounterData después de haber hecho la carga.
Monitorizar SQL Server en 8 pasos (Paso 7/8)
Paso 7: El panel de control digital
Si quieres más flexibilidad para manejar las alertas, también puedes almacenar valores de umbrales en una tabla de la base de datos. De esta manera un script externo y/o un interfaz gráfico de usuario puede comparar los valores almacenados con los umbrales y generar todo tipo de mecanismos de alerta. Esto es más potestad de los desarrolladores, pero aunque no lo seas, no es algo difícil.
Si quieres más flexibilidad para manejar las alertas, también puedes almacenar valores de umbrales en una tabla de la base de datos. De esta manera un script externo y/o un interfaz gráfico de usuario puede comparar los valores almacenados con los umbrales y generar todo tipo de mecanismos de alerta. Esto es más potestad de los desarrolladores, pero aunque no lo seas, no es algo difícil.
Monitorizar SQL Server en 8 pasos (Paso 6/8)
Paso 6: Alertas
Que siempre cuente nuestra consigna "que sea simple" para nuestro diseño. Usa los mecanismos del PerfMon para generar alertas y para registrar eventos en el log de aplicaciones, donde una herramienta centralizada podría capturarlos y tratarlos según la política de la empresa: notificar a la sala del CPD, enviar un SMS al móvil de guardia, etc...
Si tu empresa no dispone de ningún sistema de gestión de alertas, suscríbete a mi blog porque hablaré de ello en unos días.
Que siempre cuente nuestra consigna "que sea simple" para nuestro diseño. Usa los mecanismos del PerfMon para generar alertas y para registrar eventos en el log de aplicaciones, donde una herramienta centralizada podría capturarlos y tratarlos según la política de la empresa: notificar a la sala del CPD, enviar un SMS al móvil de guardia, etc...
Si tu empresa no dispone de ningún sistema de gestión de alertas, suscríbete a mi blog porque hablaré de ello en unos días.
Monitorizar SQL Server en 8 pasos (Paso 5/8)
Paso 5: Configurar la visualización en Excel
Todos tus datos están ahora almacenados en tablas de SQL Server y los interfaces los has creado utilizando vistas. Ahora puedes visualizarlos como gráficos en cualquier herramienta, como por ejemplo, Excel. Si te sientes más agusto con Reporting Services, hazlo así. Reporting services es una forma más profesional, pero por simplicidad, vamos a usar excel aquí. Además, la configuración no va a ser muy distinta.
Todos estos pasos los tienes que hacer en la máquina cliente donde quieres ver tu panel de monitorización (en inglés dashboard, por el parecido al salpicadero de un coche).
Paso 5.1: Crear un DSN de sistema para hacer la conexión a la base de datos de monitorización.
Paso 5.2: Clic en en Data - Import External Data - New Database Query.
Paso 5.3: Selecciona el origen de datos que has creado en el paso 5.1.
Paso 5.4: Ves por todos los pasos y une las diferentes vistas utilizando el campo CounterDateTime.
Paso 5.5: Carga los resultados en una nueva hoja.
Paso 5.6: Clic en Data - Import External Data – Data Range Properties y selecciona Refresh every 1 minute.
Paso 5.6: Abre el asistente para gráficas (chart wizard).
Todos tus datos están ahora almacenados en tablas de SQL Server y los interfaces los has creado utilizando vistas. Ahora puedes visualizarlos como gráficos en cualquier herramienta, como por ejemplo, Excel. Si te sientes más agusto con Reporting Services, hazlo así. Reporting services es una forma más profesional, pero por simplicidad, vamos a usar excel aquí. Además, la configuración no va a ser muy distinta.
Todos estos pasos los tienes que hacer en la máquina cliente donde quieres ver tu panel de monitorización (en inglés dashboard, por el parecido al salpicadero de un coche).
Paso 5.1: Crear un DSN de sistema para hacer la conexión a la base de datos de monitorización.
Paso 5.2: Clic en en Data - Import External Data - New Database Query.
Paso 5.3: Selecciona el origen de datos que has creado en el paso 5.1.
Paso 5.4: Ves por todos los pasos y une las diferentes vistas utilizando el campo CounterDateTime.
Paso 5.5: Carga los resultados en una nueva hoja.
Paso 5.6: Clic en Data - Import External Data – Data Range Properties y selecciona Refresh every 1 minute.
Paso 5.6: Abre el asistente para gráficas (chart wizard).
Monitorizar SQL Server en 8 pasos (Paso 4/8)
Paso 4: Profiler
La dificultad para recopilar datos del Profiler es encontrar un proceso que filtre información útil de la columna textdata. Por ejemplo: quieres investigar la duración media de un procedimiento almacenado, pero no puedes realizar una agrupación a menos que te deshagas de todos los parámetros que también se almacenan en textdata.
El script 4.1 puede ser un primer paso en tu búsqueda a cómo filtrar y consolidar los datos del SQL Profiler para que te sean útiles. Esta función depende del uso sp_executesdql, por lo que podrías necesitar personalizarlo según tus condiciones.
La dificultad para recopilar datos del Profiler es encontrar un proceso que filtre información útil de la columna textdata. Por ejemplo: quieres investigar la duración media de un procedimiento almacenado, pero no puedes realizar una agrupación a menos que te deshagas de todos los parámetros que también se almacenan en textdata.
El script 4.1 puede ser un primer paso en tu búsqueda a cómo filtrar y consolidar los datos del SQL Profiler para que te sean útiles. Esta función depende del uso sp_executesdql, por lo que podrías necesitar personalizarlo según tus condiciones.
--Script 4.1
CREATE FUNCTION fn_getSPfromTextdata (@textdata VARCHAR(4000))
RETURNS VARCHAR (4000)
AS
BEGIN
DECLARE @ret VARCHAR (4000)
SET @ret = ''
IF SUBSTRING(@textdata, 1, 18) = 'exec sp_executesql' AND SUBSTRING (@textdata, 22, 6) = 'INSERT'
BEGIN
SELECT @ret = SUBSTRING(@textdata, 22, PATINDEX ('% (%', SUBSTRING(@textdata, 6, LEN(@textdata) - 5))-17)
END
ELSE IF SUBSTRING(@textdata, 1, 18) = 'exec sp_executesql' AND SUBSTRING(@textdata, 22, 6) = 'UPDATE'
BEGIN
SELECT @ret = SUBSTRING(@textdata, 22, PATINDEX ('% SET %', SUBSTRING(@textdata, 6, LEN(@textdata) - 5))-17)
END
ELSE IF SUBSTRING(@textdata, 1, 18) = 'exec sp_executesql' AND SUBSTRING(@textdata, 22, 6) = 'DELETE'
BEGIN
SELECT @ret = SUBSTRING(@textdata, 22, PATINDEX ('% WHERE %', substring (@textdata, 6, LEN(@textdata) - 5))-17)
END
ELSE IF substring(@textdata, 1, 4) = 'exec'
BEGIN
SELECT @ret = SUBSTRING(@textdata, 6, PATINDEX ('% %', SUBSTRING(@textdata, 6, LEN(@textdata) - 5)))
END
RETURN @ret
END
Monitorizar SQL Server en 8 pasos (Paso 3/8)
Paso 3. Configurar la base de datos de monitorización
Una vez configurado el PerfMon según las indicaciones del artículo anterior, un conjunto de tablas se habrán creado automáticamente. Si quieres utilizar esas tablas para consultarlas, deberás crearles índices.
El script 3.1 ajustará tu base de datos de monitorización para que las consultas vayan más rápido.
El script 3.2 crea vistas para cada contador que puedes monitorizar. Estos scripts se usarán en uno de los pasos siguientes en los que se trata la visualización. El script 3.2 recopilará los 100 valores más recientes de conexiones de usuario.
El script 3.3 recopila los 100 valores más recientes del uso total de CPU
Más tarde, estas dos vistas se unirán en un gráfico.
Una vez configurado el PerfMon según las indicaciones del artículo anterior, un conjunto de tablas se habrán creado automáticamente. Si quieres utilizar esas tablas para consultarlas, deberás crearles índices.
El script 3.1 ajustará tu base de datos de monitorización para que las consultas vayan más rápido.
--Script 3.1
CREATE CLUSTERED INDEX ix_1 ON dbo.CounterData(CounterID) ON [PRIMARY]
CREATE UNIQUE INDEX ix_2 ON dbo.CounterData(RecordIndex, CounterID) ON [PRIMARY]
CREATE INDEX ix_1 ON dbo.CounterDetails(CounterName) ON [PRIMARY]
CREATE INDEX ix_2 ON dbo.CounterDetails(ObjectName) ON [PRIMARY]
El script 3.2 crea vistas para cada contador que puedes monitorizar. Estos scripts se usarán en uno de los pasos siguientes en los que se trata la visualización. El script 3.2 recopilará los 100 valores más recientes de conexiones de usuario.
--Script 3.2
CREATE VIEW VW_User_Connections
AS
SELECT TOP 100 * FROM
(
SELECT TOP 100 data.CounterDateTime
, data.CounterValue AS [User connections]
FROM CounterData data WITH (NOLOCK)
INNER JOIN CounterDetails details WITH (NOLOCK)
ON data.CounterID = details.CounterID
WHERE details.CounterName = 'User connections'
ORDER BY 1 desc
) AS t
ORDER BY t.CounterDateTime
El script 3.3 recopila los 100 valores más recientes del uso total de CPU
--Script 3.3
CREATE VIEW dbo.VW_CPU_total_user_time
AS
SELECT TOP 100 * FROM
(
SELECT TOP 100 data.CounterDateTime
, data.CounterValue AS [CPU Total User Time]
FROM dbo.CounterData data WITH (NOLOCK)
INNER JOIN dbo.CounterDetails details WITH (NOLOCK)
ON data.CounterID = details.CounterID
WHERE details.ObjectName = 'Processor'
AND details.CounterName = '% User Time'
AND details.InstanceName = '_Total'
ORDER BY 1 DESC
) AS t
ORDER BY t.CounterDateTime
Más tarde, estas dos vistas se unirán en un gráfico.
Monitorizar SQL Server en 8 pasos (Paso 2/8)
Paso 2: Configurar PerfMon
Los siguientes pasos cubren la configuración del Monitor de Rendimiento (perfmon). Esta configuración está específicamente diseñada para poder usar los datos en una sección posterior.
Paso 2.1. Crear un DSN de tu servidor de producción a tu servidor de monitorización.
Paso 2.2. Cambiar el inicio de sesión del servicio "Performance Logs and Alerts" por una cuenta de dominio que:
- Sea miembro del grupo de administradores local del servidor de producción
- Sea miembro del role db_owner de la base de datos a monitorizar
Paso 2.2. Iniciar Perfmon:
- Añadir un nuevo contador. En la pestaña General, clic en Add Objects para añadir contadores de los siguientes objetos:
Memory, Physical Disk, Process, Processor, SQLServer:Access Methods, SQLServer:Buffer Manager, SQLServer:Cache Manager, SQLServer:Databases, SQLServer:General Statistics, SQLServer:Latches, SQLServer:Locks, SQLServer:Memory Manager, SQLServer:SQL Statistics, SQLServer:SQL Settable.
Cambiar el intervalo a 1 minuto.
- En la pestaña Log Files. Cambiar Log file type a = SQL database. Elige el DSN que creaste en el paso 2.1. En la pstaña Schedule limita la ventana adecuada con las opciones de Start y Stop.
Cuando un fichero de log se cierra, ejecutar este comando: use this to automatically run your consolidation and cleanup script.
Paso 2.4. Inicia tu contador.
Los siguientes pasos cubren la configuración del Monitor de Rendimiento (perfmon). Esta configuración está específicamente diseñada para poder usar los datos en una sección posterior.
Paso 2.1. Crear un DSN de tu servidor de producción a tu servidor de monitorización.
Paso 2.2. Cambiar el inicio de sesión del servicio "Performance Logs and Alerts" por una cuenta de dominio que:
- Sea miembro del grupo de administradores local del servidor de producción
- Sea miembro del role db_owner de la base de datos a monitorizar
Paso 2.2. Iniciar Perfmon:
- Añadir un nuevo contador. En la pestaña General, clic en Add Objects para añadir contadores de los siguientes objetos:
Memory, Physical Disk, Process, Processor, SQLServer:Access Methods, SQLServer:Buffer Manager, SQLServer:Cache Manager, SQLServer:Databases, SQLServer:General Statistics, SQLServer:Latches, SQLServer:Locks, SQLServer:Memory Manager, SQLServer:SQL Statistics, SQLServer:SQL Settable.
Cambiar el intervalo a 1 minuto.
- En la pestaña Log Files. Cambiar Log file type a = SQL database. Elige el DSN que creaste en el paso 2.1. En la pstaña Schedule limita la ventana adecuada con las opciones de Start y Stop.
Cuando un fichero de log se cierra, ejecutar este comando: use this to automatically run your consolidation and cleanup script.
Paso 2.4. Inicia tu contador.
Monitorizar SQL Server en 8 pasos (Paso 1/8)
Paso 1: Master.dbo.sysperfinfo
La tabla de sistema master.dbo.sysperfinfo contiene todos los contadores de rendimiento interno de SQL server que se ven con PerfMon. Los pros y los contras de utilizar sysperfinfo son:
Pros:
- Está disponible online sin la necesidad de configurar un entorno de monitorización. Te puedes crear un simple script para recopilar los datos de rendimiento.
- Al contrario que Perfmon, no son necesarios permisos a nivel de Sistema Operatifo. Sólo necesitas permiso de lectura a la base de datos master. Los permisos a nivel de sistema operativo a veces son difíciles de conseguir cuando los servicios de una empresa están "outsourceados".
Contras:
- Sysperfinfo abarca sólo los contadores de SQL Server. No están los de CPU, memoria o estadísticas de I/O de disco disponibles.
El script 1.1 te enseña cómo recopilar los datos del contador 'Log Bytes Flushed/sec' durante 3 minutos tomando muestras a intervalos de 5 segundos.
El script 1.2 crea todas las tablas necesarias para un entorno de ensayo:
El script 1.3 crea el procedimiento almacenado para registrar un nuevo valor
El script 1.4 añade un registro en la tabla tbl_log_sysperfinfo. Este script se debería ejecutar en un trabajo programado de SQL Server, una vez por minuto.
La tabla de sistema master.dbo.sysperfinfo contiene todos los contadores de rendimiento interno de SQL server que se ven con PerfMon. Los pros y los contras de utilizar sysperfinfo son:
Pros:
- Está disponible online sin la necesidad de configurar un entorno de monitorización. Te puedes crear un simple script para recopilar los datos de rendimiento.
- Al contrario que Perfmon, no son necesarios permisos a nivel de Sistema Operatifo. Sólo necesitas permiso de lectura a la base de datos master. Los permisos a nivel de sistema operativo a veces son difíciles de conseguir cuando los servicios de una empresa están "outsourceados".
Contras:
- Sysperfinfo abarca sólo los contadores de SQL Server. No están los de CPU, memoria o estadísticas de I/O de disco disponibles.
El script 1.1 te enseña cómo recopilar los datos del contador 'Log Bytes Flushed/sec' durante 3 minutos tomando muestras a intervalos de 5 segundos.
--Script 1.1:
CREATE TABLE #Writes(ts DATETIME, LogBytesFlushes BIGINT)
DECLARE @lbf INT, @lbfold INT, @starttime DATETIME
SET @starttime = getdate()
SELECT @lbfold=cntr_value FROM master..sysperfinfo WHERE counter_name = 'Log Bytes Flushed/sec' AND instance_name = 'test'
WAITFOR DELAY '00:00:05'
WHILE getdate() < dateadd(mi,3,@starttime)
BEGIN
SELECT @lbf=cntr_value FROM master..sysperfinfo WHERE counter_name = 'Log Bytes Flushed/sec' AND instance_name = 'test'
INSERT INTO #writes (ts,LogBytesFlushes) VALUES (getdate(), @lbf - @lbfold)
SET @lbfold = @lbf
WAITFOR DELAY '00:00:05'
END
SELECT * FROM #writes
El script 1.2 crea todas las tablas necesarias para un entorno de ensayo:
--Script 1.2:
CREATE TABLE dbo.wt_current_sysperfinfo (
InsertTime DATETIME NULL CONSTRAINT DF_wt_current_sysperfinfo_InsertTime DEFAULT (getdate()),
object_name NCHAR (128) NOT NULL ,
counter_name NCHAR (128) NOT NULL ,
instance_name NCHAR (128) NULL ,
cntr_value INT NOT NULL ,
cntr_type INT NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE dbo.wt_previous_sysperfinfo (
InsertTime DATETIME NULL CONSTRAINT DF_wt_previous_sysperfinfo_InsertTime DEFAULT (getdate()),
object_name NCHAR (128) NOT NULL ,
counter_name NCHAR (128) NOT NULL ,
instance_name NCHAR (128) NULL ,
cntr_value INT NOT NULL ,
cntr_type INT NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE dbo.tbl_log_sysperfinfo(
InsertTime datetime NULL CONSTRAINT DF_tbl_log_sysperfinfo_InsertTime DEFAULT (getdate()),
[Batch Requests/sec] FLOAT NULL,
[Buffer cache hit ratio] FLOAT NULL,
[Page life expectancy] FLOAT NULL,
[User Connections] INT NULL
) ON [PRIMARY]
GO
El script 1.3 crea el procedimiento almacenado para registrar un nuevo valor
--Script 1.3:
CREATE PROC usp_refresh_log_sysperfinfo
as
-- declarations
DECLARE @previous_inserttime DATETIME, @current_inserttime DATETIME, @elapseTimeSec INT
DECLARE @previous_batch_request_sec FLOAT, @current_batch_request_sec FLOAT, @batch_request_sec FLOAT
DECLARE @current_Buffer_cache_hit_ratio FLOAT, @current_Buffer_cache_hit_ratio_base FLOAT, @Buffer_cache_hit_ratio FLOAT
DECLARE @Page_life_expectancy INT, @User_Connections INT
-- calculate elapse time
SELECT TOP 1 @current_inserttime = InsertTime FROM dbo.wt_current_sysperfinfo (NOLOCK)
SELECT TOP 1 @previous_inserttime = InsertTime FROM dbo.wt_previous_sysperfinfo (NOLOCK)
SELECT @elapseTimeSec = datediff(s, @previous_inserttime, @current_inserttime)
-- calculate @batch_request_sec
SELECT @current_batch_request_sec = cntr_value FROM dbo.wt_current_sysperfinfo (NOLOCK) WHERE counter_name = 'Batch Requests/sec'
SELECT @previous_batch_request_sec = cntr_value FROM dbo.wt_previous_sysperfinfo (NOLOCK) where counter_name = 'Batch Requests/sec'
SELECT @batch_request_sec = (@current_batch_request_sec - @previous_batch_request_sec) / @elapseTimeSec
-- calculate Buffer cache hit ratio
SELECT @current_Buffer_cache_hit_ratio = cntr_value FROM dbo.wt_current_sysperfinfo (NOLOCK) WHERE counter_name = 'Buffer cache hit ratio'
SELECT @current_Buffer_cache_hit_ratio_base = cntr_value FROM dbo.wt_current_sysperfinfo (NOLOCK) WHERE counter_name = 'Buffer cache hit ratio base'
SELECT @Buffer_cache_hit_ratio = @current_Buffer_cache_hit_ratio / @current_Buffer_cache_hit_ratio_base * 100.00
-- calculate Page life expectancy
SELECT @Page_life_expectancy = cntr_value FROM dbo.wt_current_sysperfinfo (NOLOCK) WHERE counter_name = 'Page life expectancy'
-- calculate User Connections
SELECT @User_Connections = cntr_value FROM dbo.wt_current_sysperfinfo (NOLOCK) WHERE counter_name = 'User Connections'
INSERT INTO dbo.tbl_log_sysperfinfo
(
[Batch Requests/sec],
[Buffer cache hit ratio],
[Page life expectancy],
[User Connections]
)
VALUES
(
@batch_request_sec,
@Buffer_cache_hit_ratio,
@Page_life_expectancy,
@User_Connections
)
El script 1.4 añade un registro en la tabla tbl_log_sysperfinfo. Este script se debería ejecutar en un trabajo programado de SQL Server, una vez por minuto.
--Script 1.4:
DROP TABLE dbo.wt_previous_sysperfinfo
SELECT * INTO wt_previous_sysperfinfo FROM dbo.wt_current_sysperfinfo
TRUNCATE TABLE dbo.wt_current_sysperfinfo
INSERT wt_current_sysperfinfo (object_name, counter_name, instance_name, cntr_value, cntr_type)
SELECT object_name, counter_name, instance_name, cntr_value, cntr_type FROM master.dbo.sysperfinfo
EXEC usp_refresh_log_sysperfinfo
Saturday, 16 de September del 2006
Romper passwords de Oracle.
Voy a escribir un artículo que a más de un dba Oracle le dará qué pensar, y si es algo proactivo, le hará realizar algún que otro cambio en las bases de datos que administra.
à ƒómo de fácil pensáis que es obtener la contraseña del usuario SYSTEM o cualquier otro usuario?
Os doy la respuesta: muy fácil.
à ˆay alguna manera de evitarlo?
Os contaré las ideas que se me han ocurrido. Pero cualquier otra que podáis aportar seguro que es bien recibida.
Introducciéndonos...
Bueno, antes de continuar quería comentar que no he encontrado ninguna información sobre este tema en castellano, y la que hay en inglés está muy dispersa.
Oracle almacena las claves en la tabla SYS.USER$, en la columna PASSWORD, utilizando un algoritmo que es una variación del estándar MD5. El algoritmo de cifrado es unidireccional, lo que significa que no se puede descifrar la clave tomando la cadena almacenada en la columna PASSWORD.
Podéis visualizar el nombre de usuario con su clave cifrada con cualquiera de estas consultas:
SE LECT NAME,PASSWORD from SYS.USER$; ó SELEC T NAME,PASSWORD from DBA_USERS;
Según leí en las news en comp.databases.oracle, más concretamente en este mensaje de 1993, se venía a decir que Oracle para generar las claves lo que hace es convertir el nombre de usuario y la password que se le da a mayúsculas, por lo que la clave cifrada será la misma si, por ejemplo, tu contraseña es "tiger" ó "Tiger" ó "TiGEr" y a partir de ahí aplica un proceso a la cadena resultante que para el tema que nos ocupa no es lo más importante.
En el mensaje al que hago referencia también habla en el punto 3 de que una de las metas cuando se diseñó el algoritmo de cifrado debía ser que para distintos usuarios con la misma password, la cadena cifrada resultante debe ser distinta:
"3. If different users have the same password, then the one-way
hash value (encrypted value) for the passwords will be different."
Esto lo consiguieron de una manera muy simple, que consiste en hacer que lo que se cifra no es sólamente la password, sino una concatenación del nombre de usuario + password, previamente convertido a mayúsculas. La forma en que se demuestra esto es la siguiente:
Si tenemos un usuario 'ARTURO' con contraseña 'Secreto', su cadena almacenada en la columna PASSWORD se cifrará a partir de 'ARTURO'+'SECRETO'.
Si tenemos un usuario 'ART' con contraseña 'Urosecreto', su cadena almacenada en la columna PASSWORD se cifrará a partir de 'ART'+'UROSECRETO'.
Con la siguiente query comprobaremos que la clave cifrada es la misma:
SQL> selec t username,password from dba_users where username LIKE 'ART%'
USERNAME PASSWORD
-- ---------------------------- ------------------------------
ARTURO 621V3E7638423350
ART 621V3E7638423350
Esta forma de cifrar la clave también significa que la clave cifrade del usuario 'SCOTT' con la contraseña 'TIGER' será 'F894844C34402B67' en todas las instalaciones de Oracle del mundo mundial.
Como ya he dicho, el algoritmo es unidireccional así que olvidaros de intentar descifrar la clave. Pero con lo que acabamos de ver, y sabiendo que ya hay programas que pueden cifrar cadenas usando el algoritmo MD5 modificado que usa Oracle, vemos que sólo con tener el nombre de usuario y la cadena de la columna PASSWORD es posible mediante comparación, por fuerza bruta o ataque de diccionario, obtener la clave.
Un ejemplo práctico de cómo romper la clave
La utilidad que encontré para "romper" claves de Oracle se llama orabf.exe, que se puede descargar con su manual directamente de aquí http://static.natalian.org/2005-11-27/orabf-v0.7.4.zip.
Esta primera prueba está hecha con el usuario SCOTT, aunque resulta inútil porque antes de empezar comprueba las passwords por defecto:
C:>orabf.exe F894844C34402B67:SCOTT -c 3
orabf v0.7.5, (C)2005 orm(at)toolcrypt.org---------------------------------------
Trying default passwords...
password found: SCOTT:TIGER
En esta segunda el usuario SCOTT tiene otra password:
orabf.exe 225E25B9A5319105:SCOTT -c 3
orabf v0.7.5, (C)2005 orm(at)toolcrypt.org---------------------------------------
Trying default passwords...done
Starting brute force session using charset:
0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ
press 'q' to quit. any other key to see status
password found: SCOTT:
51297999 passwords tried. elapsed time 00:00:44. t/s:1153610
Si a alguien le interesa mucho cómo romper la clave puede investigar más el manual de este programa. Nosotros ya hemos visto que es posible obtener las contraseñas, así que nos centraremos en cómo evitar que puedan hacerlo con nuestras bases de datos.
A proteger la base de datos...
Ahora mismo no tengo demasiado tiempo para dar explicaciones de cada una de las medidas a tomar. Iré nombrando las que se me ocurren:
1.- Estaremos protegidos siempre y cuando nadie tenga acceso a
2.- Nunca utilizar claves como "chupete", "temporal", "madrid", etc, pues son más vulnerables a ataques por diccionario.
3.- Hemos visto en la prueba de arriba, que con una ataque de fuerza bruta se comprueban aproximadamente 1 millón de contraseñas por segundo. Esto significa que tardaría menos de una semana en hacer todas las combinaciones para una contraseña de 8 caracteres y sólo unas horas si por ejemplo, repartiéramos el trabajo en 10 pcs. Por lo que el tercer consejo es: usar siempre contraseñas muy largas y cambiarlas cada pocos días.
4.- No repetir contraseñas.
A todo esto sólo añadir una cosa. Haber encontrado esta debilidad a Oracle no significa que sea peor que otra base de datos. Como habéis visto, con poco esfuerzo adicional en la administración no significará un problema. Por ejemplo SQL Server, que es otra base de datos que creo conocer bastante bien, tiene maneras más variadas de conseguir superacceso, o como lo queráis llamar, a su base de datos. Empezando porque sólo se puede instalar sobre Windows, que ya supone un problema de seguridad para todo lo que ahí se encuentre, y más aún cuando pertenece a un dominio o se tiene acceso físico a la máquina. Pero sobre esto no escribiré nada porque ya hay bastante información en español en Internet.
Wednesday, 8 de February del 2006
Versión gratuita de DB2
Gracias a las noticias de programacion.com me entero que IBM ha sacado una versión gratuita de su base de datos DB2, para competir con las versiones gratuitas de Oracle y Microsoft SQL Server. Le han dado el nombre de DB2 Express-C y se puede descargar libremente desde la web de IBM.
Tuve el placer de conocer DB2 en un proyecto de 5 meses. Después de pegarme mucho con DB2 le acabé cogiendo la lógica, y con un pequeño manual de comandos que me hice fui capaz de ir tirando.
Para alguien que conozca alguna base de datos, no le costará arrancar con las tareas básicas: crear tablespaces, usuarios, dar permisos, ver procesos... Prometo publicar el documento de los comandos.
Otras ventajas que tiene esta versión de DB2 es que no tiene ninguna limitación de usuarios, es multiplataforma en sistemas de 32 y 64 bits y soporta hasta 4 Gb de memoria y multiprocesador.
Alguien se anima a usarlo?
Tuve el placer de conocer DB2 en un proyecto de 5 meses. Después de pegarme mucho con DB2 le acabé cogiendo la lógica, y con un pequeño manual de comandos que me hice fui capaz de ir tirando.
Para alguien que conozca alguna base de datos, no le costará arrancar con las tareas básicas: crear tablespaces, usuarios, dar permisos, ver procesos... Prometo publicar el documento de los comandos.
Otras ventajas que tiene esta versión de DB2 es que no tiene ninguna limitación de usuarios, es multiplataforma en sistemas de 32 y 64 bits y soporta hasta 4 Gb de memoria y multiprocesador.
Alguien se anima a usarlo?
Wednesday, 17 de August del 2005
Solucionar el problema de usuarios huérfanos al migrar una base de datos de SQL Server
Tal vez este artículo también os sea útil: Como migrar usuarios de una base de datos a otra en SQL Server
Si se os quedan usuarios huérfanos (orphan users) al pasar una base de datos de un sitio a otro con la opción de backup y restore, podréis sincronizar los logins, siempre que existan en la instancia, uno a uno con el sp_change_users_login, pero es algo bastante incómodo cuando son varias bases de datos o bases de datos con muchos usuarios. Para sincronizar todos los usuarios de una base de datos de una sóla vez, tenemos este script:
USE database_name --Change to active database name
GO
DECLARE @UserName nvarchar(255)
DECLARE orphanuser_cur cursor for
SELECT UserName = name
FROM sysusers
WHERE issqluser = 1 and (sid is not null and sid <> 0x0) and
suser_sname(sid) is null
ORDER BY name
OPEN orphanuser_cur
FETCH NEXT FROM orphanuser_cur INTO @UserName
WHILE (@@fetch_status = 0)
BEGIN
PRINT @UserName + ' user name being resynced'
EXEC sp_change_users_login 'Update_one', @UserName, @UserName
FETCH NEXT FROM orphanuser_cur INTO @UserName
END
CLOSE orphanuser_cur
DEALLOCATE orphanuser_cur
Muy útil, verdad?
Si se os quedan usuarios huérfanos (orphan users) al pasar una base de datos de un sitio a otro con la opción de backup y restore, podréis sincronizar los logins, siempre que existan en la instancia, uno a uno con el sp_change_users_login, pero es algo bastante incómodo cuando son varias bases de datos o bases de datos con muchos usuarios. Para sincronizar todos los usuarios de una base de datos de una sóla vez, tenemos este script:
USE database_name --Change to active database name
GO
DECLARE @UserName nvarchar(255)
DECLARE orphanuser_cur cursor for
SELECT UserName = name
FROM sysusers
WHERE issqluser = 1 and (sid is not null and sid <> 0x0) and
suser_sname(sid) is null
ORDER BY name
OPEN orphanuser_cur
FETCH NEXT FROM orphanuser_cur INTO @UserName
WHILE (@@fetch_status = 0)
BEGIN
PRINT @UserName + ' user name being resynced'
EXEC sp_change_users_login 'Update_one', @UserName, @UserName
FETCH NEXT FROM orphanuser_cur INTO @UserName
END
CLOSE orphanuser_cur
DEALLOCATE orphanuser_cur
Muy útil, verdad?
Problema del puerto de DB2 db2-closed
echo $DB2INSTANCE
db2
Lo primero es chequear que el puerto está realmente cerrado.
netstat -an|grep 60004
tcp4 0 0 .60004 .* CLOSED
db2 list applications
SQL1611W No data was returned by Database System Monitor. SQLSTATE=00000
Si este comando no devuelve conexiones, como en el ejemplo, pasaremos al punto siguiente. Si este comando devuelve conexiones que serán locales ya que si el puerto esta cerrado esta es la única opción.
para tirar las conexiones, se debe ejecutar lo siguiente:
db2 force applications all
db2stop
SQL1064N DB2STOP processing was successful.
db2start
SQL1063N DB2START processing was successful
netstat -an|grep 6000[012345]
La solución a este problema es migrar db2 a versión 8, ya que es un bug de la versión 7. IBM no puede fabricar un fix para este problema ya la versión 7 está fuera de soporte.
db2
Lo primero es chequear que el puerto está realmente cerrado.
netstat -an|grep 60004
tcp4 0 0 .60004 .* CLOSED
db2 list applications
SQL1611W No data was returned by Database System Monitor. SQLSTATE=00000
Si este comando no devuelve conexiones, como en el ejemplo, pasaremos al punto siguiente. Si este comando devuelve conexiones que serán locales ya que si el puerto esta cerrado esta es la única opción.
para tirar las conexiones, se debe ejecutar lo siguiente:
db2 force applications all
db2stop
SQL1064N DB2STOP processing was successful.
db2start
SQL1063N DB2START processing was successful
netstat -an|grep 6000[012345]
La solución a este problema es migrar db2 a versión 8, ya que es un bug de la versión 7. IBM no puede fabricar un fix para este problema ya la versión 7 está fuera de soporte.
