Can you prent a DBA from viewing the data ?

  • Good Day ,

    our IT Auditors suggested the database administrators should not be able to view the Production data . Is there way to do that or should we rather invest in audit trail software that sends out a notification when certain userids are accessing the data ? Any ideas ?

  • lianvh (8/2/2012)


    Good Day ,

    our IT Auditors suggested the database administrators should not be able to view the Production data . Is there way to do that or should we rather invest in audit trail software that sends out a notification when certain userids are accessing the data ? Any ideas ?

    I think its overkill from an auditors perspective. There is no real way to prevent a DBA from viewing data without potentially restricting their ability to do their job.

    Having worked in the strictly regulated finance industry I can say that viewing data is different to fully understanding data. Segregation of duties provides this distance where required and possible.

    Alternatively you should be auditing all DBA access to data and/or systems so that an explanation of why they did this and when can be traced to a change request.

  • Another thought. Sensitive data could also be encrypted.

  • If you don't want your DBA to view data, you could restrict his permission, not grant sysadmin or database role.

    If couldn't then use encryption and audit tools.

  • DBA328 (8/2/2012)


    If you don't want your DBA to view data, you could restrict his permission, not grant sysadmin or database role.

    If couldn't then use encryption and audit tools.

    And how does one administer the server without sysadmin? OP, you have to explain to the auditor that the DBA must have access to everything. Sensitive data should always be encrypted, but who will do the encryption? I think your auditor doesn't know what he/she is doing. You must explain to them that the DBA is the top of the chain for data. If it is sensitive data that shouldn't be encrypted (HR stuff for example), then the DBA must be a trusted individual in the company. The auditor shouldn't care who has access to the data as long as the person having access is in the DBA or HR role. When I managed this type of data, I had a brief session with HR to fully understand my role in protecting the data and the sensitivity of it. Imagine telling someone that they are responsible for guarding what is in a room, but the room is locked and they don't have the key. When someone with a key comes, how do you know what they are taking in and out?

    Jared
    CE - Microsoft

  • SQLKnowItAll (8/2/2012)


    Imagine telling someone that they are responsible for guarding what is in a room, but the room is locked and they don't have the key. When someone with a key comes, how do you know what they are taking in and out?

    i like that analogy.

    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!

  • Oracle has a SYSOPER privilege allows opration such as:

    instance startup,mount & database open;

    alter database backup archive log and restore

    This privilege allows the user to perform basic operational tasks without the ability look user data.

    I know this SYSOPER could limit DBA powers, but some company has different DBA roles. And it better not allow DBA to access database directlly after application implemantation.

  • DBA328 (8/2/2012)


    Oracle has a SYSOPER privilege allows opration such as:

    instance startup,mount & database open;

    alter database backup archive log and restore

    This privilege allows the user to perform basic operational tasks without the ability look user data.

    I know this SYSOPER could limit DBA powers, but some company has different DBA roles. And it better not allow DBA to access database directlly after application implemantation.

    SQL server has the same equivilents...if i need someone who only is going to do operating system backups, i can assign the proper roles. The issue is the sysadmin role itself. someone needs to be in that role, even if it is unused, in order to actually do administration.

    kind of like saying the person who is the president of the company should not have access to the financial information of the company, because he could abuse it.

    As said in thousands of similar posts here: if you cannot trust the person in the role, remove the person.

    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!

  • lianvh (8/2/2012)


    Good Day ,

    our IT Auditors suggested the database administrators should not be able to view the Production data . Is there way to do that or should we rather invest in audit trail software that sends out a notification when certain userids are accessing the data ? Any ideas ?

    DBA/Database administrator is a very loosely defined term these days. What particular duties do the database administrators you're trying to restrict need to perform?


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • lianvh (8/2/2012)


    ...our IT Auditors suggested the database administrators should not be able to view the Production data . Is there way to do that or should we rather invest in audit trail software that sends out a notification when certain userids are accessing the data ? Any ideas ?

    It all depends on the technology. As far as I know it is not possible to do it (yet) on SQL Server but Oracle has a working solution for it, Oracle vault.

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
  • Between partially contained databases and being able to create your own server roles in 2012 I'd imagine you could do what you're asking.

    Though you'll want to have someone somewhere with access to the sysadmin role. Who would you trust with that if not the dba?

    I did a quick search and found this.

    http://www.mssqltips.com/sqlservertip/2299/sql-server-separation-of-duties/

    I have 0 experience using this framework.

    Also as suggested in this article:

    http://social.msdn.microsoft.com/Forums/eu/sqldatabaseengine/thread/96208738-b617-42e8-bb1b-b3a317e8f868

    You can always encrypt the data before it gets to sql server.

  • benjamin.reyes (8/2/2012)


    Between partially contained databases and being able to create your own server roles in 2012 I'd imagine you could do what you're asking.

    Though you'll want to have someone somewhere with access to the sysadmin role. Who would you trust with that if not the dba?

    +1

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • A good start to this would be to not grant sysadmin privileges to the DBA's every day ID's, but require them to use a special ID to perform sysadmnin tasks. That makes auditing easier, since you don't have to track the routine tasks the user does with thier normal ID. It also helps reduce the "oops" potential, especially if the privileged ID's don't have any access to dev/acceptance environments.

  • benjamin.reyes (8/2/2012)


    Between partially contained databases and being able to create your own server roles in 2012 I'd imagine you could do what you're asking.

    Though you'll want to have someone somewhere with access to the sysadmin role. Who would you trust with that if not the dba?

    I did a quick search and found this.

    http://www.mssqltips.com/sqlservertip/2299/sql-server-separation-of-duties/

    I have 0 experience using this framework.

    Also as suggested in this article:

    http://social.msdn.microsoft.com/Forums/eu/sqldatabaseengine/thread/96208738-b617-42e8-bb1b-b3a317e8f868

    You can always encrypt the data before it gets to sql server.

    Let's make it easier for people to check out these urls:

    http://www.mssqltips.com/sqlservertip/2299/sql-server-separation-of-duties/

    http://social.msdn.microsoft.com/Forums/eu/sqldatabaseengine/thread/96208738-b617-42e8-bb1b-b3a317e8f868

  • lianvh (8/2/2012)


    Good Day ,

    our IT Auditors suggested the database administrators should not be able to view the Production data . Is there way to do that or should we rather invest in audit trail software that sends out a notification when certain userids are accessing the data ? Any ideas ?

    A "database administrator" may not need membership in the full SYSADMIN role.

    Server-Level Roles

    http://msdn.microsoft.com/en-us/library/ms188659.aspx

    Database-Level Roles

    http://msdn.microsoft.com/en-us/library/ms189121.aspx

    SQL Server Agent Fixed Database Roles

    http://msdn.microsoft.com/en-us/library/ms188283.aspx

    Query accounts, domain groups, and members who have admin membership

    http://www.sqlservercentral.com/articles/Security/76919/

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

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

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