Is there a way to prevent developers leaking data while they are developing?

In larger organizations it can sometimes make sense to establish different access levels to different parts of a database for different groups of people. That's why most relational databases usually provide tools for access rights management. For example, if you want to restrict getting the credentials in a user table, you could let a trusted person add a view to the user table schema which only returns user names, and assign the rights to access the user table directly only to this person, whilst all others can only access the view directly.

However:

• Each field in the database is probably used somewhere, so you need to decide for each field who can access it or not and manage that rights. That can cause a large bureaucratic overhead. In the end, someone has to write a query somewhere, and you will have to trust that person.

• Credentials should never be stored in plain text in your DB, better store passwords as non-reversible hashes

• There are other means than just technical means to deal with problem: ever heard of "code reviews"? Especially for database queries, it is a good idea to make a trusted person proofread the code.

• There should also be something in the contract with your devs for giving the the obligation to be careful with sensitive data.

• The more you restrict database access to your developers, the more you hinder them from doing their job.

The measures you should take depend on how confidential the data is, there is no "one size fits" all solution to this. It also depends on your organization. If you have 3 trusted devs in your own company, and 30 others which are provided temporarily for your project from another company, then it may make sense to let the 3 devs provide a secure API for the 30 others. But if your organization is not that large, and you have only 3 devs at all, the overhead you would introduce is probably not worth it.

You will see varying practice, with some more traditionally minded DBAs maybe saying at the extreme 'never let developers write SQL, wrap everything in stored procedures'. My answer, for the environments I'm currently used to, is 'yes, but...'. Here are some of the reasons (which in some ways are really the same reason):

• Agile development methodologies often like to take 'vertical' slices - a single developer develops a complete piece of functionality with end-user value. This enables the developer to understand the purpose and value of what he's doing (and relate it to what a customer wants), to ensure that the code he writes fits that purpose, to shorten feedback loops, to know better when the code is actually complete, etc. Potentially it can be more motivating, too - motivation partly depends on visible individual impact on outcomes that matter. All of these things apply to the database as much as any other layer.
• To understand the application behaviour and its performance you have to consider the code and the database together. To design a database structure or query you have to understand how it's used and to write an application using it you have to understand how the database will behave. Compartmentalising knowledge of these things is a problem.
• Creating a boundary between SQL writers and developers means there's more friction to getting one side or the other changed. Need a new query, but you can do it less well by using existing queries, fetching too much and doing a join in your code? Looks like a field should have a foreign key constraint or lose an index, but you know nothing about whether the code can cope? It's tempting to take the path which doesn't involve having to ask, explain and wait (and maybe overcome resistance).

As a worst case of the first and last together you can have DBAs holding up important user-visible features they have no incentive to care about because it spoils their vision of how the database should be (or simply distracts them from something more interesting). You create misaligned goals, conflict and power struggles.

The 'but' is that you need the appropriate skills, tests, code reviews, knowledge of individual team members, design oversight, understanding of requirements and so on within your team. Not everyone will understand the locking behaviour or performance characteristics of your database, for example, but you should be able to handle this. The good news is that you already need all of these things for the rest of your code anyway.

As for your particular case, it sounds as though your API and database structure could be highly coupled. Why can you not simply leave the password hash (I really hope it's a hash) out of your ORM's definitions? Or make it a private field and add a 'verify password' method? Or leave it out of the objects that are serialised to form your API? Or out of the shared code that generates the pre-defined API object you're returning? If your SQL is tied rigidly to the API to the extent that changing a query changes the API, what are you going to do when you want to restructure your database without breaking your API callers? Or when you want two versions of your API whilst you rewrite your clients or perform gradual upgrades?

Accessing the DBs is not the problem here, if you want your developers not leak unwanted data on your api just define the interface or format of your data published. The person or persons who knows what must be publish in the api can define the format and the developers cant work with that. And you can do code reviews to make shure that keeps that way.

First of all, do not blame the developers. A leakage may not be intentional, even a bug can leak information. Sometimes, especially if developers only work on small parts of an application, they cannot see, that their change may introduce a new field to the API.

I would suggest to improve your testing. You already mention test cases and schema validation. Every API function should have a definition of input and output. The output should usually be of a certain schema type. This can be tested.

For example, if a query to the user should not return a password, the schema user should not contain a password. There should be a second schema like user_credential which contains the (hopefully hashed) password (which is a bad example, cause passwords in any form should not be returned). A good schema validator can check for unwanted properties and a test case could check, if the API returns a type user or user_credential. (But be aware, that this might introduce some breaking changes to your API.)

If you use a modern CI system with automatic checks and a metric for test coverage, you can notify the developer after every commit, if he breaks the intended quality level.

In some industries, laws and regulations exist constraining what kinds of information can be transmitted — even in API calls between servers. You need to make sure the developers have training on what regulatory frameworks apply to the application. You will also likely need a compliance team to review the system design before implementation begins. Something like this can even apply to static text on a marketing site.

When you are sure the design complies with regulatory frameworks you need to include those limitations in the description of what the developer will build. After that code review becomes essential. Make sure you have the proper tools for code reviews.

There isn't a magic tool that can inspect data flowing between systems and automatically flag improper data transmissions.

It is a combination of training and process.

No.

But, you can emphasize security and quality over throughput.

Talk with your developers about what the company's priorities are — be open, explicit, and reasonable. Insist that the developers set a comfortable, predictable pace that leaves plenty of room for "work-life balance" and optimizes for the company's priorities. Insist that they let you know when things are going too fast for comfort — or too slow to maintain their interest.

Treat them like professionals. Be explicit and clear about the company's expectations of them. And leave room to negotiate what balance of expectations is reasonable.

And then, Coach, Support, and Encourage Good Practices and Attitudes — things that they can take with them when they move onto bigger and better endeavors:

• "Clean Code" (The team gets to pick their brand; but they should be able to articulate their "clean coding" rules and explain how they lead to good, maintainable code.)
• Mandatory Code-reviews
• Pair programming for tough problems
• Unit testing
• Integration testing
• Routine Penetration Testing (maybe regularly by internal folks; annually to quarterly by external pen testers)
• Chaos testing
• Continuous education and training
• Humility (Hard to teach; but very important)
• Ownership (Also hard to teach; but they need to be invested.)

Etc.

Generally speaking, you just need to treat your engineers like professionals. (Assuming you've actually hired professionals.) You need to be clear and explicit about what you want from them. And, you need trust them, openly and explicitly.

If data security is really important, you have the option of weaving it into the design itself. One solution that we used for a while to prevent ourselves from accidentally using sensitive data wrong is to actually wrap sensitive data into a Sensitive wrapper like this:

public final class Sensitive<V> {
    ...
}

We introduced methods to manipulate the sensitive data safely, like map(), flatMap(), etc., basically making it monadic. We additionally introduced methods to forward the value to "unsafe" places, like third party libraries, the screen, etc. Because these methods stand out and are searchable, it is easy to review all usages if necessary.

You can also introduce methods to return the value only after a role-check of the end-user or similar.

Upside is that you now get help from the compiler. The downside is that you might have to change APIs to deal with sensitive data now. This may or may not be what you're looking for.

The solution is called the 3 tier model and it has been around for a while. The "problem" with it though is, to implement it, you will need some developers.