Symptômes
Si une application basée sur le rôle de l'application essaie de sélectionner tous les enregistrements d'une des tables système dans une base de données master SQL Server 2005, vous pouvez rencontrer l'un des symptômes suivants :
-
Aucun enregistrement n'est retourné.
-
Le message d'erreur suivant s'affiche :
Autorisation refusée.
Par exemple, ce problème peut se produire si l'application utilise la requête suivante.
select * from master.dbo.syslogins
Cause
Les applications basées sur le rôle de l'application sont conçues pour fonctionner avec des informations spécifiques dans une base de données. Elles ne peuvent pas accéder aux tables système dans une vue master ou dans une vue de gestion dynamique. Ces vues contiennent des informations de niveau serveur.
Résolution
Pour résoudre ce problème, utilisez des procédures signées par certificat pour accéder aux tables système au niveau du serveur. Les procédures signées par certificat offrent les avantages suivants :
-
L'utilisation d'indicateurs de trace n'est pas obligatoire.
-
Les informations de niveau de serveur inférieur peuvent être consultées. Les applications basées sur le rôle de l'application doivent utiliser des procédures stockées au lieu d'utiliser des requêtes génériques. Il est plus probable que les procédures stockées retournent uniquement les données spécifiques requises par l'application.
Contournement
Pour contourner ce problème, activez l'indicateur de trace global 4616.
Plus d'informations
Le code suivant est un exemple d'une procédure signée par certificat.
USE master
GO
CREATE DATABASE approle_db ;
GO
CREATE LOGIN some_login WITH PASSWORD = 'SomePa$$word!' ;
GO
USE approle_db
GO
CREATE USER some_user FOR LOGIN some_login
GO
EXEC sp_addapprole 'an_approle', 'SomeAppRolePa$$word!' ;
GO
---------------------------------------------------------------------
-- This section shows how to use a certificate to authenticate
-- a signed procedure.
---------------------------------------------------------------------
CREATE LOGIN execute_as_login WITH PASSWORD = 'SomePa$$word!' ;
GO
USE master
GO
GRANT VIEW ANY DEFINITION TO execute_as_login ;
GRANT VIEW SERVER STATE TO execute_as_login ;
GO
USE approle_db
GO
CREATE USER execute_as_user FOR LOGIN execute_as_login ;
GO
--
-- You must use EXECUTE AS 'authenticator' here because the application role
-- does not have a server identity. Therefore, the application role cannot use
-- the certificate permissions on the server. Therefore, you
-- need a new execution context to which you can grant
-- the needed VIEW* permissions.
--
CREATE PROC access_server_system_tables
WITH EXECUTE AS 'execute_as_user'
AS
SELECT * FROM master.dbo.syslogins ;
SELECT * FROM master.dbo.sysprocesses ;
GO
GRANT EXECUTE ON access_server_system_tables TO an_approle ;
GO
CREATE CERTIFICATE signing_cert ENCRYPTION BY PASSWORD = 'SomeCertPa$$word'
WITH SUBJECT = 'Signing Cert' ;
GO
BACKUP CERTIFICATE signing_cert TO FILE = 'signing_cert.cer' ;
GO
ADD SIGNATURE TO access_server_system_tables
BY CERTIFICATE signing_cert WITH PASSWORD = 'SomeCertPa$$word' ;
GO
---------------------------------------------------------------------
-- We must create a copy of the signing certificate in the target
-- database. In this case, the target database is the master database.
-- This copy of the signing certificate can vouch
-- for the execution contexts that enter this database from the
-- signed procedure.
---------------------------------------------------------------------
USE master
GO
CREATE CERTIFICATE signing_cert FROM FILE = 'signing_cert.cer' ;
GO
--
-- Because the VIEW* permissions in question are server-level permissions,
-- we need an AUTHENTICATE SERVER on a login-mapped certificate.
--
CREATE LOGIN signing_cert_login FROM CERTIFICATE signing_cert ;
GO
GRANT AUTHENTICATE SERVER TO signing_cert_login
GO
---------------------------------------------------------------------
-- Now you can open a new connection as "some_login" and
-- set the application role. Then, call the "access_server_system_tables"
-- procedure, and obtain verification that you can access server-level information
-- when the application role-based application runs.
---------------------------------------------------------------------
---------------------------------------------------------------------
-- Clean up after the procedure.
---------------------------------------------------------------------
USE master
GO
DROP DATABASE approle_db ;
GO
DROP LOGIN some_login;
GO
DROP LOGIN execute_as_login;
GO
DROP LOGIN signing_cert_login ;
GO
DROP CERTIFICATE signing_cert;
GO
--
-- Make sure to delete the certificate file. For example, delete
-- C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\signing_cert.cer
--
EXEC sp_configure 'show advanced options', 1 ;
GO
RECONFIGURE ;
GO
EXEC sp_configure 'xp_cmdshell', 1 ;
GO
RECONFIGURE ;
GO
EXEC xp_cmdshell 'del "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\signing_cert.cer"' ;
GO
EXEC sp_configure 'xp_cmdshell', 0 ;
GO
RECONFIGURE ;
GO
-- ============================================================================
-- - Application role access to server information - Demo usage.sql
--
--
-- This code is companion code that shows an example of application role access
-- to server information by using a certificate-signed procedure.
--
-- ============================================================================
--------------------------------------------------
-- Connect as some_login
--------------------------------------------------
USE approle_db
GO
EXEC sp_setapprole 'an_approle', 'SomeAppRolePa$$word!'
GO
EXEC access_server_system_tables
GO
