DECLARE @NewPassword NVARCHAR(25) = N'1234';
UPDATE ...
SET [Password] = HashBytes('MD5', @NewPassword)
Showing posts with label security. Show all posts
Showing posts with label security. Show all posts
SQL Server - HashBytes - save encrypted password
UPDATE ...
SET [Password] = HashBytes('MD5', @NewPassword)
How to enable a database user?
Problem:
Giving permissions to a database user in not take effect.
Posible couse:
The user is "disabled". the user is showing up as disabled - with a down reddish arrow.
Solution:
USE [DataBaseName]
GO
GRANT CONNECT TO [Username]
GO
Script fpr Role creation including permissions
-- set here the role name:
declare @RoleName varchar(50) = 'ROLE_NAME'
declare @RoleScript varchar(max)
SELECT @RoleScript = 'CREATE ROLE ' + @RoleName + char(13)
SELECT @RoleScript = @RoleScript + 'GRANT ' + prm.permission_name + ' ON ' + OBJECT_NAME(major_id) + ' TO ' + rol.name + char(13) COLLATE Latin1_General_CI_AS
FROM sys.database_permissions prm
JOIN sys.database_principals rol ON prm.grantee_principal_id = rol.principal_id
WHERE rol.name = @RoleName
PRINT @RoleScript
Hash data is SQL using HASHBYTES
HASHBYTES('<hash algorithm>', <string text or column>)
http://msdn.microsoft.com/en-us/library/ms174415.aspx
<algorithm>::= MD2 | MD4 | MD5 | SHA | SHA1 | SHA2_256 | SHA2_512
Hashing vs Encryption:
http://msdn.microsoft.com/en-us/library/ms174415.aspx
<algorithm>::= MD2 | MD4 | MD5 | SHA | SHA1 | SHA2_256 | SHA2_512
Hashing vs Encryption:
Hashing vs Encryption
Encryption is a two way process.
Hashing is unidirectional.
In order to find the source value of hashed text,
we need to hash all the optional strings (for example: hash a column data from a table), and to compare to the hashed string.
REVERT
REVERT - Switches the execution context back to the caller of the last EXECUTE AS statement.
SELECT SUSER_NAME(), USER_NAME(); -- sa, dbo
EXECUTE AS USER = 'myUser';
SELECT SUSER_NAME(), USER_NAME(); -- myUser, myUser
REVERT;
SELECT SUSER_NAME(), USER_NAME(); -- sa, dbo
Find out who modified an object in SQL Server
SELECT --T.*, 'I', I.*, 'E', E.*
E.trace_event_id, E.category_id, E.name,
T.[path], T.start_time, T.last_event_time, T.event_count,
I.NTUserName, I.NTDomainName, I.HostName,
I.ClientProcessID, I.ApplicationName,
I.LoginName, I.SPID, I.ObjectID, I.ServerName,
I.ObjectName, I.DatabaseName, I.SessionLoginName,
I.TextData, I.BinaryData
FROM sys.traces T
CROSS Apply ::fn_trace_gettable(T.path, T.max_files) I
JOIN sys.trace_events E On I.eventclass = E.trace_event_id
WHERE T.id = 1
AND E.name = 'Object:Altered' -- or 'Object:Deleted' or ...
--AND ObjectName like '%AnObjectName%' -- <- filter for a specific object
ORDER BY T.start_time desc
User can not connect remotely to SQL Server
Problem:
An user can not connect remotely to SQL Server, but he can connect in the server machine.
Solution:
Check that TCP/IP is enabled in SQL Server Configuration Manager --> SQL Server Network Configuration --> Protocols for MSSQLSERVER:
DB objects code encryption - WITH ENCRYPTION
Purpose:
We want that the users can execute the objects but can't view the code.
Solution:
CREATE Procedure .... WITH ENCRYPTION .....
Example:
CREATE Procedure [dbo].[GetStamTable]
As
Select * from dbo.StamTable
GO
CREATE Procedure [dbo].[GetStamTableWithEncrypt]
WITH ENCRYPTION
As
Select * from dbo.StamTable
GO
exec [GetStamTable]
exec [GetStamTableWithEncrypt]
-- same results
sp_helptext [GetStamTable]
-- we will get the SP code
sp_helptext [GetStamTableWithEncrypt]
--we will get an error message: The text for object 'GetStamTableWithEncrypt' is encrypted.
Notes:
1. Pay attention to save the code in order to alter it.....
2. This definition of encryption also affects the profiler, and can make it harder to work with it.
Protect From SQL Injection
1.
Never run with more privileges than necessary. Users that log into an application with their own login should normally only have EXEC permissions on stored procedures.
If you use dynamic SQL, it should be confined to reading operations so that users only need SELECT permissions.
A web site that logs into a database should not have any elevated privileges, preferably only EXEC and (maybe) SELECT permissions.
Never let the web site log in as sa!
2.
For web applications: never expose error messages from SQL Server to the end user.
3.
Always used parameterised statements. That is, in a T-SQL procedure use sp_executesql, not EXEC().
4.
If the SQL parameters strings do not include any user input, there is no opening for SQL injection.
Example:
CREATE PROCEDURE search_orders @custid nchar(5) = NULL,
@shipname nvarchar(40) = NULL AS
DECLARE @sql nvarchar(4000)
SELECT @sql = ' SELECT OrderID, OrderDate, CustomerID, ShipName ' +
' FROM dbo.Orders WHERE 1 = 1 '
IF @custid IS NOT NULL
SELECT @sql = @sql + ' AND CustomerID LIKE @custid '
IF @shipname IS NOT NULL
SELECT @sql = @sql + ' AND ShipName LIKE @shipname '
EXEC sp_executesql @sql, N'@custid nchar(5), @shipname nvarchar(40)',
@custid, @shipname
5.
When using dynamic SQL- enclose all object names in quotename().
http://copypastenet.blogspot.com/2011/08/quotename.html
6.
Stored procedures with static SQL only are more safe than Stored procedures with dynamic SQL.
7.
like '%\' + IsNull(@QCTName, '') + '%' ESCAPE '\'
Subscribe to:
Posts (Atom)