Skip to content

All topics  /  Laravel

Laravel User Roles and Permissions Using Centralized Database Tutorial

Laravel ·

Teams applying “Laravel User Roles and Permissions Using Centralized Database Tutorial” in a production project can use the official resource to record implementation, review and debugging time, then compare that effort with tests and shipped functionality instead of treating activity as performance by itself.

Before copying the example into a live application, confirm version-specific behaviour with the official Laravel website and test it against the project’s actual database and runtime.

Laravel User Roles and Permissions Using Centralized Database Tutorial

Hi dev,

In this tutorial, we will explore how to implement user roles and permissions using a centralized database approach in a Laravel application. This approach allows you to define and manage roles and permissions in a more structured and scalable way.

Implementing user roles and permissions using a centralized database in Laravel can greatly enhance your application's security and scalability. By structuring roles and permissions in a database, you can easily manage and modify access control as your application evolves.

Let's see the following steps:

Step 1: Set Up a New Laravel Project


If you don't have a Laravel project set up already, you can create one using the following command:

composer create-project --prefer-dist laravel/laravel user-roles-permissions

Step 2: Database Setup

Next, set up your database configuration in the .env file and run migrations to create the necessary tables:

php artisan migrate

Step 3: Create Roles and Permissions Tables 

php artisan make:migration create_roles_table
php artisan make:migration create_permissions_table

Step 4: Create Models 

php artisan make:model Role
php artisan make:model Permission

App/Model/CustomRole 

 
namespace App\Model;
use Illuminate\Database\Eloquent\Model;
use Spatie\Permission\Models\Role as SpatieRole;
class CustomRole extends SpatieRole
{
    protected $connection = 'mysql';
}

App/Model/CustomPermission 

 
namespace App\Model;
use Illuminate\Database\Eloquent\Model;
use Spatie\Permission\Models\Permission as SpatiePermission;
class CustomPermission extends SpatiePermission
{
    protected $connection = 'mysql';
}

Step 5: Set Environment

config/database.php 

 
use Illuminate\Support\Str;
return [
    /*
    |--------------------------------------------------------------------------
    | Default Database Connection Name
    |--------------------------------------------------------------------------
    |
    | Here you may specify which of the database connections below you wish
    | to use as your default connection for all database work. Of course
    | you may use many connections at once using the Database library.
    |
    */
    'default' => env('DB_CONNECTION', 'mysql'),
    /*
    |--------------------------------------------------------------------------
    | Database Connections
    |--------------------------------------------------------------------------
    |
    | Here are each of the database connections setup for your application.
    | Of course, examples of configuring each database platform that is
    | supported by Laravel is shown below to make development simple.
    |
    |
    | All database work in Laravel is done through the PHP PDO facilities
    | so make sure you have the driver for your particular database of
    | choice installed on your machine before you begin development.
    |
    */
    'connections' => [
        'sqlite' => [
            'driver' => 'sqlite',
            'url' => env('DATABASE_URL'),
            'database' => env('DB_DATABASE', database_path('database.sqlite')),
            'prefix' => '',
            'foreign_key_constraints' => env('DB_FOREIGN_KEYS', true),
        ],
        'mysql' => [
            'driver' => 'mysql',
            'url' => env('DATABASE_URL'),
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE', 'forge'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'unix_socket' => env('DB_SOCKET', ''),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'prefix_indexes' => true,
            'strict' => true,
            'engine' => null,
            'options' => extension_loaded('pdo_mysql') ? array_filter([
                PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
            ]) : [],
        ],
        'mysql_central_db' => [
            'driver' => 'mysql',
            'url' => env('DATABASE_URL_CENTRAL_DB'),
            'host' => env('DB_HOST_CENTRAL_DB', '127.0.0.1'),
            'port' => env('DB_PORT_CENTRAL_DB', '3306'),
            'database' => env('DB_DATABASE_CENTRAL_DB', 'forge'),
            'username' => env('DB_USERNAME_CENTRAL_DB', 'forge'),
            'password' => env('DB_PASSWORD_CENTRAL_DB', ''),
            'unix_socket' => env('DB_SOCKET_CENTRAL_DB', ''),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'prefix_indexes' => true,
            'strict' => false,
            'engine' => null,
            'options' => extension_loaded('pdo_mysql') ? array_filter([
                PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
            ]) : [],
        ],
        'pgsql' => [
            'driver' => 'pgsql',
            'url' => env('DATABASE_URL'),
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '5432'),
            'database' => env('DB_DATABASE', 'forge'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'charset' => 'utf8',
            'prefix' => '',
            'prefix_indexes' => true,
            'search_path' => 'public',
            'sslmode' => 'prefer',
        ],
        'sqlsrv' => [
            'driver' => 'sqlsrv',
            'url' => env('DATABASE_URL'),
            'host' => env('DB_HOST', 'localhost'),
            'port' => env('DB_PORT', '1433'),
            'database' => env('DB_DATABASE', 'forge'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'charset' => 'utf8',
            'prefix' => '',
            'prefix_indexes' => true,
            // 'encrypt' => env('DB_ENCRYPT', 'yes'),
            // 'trust_server_certificate' => env('DB_TRUST_SERVER_CERTIFICATE', 'false'),
        ],
    ],
    /*
    |--------------------------------------------------------------------------
    | Migration Repository Table
    |--------------------------------------------------------------------------
    |
    | This table keeps track of all the migrations that have already run for
    | your application. Using this information, we can determine which of
    | the migrations on disk haven't actually been run in the database.
    |
    */
    'migrations' => 'migrations',
    /*
    |--------------------------------------------------------------------------
    | Redis Databases
    |--------------------------------------------------------------------------
    |
    | Redis is an open source, fast, and advanced key-value store that also
    | provides a richer body of commands than a typical key-value system
    | such as APC or Memcached. Laravel makes it easy to dig right in.
    |
    */
    'redis' => [
        'client' => env('REDIS_CLIENT', 'phpredis'),
        'options' => [
            'cluster' => env('REDIS_CLUSTER', 'redis'),
            'prefix' => env('REDIS_PREFIX', Str::slug(env('APP_NAME', 'laravel'), '_').'_database_'),
        ],
        'default' => [
            'url' => env('REDIS_URL'),
            'host' => env('REDIS_HOST', '127.0.0.1'),
            'username' => env('REDIS_USERNAME'),
            'password' => env('REDIS_PASSWORD'),
            'port' => env('REDIS_PORT', '6379'),
            'database' => env('REDIS_DB', '0'),
        ],
        'cache' => [
            'url' => env('REDIS_URL'),
            'host' => env('REDIS_HOST', '127.0.0.1'),
            'username' => env('REDIS_USERNAME'),
            'password' => env('REDIS_PASSWORD'),
            'port' => env('REDIS_PORT', '6379'),
            'database' => env('REDIS_CACHE_DB', '1'),
        ],
    ],
];

Step 7: Set Env

.env 

DB_CONNECTION_CENTRAL_DB=mysql
DB_HOST_CENTRAL_DB=127.0.0.1
DB_PORT_CENTRAL_DB=3306
DB_DATABASE_CENTRAL_DB=celcius_production_new_central_login
DB_USERNAME_CENTRAL_DB=root
DB_PASSWORD_CENTRAL_DB=root

Step 6: Attach Roles and Permissions to config/permission.php 

'role' => App\Model\CustomRole::class,
'permission' => App\Model\CustomPermission::class,

Step 6: Used Centralized Database 

$customerRevenueCharges = DB::table('loads')
	->select('users.company_name as customer_name',DB::raw("SUM(loads.commission_amount) as total_amount"))
	->join(env('DB_DATABASE_CENTRAL_DB').'.users',env('DB_DATABASE_CENTRAL_DB').'.users.id','loads.user_id')
	->orderBy('total_amount','DESC')
	->groupBy('loads.user_id')
	->take(10)
	->cursor();

More on this topic