Как зашифровать все существующие хранимые процедуры базы данных
Автор вопроса: MarcОтветов: 8
Есть ли возможность зашифровать все существующие хранимые процедуры базы данных SQL Server 2008 ПОСЛЕ того, как они были созданы с помощью сценария SQLCMD?
Причина, по которой я хочу сделать это, следующая: Я хотел бы разработать хранимые процедуры без шифрования, чтобы я мог легко щелкнуть "Изменить" в SQL Server Management Studio, чтобы проверить их содержимое. Однако , для развертывания я хотел бы зашифровать их, поэтому я подумал, что, возможно, я мог бы написать скрипт, который шифрует их только после их создания. Для систем разработки я бы просто не запускал сценарий, в то время как в системах конечных пользователей сценарий запускался бы.
Мое решение состоит в том, чтобы поместить "-- WITH ENCRYPTION" во все мои хранимые процедуры. Эта версия используется разработчиками и хранится в системе контроля версий.
Затем я использую инструмент (например, sed) в своей сборке, чтобы заменить "-- WITH ENCRYPTION" на "WITH ENCRYPTION" в файлах, прежде чем отправлять их для установки.
Для чистого решения SQL вы можете использовать REPLACE.
Если вы когда-нибудь решите, что вам необходимо защитить свои хранимые процедуры SQL, и сочтете, что шифрование - это хорошая идея, БУДЬТЕ ОЧЕНЬ ОСТОРОЖНЫ!!! Шифрование хранимых процедур базы данных НЕ СЛЕДУЕТ выполнять без файлов резервных копий или какой-либо системы управления версиями для хранимых процедур. Причина, по которой я говорю это, заключается в том, что после того, как они зашифрованы, их уже не вернуть. (Да, есть сторонние инструменты, которые расшифруют ваш код, но зачем мучиться.)
Я разработал этот трюк, потому что моей компании нужно было разместить приложение на другом сервере, и мы были обеспокоены тем, что наш код может быть скомпрометирован. Итак, чтобы доставить базу данных, мы решили зашифровать все хранимые процедуры. Имея более сотни написанных процедур, я не хотел открывать каждую процедуру и вставлять "С ШИФРОВАНИЕМ" в каждую хранимую процедуру. (Для тех из вас, кто не знает, как шифровать, см. Как защитить код хранимой процедуры [^]). Поэтому я решил сделать свое собственное маленькое приложение на C#, которое делало бы то же самое.
Это приложение представляет собой консольное приложение, созданное с использованием Visual Studio 2005 и SQL Server 2005. Входными параметрами являются имя базы данных, адрес сервера, имя пользователя и пароль базы данных. Как только вы сможете предоставить эти данные, все ваши хранимые процедуры будут зашифрованы.
Я разместил здесь код своего приложения как есть. Чтобы этот код работал, вам потребуется добавить в приложение ссылку на Microsft.SQlserver.SMO, чтобы были доступны такие классы, как Database и StoredProcedure.
BEFORE YOU DO THIS, TAKE A BACKUP!!!!!!!
//Connect to the local, default instance of SQL Server.
string DB = "";
ServerConnection objServerCOnnection = new ServerConnection();
objServerCOnnection.LoginSecure = false;
Console.WriteLine("Enter name or IP Address of the Database Server.");
objServerCOnnection.ServerInstance = Console.ReadLine();
Console.WriteLine("Enter name of the Database");
DB = Console.ReadLine();
Console.WriteLine("Enter user id");
objServerCOnnection.Login = Console.ReadLine();
Console.WriteLine("Enter Password");
objServerCOnnection.Password = Console.ReadLine();
Console.WriteLine(" ");
Server srv = new Server();
try // Check to see if server connection details are ok.
{
srv = new Server(objServerCOnnection);
if (srv == null)
{
Console.WriteLine("Server details entered are wrong,"
+ " Please restart the application");
Console.ReadLine();
System.Environment.Exit(System.Environment.ExitCode);
}
}
catch
{
Console.WriteLine("Server details entered are wrong,"
+ " Please restart the application");
Console.ReadLine();
System.Environment.Exit(System.Environment.ExitCode);
}
Database db = new Database();
try // Check to see if database exists.
{
db = srv.Databases[DB];
if (db == null)
{
Console.WriteLine("Database does not exist on the current server,"
+ " Please restart the application");
Console.ReadLine();
System.Environment.Exit(System.Environment.ExitCode);
}
}
catch
{
Console.WriteLine("Database does not exist on the current server,"
+ " Please restart the application");
Console.ReadLine();
System.Environment.Exit(System.Environment.ExitCode);
}
string allSP = "";
for (int i = 0; i < db.StoredProcedures.Count; i++)
{
//Define a StoredProcedure object variable by supplying the parent database
//and name arguments in the constructor.
StoredProcedure sp;
sp = new StoredProcedure();
sp = db.StoredProcedures[i];
if (!sp.IsSystemObject)// Exclude System stored procedures
{
if (!sp.IsEncrypted) // Exclude already encrypted stored procedures
{
string text = "";// = sp.TextBody;
sp.TextMode = false;
sp.IsEncrypted = true;
sp.TextMode = true;
sp.Alter();
Console.WriteLine(sp.Name); // display name of the encrypted SP.
sp = null;
text = null;
}
}
}
Ответ 3 · рейтинг 2 · Raj More
WITH ENCRYPTION означает, что код процедуры не хранится в таблице SysComments.
Вы можете написать скрипт, который выполняет exec sp_helptext 'MyProcName' и помещает содержимое в VarChar (MAX), чтобы он мог легко хранить многострочные/большие процедуры, а затем изменять процедуру из исходного состояния.
CREATE MyProcName AS
SELECT SecretColumns From TopSecretTable
измените CREATE на ALTER и AS в окружении пробела, табуляции или новой строки (хорошее место для использования регулярных выражений) на WITH ENCRYPTION AS
ALTER MyProcName WITH ENCRYPTION AS
SELECT SecretColumns From TopSecretTable
Это скроет весь код хранимой процедуры на рабочем сервере.
Вы можете поместить это в LOOP или CURSOR (имхо, на самом деле это не операция на основе набора) для всех объектов определенного типа и/или соглашения об именах, которые вы хотите зашифровать, и запускать его каждый раз при развертывании.
Ответ 4 · рейтинг 1 · devstuff
Я бы рекомендовал создать sproc в многострочной строковой переменной, а затем вставить или изменить ее с помощью sp_executesql. Единственным досадным недостатком этого подхода является удвоение одинарных кавычек для строк.
DECLARE @action varchar(max);
SET @action = 'CREATE'; /* or "ALTER" */
DECLARE @withEncryption varchar(max);
SET @withEncryption = ''; /* or "WITH ENCRYPTION" */
DECLARE @sql varchar(max);
SET @sql = @action + ' PROCEDURE dbo.Something'
(
....
) ' + @withEncryption +
' AS
BEGIN
DECLARE @bob varchar(10);
SET @bob = ''Bob'';
....
END;
';
EXEC sp_executesql @statement = @sql;
[Обратите внимание на пробелы вокруг переменных.]
Все мои скрипты используют этот метод, который хорошо работает, когда вы привыкаете к удвоению кавычек.
Я также использую пакетный файл для вызова сценария и переменные командной строки режима SQLCMD для выбора различных вариантов поведения, что делает его воспроизводимым и простым для тестирования.
Ответ 5 · рейтинг 1 · Ranjeet
Используйте этот запрос, который шифрует все процедуры в базе данных
CREATE TABLE #backup
(
id BIGINT IDENTITY(1, 1),
sptext NVARCHAR(MAX) NOT NULL,
spname NVARCHAR(100) NOT NULL,
encrypttext NVARCHAR(MAX) NULL,
encryptstatus BIT NOT NULL
DEFAULT ( 0 )
)
DECLARE @sptexttable TABLE
(
id BIGINT IDENTITY(1, 1),
sptext NVARCHAR(MAX),
spname NVARCHAR(100)
)
INSERT INTO @sptexttable ( sptext, spname )
SELECT [text],
[name]
FROM syscomments
JOIN sysobjects ON syscomments.id = sysobjects.id
AND sysobjects.xtype = 'p'
DECLARE @sptext NVARCHAR(MAX)
DECLARE @spname NVARCHAR(100)
DECLARE @counter INT
SET @counter = 1
WHILE @counter <= ( SELECT MAX(id)
FROM @sptexttable
)
BEGIN
BEGIN TRY
INSERT INTO #backup ( sptext, spname )
SELECT sptext,
spname
FROM @sptexttable
WHERE id = @counter
END TRY
BEGIN CATCH
END CATCH
IF NOT EXISTS ( SELECT [name]
FROM sysobjects
WHERE [name] = 'ce_LastIndexOf'
AND xtype = 'FN' )
BEGIN
EXEC
( 'CREATE FUNCTION ce_LastIndexOf
(
@strValue VARCHAR(4000),
@strChar VARCHAR(50)
)
RETURNS INT
AS BEGIN
DECLARE @index INT
SET @index = 0
WHILE CHARINDEX(@strChar, @strValue) > 0
BEGIN
SET @index = @index
+ CASE WHEN CHARINDEX(@strChar, @strValue) > 1
THEN ( LEN(@strValue) - LEN(SUBSTRING(@strValue,
CHARINDEX(@strChar, @strValue)
+ LEN(@strChar),
LEN(@strValue))) )
ELSE 1
END
SET @strValue = SUBSTRING(@strValue,
CHARINDEX(@strChar, @strValue)
+ LEN(@strChar), LEN(@strValue))
END
RETURN @index
END'
)
END
DECLARE @tempproc NVARCHAR(MAX)
DECLARE @procindex INT
DECLARE @beginindex INT
DECLARE @header NVARCHAR(MAX)
DECLARE @asindex INT
DECLARE @replacetext NVARCHAR(MAX)
SET @tempproc = ( SELECT sptext
FROM @sptexttable
WHERE id = @counter
)
IF ( SELECT CHARINDEX('CREATE PROC', UPPER(@tempproc))
) > 0
BEGIN
BEGIN TRY
SELECT @procindex = CHARINDEX('PROC', UPPER(@tempproc))
PRINT @procindex
SELECT @beginindex = CHARINDEX('BEGIN', UPPER(@tempproc))
PRINT @beginindex
SELECT @header = SUBSTRING(@tempproc, @procindex,
@beginindex - @procindex)
SELECT @asindex = ( SELECT dbo.ce_lastindexof(@header, 'AS')
- 2
)
SELECT @replacetext = STUFF(@header, @asindex, 10,
CHAR(13) + 'WITH ENCRYPTION'
+ CHAR(13) + 'AS' + CHAR(13))
SET @tempproc = REPLACE(@tempproc, @header, @replacetext)
END TRY
BEGIN CATCH
END CATCH
END
UPDATE @sptexttable
SET sptext = @tempproc
WHERE id = @counter
--PLAY HERE TO M AKE SURE ALL PROCS ARE ALTERED
UPDATE @sptexttable
SET sptext = ( SELECT REPLACE(sptext, 'CREATE PROC',
'ALTER PROC')
FROM @sptexttable
WHERE id = @counter
)
WHERE id = @counter
SELECT @sptext = sptext,
@spname = spname
FROM @sptexttable
WHERE id = @counter
BEGIN TRY
EXEC ( @sptext
)
UPDATE #backup
SET encrypttext = @sptext,
encryptstatus = 1
WHERE id = @counter
END TRY
BEGIN CATCH
PRINT 'the stored procedure ' + @spname
+ ' cannot be encrypted automatically'
END CATCH
SET @counter = @counter + 1
END
SELECT *
FROM #backup
Ответ 6 · рейтинг 1 · Jannie Dieanderou Geyser
Я написал курсор, проходит и шифрует большинство объектов.
DECLARE cur_ENCRYPT_ANTHING CURSOR READ_ONLY
FOR
SELECT STUFF(src.definition,
CASE WHEN CHARINDEX('AS' + CHAR(13),src.definition,1) = 0
THEN CASE WHEN CHARINDEX('AS ' + CHAR(13),src.definition,1) = 0 THEN CHARINDEX('AS ',src.definition,1)
ELSE CHARINDEX('AS ' + CHAR(13),src.definition,1)
END
ELSE CHARINDEX('AS' + CHAR(13),src.definition,1)
END,3,'WITH ENCRYPTION AS' + CHAR(13))
FROM (SELECT o.name
, STUFF(RIGHT(sm.definition,LEN(sm.definition) - CHARINDEX('CREATE ',sm.definition,1) + 1),1,6,'ALTER') AS definition
FROM sys.sql_modules AS sm
JOIN sys.objects AS o ON sm.object_id = o.object_id
WHERE CAST(CASE WHEN sm.definition IS NULL THEN 1
ELSE 0
END AS BIT) = 0
AND type <> 'TR'
) AS src
DECLARE @VLS NVARCHAR(MAX)
OPEN cur_ENCRYPT_ANTHING
FETCH NEXT FROM cur_ENCRYPT_ANTHING INTO @VLS
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
BEGIN TRY
EXEC (@VLS)
END TRY
BEGIN CATCH
PRINT ERROR_MESSAGE()
PRINT ''
PRINT @VLS
END CATCH
END
FETCH NEXT FROM cur_ENCRYPT_ANTHING INTO @VLS
END
CLOSE cur_ENCRYPT_ANTHING
DEALLOCATE cur_ENCRYPT_ANTHING
Ответ 7 · thomas correge
1) Я экспортирую Create code для SP и функций. Сохраняйте резервную копию. например D:\SP2.sql"
2) этот транзакционный код SQL, сгенерируйте скрипт для удаления существующих SP & Functions
SELECT 'DROP PROCEDURE [' + SCHEMA_NAME(p.schema_id) + '].[' + p.NAME + ']' as A
FROM sys.procedures p
union
SELECT 'DROP FUNCTION ' + [name]
FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0
order by a
Было бы быстрее с .replace( ,), но проблема с концом строк...
4) запустите SP-WithEncrypt.sql в SSMS
Ответ 8 · Conroy Smith
Я обновил один из приведенных выше ответов, удалив зависимость от начального тега Begin. У меня была ситуация, когда не все мои хранимые процедуры имели BEGIN и END.
Вместо этого я использовал предложение AS, а также использовал версию charindex с учетом регистра (путем добавления сопоставления)
Это не идеальное решение, но помогло зашифровать больше моих хранимых процедур.
Вот мой обновленный код:
IF OBJECT_ID('tempdb..#backup', 'U') IS NOT NULL
BEGIN
DROP TABLE #backup
END
CREATE TABLE #backup
(
id BIGINT IDENTITY(1, 1),
sptext NVARCHAR(MAX) NOT NULL,
spname NVARCHAR(100) NOT NULL,
encrypttext NVARCHAR(MAX) NULL,
encryptstatus BIT NOT NULL
DEFAULT ( 0 )
)
DECLARE @sptexttable TABLE
(
id BIGINT IDENTITY(1, 1),
sptext NVARCHAR(MAX),
spname NVARCHAR(100)
)
INSERT INTO @sptexttable ( sptext, spname )
SELECT [text],
[name]
FROM syscomments
JOIN sysobjects ON syscomments.id = sysobjects.id
AND sysobjects.xtype = 'p'
DECLARE @sptext NVARCHAR(MAX)
DECLARE @spname NVARCHAR(100)
DECLARE @counter INT
SET @counter = 1
WHILE @counter <= ( SELECT MAX(id)
FROM @sptexttable
)
BEGIN
BEGIN TRY
INSERT INTO #backup ( sptext, spname )
SELECT sptext,
spname
FROM @sptexttable
WHERE id = @counter
END TRY
BEGIN CATCH
END CATCH
IF NOT EXISTS ( SELECT [name]
FROM sysobjects
WHERE [name] = 'CaseSensitiveIndex'
AND xtype = 'FN' )
BEGIN
EXEC (
'CREATE FUNCTION dbo.CaseSensitiveIndex(@source nvarchar(max), @pattern VARCHAR(50))
RETURNS int
BEGIN
return CHARINDEX(@pattern COLLATE Latin1_General_CS_AS, @source COLLATE Latin1_General_CS_AS)
END; '
)
end
IF NOT EXISTS ( SELECT [name]
FROM sysobjects
WHERE [name] = 'ce_LastIndexOf'
AND xtype = 'FN' )
BEGIN
EXEC
( 'CREATE FUNCTION ce_LastIndexOf
(@strValue VARCHAR(max),
@strChar VARCHAR(50))
RETURNS INT
AS
BEGIN
DECLARE @index INT
SET @index = 0
WHILE CHARINDEX(@strChar, @strValue) > 0
BEGIN
SET @index = @index + CASE WHEN CHARINDEX(@strChar, @strValue) > 1
THEN
(LEN(@strValue) - LEN(SUBSTRING(@strValue,CHARINDEX(@strChar, @strValue) + LEN(@strChar),LEN(@strValue))))
ELSE
1
END
SET @strValue = SUBSTRING(@strValue,CHARINDEX(@strChar, @strValue) + len(@strChar),LEN(@strValue))
END
RETURN @index
END'
)
END
DECLARE @tempproc NVARCHAR(MAX)
DECLARE @procindex INT
DECLARE @beginindex INT
DECLARE @header NVARCHAR(MAX)
DECLARE @asindex INT
DECLARE @replacetext NVARCHAR(MAX)
SET @tempproc = ( SELECT sptext
FROM @sptexttable
WHERE id = @counter
)
IF ( SELECT CHARINDEX('CREATE PROC', UPPER(@tempproc))
) > 0
BEGIN
BEGIN TRY
SELECT @procindex = CHARINDEX('PROC', UPPER(@tempproc))
PRINT @procindex
SELECT @beginindex=(select dbo.CaseSensitiveIndex(@tempproc, 'AS'))
if(@beginindex=0) begin set @beginindex=( SELECT dbo.ce_lastindexof(@tempproc, 'AS'))end
SELECT @header = SUBSTRING(@tempproc, @procindex,
@beginindex )
SELECT @asindex = ( SELECT dbo.ce_lastindexof(@header, 'AS')
- 2
)
SELECT @replacetext = STUFF(@header, @asindex, 3,
CHAR(13) + 'WITH ENCRYPTION'
+ CHAR(13) + 'AS' + CHAR(13))
SET @tempproc = REPLACE(@tempproc, @header, @replacetext)
END TRY
BEGIN CATCH
END CATCH
END
UPDATE @sptexttable
SET sptext = @tempproc
WHERE id = @counter
--PLAY HERE TO MAKE SURE ALL PROCS ARE ALTERED
UPDATE @sptexttable
SET sptext = ( SELECT REPLACE(sptext, 'CREATE PROC',
'ALTER PROC')
FROM @sptexttable
WHERE id = @counter
)
WHERE id = @counter
SELECT @sptext = sptext,
@spname = spname
FROM @sptexttable
WHERE id = @counter
BEGIN TRY
EXEC ( @sptext)
UPDATE #backup
SET encrypttext = @sptext,
encryptstatus = 1
WHERE id = @counter
END TRY
BEGIN CATCH
PRINT 'the stored procedure ' + @spname
+ ' cannot be encrypted automatically'
END CATCH
SET @counter = @counter + 1
END
SELECT *
FROM #backup where encryptstatus =0
