locked
Where customers' accounts shall be stored? RRS feed

  • Question

  • Hi,

    I am using SQL server 2008 R2. Now there's only 20 employees that access customers' database. We are developping a web application that will be put on a DMZ for customers to access their accounts and make some updates to it or even make Orders.

    My question is regarding the account of the customers. Where shall i put the accounts? there's about 10,000 customers. Shall i create a table contains Username and passwords of the customers, and i manage the security access on the application level based on which group the customer related to?

     Or let the script creates the customer account and put it in "SQL Server Auhentication" ie. under security / logins.

    Thanks in advance,

    Saturday, October 9, 2010 11:53 PM

Answers

  • I would prefer to have it in a table. You should use encryption to store the password so that they are not in clear text in the table.

    If you take "one login per customer" approach, you have to maintain permission for all the logins and it would be difficult to troubleshoot the problem of permission (if anything is missed). If you add new customer, then you have to give permssion to execute proc, select from table etc. So, there is more administrative overhead of you go with "one login per customer" approach.
    With the assumption all modification would be done by application only and customer would not perform direct update to tables, you have have just one login for application (or may be more based on permission i.e. superuser, reportuser, transactionuser) and use that to connect to SQL.

    Balmukund Lakhani | Please mark solved if I've answered your question
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog: http://blogs.msdn.com/blakhani
    Team Blog: http://blogs.msdn.com/sqlserverfaq
    • Marked as answer by Sadirov Monday, October 11, 2010 6:04 AM
    Sunday, October 10, 2010 12:15 AM
  • Store the accounts in the table. You can encrypt the columns as Balmukund mentioned

    USE AdventureWorks;
    GO

    --If there is no master key, create one now
    IF NOT EXISTS
        (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
        CREATE MASTER KEY ENCRYPTION BY
        PASSWORD = '23987hxJKL95QYV4369#ghf0%94467GRdkjuw54ie5y01478dDkjdahflkujaslekjg5k3fd117r$$#1946kcj$n44ncjhdlj'
    GO

    CREATE CERTIFICATE HumanResources037
       WITH SUBJECT = 'Employee Social Security Numbers';
    GO

    CREATE SYMMETRIC KEY SSN_Key_01
        WITH ALGORITHM = Triple_DES
        ENCRYPTION BY CERTIFICATE HumanResources037;
    GO

    USE [AdventureWorks];
    GO

    -- Create a column in which to store the encrypted data
    ALTER TABLE HumanResources.Employee
        ADD EncryptedNationalIDNumber varbinary(128);
    GO
    select * from HumanResources.Employee
    -- Open the symmetric key with which to encrypt the data
    OPEN SYMMETRIC KEY SSN_Key_01
       DECRYPTION BY CERTIFICATE HumanResources037;

    -- Encrypt the value in column NationalIDNumber with symmetric
    -- key SSN_Key_01. Save the result in column EncryptedNationalIDNumber.
    UPDATE HumanResources.Employee
    SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'), NationalIDNumber);
    GO

    -- Verify the encryption.
    -- First, open the symmetric key with which to decrypt the data
    OPEN SYMMETRIC KEY SSN_Key_01
       DECRYPTION BY CERTIFICATE HumanResources037;
    GO

    -- Now list the original ID, the encrypted ID, and the
    -- decrypted ciphertext. If the decryption worked, the original
    -- and the decrypted ID will match.
    SELECT NationalIDNumber, EncryptedNationalIDNumber
        AS "Encrypted ID Number",
        CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))
        AS "Decrypted ID Number"
        FROM HumanResources.Employee;
    GO


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Marked as answer by Sadirov Monday, October 11, 2010 6:00 AM
    Sunday, October 10, 2010 8:05 AM

All replies

  • I would prefer to have it in a table. You should use encryption to store the password so that they are not in clear text in the table.

    If you take "one login per customer" approach, you have to maintain permission for all the logins and it would be difficult to troubleshoot the problem of permission (if anything is missed). If you add new customer, then you have to give permssion to execute proc, select from table etc. So, there is more administrative overhead of you go with "one login per customer" approach.
    With the assumption all modification would be done by application only and customer would not perform direct update to tables, you have have just one login for application (or may be more based on permission i.e. superuser, reportuser, transactionuser) and use that to connect to SQL.

    Balmukund Lakhani | Please mark solved if I've answered your question
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog: http://blogs.msdn.com/blakhani
    Team Blog: http://blogs.msdn.com/sqlserverfaq
    • Marked as answer by Sadirov Monday, October 11, 2010 6:04 AM
    Sunday, October 10, 2010 12:15 AM
  • Store the accounts in the table. You can encrypt the columns as Balmukund mentioned

    USE AdventureWorks;
    GO

    --If there is no master key, create one now
    IF NOT EXISTS
        (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
        CREATE MASTER KEY ENCRYPTION BY
        PASSWORD = '23987hxJKL95QYV4369#ghf0%94467GRdkjuw54ie5y01478dDkjdahflkujaslekjg5k3fd117r$$#1946kcj$n44ncjhdlj'
    GO

    CREATE CERTIFICATE HumanResources037
       WITH SUBJECT = 'Employee Social Security Numbers';
    GO

    CREATE SYMMETRIC KEY SSN_Key_01
        WITH ALGORITHM = Triple_DES
        ENCRYPTION BY CERTIFICATE HumanResources037;
    GO

    USE [AdventureWorks];
    GO

    -- Create a column in which to store the encrypted data
    ALTER TABLE HumanResources.Employee
        ADD EncryptedNationalIDNumber varbinary(128);
    GO
    select * from HumanResources.Employee
    -- Open the symmetric key with which to encrypt the data
    OPEN SYMMETRIC KEY SSN_Key_01
       DECRYPTION BY CERTIFICATE HumanResources037;

    -- Encrypt the value in column NationalIDNumber with symmetric
    -- key SSN_Key_01. Save the result in column EncryptedNationalIDNumber.
    UPDATE HumanResources.Employee
    SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'), NationalIDNumber);
    GO

    -- Verify the encryption.
    -- First, open the symmetric key with which to decrypt the data
    OPEN SYMMETRIC KEY SSN_Key_01
       DECRYPTION BY CERTIFICATE HumanResources037;
    GO

    -- Now list the original ID, the encrypted ID, and the
    -- decrypted ciphertext. If the decryption worked, the original
    -- and the decrypted ID will match.
    SELECT NationalIDNumber, EncryptedNationalIDNumber
        AS "Encrypted ID Number",
        CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))
        AS "Decrypted ID Number"
        FROM HumanResources.Employee;
    GO


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Marked as answer by Sadirov Monday, October 11, 2010 6:00 AM
    Sunday, October 10, 2010 8:05 AM
  • Store the accounts in the table. You can encrypt the columns as Balmukund mentioned

    USE AdventureWorks;
    GO

    --If there is no master key, create one now
    IF NOT EXISTS
        (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
        CREATE MASTER KEY ENCRYPTION BY
        PASSWORD = '23987hxJKL95QYV4369#ghf0%94467GRdkjuw54ie5y01478dDkjdahflkujaslekjg5k3fd117r$$#1946kcj$n44ncjhdlj'
    GO

    CREATE CERTIFICATE HumanResources037
       WITH SUBJECT = 'Employee Social Security Numbers';
    GO

    CREATE SYMMETRIC KEY SSN_Key_01
        WITH ALGORITHM = Triple_DES
        ENCRYPTION BY CERTIFICATE HumanResources037;
    GO

    USE [AdventureWorks];
    GO

    -- Create a column in which to store the encrypted data
    ALTER TABLE HumanResources.Employee
        ADD EncryptedNationalIDNumber varbinary(128);
    GO
    select * from HumanResources.Employee
    -- Open the symmetric key with which to encrypt the data
    OPEN SYMMETRIC KEY SSN_Key_01
       DECRYPTION BY CERTIFICATE HumanResources037;

    -- Encrypt the value in column NationalIDNumber with symmetric
    -- key SSN_Key_01. Save the result in column EncryptedNationalIDNumber.
    UPDATE HumanResources.Employee
    SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'), NationalIDNumber);
    GO

    -- Verify the encryption.
    -- First, open the symmetric key with which to decrypt the data
    OPEN SYMMETRIC KEY SSN_Key_01
       DECRYPTION BY CERTIFICATE HumanResources037;
    GO

    -- Now list the original ID, the encrypted ID, and the
    -- decrypted ciphertext. If the decryption worked, the original
    -- and the decrypted ID will match.
    SELECT NationalIDNumber, EncryptedNationalIDNumber
        AS "Encrypted ID Number",
        CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))
        AS "Decrypted ID Number"
        FROM HumanResources.Employee;
    GO


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, October 10, 2010 8:05 AM