Tag Info

Hot answers tagged

14

Your problem boils down to access control. The first defense I'd propose is to simply deny access to the untrusted users. If they can't get into the database, they can't query the database and get at the sensitive data. If they must be allowed to access the database server, you can look at either explicitly granting them read permission to the tables they ...


7

Q1a: Is the master key password created per DB instance? A1a: Assuming the question really means "Is the master key created per DB?"" then answer is Yes. Each DB has an different master key. there is also a thing called the service master key, which is per SQL Server instance. Q1b: When I backup that DB (.bak) will I be able to restore the DB to ...


7

\df *crypt in psql reveals the argument types of the pgcrypto encrypt and decrypt functions (as do the PgCrypto docs): List of functions Schema | Name | Result data type | Argument data types | Type --------+-----------------+------------------+--------------------------+-------- ... public | decrypt ...


7

The only way to use encryption to protect the data against your own administrators/IT is when the user enters the decryption password him/herself, every time it queries the data. If your application presents the user with a password dialog and then issues an OPEN SYMMETRIC KEY ... DECRYPTION BY CERTIFICATE ... WTIHT PASSWORD ... (or some equivalent) to open ...


7

The problem with cell level encryption is that the column itself isn't really encrypted, it's the data contained in that column. The columns themselves are just varbinary columns (because that's what's required) and could contain completely legible data. It's the use of the ENCRYPTBY* and DECRYPTBY* functions that truly make the data encrypted. You can ...


5

Why would you want to do this? What risk does it mitigate? This sounds like a great way to lock yourself out of your database in a way that requires downtime to recover. Limit your exposure by securing the servers with firewalls and SSH and keep MySQL authentication simple. If you are worried about brute force attacks from within your trusted network, ...


5

No, this is not true, and there is an easy proof. On one server, create a database, and store some data using ENCRYPTBYPASSPHRASE(): CREATE DATABASE blat; GO USE blat; GO CREATE TABLE dbo.mort(floob INT, splunge VARBINARY(64)); GO INSERT dbo.mort VALUES (1, ENCRYPTBYPASSPHRASE(N'kerplunk', N'secret')), (2, ENCRYPTBYPASSPHRASE(N'kerplunk', N'hidden')); ...


5

As a rule of thumb: If your data is well structured, well known (in advance) and of a limited size per entry (no mega BLOBs), relational databases are really good at storing it. Even if you don't use the advanced indexing features. Managing space, especially empty space in data files, is a very hard problem to solve. Relational databases have been dealing ...


4

Great question! There's a handful of ways of doing this, depending on how deeply you want to be involved in the configuration and long term maintenance. You certainly could use Cassandra on top of an encrypted filesystem, such as TrueCrypt or eCryptfs, and it should function normally. While possible, it's a little complicated to set up encrypted filesystem ...


4

General guidelines: Server physical security: behind a locked door, with a very short list of people able to access it (as a point of interest, I myself can't access our server room without being escorted). Backup security: Make sure your backups are encrypted (if you use physical media for backups, make sure they're stored with a similar level of security ...


4

You will need to encrypt the data within the application and store the encrypted values within the database. Things like TDE and storage level encryption will not meet your requirements.


4

This article on msdn indicates it's a new feature in SQL Server 2008. To me that means it's not available in 2005. There are a lot of resources available with step-by-step guides for other data encryption types in 2005, though. I think it's probably beyond the scope of a question on a Q&A site, however.


4

Since TDE relies on a certificate stored in master (which is used to encrypt the database encryption key), then this would work only work if you could restore the master database to another server in such a way that the certificate could be decrypted. This is the TDE encryption hierarchy: Service master key (protected by Windows; tied to the service ...


4

When you encrypt the first database in the instance, tempdb will also be encrypted. As per this reference on TDE: Transparent Data Encryption and the tempdb System Database The tempdb system database will be encrypted if any other database on the instance of SQL Server is encrypted by using TDE. This will be the case when you set ENCRYPTION ON ...


4

Assuming you are talking about data that is encrypted with SQL Server keys, there is way to find these columns. The Key_name() Function will return the name of the key used for the encryption for that particular value and will return NULL if there isn't anything encrypted with a "known" key (3rd party, or simple not encrypted). With that knowlegde we can ...


4

When you restore a database, TRUSTWORTHY is automatically set to OFF. For Service Broker, if you don't use encryption and do use cross-database message transmission, TRUSTWORTHY needs to be set to ON. Try ALTER DATABASE MyDB SET TRUSTWORTHY ON ...for all restored databases involved with Broker.


3

I'm not sure about pgPool, but pgBouncer (another connection pool) can use SSL by using Stunnel. Check your functional requirements to see if pgBouncer can solve your problem. Maybe pgPool can also use Stunnel, you could give it a try.


3

TDE is a new feature in SQL Server 2008. You can do column level encryption of the data within the database or in the application tier but that's about it. What's the goal of the encryption? Protecting data in the database? Protecting data in the data file? Protecting data in the backup files? Etc?


3

PostgreSQL's built-in two-way encryption functions are documented here. The functions that come in the encrypt/decrypt pairs are the two-way encryption options.


3

Short answer: as of April 20, 2012 column encryption is not supported in SQL Azure, see: http://msdn.microsoft.com/en-us/library/windowsazure/ee336253.aspx and search for ENCRYPT. You will see that all of the encryption functions are listed as unsupported. If you need to encrypt your data at rest and have to support SQL Azure then you are going to have to ...


3

Usually, you don't want to do this. Have you given second thoughts to this? Else, pg_crypto is your friend. But as the above-mentioned slides suggest, it isn't necessarily a panacea.


3

Things that come to my mind: use the contrib module pgcrypto to encrypt the column contents manually (will require a change to your application) Store the data on an encrypted volume/partition (this can be done on table level using tablespaces that are located on the encrypted volume)


3

I think what you are referring to is cell-level encryption, but using this feature requires changing the application. TDE (transparent database encryption) is another option, this encrypts the files on the disk, but they are decrypted in-memory, so anyone with direct access to the machine and sufficient privileges to look into the block buffer cache directly ...


3

Passport numbers should definitely be encrypted in the database. You should investigate the legality of collecting and storing this information before proceeding.


3

Encrypting files through SSIS is possible, but there is no real easy native solution available with SSIS. There are a wealth of third party productions out there than can assist with this. Time is money, so it all depends on how much you want to spend on trying to come up with a customized solution or just go buy something that can help you do it quicker. ...


3

The sequence of steps is entirely depended on you current (old db) encryption hierarchy. There is no cookie-cutter recipe that can be applied w/o knowing your encryption hierarchy, which you did not present. It cannot be deduce from the code you posted, steps like creating a new certificate after the database copy is attached make not much sense. Normally ...


3

Yes, it is normal. The certificates and keys are part of you database. CREATE CERTIFICATE my_certificate ENCRYPTION BY PASSWORD = 'blahblah' ...; CREATE SYMMETRIC KEY my_symmetric_key ... ENCRYPTION BY CERTIFICATE my_certificate; Your posted code creates a symmetric key encrypted with a certificate encrypted with a password. The master key is not involve ...


3

The specific permissions required are: GRANT CONTROL ON CERTIFICATE :: [cert] TO [user] GRANT VIEW DEFINITION ON SYMMETRIC KEY :: [key] TO [user] However, rather than grant permissions directly on the Certificate and Key, I like to have two stored procedures - one for encryption and one for decryption. We generally have different users that perform the ...


3

No. Scalar operators cannot magically transform themselves into relational operators. You use the ENCRYPTBYPASSPHRASE to encrypt a column (there is no way to 'encrypt a table') and you use DECRYPTBYPASSPHRASE to decrypt a column. Using passphrases to encrypt the data directly is a very very poor choice. A proper key hierarchy is needed where data is ...



Only top voted, non community-wiki answers of a minimum length are eligible