Início / dba / Perguntas / 53509
Accepted
Julia McGuigan
Asked: 2013-11-19 12:29:05 +0800 CST

Configurando permissões de usuário para diferentes esquemas do SQL Server

  • 772

Eu preciso limitar o acesso a um usuário específico, mas eles ainda precisam ver os dados nas tabelas de propriedade do dbo.

Estou tentando fazer o seguinte:

  1. O esquema dbo funciona normalmente, tem acesso a tudo
  2. schema1 schema tem acesso apenas a objetos schema1
  3. se uma visualização do esquema1 ou procedimento armazenado acessar dados em tabelas de propriedade do dbo, a cadeia de permissões apropriadamente
  4. user1 tem acesso ao schema1 e nada mais; exceto no caso de #3

Aqui está o que eu tentei:

  1. Crie um usuário user1 mapeado para um login de teste com uma senha aleatória
  2. Criou algumas tabelas no esquema dbo com alguns dados de teste nelas
  3. Criado um esquema schema1
  4. Criou um schema1.get_profiles que seleciona a partir de uma visualização chamada schema1.profiles que acessa dados em dbo.people, dbo.taglinks e dbo.tags

No entanto, usando a seguinte instrução enquanto estiver conectado como user1:

EXEC get_profiles 1

resulta em:

The SELECT permission was denied on the object 'tags', database 'schema_test', schema 'dbo'.

Eu tentei WITH EXECUTE AS OWNERe não consigo entender como o "encadeamento de propriedade" deve funcionar.

eu também tentei

GRANT EXECUTE ON SCHEMA::schema1 TO user1
GRANT INSERT ON SCHEMA::schema1 TO user1
GRANT SELECT ON SCHEMA::schema1 TO user1
GRANT UPDATE ON SCHEMA::schema1 TO user1
GRANT VIEW DEFINITION ON SCHEMA::schema1 TO user1

mas recebo o seguinte erro (apesar de ser um usuário com acesso de nível dbo):

Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.

O que eu preciso é que o usuário1 possa acessar os dados por meio dos procedimentos armazenados que eu forneço, e nada mais.

Além disso, isso se destina a eventualmente residir em um banco de dados SQL Azure existente, mas estou testando primeiro em um banco de dados fictício local.

sql-server sql-server-2008

1 respostas

  1. Best Answer

    O conceito básico é usar permissões de esquema GRANT/DENY . Você pode gerenciar permissões com eficiência criando uma função e adicionando membros a ela.

    Abaixo está um exemplo que irá explicar em detalhes

    use master
go
--Create Logins
CREATE LOGIN UserA WITH Password='UserA123';
go
CREATE LOGIN UserB WITH Password='UserB123';

use AdventureWorks2008R2
go
--Create Database Users
CREATE USER UserA;
go
CREATE USER UserB;
go
--Create the Test Schemas
CREATE SCHEMA SchemaA AUTHORIZATION UserA
go
CREATE SCHEMA SchemaB AUTHORIZATION UserB
go

-- create test tables
create table schemaA.TableA (fname char(5))
go
insert into schemaA.TableA (fname) values ('Kin-A')
go

create table SchemaB.TableB (fname char(5))
go
insert into SchemaB.TableB (fname) values ('Kin-B')
go

    Agora teste:

    --Test for UserA in SchemaA
EXEC('select * from schemaA.TableA') AS USER = 'UserA'
go
--Kin-A

-- Test for UserB in SchemaB == this should fail
EXEC('select * from SchemaB.TableB') AS USER = 'UserA'
go
--Msg 229, Level 14, State 5, Line 1
--The SELECT permission was denied on the object 'TableB', database 'AdventureWorks2008R2', schema 'SchemaB'.

    Agora crie procedimentos armazenados:

    CREATE PROCEDURE SchemaB.proc_SelectUserB
AS
    select * from schemaA.TableA;
go
create procedure schemaA.proc_SchemaA
as 
    select * from schemaA.TableA

    Agora conceda permissões de execução para UserA no SP do schemaB

    GRANT EXECUTE ON OBJECT::[SchemaB].[proc_SelectUserB] TO [UserA] 
go

    Teste-o .. para ver se o UserA é capaz de executar o SP do schemaB. Isto vai passar

    EXECUTE AS LOGIN='UserA';
    Exec SchemaB.proc_SelectUserB;
    revert;
go
--- Kin-A

    Mas o UserA não poderá ver os dados do SchemaB

    EXECUTE AS LOGIN='UserA';
    select * from SchemaB.TableB
revert;
go

--- Msg 229, Level 14, State 5, Line 3
--- The SELECT permission was denied on the object 'TableB', database 'AdventureWorks2008R2', schema 'SchemaB'.

    Alternativamente, você pode usar DATABASE ROLE e apenas adicionar usuários a ele para melhor gerenciamento de permissões:

    EXEC sp_addrole 'SchemaBUsesSchemaAProc'
go
EXEC sp_addrolemember 'SchemaBUsesSchemaAProc','UserA';
go

    A instrução abaixo garantirá que o UserA possa ver o esquemaA e NÃO o esquemaB. O bom é que você pode simplesmente adicionar usuários à SchemaBUsesSchemaAProcfunção e eles herdarão todas as permissões concedidas a essa função.

    GRANT SELECT ON SCHEMA::SchemaA TO SchemaBUsesSchemaAProc;
go

    Se você deseja apenas permitir que o UserA execute SPs pertencentes ao SchemaB, a instrução abaixo fará o trabalho:

    GRANT EXECUTE ON OBJECT::[SchemaB].[proc_SelectUserB] TO [SchemaBUsesSchemaAProc] 
go

    Desta forma, UserA não consegue ver as tabelas do SchemaB, mas ainda pode executar procs do SchemaB.

    Abaixo irá explicar a hierarquia de permissões :

    insira a descrição da imagem aqui

    • 17

relate perguntas