In this lesson, we're going to look at how you can run authorization policies in the database. So imagine you have a CRM style app like this, which lists all your customers, as well as the sales reps that are responsible for those customers. Note here that Ted Bossman is considered an owner in this application. Let's take a look at the database migrations. Let's look at the users migration first. As you can see, we have a Boolean that specifies if the user is an owner. Now let's look at the customers migration. In here, you'll notice a sales_rep_id, and this basically just connects a customer to a user. Now imagine that a new requirement came in that said sales reps are only allowed to see

In here, you'll notice a salesRepId, and this basically just connects a Customer to a User. Now imagine that a new requirement came in that said salesReps are only allowed to see their own customers, with the exception of owners. Ted Bossman here is always allowed to see all customers. How would you go about implementing something like that? Initially, this sounds a lot like the job of a policy. So let's start by creating one. Let's go to the terminal, and let's just say php artisan make:policy CustomerPolicy, and we're going to create a CustomerPolicy. Let's generate that policy now.

and we're going to create a CustomerPolicy. Let's generate that policy now. All right, we are going to create a public function view method. So this will serve as our view policy, which takes a User, and it takes a Customer. Before we forget, let's import the Customer class, and then let's implement this policy. We know that if the User is an owner, they'll always be able to see the Customer, so we can just check for that right away. Otherwise, the User can only see the Customer if they are the customer's sales rep. Okay, so before we can test that out, we actually need to log in a User. Now, I don't want to set up the whole auth scaffold just to show you this,

Okay, so before we can test that out, we actually need to log in a User. Now, I don't want to set up the whole auth scaffold just to show you this, so we're just going to manually log in the User. We can do that using the auth()->login() function. So we'll just say User::where('name', 'Sarah Seller'), and we'll just get the first result. Okay, so now that we have a User logged in, how do we go about running our policy check? You might be inclined to do this in our template. So let's go to our customers.blade.php template, and let's go down to where we actually output that customer.

So let's go to our customer's Blade template, and let's go down to where we actually output that customer. So let's just go in here and use the can helper and say, can the current User view this customer? And then we'll just close that down here, and can. And if we reload the page, we'll see that we have a new User. And can. And if we reload the page now, we should only see Sarah Seller's customers. And sure enough, we do. But you might notice a problem right away here.

And sure enough, we do. But you might notice a problem right away here. We are getting 75 results, and it's showing as 1 to 15, but we can only see 4. And that's because we only happen to have 4 of SarahSeller's customers on the first page. And if we go to the second page, it's the same kind of problem. We should have 15 results, but we don't. So obviously, this isn't a very good way of implementing this, because the pagination's all thrown off. OK, what can we do instead? Let's go back to our customers.blade.php file here, and let's just undo that change.

OK, what can we do instead? Let's go back to our customer's blade file here, and let's just undo that change. And let's go back to our customer's controller. And let's just say, OK, this is not going to be possible with pagination. So we're just going to do a get here instead. And then what we'll do is we'll just filter these results out manually. So we'll pass through the customer here, and we'll just do an auth check. So auth user, and can they view this customer? All right. And if we reload here, we should get an error, which we do,

All right. And if we reload here, we should get an error, which we do, because the links are no longer working, because we removed the pagination. So let's go back to our customers.blade.php view, and let's just disable the links for a second. If we hit reload here, now it's working exactly how we want. Mostly. This page is showing all of Sarah's Customers now, but we've given up pagination in the process. And even worse than that, if we look at our queries here, we can see we're getting all the customers from the database,

And even worse than that, if we look at our queries here, we can see we're getting all the customers from the database, not just the customers for Sarah Seller. If this application had thousands or even tens of thousands of customers, this would become a serious performance problem. And this is why it really makes sense to push this work into the database layer. Let's undo this change to re-enable the pagination links, and let's go back to our CustomersController. I'll show you how I like to solve this problem. Let's bring this back to the way it was before with the pagination.

I'll show you how I like to solve this problem. Let's bring this back to the way it was before with the pagination. And this time, let's add a new scope to our Customer model. And the scope we're going to create is called visibleTo, and it's going to take one argument, and that's going to be a User. And in this situation, that's going to be our current User. Let's write that scope. So public function scopeVisibleTo, because all scopes start with the word scope, visibleTo, and it's going to take an instance of the QueryBuilder, and the User that we pass through as our first argument.

visible to, and it's going to take an instance of the query builder, and the user that we pass through as our first argument. So this is going to feel a little bit like the policy we wrote, except we're going to have to write it in a way that the database understands. We'll start by checking to see if this customer's salesRepID equals this user's ID. So that's going to handle the situation where the user is not an owner. But what if they are an owner? Well, if you think about it, if they're an owner, we don't need to limit these records at all. So what we can do is we can just add an extra check to say,

we don't need to limit these records at all. So what we can do is we can just add an extra check to say, if the User is an owner, then just return early. So this should be all we need. Now, if we go back to our page and we hit reload here, you can see that our pagination is working again, 25 results, 1 to 15. And here's the second page. And if we look at our queries, we can see that the database query is now only grabbing the customers.

And if we look at our queries, we can see that the database query is now only grabbing the customers for salesRep number two, which is Sarah Seller. Now let's try this again for our owner, Ted Bosman. Let's log in as Ted instead. And now if we hit refresh, you can see that we're now getting all of the customers back. This is just like the way it was before, which is what we want. And if we look at our customers query here, we can see that we're selecting * from customers with no where condition.

And if we look at our customers query here, we can see that we're selecting * from customers with no where condition. Now, obviously this is a very simple example, but policy style scopes like this can be really, really helpful in instances where you have too much data, where it really doesn't make sense to do this work in PHP and where you really need to push this work to the database layer. I've used this technique for much more complicated situations, and it works really, really well. So definitely keep this technique in mind for your applications.