Administering Securely

  • Comments posted to this topic are about the item Administering Securely

  • Remember the 2008 case of the City IT manager who was soooo good at his job and only he had the passwords to all the routers and had buit a network so secure it frightened the bejeesuz out of everyone in his dept. Wasn't he originally charged with something stoopid like terrorism?

    Security is not only about peeking at salaries but being able to recover from disaster. Maintainability.

    To address your question however, could a simple answer be to have access based on a two field table with MD5 encryption, one field entered by each of two managers, rather like two keys on a bank security deposit box.

    From there on, add all the audit tracking you please, and so on. written to a security tape, picked up each night by a $7/hr employee for archiving off-site.

    Row-columns of payroll data would have an encrypted key field related to employee seniority, to inhibit the two sys admins mentioned above from reading the table values. The company CFO should, presumably, have R/W access but would have to sit in a closed glass cubicle dressed only in boxer shorts while editing.

    Software coders who wrote the necessarily obfuscated code for login and payroll access would of course be fired immediately after the package is tested, to remove them from the security problem.


  • If you don't trust your administrators, then they shouldn't be administrators! 😛

    Trying to prevent a sysadmin from reading your data is like giving someone the keys to your house and asking them not to steal the money you've got hidden under your mattress. If you don't trust them not to steal your money, why are you giving them your keys?

  • You would have to separate the two functions -- encryption and storage. Encryption would then need to take place before storage, and decryption would have to take place after retrieval. The trick here is to embed some type of trace code in the hash algorithm of the encrypted data which would allow for meaningful retrieval. (Of course, the dedicated analyst would still be able to tell alot about the data just by its organization, but it's a lot more work.)

    I don't believe we have anything COTS that sophisticated. And the hash algorithm would almost mandate a custom solution, since the hash would need to know something about the structure and boundaries of the data to be able to embed queriable codes into the encrypted data. Even then, it would place burdonsome constraints on the types of queries you could perform. But it's the only way you'd be able to enable the administrator to troubleshoot and tune the database without exposing your data.

  • One way to do this is to encrypt sensitive fields in the application (and store the encrypted value in the database). Not all of the data, just the columns that are sensitive. This is commonly done with credit card numbers, social security numbers, bank account numbers. Why not just encrypt the salaries in your example? People with access through a UI in the application can view and modify values, but no one can just browse the data.

    The developers don't have access to the production database, and certainly don't have access to execute code that isn't already checked into version control, so no one can see these columns' values except through the UI. The rest of the row is unencrypted, so DBAs and sysadmins can still use all of the regular monitoring tools and you don't have to worry about not being able to query by last_name or start_date or department. If you want to report by salary range you could always have another column (maybe an integer) which represents a "range" of salaries.

  • The issue with encryption is that many times the DBA can still get to the keys, or add themselves to the group that can access the keys for encryption/decryption.

    Or they get the keys so they can debug some issue.

    I agree that you need to trust administrators, or you want to, but that doesn't mean you want your administrators reading every piece of data you have. Compartmentalization is needed in some industries.

    It's a hard problem, and I don't have a solution. Two factor authentication is an interesting one, but I'm not sure that really works in the real world.

  • However, as already suggested, if the en/decryption takes place before/after storage/retrieval, then there should be no reason for obtaining the key to troubleshoot. Any database activity the administrator can address would be on the encrypted data anyway, so needing the keys is a red herring, and you've just been compromised by social engineering -- the weakest link in the chain.

    Unless, of course, the administrator is also the developer. In that case, you've failed to properly compartmentalize. In which case, you've just been compromised by social engineering -- the weakest link in the chain.

    As the other poster noted, when the salary is encrypted before entry, you effectively address this issue. But in this extreme case, you loose all ability to query on the salary data. You can still request it, but you can't request it within a range. That would need to be decrypted before you know it. Instead, you need a hashing/encryption algorithm that includes a limited amount of information, say a bin, embedded into the encryption data or maintained in a separate field or table that would allow you to perform at least some minimal searches on that data.

    For example, post-pend a four-digit number at the end of each encrypted value, 0000 through 9999, to indicate a range of salaries. Then, you could query a specific bin or range of bins based on those last four digits. Or include a digital signature as part of the salary, to indicate who entered the data. (A separate table would work better, of course, since it's more normalized.) Again, in both cases, you need to know something about the type of data and the scope of possible queries during the design.

    Of course it's expensive. And it doesn't get you out of auditing. Watching the sign that says the gate is closed doesn't tell you diddley about the guy climbing over the fence. So before you start down this road, you need to decide up front whether it will cost more to recover from compromised data, or to deter compromises.

    Keep in mind that the first lesson in cypher is that there is no such thing as an unbreakable cypher. You can't prevent someone from getting access to your data no matter how hard you try. The question is rather, can you make hard enough that the subject would find it easier to go elsewhere, or if not, take long enough to decypher that by the time they get the data, it's no longer of value.

  • The main problem with solutions that make data secure, is that they also make data losable.

    If, for example, only one person knows the key to the data, and that person dies, your data is now useless.

    There's always a compromise between confidentiality of data and recoverability of data.

    At one end, if everyone (or a significant percentage of the population of the planet) knows some fact, and it's widely recorded in easily accessible media, the probability of losing it approaches zero. This would include data like the recipe for boiling rice.

    At the other end are single-use, hidden encryption schemes, where nobody knows the key, it's recorded in an encrypted computer system, and it's only valid for one message to one destination. Highly secure. Theoretically unbreakable. But if you lose the computer that holds the only existing keys, you lose all the messages and they are unrecoverable.

    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • A good point, one I've seen happen before. To be precise, though, there is no such thing as theoretically unbreakable. As I said before, the first axom of encryption is that there is no such thing as unbreakable, only so resource heavy as to become impracticable (or untractable). Furthermore, the data isn't lost, it's just not readable. 😎

    Key management can be addressed, however. First, keep a sealed copy of the keys. When the seal is broken, create new keys, and force an immediate backup of the data with the newly encrypted data, while locking out all access to the data source, and finishing up by purging all of the old backup sets. That functionality can even be built into the application, which should require a periodic change of keys anyway, simply because, theoretically, no key is unbreakable. 😉

  • If you can't trust your DBA ... You may have more problems then someone reading DATA..... a DBA with a push of a button could bing down your whole company...!!!

  • There are theoretically unbreakable codes, but not cyphers.

    For example, if I tell you that the phrase, "the bear is in the woods", means, "blow up Moscow now", and you and I are the only people who know that, the only way to "break" it is to torture one of us into revealing it. That's not a mathematical decryption, so it comes under the heading of "unbreakable code".

    If, in that case, I sent an e-mail that said, "the bear is in the woods", to you, but you had died, and I died after sending the message, nobody would ever be able to retrieve the data that an order to blow up Moscow had been issued. That data is permanently lost.

    The message is there, but no one alive will ever know what it means, or if it even means anything at all.

    That's one extreme of the encryption/data permanence spectrum.

    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • I am genuinely surprised that companies such as Microsoft don't take this problem more seriously. The issue of SysAdmins having full access to all data in all databases is huge. I think that most senior managers simply don't know that a junior DBA has more access to privileged data than they do; if they did know, surely there would be more demands to change?

    Still, exactly the same situation exists in other areas of IT, for example Active Directory. You can create all kinds of complex ACLs on folders, but I bet the "Administrators" group has full rights over all folders. If nothing else, backup programs will fail without global access; you just cannot lock down a folder so that ONLY senior managers can access it.

    The requirement of 2-people-to-launch-a-nuke has often been taken as a model for IT systems, but I've never seen it implemented. I do think it could be a solution though.

    Is it really that hard to design an RDBMS whereby rows/columns/tables within a database are unavailable to the SysAdmin? One problem I can see is that the SysAdmin is usually in charge of Security as well......


  • Yes, it really is that hard, nearly impossible, to lock a DBA out of rows, columns, or anything. First, they need access to do backups and for running analysis scenarios for tuning. Second, and this is more the issue, is that they are the ones that manage accounts that grant all access rights. They want in, they just go to the manager that wrote the data, change the manager's password, login as that manager, and voila! Next morning, they just explain that the database backup corrupted the security on the database and they had to reset everyone's (or some) passwords.

    This is the one area that a double-key access might help. But it still doesn't address tuning and backups. Backups especially create an issue, because data access (and encryption, if it's applied) are all done using the password at the time the backup was performed.

    That's important to keep in mind for two reasons. First, because restoring the data requires you to know what your passwords were at the time the backup was performed -- not what they are today. Second, because if someone does compromise your system and gain access to your passwords, changing the password is not sufficient to ensure protection of your data -- the compromising agent can still access your backups.

    The last issue is with tuning, which requires that the person (or tool) running analysis on the database have access to the actual data. It's hard to improve the response of a query, or even perform a query, if the data is encrypted or inaccessible. How do you run a report for the CFO on all Level 2 managers with a salary greater than X, when X is an encrypted value?

    The first, most obvious choice, would be to create an encryption algorithm that had a skelton key, and grant that to the tool. Only, it would be easy to determine that a skeleton key exists, and then the hunt is on. And once it's cracked (and it will be cracked, eventually), how to change the value of the key? Oh, and don't forget about wiping all the previous backups.

  • Interesting scenarios, thanks. I do agree with what you say, with our current RDBMSs. But I was postulating whether an RDBMS could be designed slightly differently. For example, we don't typically have "direct access" to a piece of data in a table, we access it via SELECT statements or Views, etc. Could the data be held, unencrypted, within the RDBMS (so that statistics, execution plans, etc could be properly calculated), but access to that data only provided via Views that require, for example, 2-factor authentication?

    Yes, if you are willing to "hack" into the Manager's account, then you would get around single-factor auth.; however, even here, auditing and other measures should identify this illegal access, making it a very risky proposition. Two-factor auth would be much harder to get around.


  • I ran into this article ( and immediately recognized the potential applications here. Thought other readers might find this interesting.

Viewing 15 posts - 1 through 15 (of 17 total)

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