encryption feature for column having data type timestamp

Question

encryption feature for column having data type timestamp

sabahsuhail_fjwu

SSC Journeyman

Points: 80
More actions
October 14, 2011 at 1:07 pm

#245334

Hello SQL,
I am new to this forum. I want to ask a question regarding encryption feature of sql. Is there any encryption feature for column having data type timestamp? i have a column of data type timestam and i want to encrypt it. I read few articles in which it is stated that encryption is available for few specific data type.
Moreover i also want to know that how can user get to know that any update is performed on data values by seeing the value of timestamp column?
Also is there any way to tell what is the physical adddress of machine that have modified database table?
thanks
sabahmed

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286609 More actions · Answer 1

Why do you care about encrypting a timestamp column?? timestamp (or rowversion) is a deprecated datatype anyway and should be avoided for new development. http://msdn.microsoft.com/en-us/library/ms182776%28v=SQL.105%29.aspx

Encrypting this seems like overkill because the data inside it is useless to a human anyway.

As for your question about

Also is there any way to tell what is the physical adddress of machine that have modified database table?

select @@SERVERNAME

The machine that modified the data is and always will be the database server. If you mean the IP of the client machine that called the update, I don't know of anyway for sql to know the address of the machine that connected to it. This could be possible but I don't know how to find it.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lowell SSC Guru Points: 323547 More actions · Answer 2

the OP may be a little loose in the definition of "timestamp" here;

i'm thinking it's simply a datetime field he's refering to, but I'm with Sean: no need to encrypt it.

you selectively encrypt sensitive data, and alstUpdateTime is not senstitive in my book.

as far as the IP address, you can get it, but you might need to grant access to the views to do it:

sys.dm_exec_connections

--the auditing snippet below works fine in a

--login trigger,

--database trigger

--or any stored procedure.

SELECT

getdate() AS EventDate,

DB_NAME() AS DBName,

HOST_NAME() AS HostName,

APP_NAME() AS ApplicationName,

OBJECT_NAME(@@PROCID) AS ProcedureName,

USER_ID() AS Userid,

USER_NAME() AS UserName,

SUSER_ID() AS sUserid,

SUSER_SNAME() AS sUserName,

IS_SRVROLEMEMBER ('sysadmin') AS [Is_ServerAdmin_Sysadmin],

IS_MEMBER('db_owner') AS [Is_DB_owner],

IS_MEMBER('db_ddladmin') AS [Is_DDL_Admin],

IS_MEMBER('db_datareader') AS [Is_DB_Datareader],

ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN],

client_net_address AS ipaddress,

auth_scheme AS AuthenticationType

FROM sys.dm_exec_connections where session_id = @@spid

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Sean Lange SSC Guru Points: 286609 More actions · Answer 3

Thanks for the reminder Lowell. I sort of vaguely remembered there was view with that info but couldn't think of it. Now I don't have to.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

sabahsuhail_fjwu SSC Journeyman Points: 80 More actions · Answer 4

Sean Lange (10/14/2011)
Why do you care about encrypting a timestamp column?? timestamp (or rowversion) is a deprecated datatype anyway and should be avoided for new development. http://msdn.microsoft.com/en-us/library/ms182776%28v=SQL.105%29.aspx
Encrypting this seems like overkill because the data inside it is useless to a human anyway.
As for your question about
Also is there any way to tell what is the physical adddress of machine that have modified database table?
select @@SERVERNAME
The machine that modified the data is and always will be the database server. If you mean the IP of the client machine that called the update, I don't know of anyway for sql to know the address of the machine that connected to it. This could be possible but I don't know how to find it.

Thanks a lot for your reply. As you mention that "data inside it is useless to a human anyway..." then it means that when we say timestamp column keeps track of any updates made to the table; this information is for the use of database only?

how can we use this timestamp information?or how this information can be usefull to us???

What i want to do is :keep track of all changes made to the table. So that if any malicious activity is done it can be reported. i made triggers too. but the scenario i am following is related to watermarking databases. and since watermarked data is available online for use. What should i do then to keep track of any DML operation made to the data.

Sean Lange SSC Guru Points: 286609 More actions · Answer 5

sabahsuhail_fjwu (10/15/2011)
Sean Lange (10/14/2011)
Why do you care about encrypting a timestamp column?? timestamp (or rowversion) is a deprecated datatype anyway and should be avoided for new development. http://msdn.microsoft.com/en-us/library/ms182776%28v=SQL.105%29.aspx
Encrypting this seems like overkill because the data inside it is useless to a human anyway.
As for your question about
Also is there any way to tell what is the physical adddress of machine that have modified database table?
select @@SERVERNAME
The machine that modified the data is and always will be the database server. If you mean the IP of the client machine that called the update, I don't know of anyway for sql to know the address of the machine that connected to it. This could be possible but I don't know how to find it.
Thanks a lot for your reply. As you mention that "data inside it is useless to a human anyway..." then it means that when we say timestamp column keeps track of any updates made to the table; this information is for the use of database only?
how can we use this timestamp information?or how this information can be usefull to us???
What i want to do is :keep track of all changes made to the table. So that if any malicious activity is done it can be reported. i made triggers too. but the scenario i am following is related to watermarking databases. and since watermarked data is available online for use. What should i do then to keep track of any DML operation made to the data.

Read the article I linked for all the details about the timestamp datatype. If what you want is to capture data changes you will either need to implement this via triggers or take a look at CDC (Change Data Capture).

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/