Cómo crear inicios de sesión, usuarios y otorgar permisos en SQL Server

En este artículo, aprenderemos cómo crear una nueva cuenta de usuario y otorgar/revocar permisos y roles para un objeto de base de datos, así como también cómo verificar los permisos de usuario de SQL Server en una base de datos usando T-SQL y dbForge Studio para SQL. Servidor.

Cuando se trata de proteger datos confidenciales y garantizar la seguridad del entorno de la base de datos, la administración de permisos y privilegios de la base de datos es central. Por ejemplo, desea que cierta persona pueda cambiar los datos personales y manipularlos, mientras que otros solo pueden ver esos datos. En este caso, el administrador de la base de datos deberá crear un nuevo usuario o rol con ciertos permisos que se pueden asignar a nivel de servidor, base de datos o esquema. Para realizar esta tarea, pueden usar declaraciones T-SQL proporcionadas por SQL Server o herramientas de terceros.

Para continuar, vamos a profundizar en las siguientes instrucciones:

Índice

    Crear un nuevo inicio de sesión en SQL Server

    Antes de poder crear una base de datos de usuarios, primero debe crear un nuevo inicio de sesión basado en la autenticación de Windows, la autenticación de SQL Server, el certificado o la clave asimétrica.

    Para agregar un nuevo inicio de sesión, use la instrucción CREATE LOGIN. Crea un inicio de sesión asociado con una instancia de SQL Server. El inicio de sesión se asignará a la cuenta de usuario especificada. La sintaxis es la siguiente:

    Autenticación de Windows

    CREATE LOGIN login_name   
       FROM WINDOWS
    [ WITH DEFAULT_DATABASE = database_name
    | DEFAULT_LANGUAGE = language_name ];

    Autenticación de servidor SQL

     CREATE LOGIN login_name 
      WITH PASSWORD = { 'password' | hashed_password HASHED } [ MUST_CHANGE ]
    [ , SID = sid_value
      | DEFAULT_DATABASE = database_name
      | DEFAULT_LANGUAGE = language_name
      | CHECK_EXPIRATION = { ON | OFF }
      | CHECK_POLICY = { ON | OFF }
      | CREDENTIAL = credential_name ];

    Certificado

    CREATE LOGIN login_name
    FROM CERTIFICATE certificate_name;

    Clave asimétrica

    CREATE LOGIN login_name
    FROM ASYMMETRIC KEY asym_key_name;

    La tabla describe los argumentos utilizados en las sentencias CREATE LOGIN.

    ArgumentoDescripción
    nombre de inicio de sesiónEl nombre del inicio de sesión conectado al servidor.
    nombre de la base de datosEl nombre de la base de datos predeterminada a la que se asignará el inicio de sesión.
    Nombre del lenguajeEl idioma predeterminado para el inicio de sesión que creó.
    contraseñaContraseña para el inicio de sesión que crea.
    hash_contraseñaEl valor hash de la contraseña para el inicio de sesión que creó.
    DEBE CAMBIARPide cambiar la contraseña al conectarse.
    valor_sidEl valor utilizado para recuperar la entrada. Solo se puede usar para iniciar sesión con la autenticación de SQL Server. Si no se especifica sid_value, SQL Server asignará un nuevo SID.
    CHECK_EXPIRATIONDetermina si se aplica la política de caducidad de la contraseña. Debe establecerse en ON si utiliza la opción MUST_CHANGE.
    CHECK_POLICYSi el argumento se establece en ON, significa que la política de contraseñas de Windows de la ordenador que ejecuta SQL Server también se debe aplicar al inicio de sesión.
    credencialesnombreEl nombre de las credenciales que se utilizarán para iniciar sesión en SQL Server.
    Nombre del certificadoEl nombre del certificado que se asignará al inicio de sesión de SQL Server.
    nombre_clave_asimétricaEl nombre de la clave asimétrica que se asignará al inicio de sesión de SQL Server.

    Declaración CREAR INICIO DE SESIÓN utilizando la autenticación de Windows

    Para agregar un usuario de SQL Server basado en la autenticación de Windows, ejecute la instrucción CREATE LOGIN con los siguientes argumentos:

    CREATE LOGIN JordanS 
    FROM WINDOWS;

    Esto creará un nuevo inicio de sesión. JordanS en una instancia de SQL Server utilizando la autenticación de Windows.

    Cree un inicio de sesión para autenticar SQL Server

    Por ejemplo, vamos a crear un inicio de sesión JordanS con la contraseña 'pass123' utilizando la autenticación de SQL Server. Para hacer esto, ejecute el siguiente comando:

    CREATE LOGIN JordanS
    WITH PASSWORD = 'pass123';

    Si desea cambiar la contraseña la primera vez que inicia sesión, agregue el argumento MUST_CHANGE con CHECK_EXPIRATION habilitado a la instrucción CREATE LOGIN:

    CREATE LOGIN JordanS
    WITH PASSWORD = 'pass123' MUST_CHANGE, 
    CHECK_EXPIRATION = ON;

    Crear un inicio de sesión desde el certificado

    Ahora veamos cómo crear un inicio de sesión JordanS utilizando un certificado de SQL Server certificado 123.

    CREATE LOGIN JordanS
    FROM CERTIFICATE certificate123;

    Crear un inicio de sesión con una clave asimétrica

    Finalmente, agregue un nuevo inicio de sesión JordanS que utiliza una clave asimétrica clave_123 en el servidor SQL.

    CREATE LOGIN JordanS
    FROM ASYMMETRIC KEY key_123;

    Crear un nuevo usuario usando T-SQL

    Una vez que se ha creado el inicio de sesión, es hora de agregar un nuevo usuario. La sintaxis básica es la siguiente:

    CREATE USER <username> for login <login_name>

    Para crear un nuevo usuario, por ejemplo, Gerenteentrar JordanMejecutar CREATE USER declaración:

    CREATE USER Manager FOR LOGIN JordanM;

    La siguiente vista del sistema sys.database_principals le permite obtener una lista de todos los usuarios creados en la base de datos de SQL Server. Ejecute el comando para ver que se ha creado el usuario.

    SELECT *
    FROM AdventureWorks2019.sys.database_principals;

    La salida debe ser la siguiente:

    Una vez creado el usuario, podemos proceder a asignarle permisos y privilegios.

    Dar permisos usando T-SQL

    Los permisos y privilegios controlan el acceso a los datos y objetos de la base de datos de SQL Server. Puede otorgar privilegios a los usuarios en varios objetos de la base de datos en SQL Server.

    Los privilegios pueden ser de dos tipos:

    • El sistema privilegios que permiten a los usuarios crear, modificar o eliminar objetos de la base de datos.
    • Objeto privilegios que permiten a los usuarios ejecutar, seleccionar, insertar, actualizar o eliminar datos sobre los objetos de la base de datos a los que se han asignado privilegios.

    Cabe señalar que solo los administradores de la base de datos o los propietarios de los objetos de la base de datos pueden otorgar o revocar privilegios.

    La instrucción GRANT otorga al usuario acceso y permisos sobre los objetos de la base de datos. La sintaxis básica es la siguiente:

    GRANT privileges 
      ON database_name.object
    TO {user_name |PUBLIC |role_name}
    [WITH GRANT OPTION]; 

    La tabla describe los argumentos utilizados en las sentencias GRANT.

    ArgumentoDescripción
    privilegiosLos permisos que desea otorgar. Estos incluyen las declaraciones SELECT, INSERT, UPDATE, DELETE, REFERENCES, ALTER o ALL.
    nombre de la base de datosEl nombre de la base de datos a la que pertenece el objeto de la base de datos.
    objetoEl objeto de base de datos al que se asignarán los privilegios especificados.
    nombre de usuarioEl nombre del usuario al que se otorgarán los privilegios.
    PÚBLICOSe utiliza para otorgar permisos a todos los usuarios.
    nombre de rolUn conjunto de privilegios agrupados en una categoría.
    CON GRANDE VERRUGASSe utiliza para otorgar permisos a otros usuarios.

    Por ejemplo, démosle privilegios SELECCIONAR, INSERTAR y ALTERAR para Recursos humanos Empleado tabla para el usuario Gerente que creamos en la sección anterior.

    GRANT SELECT, INSERT, ALTER ON HumanResources.Employee TO Manager;

    Ahora veamos en qué está la lista de permisos Recursos humanos Empleado la tabla incluye los permisos que le asignamos al usuario Gerente. Para hacer esto, usaremos la vista del sistema: fn_mis_permisos - y ejecuta el siguiente comando:

    EXECUTE AS USER = 'Manager';
    GO
    USE AdventureWorks2019
    GO
    SELECT * FROM fn_my_permissions('HumanResources.Employee', 'OBJECT')
    GO

    La salida es la siguiente:

    Revocar todos los privilegios usando T-SQL

    Si desea eliminar los privilegios de usuario en un objeto de base de datos, puede usar el comando REVOKE.

    REVOKE privileges
    ON object
    FROM {user_name |PUBLIC |role_name}

    Puedes reemplazar privileges con valores como SELECCIONAR, INSERTAR, ACTUALIZAR, ELIMINAR, REFERENCIAR, ALTERAR o TODO.

    Para continuar, elimine los permisos INSERT y ALTER en Recursos humanos Empleado tabla definida por el usuario Gerente ejecutando el siguiente comando:

    REVOKE INSERT, ALTER ON HumanResources.Employee TO Manager;

    Para verificar el resultado, ejecute el siguiente comando usando fn_mis_permisos vista:

    EXECUTE AS USER = 'Manager';
    GO
    USE AdventureWorks2019
    GO
    SELECT * FROM fn_my_permissions('HumanResources.Employee', 'OBJECT')
    GO

    Como puede ver, se han eliminado los permisos INSERT y ALTER. Solo se sigue asignando el permiso SELECT.

    Deshabilitar permisos usando T-SQL

    El permiso DENY bloquea el acceso al objeto de la base de datos. En caso de que el objeto tenga derechos GRANT y DENY, los permisos DENY prevalecerán sobre los permisos GRANT. Por ejemplo, ejecute el siguiente comando:

    DENY INSERT ON HumanResources.Employee TO Manager;

    En este caso, el permiso INSERT será bloqueado para Gerente usuario.

    Asignar roles en SQL Server

    ¿Qué sucede si necesita aplicar privilegios a un grupo de usuarios en lugar de a un usuario? En este caso, sería mejor determinar papel, un conjunto de privilegios y permisos. De esta forma, el usuario al que se le asigna el rol puede acceder y administrar los objetos de la base de datos con los mismos derechos que el rol.

    Cabe señalar que para manipular un rol se debe tener permiso ALTER por rol, permiso ALTER ANY ROLE a nivel de base de datos y membresía en db_securityadmin función de base de datos fija.

    Primero, cree un rol usando la instrucción CREATE ROLE:

    CREATE ROLE role_name;

    donde role_name este es el nombre del rol que desea crear.

    Por ejemplo, queremos crear un rol. Gerentes:

    CREATE ROLE managers;

    Dado que el rol aún no tiene privilegios, el siguiente paso será agregar privilegios al rol. Para ello, se debe ejecutar el comando GRANT, que puede asignar privilegios a roles en bases de datos y objetos de bases de datos.

    Por ejemplo, aplique los privilegios SELECCIONAR, ACTUALIZAR, ALTERAR, INSERTAR, ELIMINAR en Persona Dirección tabla a rol Gerentes.

    GRANT SELECT, UPDATE, ALTER, INSERT, DELETE ON Person.Address TO managers;

    A continuación, agregue usuarios al rol ejecutando la instrucción ALTER ROLE.

    Agrega o elimina usuarios de un rol de base de datos o se puede usar para cambiar el nombre de un rol.

    La sintaxis es la siguiente:

    ALTER ROLE  role_name  
    ADD MEMBER user_name;

    donde

    role_name este es el nombre del rol que desea cambiar o restablecer.

    user_name este es el nombre del usuario existente que desea agregar al rol.

    Si desea quitar un usuario de un rol, use la instrucción ALTER ROLE con las siguientes propiedades:

    ALTER ROLE role_name DROP MEMBER user_name;

    Nota: user_name no puede ser una función de base de datos fija o una entidad de seguridad del servidor.

    Para cambiar el nombre del rol, ejecute la siguiente declaración:

    ALTER ROLE role_name WITH NAME = new_name;

    donde new_name este es el nuevo título del rol. Tenga en cuenta que el nombre de la función de base de datos fija no se puede cambiar.

    Crear un usuario en dbForge Studio para SQL Server

    Ahora veamos lo conveniente que es tratar con usuarios, roles y privilegios con una poderosa herramienta de administración de SQL Server, Gerente de seguridaddisponible en dbForge Studio para SQL Server.

    Security Manager es una herramienta robusta que proporciona una manera segura y eficiente de manejar el acceso a los objetos y datos de la base de datos, crear y manipular usuarios y roles, y otorgar y revocar privilegios y permisos.

    Para comenzar, abra el Administrador de seguridad de una de las siguientes maneras:

    • En la página de inicio, cambie a Administración pestaña y haga clic Administrador de seguridad del servidor.
    • Sobre Base de datos menú, seleccione Gerente de seguridad.

    En el Administrador de seguridad, seleccione crear un usuario de la lista desplegable. Sobre Común en la pestaña, ingrese la configuración de la cuenta de usuario y haga clic en Ahorrar.

    Asignar permisos en dbForge Studio para SQL Server

    Para otorgar permisos al usuario, cambie a Permisos de objeto camino. У Objetos bloque, seleccione el objeto de la base de datos al que desea otorgar privilegios. У Privilegios disponibles bloquear, seleccione los permisos que desea asignar y presione Ahorrar.

    Si necesita deshacer algunos cambios, seleccione un privilegio, haga clic en Revocar privilegios seleccionadosy luego presione Ahorrar. Para denegar el permiso, seleccione la casilla de verificación junto al privilegio correspondiente.

    Como alternativa, puede eliminar todos los privilegios concedidos haciendo clic en Abolir todos los privilegios.

    Verifique los derechos de usuario e inicie sesión en dbForge Studio para SQL Server

    Para obtener una lista de permisos a nivel de objeto asignados a un usuario, en Gerente de seguridadseleccione un usuario y cambie a Permisos de objeto camino. Luego seleccione el objeto y vea los permisos disponibles.

    Alternativamente, puede consultar la lista de permisos otorgados para iniciar sesión a nivel de servidor. Para hacer esto en Gerente de seguridadseleccione el inicio de sesión para el que desea ver los permisos y cambie a permisos del servidor camino. Habrá una casilla de verificación seleccionada junto a los permisos asignados Previsto columna.

    Conclusión

    En este artículo, describimos cómo crear una cuenta de usuario de SQL Server para objetos de base de datos y asignar o revocar privilegios de usuario usando T-SQL y dbForge Studio para SQL Server. Como puede ver, con el Administrador de seguridad disponible en dbForge Studio para SQL Server, puede realizar estas tareas con unos pocos clics mucho más rápido, lo que le permite ahorrar tiempo y aumentar la productividad.

    Para apreciar las otras excelentes funciones y capacidades que ofrece dbForge Studio para SQL Server, descargue la versión de prueba gratuita de 30 días de la herramienta. Después de la fecha de vencimiento, siente que desea comprar la versión completa de dbForge Studio para SQL Server, ¡sin duda!

    Artículos de interés

    Subir