Anterior

Siguientes

Descripción de los permisos de objeto y de base de datos

Completado

Todas las plataformas de administración de bases de datos relacionales tienen cuatro permisos básicos que controlan las operaciones del lenguaje de manipulación de datos (DML). Estos permisos son SELECT, INSERT, UPDATE y DELETE, y se aplican a todas las plataformas de SQL Server. Todos estos permisos se pueden conceder, revocar o denegar en tablas y vistas. Si se concede un permiso mediante la instrucción GRANT, se le concederá el permiso al usuario o al rol al que se hace referencia en la instrucción GRANT. También se pueden denegar permisos a los usuarios mediante el comando DENY. Si a un usuario se le concede un permiso y se le deniega ese mismo permiso, DENY siempre prevalecerá sobre GRANT y se denegará al usuario el acceso al objeto específico.

En el ejemplo anterior, al usuario Demo se le conceden los permisos SELECT y se le deniegan los permisos SELECT en la tabla dbo.Company. Cuando el usuario intenta ejecutar una consulta que selecciona datos de la tabla dbo.Company, recibe un error que indica que se le ha denegado el permiso SELECT.

Permisos de tablas y vistas

Las tablas y vistas representan los objetos para los que se pueden conceder permisos dentro de una base de datos. En esas tablas y vistas, también puede restringir las columnas a las que puede tener acceso una entidad de seguridad determinada (usuario o inicio de sesión). SQL Server y Azure SQL Database también incluyen la seguridad de nivel de fila, que se puede usar para restringir aún más el acceso.

Permiso	Definición
SELECT	Permite al usuario ver los datos del objeto (tabla o vista). Cuando se deniega, el usuario no podrá ver los datos del objeto.
INSERT	Permite al usuario insertar los datos en el objeto. Cuando se deniega, se impedirá al usuario que inserte datos en el objeto.
UPDATE	Permite al usuario actualizar los datos del objeto. Cuando se deniega, se impedirá al usuario que actualice los datos del objeto.
DELETE	Permite al usuario eliminar los datos del objeto. Cuando se deniega, se impedirá al usuario que elimine los datos del objeto.

Azure SQL Database y Microsoft SQL Server tienen otros permisos que se pueden conceder, revocar o denegar según sea necesario.

Permiso	Definición
CONTROL	Concede todos los derechos a los objetos. De este modo, el usuario que tiene este permiso podrá realizar cualquier acción que desee en el objeto; por ejemplo, eliminarlo.
REFERENCES	Concede al usuario la capacidad de ver las claves externas del objeto.
TAKE OWNERSHIP	Permite al usuario la capacidad de asumir la propiedad del objeto.
VIEW CHANGE TRACKING	Permite al usuario ver la configuración del seguimiento de cambios del objeto.
VIEW DEFINITION	Permite al usuario ver la definición del objeto.

Permisos de funciones y procedimientos almacenados

Al igual que las tablas y vistas, las funciones y los procedimientos almacenados tienen varios permisos que se pueden conceder o denegar.

Permiso	Definición
ALTER	Concede al usuario la capacidad de cambiar la definición del objeto.
CONTROL	Concede al usuario todos los derechos del objeto.
EXECUTE	Concede al usuario la capacidad de ejecutar el objeto.
VIEW CHANGE TRACKING	Permite al usuario ver la configuración del seguimiento de cambios del objeto.
VIEW DEFINITION	Permite al usuario ver la definición del objeto.

EXECUTE AS

Los comandos EXECUTE AS [user name] y EXECUTE AS [login name] (solo disponibles en SQL Server y en Azure SQL Managed Instance) permiten cambiar el contexto de usuario. Los comandos y las instrucciones siguientes se ejecutarán con el nuevo contexto con los permisos concedidos a ese contexto.

Si un usuario tiene un permiso y ese usuario ya no necesita tener dicho permiso, se pueden quitar los permisos (se concede o deniega) mediante el comando REVOKE. El comando REVOKE quitará todos los permisos GRANT o DENY del derecho especificado para el usuario determinado.

Cadenas de propiedad

Un concepto denominado "encadenamiento" se aplica a los permisos, lo que permite a los usuarios heredar permisos de otros objetos. El ejemplo más común de encadenamiento es una función o un procedimiento almacenado que accede a una tabla durante su ejecución. Si el procedimiento tiene el mismo propietario que la tabla, el procedimiento almacenado se puede ejecutar y puede acceder a la tabla, aunque el usuario no tenga derechos de acceso a la tabla directamente. Este acceso está disponible porque el usuario hereda los derechos de acceso a la tabla desde el procedimiento almacenado, pero solo durante la ejecución del procedimiento almacenado y dentro del contexto de la ejecución de los procedimientos almacenados.

En el ejemplo siguiente, se ejecuta como propietario de la base de datos o como administrador del servidor, se crea un usuario y se agrega como miembro de un nuevo rol SalesReader, al que se le concede permiso para seleccionar datos de cualquier objeto y ejecutar cualquier procedimiento en el esquema Sales. Luego, se crea un procedimiento almacenado en el esquema Sales que accede a una tabla del esquema Production.

A continuación, el ejemplo cambia el contenido para que sea el nuevo usuario y se realiza un intento de seleccionar datos directamente desde la tabla del esquema Production.

USE AdventureWorks2016;
GO

CREATE USER [DP300User1] WITH PASSWORD = 'Pa55.w.rd';
GO

CREATE ROLE [SalesReader];
GO

ALTER ROLE [SalesReader] ADD MEMBER [DP300User1];
GO

GRANT SELECT, EXECUTE ON SCHEMA::Sales TO [SalesReader];
GO

CREATE OR ALTER PROCEDURE Sales.DemoProc
AS
SELECT P.Name, 
    SUM(SOD.LineTotal) AS TotalSales,
    SOH.OrderDate 
FROM Production.Product P
    INNER JOIN Sales.SalesOrderDetail SOD ON (SOD.ProductID = P.ProductID)
    INNER JOIN Sales.SalesOrderHeader SOH ON (SOH.SalesOrderID = SOD.SalesOrderID)
GROUP BY P.Name, 
    SOH.OrderDate
ORDER BY TotalSales DESC;

GO

EXECUTE AS USER = 'DP300User1';

SELECT P.Name, 
    SUM(SOD.LineTotal) AS TotalSales,
    SOH.OrderDate 
FROM Production.Product P
    INNER JOIN Sales.SalesOrderDetail SOD ON (SOD.ProductID = P.ProductID)
    INNER JOIN Sales.SalesOrderHeader SOH ON (SOH.SalesOrderID = SOD.SalesOrderID)
GROUP BY P.Name, 
    SOH.OrderDate
ORDER BY TotalSales DESC;

La consulta anterior genera un error que indica que el usuario DP300User1 no tiene el permiso SELECT, ya que el rol al que pertenece el usuario no tiene ningún privilegio en el esquema Production. Ahora podemos intentar ejecutar el procedimiento almacenado:

EXECUTE AS USER = 'DP300User1';

EXECUTE Sales.DemoProc;

El usuario DP300User1 tiene el permiso EXECUTE para el procedimiento almacenado en el esquema Sales, ya que el rol del usuario tiene el permiso EXECUTE en el esquema Sales. Como la tabla tiene el mismo propietario que el procedimiento, tenemos una cadena de propiedad continua, y la ejecución se realizará correctamente y se devolverán los resultados.

Los cambios de permisos no se aplican cuando se usa SQL dinámico en procedimientos almacenados. La razón por la que SQL dinámico divide la cadena de permisos se debe al hecho de que esta técnica se ejecuta fuera del contexto del procedimiento almacenado que realiza la llamada. Puede ver este comportamiento cambiando el procedimiento almacenado anterior para que se ejecute con SQL dinámico, como se muestra a continuación.

CREATE OR ALTER PROCEDURE Sales.DemoProc
AS
DECLARE @sqlstring NVARCHAR(MAX)

SET @sqlstring = '
SELECT P.Name, 
    SUM(SOD.LineTotal) AS TotalSales, 
    SOH.OrderDate 
FROM Production.Product P
    INNER JOIN Sales.SalesOrderDetail SOD ON (SOD.ProductID = P.ProductID)
    INNER JOIN Sales.SalesOrderHeader SOH ON (SOH.SalesOrderID = SOD.SalesOrderID)
GROUP BY P.Name, SOH.OrderDate'

EXECUTE sp_executesql @sqlstring
GO

--

EXECUTE AS USER = 'DP300User1'

EXECUTE Sales.DemoProc

El usuario DP300User1 recibirá un error que le informará de que el usuario no tiene el permiso SELECT en la tabla Production.Product, al igual que el usuario intentó ejecutar la consulta directamente. Las cadenas de permisos no se aplican y la cuenta de usuario que ejecuta la técnica de SQL dinámico debe tener derechos para las tablas y vistas que usa el código en SQL dinámico.

Principio de privilegio mínimo

El principio de privilegios mínimos es bastante sencillo. La idea básica del concepto es que los usuarios y las aplicaciones solo deben tener los permisos necesarios para que puedan completar una tarea. Las aplicaciones solo deben tener los permisos necesarios para completar la tarea en cuestión.

Por ejemplo, si una aplicación tiene acceso a todos los datos a través de procedimientos almacenados, la aplicación solo debe tener el permiso para ejecutar los procedimientos almacenados, sin acceso a las tablas.

SQL dinámico

SQL dinámico es un concepto en el que una consulta se genera mediante programación. SQL dinámico permite generar instrucciones T-SQL dentro de un procedimiento almacenado o una consulta. A continuación se muestra un ejemplo sencillo.

SELECT 'BACKUP DATABASE ' + name + ' TO DISK =''\\backup\sql1\' + name + '.bak'''
FROM sys.databases

La instrucción anterior generará una lista de instrucciones T-SQL para realizar una copia de seguridad de toda la base de datos del servidor. Normalmente, esta instrucción T-SQL generada se ejecutará con sp_executesql o se pasará a otro programa para ejecutarse.

Continuar