主页 / dba / 问题 / 245016
Accepted
user9516827
Asked: 2019-08-10 10:56:05 +0800 CST

为用户 SQL Server 创建同义词错误

  • 772

我有一个用户有权在我的 sql server 2016 数据库中创建同义词。该用户还具有数据读取器权限。当用户尝试创建同义词时，如下所示。

USE [DEMODB] GO CREATE SYNONYM [dbo].[MYSYN] FOR [SERVER01].[DEMODB].[dbo].[CHILD] GO 用户收到以下错误消息。 The specified schema name "dbo" either does not exist or you do not have permission to use it.

我检查了登录/用户设置。并且登录属于公共服务器角色，用户映射到默认模式为 [dbo] 的数据库

并且还为该用户授予该数据库的创建同义词权限，如前所述。

我怎样才能解决这个问题。

sql-server permissions

3 个回答

  1. 用户不仅需要CREATE SYNONYM语句权限，还需要ALTER模式权限才能创建同义词。应谨慎授予ALTER对模式的权限，尤其是，以遵循最小权限的安全原则。dbo如果用户在他们自己的模式中或在具有所有者的模式中创建同义词，而不是dbo为了使用户被沙箱化到该模式中，那会更好（也更容易） ALTER。

    如果最低权限用户必须在dbo架构中创建同义词，请考虑将CREATE SYNONYMDDL 封装在存储过程中并使用模块签名来提升权限。这样，对 proc 具有执行权限的用户可以在dbo模式中创建同义词，但仅限于存储过程执行的功能。下面是这种技术的一个例子。

    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

    如Microsoft 的文档中所述CREATE SCHEMA：

    “要在给定架构中创建同义词，用户必须拥有 CREATE SYNONYM 权限，并且要么拥有架构，要么拥有 ALTER SCHEMA 权限。”

    • 1

  3. 我怎样才能解决这个问题

    你应该如何解决它是

    1) 与更高权限的用户连接以创建同义词。

    或者

    2) 提供用户拥有的模式并让他们在那里创建同义词。

    您不应该如何解决此问题是授予“具有数据读取权限”的用户 ALTER 对 schema::dbo。SQL Server 安全的基本规则是，只有特权用户才能在其他用户的架构中创建对象，因为这无异于允许他们冒充架构所有者。

    • 1

相关问题