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?

  • 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
  • 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. – a CVn Sep 28 '16 at 12:06
  • 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
  • 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 3:20
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.
  • What if these folks only had access to VIEWs where the particular columns are excluded. These VIEWs 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? – Bryan Field 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 VIEWs 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

-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.

  • 2
    The database server should generally NEVER be accessible from untrusted networks. "Untrusted networks" includes, but is not limited to, the Internet at large. – a CVn Sep 29 '16 at 9:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.