Aiello DBA

Esta vez voy a hacer un post bien simple pero útil. Hay dos opciones muy interesantes al crear logins que son el check_policy y el check_expiration. La primera nos permite controlar que las contraseñas ingresadas por los usuarios cumplan con los requisitos de seguridad de complejidad de contraseña mientras que la segunda habilita que las contraseñas expiran una vez vencido cierto tiempo. Es importante notar (error muy común) que el check_policy se evalúa AL MOMENTO de setear la contraseña. Esto quiere decir que si creo un usuario, le pongo como contraseña '1' y luego habilito el check_poclicy, dicho usuario va a poder conectarse sin problema. Pero sin en cambio creo el usuario, habilito el check_policy y luego pongo como contraseña '1' no me dejará definirla por no cumplir los requisitos de seguridad. En el primer caso la contraseña '1' le permitirá loguearse pero al momento de querer cambiarla no podrá poner como contraseña '2' sino que ahi si deberá cumplir las reglas definidas.

Una vez terminada la introducción y dejando los conceptos en claro vamos a los bifes. La idea es hacer una consulta que me liste todos los usuarios que no cumplen esta buena práctica y poder cambiar su estado. El script que armé es el siguiente:

-- =============================================
-- Author: Andrés Aiello
-- Create date: 02/05/2011
-- Setea el check_policy para los logins que no lo tengan
-- =============================================

DECLARE @SQLQuery varchar(1000)
DECLARE @LoginName varchar(255)

DECLARE cLogins CURSOR FORWARD_ONLY FOR
SELECT name
--,is_policy_checked,'ALTER LOGIN [' + name + '] WITH CHECK_POLICY=ON' Query
FROM sys.sql_logins
WHERE is_policy_checked = 0
/* Ignoro los logins que considere que por algun motivo no deben ser tenidos en cuenta */
AND NOT name IN ('xxxxxxxxxxx')
ORDER BY NAME

OPEN cLogins
FETCH NEXT FROM cLogins INTO @LoginName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQLQuery = 'ALTER LOGIN [' + @LoginName + '] WITH CHECK_POLICY=ON'
PRINT @SQLQuery
FETCH NEXT FROM cLogins INTO @LoginName
END
CLOSE cLogins
DEALLOCATE cLogins

Este script como pueden ver no ejecuta el código sino que lo saca por la salida. Esto se debe a que una buena idea sería previo a hacer los cambios documentarlos salvando el script y luego ejecutarlo.

En este ejemplo fue para el check_policy, pero si quisieran hacer lo mismo para el expiration es igual solamente que el campo a filtrar es is_expiration_checked.
Andrés

Controlar CMDShell en SQL

Todos saben que no es deseable tener el CMDShell activado en los servidores, pero hay veces que uno llega a un server y ya se encuentra activado y ahí surge la pregunta de que hacer. La primer medida es cambiar las credenciales para que se ejecute con permisos mínimos, pero no hablaremos de eso ahora. Junto con esto es importante identificar donde se utiliza visto que los permisos dependerán de las llamadas que haga. Como no podemos recorrer base por base viendo si se utiliza, les dejo una consulta que hice para este fin

EXEC sp_MSForeachdb 'SELECT ''?'' DBName, text FROM [?].SYS.SYSCOMMENTS WHERE text LIKE ''%XP_CMDSHELL%'''

Esta consulta retorna todos los lugares donde se utiliza el cmdshell. Hay algunas referencias del sistema que las deberán ignorar.

Saludos!

nota: recordar que la función sp_MSForeachdb es una función no documentada

miércoles, 21 de septiembre de 2011

Renombrar filename de una base SQL

Hoy quiero contar un problema que me presentó un amigo hace unos días y me parece que a muchos les puede pasar. La persona en cuestión muchas veces había renombrado una base de datos, o había cambiado su nombre lógico, pero las veces que tenía que cambiar el nombre físico el procedimiento era:
1) Deatach de la base
2) Renombrado del file
3) Atach de la base

Es procedimiento es un poco extremo y aprovecharé para explicarles como hacerlo de forma mas simple.
El primer paso es asegurarme cuales son los files que tengo definidos en la base, en el ejemplo consideraremos la base "Librería". Para obtener esta información ejecutamos:

SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID('Libreria');

Ahí obtendremos como resultado tres columnas, Name (nombre lógico), CurrentLocation (donde se encuentra actualmente el file) y state_desc (estado del file). Es importante que se recuerde que el nombre lógico es como se refenciará internamente al archivo, mientras que el físico es su nombre real. Puedo cambiar uno sin cambiar el otro.
La consulta dará como mínimo dos registros de salida, una para el archivo mdf (data) y otra para el ldf (log).

Supongamos que quiero cambiar ambos archivos y renombrarlos LibreriaDATA y LibreriaLOG respectivamente, entonces debo poner offline la base y luego ejecutar la siguiente sentencia:

-- Pongo a la base offline
ALTER DATABASE Libreria SET OFFLINE WITH ROLLBACK IMMEDIATE

-- Cambio los nombres a donde apuntan. El NAME es el nombre logico que quiero modificar y el FILENAME es el NUEVO nombre fisico
ALTER DATABASE Libreria
MODIFY FILE (NAME = Libreria, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA\LibreriaDATA.mdf' )
ALTER DATABASE Libreria
MODIFY FILE (NAME = Libreria_log, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA\LibreriaLOG.LDF' )
-- Muevo los archivos a nivel sistema operativo

Si todo está ok pongo la base nuevamente en línea. Es importante que noten que puedo hacer el alter apuntando a un archivo inexistente, el control se hará al momento de poner la base online.

-- Una vez renombrados la pongo nuevamente en linea
ALTER DATABASE Libreria SET ONLINE

-- Controlo que todo quede como yo quería
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files

Si todo está ok ya terminé mi trabajo. En caso de que haya por error apuntado a un archivo inexistente (ej. me faltó renombrar un archivo) la base no se pondrá online arrojando el siguiente error:

Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file

Es una buena práctica al finalizar ejecutar nuevamente la consulta inicial y corroborar que realmente todo quedó como queríamos que quedara.
Espero que les sirva!

Que no te ataquen!!! Desarrollando código seguro en SQL

Les dejo la invitación al Codecamp de este año. Estaré dando una charla sobre código seguro en SQL junto a mi amigo Mariano Alvarez (http://blog.josemarianoalvarez.com). Espero que pueda ir mucha gente!!!!

lunes, 12 de septiembre de 2011

Screencast: CTE recursivas en SQL Server

Mi primer Screencast!!! Espero que les guste mi primer screencast, trata sobre CTE recursivas (ver post anterior)

La recursion es divina - Consultas recursivas en SQL

El lenguaje SQL no solía ser lo mas comodo para realizar consultas en donde participe el concepto de "recursión", pero claramente algunos problemas son muy incomodos si los abordamos de otra forma. Por suerte para nosotros, desde la aparición de las CTE, esto ha cambiado y ahora es "simple" realizar consultas recursivas, veremos un ejemplo de como podemos realizarlo.

Consideremos la siguiente tabla con datos de ejemplo:

CREATE TABLE #Empleados(idEmpleado INT, Nombre VARCHAR(10), idJefe INT)

INSERT INTO #Empleados
SELECT 0, 'Bob', null UNION
SELECT 1, 'Tom', 0 UNION
SELECT 2, 'Joe', null UNION
SELECT 3, 'John', 2 UNION
SELECT 4, 'Ringo', 1 UNION
SELECT 5, 'Paul', 4 

SELECT * FROM #Empleados

Esta tabla contiene los empleados de una empresa e indica sus respectivos jefes. Lo divertido es que los jefes son también empleados, por lo tanto son registros de la misma tabla.

Supongamos que quiero averiguar el nombre del jefe de cada empleado. Esto lo puedo realizar con un "self join" sin necesidad de recursividad.

SELECT e.Nombre Empleado, j.Nombre Jefe
FROM #Empleados e
INNER JOIN #Empleados j ON e.idJefe=j.idEmpleado;

Pero ahora supongamos que quiero una consulta mas compleja, una que me retorne no el jefe directo, sino el jefe superior de cada empleado. Cualquiera que tenga conocimientos de algoritmos sabe que la mejor forma de responder a esta pregunta es con una estructura recursiva. No pretendo en éste artículo escribir que es la recursión, sino como utilizarla en SQL, así que daré por sentado que saben de que hablo.

Una estructura recursiva requiere básicamente de dos partes:

1) El caso base

2) El caso recursivo

Para escribir esto utilizaremos una CTE que tendrá estas dos partes, y deben unirse con un UNION ALL. La primer parte será el caso base (en el WHERE debo filtrar para que lo sea) y en la segunda el caso recursivo, el cual hace un JOIN con la CTE. Este join es lo que indica que es un caso recursivo. Nótese que defino la cte utilizandola a ella misma, por eso mismo es recursiva.

WITH cte
AS(
-- Caso base
SELECT e.idEmpleado, e.Nombre Nombre, e.idEmpleado Jefe
FROM #Empleados e
WHERE e.idJefe IS NULL
-- Fin Caso base
UNION ALL
-- Caso recursivo
SELECT e.idEmpleado, e.Nombre Nombre, j.Jefe
FROM #Empleados e
INNER JOIN cte j ON e.idJefe=j.idEmpleado
-- Fin Caso recursivo
)
SELECT c.Nombre Empleado, j.Nombre Jefe
FROM cte c
INNER JOIN #Empleados j ON c.Jefe=j.idEmpleado

DROP TABLE #Empleados

Con esto obtengo el código del jefe superior de cada uno, entonces en el SELECT lo cruzo con la tabla de empleados para obtener su nombre. Parece medio confuso pero es cuestión de acostumbrarse.

Hay algunos inconvenientes que pueden presentarse, pero eso lo comentaré en el próximo artículo.

jueves, 8 de septiembre de 2011

Como leer desde SQL los MeasureGroups de AS

Hace poco me pasó que necesitaba trabajar con los id de los MeasureGroups desde SQL, y me encontré con que las consultas que podía hacer retornaban siempre el nombre pero no el id. Por este motivo hice un script de powershell que me brinde la información y la cargue en una tabla. La parte de SQL la hice rápido y podría mejorarse con objetos de powershell pero lo importante es lo otro.

#Datos del ejemplo:

#Servidor de AS: AS1

#Base en AS: DB1

#Cubo: Cub1

## El primer parametro es la instancia donde guarda la informacion, segundo base

$sqlServer = $args[0];
$dbName = $args[1];
$tbl = "mgRecolectadas"

# Recolecto la informacion del AS
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") > $null
[Microsoft.AnalysisServices.Server]$svr = new-Object([Microsoft.AnalysisServices.Server])

# Tomo uno como ejemplo
$svr.Connect("AS1")
$DatabaseID = "DB1"
$db = $svr.Databases.Item($DatabaseID)
$cubo = $db.Cubes.Item("Cub1")

#Variables donde armaré la cadena de SQL
$mgid = ""
$mgname = ""
$sql = ""
#Recorro la coleccion de MeasureGroups

foreach ($mg in $cubo.MeasureGroups){

    #Obtengo el ID
    $mgid = $mg.id
    $mgname = $mg.name
    #Armo la cadena
    if ($sql){
        $sql = "$sql UNION "
    }
    $sql = "$sql
        SELECT '$mgid' mg, '$mgname' mgname"
}
$svr.Disconnect()

## Establezco la conexion para guardar la info
$conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=$sqlServer;Initial Catalog=$dbName; Integrated Security=SSPI")
$conn.Open()
$cmd = $conn.CreateCommand()

# Borro lo anterior
$cmd.CommandText = "TRUNCATE TABLE $tbl"
$null = $cmd.ExecuteNonQuery()
echo "Table truncada"

# Agrego todos los registros nuevos
$cmd.CommandText = "INSERT INTO $tbl SELECT * FROM ($sql) t"
$null = $cmd.ExecuteNonQuery()
echo "Insercion finalizada"

$conn.Close()