Faaala pessoal!!
Nesse post eu gostaria de abordar um assunto muito comum no dia a dia do DBA, que é o schema padrão dos usuários no banco de dados, que pode ser ou não, diferente do padrão (dbo), conforme definição da TI.
O que é schema no SQL Server? Para que serve?
Clique aqui para visualizar o conteúdo desse tópicoSchema é basicamente uma coleção de objetos que tem o intuito de agrupar vários objetos de um departamento, área ou finalidade, seja por questões de organização de tabelas, Stored Procedures, etc, ou por motivos de controle de acesso e segurança.
Organização de tabelas
Uma das finalidades de se utilizar um schema, é organizar melhor os objetos no banco de dados. Quando todos os seus objetos estão no mesmo schema, só pode existir uma tabela “Enderecos”, por exemplo. Caso você tente criar uma nova tabela com o mesmo nome, o SQL Server irá te alertar que já existe um tabela com esse nome e interromper o seu comando.
Quando utilizamos schemas para organizar os objetos, podemos ter uma tabela “Enderecos” no schema cliente, que são os endereços dos clientes e podemos ter a tabela “Enderecos” no schema Fornecedores, que são os endereços dos fornecedores da empresa. Esse tipo de organização dos objetos de banco de dados deixa a finalidade de tabelas, procedures, funções, etc, muito mais intuitiva.
Uma forma bem clássica de organizar objetos é em projetos de BI. É bem comum que arquitetos de BI utilizem schemas dim (Dimensão), fato (Tabelas Fato), stg (Staging) e outros schemas para organizar as camadas e finalidades de cada tabela, onde, por exemplo, a tabela stg.Clientes é a tabela “Clientes” que foi importada na origem e carregada na camada de Staging e a tabela dim.Clientes já é a dimensão de clientes, uma tabela já tratada e preparada para ser utilizada nos projetos de BI.
Controle de acesso e Segurança
Outra finalidade muito importante do uso de schemas é quando falamos em controle de acessos e segurança. Como você deve saber, no SQL Server podemos liberar permissões em tabelas individuais (Ex: GRANT SELECT ON dbo.Tabela TO usuario) e também a nível global no database (Ex: GRANT SELECT TO usuario), onde ele terá acesso em todos os objetos desse banco de dados.
Esse tipo de abordagem acaba dificultando o controle de acessos em ambientes com centenas ou milhares de objetos quando todos estão no mesmo schema, pois ou você tem que analisar cada objeto individualmente ou libera permissão em todos.
Uma forma de melhorar o controle sobre as permissões de objetos é utilizando os schemas, atuando como um nível intermediário entre o acesso individual e o acesso global, onde você pode liberar permissão de SELECT somente no schema cliente, por exemplo. O usuário que recebeu essa permissão poderá consultar todas as tabelas que estão no schema cliente, mas apenas esses objetos (a não ser que ele tenha outras permissões).
Quando você agrupa seus objetos utilizando um schema por departamento, finalidade ou por sistema, por exemplo, o controle acaba ficando muito mais simples do que ter que controlar individualmente cada tabela ou Stored Procedure.
Tabelas “privadas”
Outra finalidade de se utilizar schemas é para a criação de tabelas “privadas”, onde as tabelas são criadas, por padrão, em um schema igual ao nome do usuário ao se utilizar autenticação AD e owner padrão não definido.
Isso faz com que o usuário possa criar tabelas de teste com qualquer nome que ele queira, já que apesar da tabela [dbo].Clientes existir, a tabela [DOMINIO\dirceu.resende].Clientes não. Além disso, fica fácil identificar de quem são determinadas tabelas “temporárias” criadas no database.
Tudo isso sem impactar outros usuários ou a aplicação.
FAQ: Dúvidas básicas sobre Schemas
Onde ficam os schemas do meu banco de dados?
Exibição dos schemas do database de exemplo
Como as tabelas ficam organizadas nos schemas?
Como consulto dados de tabelas com esquemas personalizados?
Como crio um novo schema?
Como altero o schema padrão de um usuário?
Como identificar usuários com schema padrão igual a nome de usuário
No dia a dia do DBA, é comum encontrar ambientes onde o schema padrão de alguns usuários com autenticação Windows não foi definido na criação, sendo atribuindo assim, um novo schema igual ao nome desse usuário. Como resultado, usuários que não tem tanto conhecimento de banco de dados acabam criando vários objetos privados no ambiente (sem saber) e reclamando com o DBA que outros processos e usuários não enxergam as tabelas que eles criam.
Para te ajudar nessa missão, vou disponibilizar um script simples para identificar qual o schema de cada usuário e quem é o owner desses schemas.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SELECT A.[name] AS username, A.type, A.[default_schema_name] AS [default_schema], C.[name] AS [schema_owner] FROM sys.database_principals A LEFT JOIN sys.schemas B ON A.default_schema_name = B.[name] LEFT JOIN sys.database_principals C ON B.principal_id = C.principal_id WHERE A.[type] IN ('U', 'S', 'G') AND A.principal_id > 4 ORDER BY 1 |
Resultado:
E agora, como identificar os usuários com schema padrão diferente do padrão (dbo):
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
SELECT A.[name] AS username, A.type, A.[default_schema_name] AS [default_schema], C.[name] AS [schema_owner], 'ALTER USER [' + A.[name] + '] WITH DEFAULT_SCHEMA=[dbo]' AS change_schema, 'ALTER USER [' + A.[name] + '] WITH DEFAULT_SCHEMA=[' + A.[default_schema_name] + ']' AS current_schema FROM sys.database_principals A LEFT JOIN sys.schemas B ON A.default_schema_name = B.[name] LEFT JOIN sys.database_principals C ON B.principal_id = C.principal_id WHERE A.[type] IN ('U', 'S', 'G') AND A.principal_id > 4 AND (B.[schema_id] IS NULL OR B.[name] NOT IN ('dbo', 'guest')) ORDER BY 1 |
Resultado:
Como identificar usuários que são donos de algum schema
Outra terefa bem comum, é identificar schemas que são propriedades de determinados usuários. Esse cenário impede a exclusão de usuários, com a mensagem de erro abaixo:
Msg 15138, Level 16, State 1, Line 41
The database principal owns a schema in the database, and cannot be dropped.
Para conseguir excluir esse usuário, você terá que, antes disso, excluir os schemas onde ele é o owner ou migrar a propriedade destes schemas para outro usuário. O script abaixo vai te ajudar a identificar quais são esses schemas:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT B.[name] AS [schema], A.[name] AS [schema_owner], 'DROP SCHEMA [' + B.[name] + '];' AS drop_schema, 'CREATE SCHEMA [' + B.[name] + ']; ALTER AUTHORIZATION ON SCHEMA::[' + B.[name] + '] TO [' + A.[name] + '];' AS create_schema FROM sys.database_principals A JOIN sys.schemas B ON A.principal_id = B.principal_id WHERE A.[type] IN ('U', 'S', 'G') AND A.principal_id > 4 ORDER BY 2 |
Resultado:
Como alterar o schema padrão em todos os databases
Para aplicar o script anterior em todos os databases, vamos recorrer à conhecida sp_MSforeachdb:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
IF (OBJECT_ID('tempdb..#Dados') IS NOT NULL) DROP TABLE #Dados CREATE TABLE #Dados ( [database] NVARCHAR(128), [username] NVARCHAR(128), [type] CHAR(1), [default_schema] NVARCHAR(128), [schema_owner] NVARCHAR(128), [change_schema] NVARCHAR(MAX), [current_schema] NVARCHAR(MAX) ) INSERT INTO #Dados EXEC master.dbo.sp_MSforeachdb ' IF (''?'' NOT IN (''master'', ''msdb'', ''model'', ''tempdb'')) BEGIN SELECT ''?'' AS [database], A.[name] AS username, A.type, A.[default_schema_name] AS [default_schema], C.[name] AS [schema_owner], ''USE [?]; ALTER USER ['' + A.[name] + ''] WITH DEFAULT_SCHEMA=[dbo]'' AS change_schema, ''USE [?]; ALTER USER ['' + A.[name] + ''] WITH DEFAULT_SCHEMA=['' + A.[default_schema_name] + '']'' AS current_schema FROM [?].sys.database_principals A LEFT JOIN [?].sys.schemas B ON A.default_schema_name = B.[name] LEFT JOIN [?].sys.database_principals C ON B.principal_id = C.principal_id WHERE A.[type] IN (''U'', ''S'', ''G'') AND A.principal_id > 4 AND (B.[schema_id] IS NULL OR B.[name] NOT IN (''dbo'', ''guest'')) END' SELECT * FROM #Dados |
Resultado:
Como identificar objetos criados em schemas diferentes do padrão
Outra tarefa comum no dia a dia do DBA é identificar objetos que foram criados em schemas de usuários e movê-los para o schema dbo (ou outro qualquer), quando necessário. Para isso, vou disponibilizar o script abaixo:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
SELECT A.[name] AS [object], A.[type_desc], A.create_date, A.modify_date, A.[default_schema_name] AS [schema_name], C.[type_desc] AS [user_type], 'ALTER SCHEMA [dbo] TRANSFER [' + B.[name] + '].[' + A.[name] + '];' AS [to_default_schema] FROM sys.objects A JOIN sys.schemas B ON B.[schema_id] = A.[schema_id] JOIN sys.database_principals C ON B.[name] = C.[name] WHERE C.[type] IN (''U'', ''S'', ''G'') AND C.principal_id > 4 AND C.is_fixed_role = 0 ORDER BY 1 |
Resultado:
E se eu quiser mover os objetos de um schema para outro ?
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
DECLARE @schema_origem VARCHAR(128) = 'dirceu.resende', @schema_destino VARCHAR(128) = 'clientes' SELECT A.[name] AS [object], A.[type_desc], A.create_date, A.modify_date, B.[name] AS [schema_name], C.[type_desc] AS [user_type], 'ALTER SCHEMA [' + @schema_destino + '] TRANSFER [' + B.[name] + '].[' + A.[name] + '];' AS [to_default_schema] FROM sys.objects A JOIN sys.schemas B ON B.[schema_id] = A.[schema_id] JOIN sys.database_principals C ON B.[name] = C.[name] WHERE B.[name] = @schema_origem ORDER BY 1 |
Resultado:
Stored Procedure para unificar schema padrão e objetos
Caso você esteja buscando uma solução bem prática para redefinir o schema padrão de todos os usuários para o padrão (dbo) e mover todos os objetos privados para o schema dbo também, utilize a Stored Procedure abaixo:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 |
IF (OBJECT_ID('dbo.stpRedefine_Schema_Padrao') IS NULL) EXEC('CREATE PROCEDURE dbo.stpRedefine_Schema_Padrao AS SELECT 1') GO ALTER PROCEDURE dbo.stpRedefine_Schema_Padrao ( @Fl_Altera_Usuarios BIT = 1, @Fl_Altera_Objetos BIT = 1, @Fl_Debug BIT = 0 ) AS BEGIN SET NOCOUNT ON DECLARE @Comando VARCHAR(MAX) IF (@Fl_Altera_Usuarios = 1) BEGIN IF (OBJECT_ID('tempdb..#Usuarios') IS NOT NULL) DROP TABLE #Usuarios CREATE TABLE #Usuarios ( [database] NVARCHAR(128), [username] NVARCHAR(128), [type] CHAR(1), [default_schema] NVARCHAR(128), [schema_owner] NVARCHAR(128), [change_schema] NVARCHAR(MAX), [current_schema] NVARCHAR(MAX) ) INSERT INTO #Usuarios EXEC master.dbo.sp_MSforeachdb ' IF (''?'' NOT IN (''master'', ''msdb'', ''model'', ''tempdb'')) BEGIN SELECT ''?'' AS [database], A.[name] AS username, A.type, A.[default_schema_name] AS [default_schema], C.[name] AS [schema_owner], ''USE [?]; ALTER USER ['' + A.[name] + ''] WITH DEFAULT_SCHEMA=[dbo];'' AS change_schema, ''USE [?]; ALTER USER ['' + A.[name] + ''] WITH DEFAULT_SCHEMA=['' + A.[default_schema_name] + ''];'' AS current_schema FROM [?].sys.database_principals A LEFT JOIN [?].sys.schemas B ON A.default_schema_name = B.[name] LEFT JOIN [?].sys.database_principals C ON B.principal_id = C.principal_id WHERE A.[type] IN (''U'', ''S'', ''G'') AND A.principal_id > 4 AND (B.[schema_id] IS NULL OR B.[name] NOT IN (''dbo'', ''guest'')) END' SET @Comando = '' SELECT @Comando += change_schema + CHAR(10) FROM #Usuarios ORDER BY [database], username IF (@Fl_Debug = 1) BEGIN PRINT '------------------- Alterando o schema padrão dos usuários -------------------' PRINT '' PRINT @Comando END ELSE EXEC(@Comando) END IF (@Fl_Altera_Objetos = 1) BEGIN IF (OBJECT_ID('tempdb..#Objetos') IS NOT NULL) DROP TABLE #Objetos CREATE TABLE #Objetos ( [database] NVARCHAR(128), [object] NVARCHAR(128), [type] VARCHAR(30), [schema_name] NVARCHAR(128), [schema_owner] NVARCHAR(128), [change_schema] NVARCHAR(MAX) ) INSERT INTO #Objetos EXEC master.sys.sp_MSforeachdb ' IF (''?'' NOT IN (''master'', ''msdb'', ''model'', ''tempdb'')) BEGIN SELECT ''?'' AS [database], A.[name] AS [object], A.[type_desc], A.[default_schema_name] AS [schema_name], C.[type_desc] AS [user_type], ''USE [?]; ALTER SCHEMA [dbo] TRANSFER ['' + B.[name] + ''].['' + A.[name] + ''];'' AS [to_default_schema] FROM [?].sys.objects A JOIN [?].sys.schemas B ON B.[schema_id] = A.[schema_id] JOIN [?].sys.database_principals C ON B.[name] = C.[name] WHERE C.[type] IN (''U'', ''S'', ''G'') AND C.principal_id > 4 AND C.is_fixed_role = 0 END' SET @Comando = '' SELECT @Comando += change_schema + CHAR(10) FROM #Objetos ORDER BY [database], [object] IF (@Fl_Debug = 1) BEGIN PRINT '------------------- Alterando o schema padrão dos objetos -------------------' PRINT '' PRINT @Comando END ELSE EXEC(@Comando) END END |
Exemplo de uso:
|
1 2 3 4 |
EXEC dbo.stpRedefine_Schema_Padrao @Fl_Altera_Usuarios = 1, -- bit @Fl_Altera_Objetos = 1, -- bit @Fl_Debug = 1 -- bit |
Resultado:
Referências
– https://pt.stackoverflow.com/questions/21031/o-que-s%C3%A3o-schemas-quais-as-vantagens-de-us%C3%A1-lo
– https://www.devmedia.com.br/schemas-no-sql-server/24328
– http://www.informit.com/articles/article.aspx?p=1216889&seqNum=2
– https://blog.sqlauthority.com/2009/09/07/sql-server-importance-of-database-schemas-in-sql-server/
– https://www.quackit.com/sql_server/sql_server_2008/tutorial/sql_server_database_schemas.cfm
