22

Some of the non-DBA workers like Developers (for crises handling), Fraud (with read permissions only) analysts (with read permissions only) and a few more that's needs direct access to databases to write their own queries. I cant reduce the permissions and they are approved by the management. Every time they need a different query and I cant limit them to pre-built programmatic methods.

PCI DSS requirement 8.7.b stats "Only database administrators have the ability to directly access or query databases"

How can I still fulfill this requirement? Are their any relevant compensating controls you can suggest?

BokerTov
  • 539
  • 4
  • 10
  • 4
    What database? Dev, Test, Prod? What keeps them from having a local database with test data in it? – marstato Sep 28 '16 at 11:46
  • 2
    Prod. and PCI chapter 6 guides not to use real PAN for testing so i wont allow this kind of data in DEV & TEST – BokerTov Sep 28 '16 at 12:01
  • 25
    Why do developers in particular have to test against real data? If you need to test against large datasets (for performance testing, for example), you can synthesize any amount of data required. – user Sep 28 '16 at 12:06
  • 1
    I can't speak to PCI but from a database owner perspective no one should be directly accessing your Production database. For analysts, this is what DataMarts and DataWarehouses were designed for. Developers should be working with lower environments with scrubbed/masked data. – DanK Sep 28 '16 at 16:23
  • 11
    PCI aside, this is a bad idea from an availability standpoint. All it takes is one or two analysts writing crappy SQL queries to drag your production DB to its knees. Sideload sanitized data and let them hammer that DB instead. – Ivan Sep 28 '16 at 17:06
  • 19
    As a former developer, we all demand direct access to the database. Please don't give it to us. We don't need it, no matter how much we bitch and moan about it. – corsiKa Sep 28 '16 at 20:25
  • 1
    The need to test against real data is not a symptom of bad data but a symptom of a lack of proper tests. If you cannot test something without looking at real data is because you have a humongous backlog of tests to write. And yes, I have been there. Worked for a PCI compliant place that made developers test against real data. After 3 years of hearing "we need to write a testing tool" and nothing happening I resigned. – grochmal Sep 28 '16 at 21:19
  • 3
    As a developer, I am often unaware of all the ways that the data can potentially misbehave (edge cases, miskeyed original entries, data imported from legacy systems, etc.) Testing against live data is one way for me to discover this information. Sometimes there is a helpful subject matter expert who can enlighten me, but what do I do the rest of the time? – John Gordon Sep 29 '16 at 03:20
  • @Johnny, you should run non-critical queries on a slave database, whether ad-hoc manual queries or automated batch processing. – Paul Draper Sep 29 '16 at 04:22
  • @corsiKa Why would you want it? I frequently find myself needing access to the production data--but a copy of it, not the live data! – Loren Pechtel Sep 29 '16 at 05:19
  • @Johnny, they have only read permissions – BokerTov Sep 29 '16 at 05:24
  • @MichaelKjörling they need access in times of crises – BokerTov Sep 29 '16 at 09:02
  • @BokerTov doesn't matter. I had SELECT statements in mind when I said that-- a few analysts trying to join 15 tables together and perform aggregations during peak hours would put a lot of resource strain on a DB that needs to be available for multiple departments. No matter what your use case is, it's a bad idea in practice. – Ivan Sep 29 '16 at 11:03
  • 1
    One of my former workplaces solved this by having 12 Database Administrators (the devs & other people that needed DB access), and the "real" DBA as the "Master Database Administrator". I know, it's kinda cheating. But it worked. – T. Sar Sep 29 '16 at 12:11
  • @ Thales Pereira, i think that this might be it for me – BokerTov Oct 05 '16 at 06:41

3 Answers3

33

Some of the non DBA workers (DEV, Fraud, analysts ... ) in my organization need direct access to databases to write their own queries.

...

PCI DSS requirement 8.7 stats "Only database administrators have the ability to directly access or query databases"

The usual way of handling this is to create a scrubbed version of the database, with PAN data and other sensitive data, removed or replaced. This scrubbed database is then outside of PCI scope, and can be directly queried by the people you describe.

(The Fraud people may need access to PAN data for their jobs... but they don't get to use free-form queries for that, you write an application which provides access to the data in the real database they need through pre-formed queries).

For developers and analysts, the scrubbed version should be sufficient to their needs.

You may wish to also consider the question of PII. Data science analysts need access to that level of detail; developers may not. That's outside DSS requirements, however.

There are a couple of drawbacks to this method:

  1. Depending on the size of the source database, scrubbing may be expensive in time and resources.
  2. The table indexes, which derive from the cardinality of the data, will be altered when you remove or replace indexed columns (such as PAN), meaning that performance measurements against the "scrubbed" database will not be 1:1 equivalent to the "real" database.
gowenfawr
  • 71,975
  • 17
  • 161
  • 198
  • What if these folks only had access to `VIEW`s where the particular columns are excluded. These `VIEW`s could be located in a separate Schema (aka database) perhaps, but I'm not sure if this would violate PCI DSS. Surely the point is that there must be no way for them to see DSS-scoped data? – 700 Software Sep 28 '16 at 17:20
  • 3
    @GeorgeBailey the DSS pretty clearly states (8.7.b) "verify that all user access to, user queries of, and user actions on (for example, move, copy, delete), the database are through programmatic methods only (for example, through stored procedures)." Enforcing access controls via `VIEW`s and other schematic methods probably doesn't work for the average QSA... the problem with ACLs is that, in a complex system, history shows they usually can't match the problem complexity. – gowenfawr Sep 28 '16 at 17:55
  • Basically, create a stripped-down version for them to test against without breaking compliance. This is how it should be done. +1. – Mast Sep 29 '16 at 15:55
9

AFAIK, you cannot. If you want to be conformant to th PCI DSS, neither devs nor analysts should directly access the production database.

Once that has been said, you should wonder why they need direct access to the production database. Devs normally should be satisfied by a dev database containing fake data, provided the size is coherent for performance tests. An analysts should have extracts of the prod database in a private base to perform their queries. You could even wonder if a BI solution would not be a possible way here: Prod database -> ETL -> BI database

It is indeed more complex than allowing direct accesses, but security (and certifications...) only comes at a cost

Serge Ballesta
  • 25,636
  • 4
  • 42
  • 84
-1

I just looked up PCI (payment card industry)
OK you have some sensitive data

Database port (e.g. 1433) should not even be open to the the outside world

I don't think that give them read only access to a view would satisfy the regulation

I would try and give them an application with strong faceted search where you only use parameterized queries.

They may want direct access but that does mean they are going to get it.

What I do is have a table of Views they can run. I create the View and put the name in the table with a description. The app will read the table so it is now just a new report they can run. They do not run View directly - the app runs the view and reports the results. But in a way you are releasing directly into Dev so that is kind of dangerous.

paparazzo
  • 181
  • 7
  • 2
    The database server should generally ***NEVER*** be accessible from untrusted networks. "Untrusted networks" includes, but is not limited to, the Internet at large. – user Sep 29 '16 at 09:06