Início / dba / Perguntas / 245016
Accepted
user9516827
Asked: 2019-08-10 10:56:05 +0800 CST

Criar erro de sinônimo para um usuário-SQL Server

  • 772

Eu tenho um usuário que tem permissão para criar sinônimo no meu banco de dados sql server 2016. Esse usuário também tem permissão de leitor de dados. Quando o usuário tentou criar sinônimo conforme mostrado abaixo.

USE [DEMODB] GO CREATE SYNONYM [dbo].[MYSYN] FOR [SERVER01].[DEMODB].[dbo].[CHILD] GO O usuário recebe a mensagem de erro abaixo. The specified schema name "dbo" either does not exist or you do not have permission to use it.

Verifiquei as configurações de login/usuário. E o login pertence à função de servidor público e o usuário é mapeado para o banco de dados com o esquema padrão como [dbo]

E também a permissão de criação de sinônimos é concedida para esse banco de dados para esse usuário, conforme mencionado anteriormente.

Como posso consertar isso.

sql-server permissions

3 respostas

  1. Um usuário requer não apenas CREATE SYNONYMpermissões de instrução, mas também ALTERpermissões no esquema para criar um sinônimo. ALTERpermissão em um esquema, especialmente dbo, deve ser concedida com cuidado para seguir o princípio de segurança de privilégio mínimo. Seria melhor (e mais fácil) se o usuário criasse o sinônimo em seu próprio esquema ou em um esquema com um proprietário diferente dbopara que o usuário fosse colocado em sandbox nesse esquema para ALTERfins.

    Se o usuário com privilégios mínimos precisar criar o sinônimo no dboesquema, considere encapsular o CREATE SYNONYMDDL em um procedimento armazenado e use a assinatura do módulo para elevar as permissões. Dessa forma, um usuário com permissões de execução no proc pode criar sinônimos no dboesquema, mas está limitado à funcionalidade executada pelo procedimento armazenado. Abaixo está um exemplo desta técnica.

    CREATE OR ALTER PROC dbo.CreateSynonym
      @SynonymSchemaName sysname
    , @SynonymName sysname
    , @SynonymForServerName sysname
    , @SynonymForDatabaseName sysname
    , @SynonymForSchemaName sysname
    , @SynonymForObjectName sysname
AS
DECLARE @CreateSynonymStatement nvarchar(MAX) = N'CREATE SYNONYM ' 
    + QUOTENAME(@SynonymSchemaName)
    + N'.'
    + QUOTENAME(@SynonymName)
    + N' FOR '
    + QUOTENAME(@SynonymForServerName)
    + N'.'
    + QUOTENAME(@SynonymForDatabaseName)
    + N'.'
    + QUOTENAME(@SynonymForSchemaName)
    + N'.'
    + QUOTENAME(@SynonymForObjectName)
    + N';'
EXEC sp_executesql @CreateSynonymStatement;
GO

--create certificate and sign proc
CREATE CERTIFICATE CreateSynonymCert
   ENCRYPTION BY PASSWORD = 'T3mP0@rypAsso0rd'
   WITH SUBJECT = 'For CREATE SYNONYM in dbo schema';
ADD SIGNATURE TO dbo.CreateSynonym BY CERTIFICATE CreateSynonymCert WITH PASSWORD = 'T3mP0@rypAsso0rd';

--private key is ephemoral for this purpose so we remove it
ALTER CERTIFICATE CreateSynonymCert REMOVE PRIVATE KEY;

--create a user from certificate with the needed permissions
CREATE USER CreateSynonymCertUser FROM CERTIFICATE CreateSynonymCert;
GRANT CREATE SYNONYM TO CreateSynonymCertUser;
GRANT ALTER ON SCHEMA::dbo TO CreateSynonymCertUser;

--We could grant execute directly to the user but a role is easier to manage
--when multiple users are involved and adheres to RBAC security principles.
CREATE ROLE CreateSynonymRole;
ALTER ROLE CreateSynonymRole ADD MEMBER YourUser;
GRANT EXECUTE ON dbo.CreateSynonym TO CreateSynonymRole;
GO

--example usage
EXECUTE AS USER = 'YourUser';
GO
EXEC dbo.CreateSynonym
      @SynonymSchemaName = N'dbo'
    , @SynonymName = N'MYSYN'
    , @SynonymForServerName  = N'SERVER01'
    , @SynonymForDatabaseName  = N'DEMODB'
    , @SynonymForSchemaName  = N'dbo'
    , @SynonymForObjectName  = N'CHILD';
GO
REVERT;
GO
    • 2
  2. Best Answer

    Conforme observado na documentação da Microsoft para CREATE SCHEMA:

    "Para criar um sinônimo em um determinado esquema, um usuário deve ter a permissão CREATE SYNONYM e possuir o esquema ou ter a permissão ALTER SCHEMA ."

    • 1

  3. Como posso consertar isso

    Como você deve corrigi-lo é para qualquer um

    1) Conecte-se com um usuário com privilégios mais altos para criar o sinônimo.

    ou

    2) Forneça um esquema de propriedade do usuário e faça com que ele crie o sinônimo lá.

    Como você não deve corrigir isso é conceder a um usuário que "tem permissão de leitor de dados" ALTER no schema::dbo. É uma regra fundamental da segurança do SQL Server que apenas um usuário privilegiado pode criar objetos nos esquemas de outros usuários, pois isso equivale a permitir que eles representem o proprietário do esquema.

    • 1

relate perguntas