I've got a project with an HTTP API which returns data from a database. The layers it goes through to get to the API look like this:

DB -> Repository -> Controller

I'm looking to restrict the results which are returned based on the permissions of the requester. Should this be included in the repository layer, or the controller layer and why?

  • Repository layer should be 3 layered. Access control goes in middle layer Commented May 19, 2020 at 4:04
  • What are the other layers within the repository layer?
    – Joundill
    Commented May 19, 2020 at 4:06
  • 1
    top layer will provide public interface for other components. Changing Oracle to MySQL should affect bottom layer only Commented May 19, 2020 at 4:49
  • Middle layer has highest number of responsibilities like... limit max connections, access control, caching, validate client if this repository layer can be reused in non-webapp scenario etc... Commented May 19, 2020 at 4:57
  • So the bottom layer should provide the interface with the data source, the middle has the data logic, and top is the interface which the service layer or controllers talk to? What's the disadvantage of combining the three into one repository layer?
    – Joundill
    Commented May 19, 2020 at 6:41

4 Answers 4


Ideally, if you really require a high level of security, the DB itself should not return any data for which the requester does not have the permission. With this idealistic idea in mind, the answer would be "neither" - the DB itself should provide an adequate security model, know about the permissions of a specific requester and restrict the data.

Unfortunately, for most real world applications and typical RDBMS, it is quite unrealistic to have application users mapped to database users in a 1:1 fashion, because that does not scale beyond a very small user base and a very restricted set of requirements. Hence, most applications of today implement their own user/role/permission model, which makes it hard to implement the security model easily inside the DB.

So I see two possible ways to define the reponsibilities:

  • Approach 1 is to keep as much business logic out of the repositories. In this case, the repos should only to provide the tools for restricting the returned result sets, but don't know which permissions the requester has. So here, the Controller might be responsible for evaluating the permissions. This may be feasible if there are only a few places in code where permissions have influence on the result sets of DB queries.

  • Approach 2 is to restrict the result sets directly in the repos. That approach makes sense if there are a lot of different places in the system from which the repositories are called and reused, and all of them should obey to the same permission model which involves returning different result sets.

Note that approach 2 can be implemented by splitting the repos into two different layers: one generic layer without any knowledge about the permissions, and one layer on top (maybe by "decorated" repos, or "proxy repos"), which implements the security model:

DB -> Generic Repositories -> Secured Repositories -> Controller

You need to decide for yourself if the kind of security requirements justifies this additional layering (and maybe further layers, as it was suggested in a comment), which comes for the price of more complexity. There is no "one-size-fits-all" solution to this, one has to make such design decisions deliberate with the overall system in mind.

  • wait are you recommending each user have their own db user?
    – Ewan
    Commented May 20, 2020 at 6:06
  • @Ewan: no, I am not recommending any implementation details. But I guess I know what you have in mind: most web applications today will usually manage their own user/role/permission model which makes it actually hard to implement this inside the DB. See 2nd paragraph above.
    – Doc Brown
    Commented May 20, 2020 at 8:31
  • it reads like that is your prefered option though. I was surprised enough to feel the need to write my own answer
    – Ewan
    Commented May 20, 2020 at 8:33
  • @Ewan: see my edit. Better now?
    – Doc Brown
    Commented May 20, 2020 at 8:37
  • 1
    shrug to me the "DB itself should provide an adequate security model" sounds like you mean, "users should run sql as their own db user". which (to me) sounds bad and prompted me to write my own answer, but normally we agree on stuff so I wasn't sure if you had some special situation in mind, meant something else entirely or simply disagreed.
    – Ewan
    Commented May 20, 2020 at 8:58

Security should be applied by your framework before the Controller.

In most frameworks this means you grant access to Controller Methods.


The controller will have access to the authenticated user's details and can use these as parameters to filter data accordingly, e.g.

[Authorize(Role='editor')] //framework binding to requests limits access
     var userId = context.user.Id; //required data extracted from auth token/user context


A Repository's responsibility is returning data, so you can have a method on it such as GetDataByUserId, but it shouldn't be doing security.





Passing the user object or context into the repo violates the single responsibility rule and should not be attempted


Most databases don't have fine grained enough security controls for business logic and a web API will normally be using some sort of central sign on token. You don't want to have to duplicate all your web users in the database's security or be sending their credentials to the API so that it can log on as them.


var connstr = appsettings[connstringWithServiceUser]
var repo = new repo(connstr)


var connstr = "database;user" + context.user + context.password

This exposes the users' password; you shouldn't have access to it.


var connstr = "database;user=thisProcess"
...impersonate calling user

This is super tricky to pull off correctly. You will need Kerberos multihop impersonation and row level database security.

Your databases security model just isn't designed to hold all the front end users of your website. Having them all in there is an auditing nightmare and will lower your security.

Additionally you now have to implement your "user can only see their own articles" business logic via the DB's row level permissions. Good luck unit testing that. It's a super bad idea and I can't believe its getting upvotes.

Auth → Controller → Repo → DB

Is the almost universal solution for web APIs.

  • You are either describing an antipattern where business logic depends on the controller layer or your answer isn't specific enough about the difference between authentication and authorization. Commented May 20, 2020 at 20:43
  • 2
    I left the service layer out because the OP didnt mention it and I dont think it adds anything to the answer
    – Ewan
    Commented May 20, 2020 at 20:57
  • 3
    theres no real difference, you just put it inbetween the controler and the repo to separate your business logic out
    – Ewan
    Commented May 21, 2020 at 5:28
  • 1
    @Joundill if you consider authorization rules as your „domain” or „business rules”, it should not be implemented in the controller/web layer. The original question mentioned only two modules and there’s the problem that the architecture has too few boundaries. The repository is responsible for abstracting away the database, the controller is to handle the HTTP protocol, so who’s responsible for the authorization? You end up with a Fat Controller. Commented May 22, 2020 at 5:22
  • 2
    3 years later, I think this is the most correct answer. It is worth mentioning that the access control in this approach is more likely enforced by middleware than the controller itself.
    – Goku
    Commented Nov 11, 2023 at 16:47

Security is Layered

The truth of the mater is that Bad People™ will try and by pass your security.

By adding security you want to make it as genuinely difficult as possible for someone who is not there legitimately to do anything, while also reducing the amount of being in the way experienced by people who legitimately use the system everyday.


Your picture isn't entirely accurate, it looks more like:

DB -> Repository -> Controller -> User

The -> are the boundaries. Even though that is still a simplification, we could through networks and other issues in.

The point is that each boundary needs to allow the good stuff through, while making the bad stuff difficult/impossible to do.

You could place all of your security between the user and the controller, but if someone bypassed that, then they would have a field day.

Similarly you can't just place all of the security between the Database and Repository. It's already to late as the service itself is exposed, and any data being passed to it, must presumably be available to anyone using it. Which probably isn't reasonable to expect.

DB -> Repository

The actual Database Engine needs to enforce permissions, to the Repository. Obviously the Repository can't just do anything:

  • creating/dropping tables,
  • adding indices,
  • updating rows in these tables,
  • inserting rows to these other tables,
  • etc...

The Engine should give exactly the rights needed for that repository, and no more.

Repository -> Controller

The Repository similarly needs to ensure that a controller can't just do anything with it:

  • Delete a customer record, especially if it is linked to other records
  • Insert Multiple Payments against an already paid order
  • Permit the viewOrder controller to insert User Records.

This is partly done by implementing business sanity checks, and partly done by checking to see if the Controller has the right to do so. This is usually implemented by some form of whitelist checking that the caller is on the list, and has the required rights, or by some permissions object that can itself be verified.

Controller -> User

The Controller itself has to establish that the user has the required permissions. Usually done by some login method, or certificate. If the login/certificate passes muster, the users permissions are checked to see if they can access the controller.

This provides layers of security.

If for example an admin account got breached, and the used the OrderViewController with some sort of exploit, then the repository will reject odd requests, as the controller only has privilege X, Y, Z.

If they bypassed the controller and somehow got to within the repository, then they can't just drop all the data, or use it for storing their own in an easy way.


The approach you take depends upon the method of authentication you're going to choose. Your authentication can be as simple as verifying user's identity using username and password in the first call and thereafter you might issue a token for subsequent validation.

The amount of information stored in the token will decide whether access control would happen at controller level or at the model/repository level. Let's say your application has two kinds of users

  1. Admin User - This one has access to all kinds of records stored in the database
  2. Normal User - This one has access to only records it has created

If your authentication token only has the information about the user's identifier then to extract full information about the user from token you'll have to query the database and interaction with the database is not the work of controller it should be done by repositories as discussed here in the question Check permissions on the top layer before going deeper .

If your authentication token has all the information about the user's access and its role then in that case at the controller level itself you can extract all the permissions by decrypting the token and reject and accept the request based on the information you got from token.

Better approach IMO would be to store as much information as possible in the token, as mentioned here in the question Should access permissions and roles be included in the payload of JWT.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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