SQL Server: ¿cómo permisos de esquemas?

Inspirado por varias preguntas relacionadas con el esquema que he visto …

El encadenamiento de propiedad me permite GRANT EXECUTE en un procedimiento almacenado sin permisos explícitos en las tablas que uso, si tanto el procedimiento almacenado como las tablas están en el mismo esquema.

Si usamos esquemas separados, entonces tendría que CONCEDER explícitamente XXX en las tablas de diferentes esquemas. El ejemplo de encadenamiento de propiedad demuestra eso. Esto significa que el usuario que ejecuta el proceso almacenado puede leer / escribir sus tablas directamente.

Esto sería como tener acceso directo a las variables de su instancia en una clase, evitando getter / setters, rompiendo la encapsulación.

También utilizamos seguridad a nivel de fila para restringir lo que alguien ve y aplicamos esto en los procedimientos almacenados.

Entonces, ¿cómo podemos mantener la separación del esquema y evitar el acceso directo a la tabla?

Por supuesto, la pregunta no se aplicará si usa un ORM o no usa procs almacenados. Pero no estoy preguntando si debería usar un ORM o un proc almacenado en caso de que alguien sienta la necesidad de iluminarme …

Editar, ejemplo

CREATE USER OwnsMultiSchema WITHOUT LOGIN GO CREATE SCHEMA MultiSchema1 AUTHORIZATION OwnsMultiSchema GO CREATE SCHEMA MultiSchema2 AUTHORIZATION OwnsMultiSchema GO CREATE USER OwnsOtherSchema WITHOUT LOGIN GO CREATE SCHEMA OtherSchema AUTHORIZATION OwnsOtherSchema GO CREATE TABLE MultiSchema1.T1 (foo int) GO CREATE TABLE MultiSchema2.T2 (foo int) GO CREATE TABLE OtherSchema.TA (foo int) GO CREATE PROC MultiSchema1.P1 AS SELECT * FROM MultiSchema1.T1 SELECT * FROM MultiSchema2.T2 SELECT * FROM OtherSchema.TA Go EXEC AS USER = 'OwnsMultiSchema' GO --gives error on OtherSchema EXEC MultiSchema1.P1 GO REVERT GO CREATE PROC OtherSchema.PA AS SELECT * FROM MultiSchema1.T1 SELECT * FROM MultiSchema2.T2 SELECT * FROM OtherSchema.TA Go GRANT EXEC ON OtherSchema.PA TO OwnsMultiSchema GO EXEC AS USER = 'OwnsMultiSchema' GO --works EXEC OtherSchema.PA GO REVERT GO 

Editar 2:

  • No usamos “encadenamiento de propiedad de bases de datos cruzadas”
  • La seguridad a nivel de fila es una pista falsa e irrelevante: no la usamos en todas partes

Me temo que su descripción o su concepción de la cadena de propiedad no está clara, así que permítanme comenzar con eso:

El “encadenamiento de propiedad” simplemente se refiere al hecho de que al ejecutar un procedimiento almacenado (o vista) en SQL Server, el lote actualmente en ejecución adquiere temporalmente los derechos / permisos del propietario de sProc (o el propietario del esquema de sProc) mientras ejecuta ese código SQL. Entonces, en el caso de un sProc, el usuario no puede usar esos privs para hacer algo que el código de sProc no implemente para ellos. Tenga en cuenta especialmente que nunca adquiere la Identidad del propietario, solo sus derechos, temporalmente (sin embargo, EJECUTAR COMO … lo hace).

Entonces, el enfoque típico para aprovechar esto para la seguridad es:

  1. Coloque todas las Tablas de datos (y todas las Vistas que no sean de seguridad) en su propio Esquema, llamémoslo [datos] (aunque típicamente [dbo] se usa porque ya está allí y tiene privilegios para el esquema del Usuario). Asegúrese de que ningún usuario, esquema o propietario existente tenga acceso a este esquema [de datos].

  2. Cree un esquema llamado [exec] para todos los sProcs (y / o posiblemente cualquier Vista de seguridad). Asegúrese de que el propietario de este esquema tenga acceso al esquema [data] (esto es fácil si hace que dbo sea el propietario de este esquema).

  3. Cree una nueva función db-Role llamada “Usuarios” y déle acceso EXECUTE al esquema [exec]. Ahora agregue todos los usuarios a este rol. Asegúrese de que sus usuarios solo tengan derechos de conexión y no tengan acceso otorgado a ningún otro esquema, incluido [dbo].

Ahora sus usuarios pueden acceder a los datos solo ejecutando los sProcs en [exec]. No pueden acceder a ningún otro dato ni ejecutar ningún otro objeto.

No estoy seguro de si esto responde su pregunta (porque no estaba seguro de cuál era exactamente la pregunta), así que siéntase libre de redirigirme.


En cuanto a la seguridad a nivel de fila, así es como siempre lo hago con el esquema de seguridad anterior:

  1. Siempre implemento seguridad a nivel de fila como una serie de Vistas que repliquen todas las tablas y comparen la identidad del Usuario (generalmente con Suser_Sname () o uno de los otros) a una lista de seguridad con clave de un código de seguridad en la misma fila. Estas son las Vistas de Seguridad.

  2. Cree un nuevo esquema llamado [filas], otorgue a su propietario acceso al esquema de [datos] y nada más. Coloque todas las Vistas de seguridad en este esquema.

  3. Revoca el acceso del propietario [exec] al esquema [data] y en su lugar otórgale acceso de datos al esquema [rows].

Hecho. Ahora la seguridad a nivel de fila se ha implementado deslizándola de forma transparente entre los sProcs y las tablas.


Finalmente, he aquí un documento guardado que utilizo para ayudarme a recordar qué parte de este elemento oscuro de seguridad funciona e interactúa consigo mismo (¡ Vaya, versión corregida del código ):

 CREATE proc [TestCnxOnly].[spShowProc_Security_NoEX] as --no "With Execute as Owner" for this version --create User [UserNoLogin] without login --Grant connect on database :: TestSecurity to Guest --alter database TestSecurity set trustworthy on --Show current user context: select current_user as current_ , session_user as session , user_name() as _name , suser_name() as [suser (sproc)] , suser_sname() as sname , system_user as system_ --Execute As Login = 'UserNoLogin' select current_user as current_ , session_user as session , user_name() as _name , suser_name() as [suser (after exec as)] , suser_sname() as sname , system_user as system_ EXEC('select current_user as current_ , session_user as session , user_name() as _name , suser_name() as [suser (in Exec(sql))] , suser_sname() as sname , system_user as system_') EXEC sp_ExecuteSQL N'select current_user as current_ , session_user as session , user_name() as _name , suser_name() as [suser (in sp_Executesql)] , suser_sname() as sname , system_user as system_' --Revert select current_user as current_ , session_user as session , user_name() as _name , suser_name() as [suser (aftr revert)] , suser_sname() as sname , system_user as system_ 

[EDIT: versión corregida del código)

Mi 2c: el encadenamiento de propiedad es legado. Data de días en que no había alternativas, y en comparación con las alternativas de hoy es inseguro y grosero.

Digo que la alternativa no son los permisos de esquema, la alternativa es la firma de código. Con la firma de código puede otorgar los permisos necesarios en la firma del procedimiento y otorgar acceso de ejecución amplio en el procedimiento mientras el acceso a los datos está estrechamente controlado. La firma de código ofrece un control más granular y más preciso, y no se puede abusar de la forma en que puede hacerlo el encadenamiento de propiedad. Funciona dentro del esquema, funciona a través del esquema, funciona en toda la base de datos y no requiere que se abra el gran agujero de seguridad del encadenamiento de propiedad de la base de datos cruzada. Y no requiere el secuestro de la propiedad del objeto para fines de acceso: el propietario del procedimiento puede ser cualquier usuario.

En cuanto a su segunda pregunta sobre la seguridad a nivel de fila: la seguridad a nivel de fila realmente no existe en las versiones de SQL Server 2014 y anteriores, como una característica ofrecida por el motor. Tiene varias soluciones alternativas, y esas soluciones funcionan realmente mejor con la firma de código que con el encadenamiento de propiedad. Como sys.login_token contiene las firmas de contexto y las contrafirmas, en realidad puede hacer comprobaciones más complejas de lo que podría hacerlo en un contexto de encadenamiento de propiedad.

Desde la versión 2016 SQL Server es totalmente compatible con la seguridad de nivel de fila .

Usted puede:

 Grant Execute On Schema::[schema_name] To [user_name] 

para permitir al usuario ejecutar cualquier procedimiento en el esquema. Si no desea que pueda ejecutarlos todos, puede denegar explícitamente la ejecución de un procedimiento determinado al usuario. Denegar tendrá prioridad en este caso.

    Intereting Posts