I Use This!
Very High Activity

News

Analyzed 21 days ago. based on code collected 21 days ago.
Posted 6 days ago by Justin Swanhart
I have bumped the minor version to 4.1.2 with this release which incorporates various pull requests from contributors. Of note is support for ALTER statements in PHPSQLCreator, which is the components of PHP-SQL-Parser responsible for turning a parse tree back into an executable SQL statement, basically an "unparser".
Posted 6 days ago by Severalnines
Easily Integrate ClusterControl With Your Existing DevOps Tools via s9s - Our New Command Line Interface We’ve heard your call (and, selfishly, our own): please meet s9s - the new command line interface (CLI) for ClusterControl, our ... [More] all-inclusive open source database management system. At every conference we’ve attended so far, visitors have been asking us whether there is a command line interface for ClusterControl. And, we’re not afraid to admit, some of us at Severalnines have always wanted to have one as well. So those same colleagues have gone and created the s9s CLI for ClusterControl, which we’re happy to present today. In fact, Johan Andersson, our CTO, is one of our command line aficionados and he describes the new CLI as follows: What’s the ClusterControl CLI all about? The ClusterControl CLI, is an open source project and optional package introduced with ClusterControl version 1.4.1. It is a command line tool to interact, control and manage your entire database infrastructure using ClusterControl. The s9s command line project is open source and is located on GitHub. The ClusterControl CLI opens a new door for cluster automation where you can easily integrate it with existing deployment automation tools like Ansible, Puppet, Chef or Salt. This allows you to easily integrate scripts from your orchestration tools inside the CLI. Users who have downloaded ClusterControl can use the CLI for all the ClusterControl features while they’re on the Enterprise trial of ClusterControl. Community users can then use the deployment and monitoring functionalities of ClusterControl. Existing customers can use the CLI to the full extent of ClusterControl. Usage and Installation The CLI can be installed by adding the s9s tools repository and using a package manager, as well as be compiled from source. The current installation script to install ClusterControl, install-cc, will automatically install the command line client. The command line client can also be installed on another computer or workstation for remote management. Finally, the CLI requires ClusterControl 1.4.1 or later. Moreover, all communication between the client and the controller is encrypted and secured using TLS. The ClusterControl CLI allows you to deploy and manage open source databases and load balancers in a way that is fully integrated and aligned with the ClusterControl core and GUI. The s9s command line project is open source and located on GitHub: https://github.com/severalnines/s9s-tools For examples and additional information, e.g, how to setup users and authentication, please visithttps://severalnines.com/docs/components.html#clustercontrol-cli Before you get started, you need to have ClusterControl version 1.4.1 or later installed, see https://severalnines.com/download-clustercontrol-database-management-system Some of the things you can do from the CLI in ClusterControl Deploy and manage database clusters MySQL PostgreSQL MongoDB to be added soon Monitor your databases Status of nodes and clusters Cluster properties can be extracted Gives detailed enough information about your clusters Manage your systems and integrate with DevOps tools Create, stop or start clusters Add, remove, or restart nodes in the cluster Create database users (CREATE USER, GRANT privileges to user) Users created in the CLI are traceable through the system Create load balancers (HAProxy, ProxySQL) Create and Restore backups Use maintenance mode Conduct configuration changes of db nodes Integrate with existing deployment automation Ansible, Puppet, Chef or Salt, ... Actions you take from the CLI will be visible in the ClusterControl Web UI and vice versa. How to contribute The CLI project (aka s9s-tools) can be accessed via GitHub. We encourage users to contribute to the project by: Trying out the CLI and give us feedback Letting us know about missing features, wishes, or problems by opening issues on GitHub Contributing patches to the project Related resources  About the ClusterControl CLI  Introduction video  CLI on GitHub  Documentation To sum things up The ClusterControl CLI and GUI are fully integrated and synced to allow you to utilize the CLI for deployment and management of your databases and load balancers, whilst using the advanced graphs in the GUI for monitoring and troubleshooting. The CLI offers detailed information about node stats and cluster stats, enabling scripts and other tools to benefit from those. In our experience, System Administrators and DevOps professionals are the mostly likely to benefit from a CLI for ClusterControl as they are accustomed to using scripts to perform their daily tasks. Happy command-line-clustering! Tags:  CLI clustercontrol database management MySQL PostgreSQL [Less]
Posted 7 days ago by InsideMySQL.com
Dear MySQL Users, A new GA (general availability) version of MySQL Connector/C++ has been made available: MySQL Connector/C++ 1.1.9 GA. The MySQL Connector/C++ provides a C++ API for connecting client applications to the MySQL Server 5.5 or newer. ... [More] You can download the production release at: http://dev.mysql.com/downloads/connector/cpp/1.1.html MySQL Connector C++ (Commercial) will be available for download on the My Oracle Support (MOS) website. This release will be available on eDelivery (OSDC) in next month’s upload cycle. The MySQL driver for C++ offers an easy to use API derived from JDBC 4.0. MySQL Workbench has used it successfully for years. We have improved the driver since the last GA release. Please see the documentation and the CHANGES file in the source distribution for a detailed description of bugs that have been fixed. Bug descriptions are also listed below. Enjoy! Changes in MySQL Connector/C++ 1.1.9 (2017-05-16, General Availability) Compilation Notes * The Windows version of Connector/C++ Community is now built using the dynamic C++ runtime library (that is, with the /MD compiler option), with the following implications for users: + Target hosts running Windows applications that use Connector/C++ Community now need the Visual C++ Redistributable for Visual Studio 2013 (https://www.microsoft.com/en-us/download/details.aspx?id=40784) installed on them. + Client applications on Windows that use Connector/C++ Community should be compiled with the /MD compiler option. Security Notes * The linked OpenSSL library for Connector/C++ 1.1.9 Commercial has been updated to version 1.0.2k. For a description of issues fixed in this version, seehttp://www.openssl.org/news/vulnerabilities.html This change does not affect the Oracle-produced MySQL Community build of Connector/C++, which uses the yaSSL library instead. Bugs Fixed * Values returned by getDouble() from DOUBLE table columns were truncated (decimal part missing) if the locale was set to fr_CA, which uses comma as the decimal separator. (Bug #17227390, Bug #69719) * Connections to localhost failed if the local server was bound only to its IPv6 interface. (Bug #17050354, Bug #69663) On Behalf of the MySQL/ORACLE RE Team Balasubramanian Kandasamy [Less]
Posted 7 days ago by Balasubramanian Kandasamy
Dear MySQL Users, A new GA (general availability) version of MySQL Connector/C++ has been made available: MySQL Connector/C++ 1.1.9 GA. The MySQL Connector/C++ provides a C++ API for connecting client applications to the MySQL Server 5.5 or ... [More] newer. You can download the production release at: http://dev.mysql.com/downloads/connector/cpp/1.1.html MySQL Connector C++ (Commercial) will be available for download on the My Oracle Support (MOS) website. This release will be available on eDelivery (OSDC) in next month's upload cycle. The MySQL driver for C++ offers an easy to use API derived from JDBC 4.0. MySQL Workbench has used it successfully for years. We have improved the driver since the last GA release. Please see the documentation and the CHANGES file in the source distribution for a detailed description of bugs that have been fixed. Bug descriptions are also listed below. Enjoy! Changes in MySQL Connector/C++ 1.1.9 (2017-05-16, General Availability) Compilation Notes * The Windows version of Connector/C++ Community is now built using the dynamic C++ runtime library (that is, with the /MD compiler option), with the following implications for users: + Target hosts running Windows applications that use Connector/C++ Community now need the Visual C++ Redistributable for Visual Studio 2013 (https://www.microsoft.com/en-us/download/details.aspx?id=40784) installed on them. + Client applications on Windows that use Connector/C++ Community should be compiled with the /MD compiler option. Security Notes * The linked OpenSSL library for Connector/C++ 1.1.9 Commercial has been updated to version 1.0.2k. For a description of issues fixed in this version, see http://www.openssl.org/news/vulnerabilities.html. This change does not affect the Oracle-produced MySQL Community build of Connector/C++, which uses the yaSSL library instead. Bugs Fixed * Values returned by getDouble() from DOUBLE table columns were truncated (decimal part missing) if the locale was set to fr_CA, which uses comma as the decimal separator. (Bug #17227390, Bug #69719) * Connections to localhost failed if the local server was bound only to its IPv6 interface. (Bug #17050354, Bug #69663) On Behalf of the MySQL/ORACLE RE Team Balasubramanian Kandasamy [Less]
Posted 7 days ago by Scotch.io
What We'll Build When building an application, we often need to set up an access control list (ACL). An ACL specifies the level of permission granted to a user of an application. For example a user John may have the permission to read and write to a ... [More] resource while another user Smith may have the permission only to read the resource. In this tutorial, I will teach you how to add access control to a Laravel app using Laravel-permission package. For this tutorial we will build a simple blog application where users can be assigned different levels of permission. Our user admin page will look like this: Why Use Laravel-Permission The Laravel-Permission package is built on top of Laravel's authorization features introduced in the 5.1.1 release. Although there are other packages that claim to offer similar functionalities, none of them have the same level of activity and maintenance as the laravel-permission package. Development Environment and Installation You can get Laravel up and running by first downloading the installer composer global require "laravel/installer" Then add $HOME/.composer/vendor/bin to your $PATH so the laravel executable can be located by your system. Now you can install the latest stable version of Laravel by running laravel new To install the laravel-permission package run composer require spatie/laravel-permission Next include the package to our list of service providers, in config/app.php add Spatie\Permission\PermissionServiceProvider::class so our file looks like this 'providers' => [ ... Spatie\Permission\PermissionServiceProvider::class, ]; Next publish the migration file for this package with the command php artisan vendor:publish --provider="Spatie\Permission\PermissionServiceProvider" --tag="migrations" Database Setup and Migrations Next create the database and update the .env file to include the database information. For example, for this tutorial the database information section of the .env looks like this: DB_CONNECTION=mysql DB_HOST=127.0.0.1 DB_PORT=3306 DB_DATABASE=acl4 DB_USERNAME=root DB_PASSWORD= To build the tables, run php artisan migrate Please note that in Laravel 5.4 the default character set is changed to utf8mb4, therefore if you are running MariaDB or MYSQL version lower than 5.7.7 you may get this error when trying to run migration files [Illuminate\Database\QueryException] SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes (SQL: alter table users add unique users_email_unique(email)) [PDOException] SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes To fix this error edit the app\Providers\AppServiceProvider.php file, setting the default string length in the boot method use Illuminate\Support\Facades\Schema; public function boot() { Schema::defaultStringLength(191); } After that run the migration again. If it works as normal you would find the following tables in your database: migrations: This keeps track of migration process that have ran users: This holds the users data of the application password_resets: Holds token information when users request a new password permissions: This holds the various permissions needed in the application roles: This holds the roles in our application role_has_permission: This is a pivot table that holds relationship information between the permissions table and the role table user_has_roles: Also a pivot table, holds relationship information between the roles and the users table. user_has_permissions: Also a pivot table, holds relationship information between the users table and the permissions table. Publish the configuration file for this package by running php artisan vendor:publish --provider="Spatie\Permission\PermissionServiceProvider" --tag="config" The config file allows us to set the location of the Eloquent model of the permission and role class. You can also manually set the table names that should be used to retrieve your roles and permissions. Next we need to add the HasRoles trait to the User model: use Illuminate\Foundation\Auth\User as Authenticatable; use Spatie\Permission\Traits\HasRoles; class User extends Authenticatable { use HasRoles; // ... } Laravel Collective HTML Form builder Next install Laravel Collective HTML Form builder as this will be useful further on when we are creating our forms: composer require laravelcollective/html Then add your new provider to the providers array of config/app.php: 'providers' => [ ... Collective\Html\HtmlServiceProvider::class, ]; Finally, add two class aliases to the aliases array of config/app.php: 'aliases' => [ // ... 'Form' => Collective\Html\FormFacade::class, 'Html' => Collective\Html\HtmlFacade::class, // ... ], That's all the installation and configuration needed. A role can be created like a regular Eloquent model, like this: use Spatie\Permission\Models\Role; use Spatie\Permission\Models\Permission; $role = Role::create(['name' => 'writer']); $permission = Permission::create(['name' => 'edit articles']); You can also get the permissions associated to a user like this: $permissions = $user->permissions; And using the pluck method, pluck() you can get the role names associated with a user like this: $roles = $user->roles()->pluck('name'); Other methods available to us include: givePermissionTo(): Allows us to give persmission to a user or role revokePermissionTo(): Revoke permission from a user or role hasPermissionTo(): Check if a user or role has a given permission assignRole(): Assigns role to a user removeRole(): Removes role from a user hasRole(): Checks if a user has a role hasAnyRole(Role::all()): Checks if a user has any of a given list of roles hasAllRoles(Role::all()): Checks if a user has all of a given list of role The methods assignRole, hasRole, hasAnyRole, hasAllRoles and removeRole can accept a string, a Spatie\Permission\Models\Role-object or an \Illuminate\Support\Collection object. The givePermissionTo and revokePermissionTo methods can accept a string or a Spatie\Permission\Models\Permission object. Laravel-Permission also allows to use Blade directives to verify if the logged in user has all or any of a given list of roles: @role('writer') I'm a writer! @else I'm not a writer... @endrole @hasrole('writer') I'm a writer! @else I'm not a writer... @endhasrole @hasanyrole(Role::all()) I have one or more of these roles! @else I have none of these roles... @endhasanyrole @hasallroles(Role::all()) I have all of these roles! @else I don't have all of these roles... @endhasallroles The Blade directives above depends on the users role. Sometimes we need to check directly in our view if a user has a certain permission. You can do that using Laravel's native @can directive: @can('Edit Post') I have permission to edit @endcan Controllers, Authentication and Views You will need a total of four controllers for this application. Let's use resource controllers, as this automatically adds stub methods for us. Our controllers will be called PostController UserController RoleController PermissionController Before working on these controllers let's create our authentication system. With one command Laravel provides a quick way to scaffold all of the routes and views needed for authentication. php artisan make:auth After running this command you would notice two new links for user login and registration in the home page. This command also creates a HomeController (you can delete this as it won't be needed), a resources/views/layouts/app.blade.php file which contains markup that would be shared by all our views and an app/Http/Controllers/Auth directory which contains the controllers for registration and login. Switch into this directory and open the RegisterController.phpfile. Remove the bcrypt function in the create method, so the the method looks like this protected function create(array $data) { return User::create([ 'name' => $data['name'], 'email' => $data['email'], 'password' => $data['password'], ]); } Instead let's define a mutator in app\User.php which would encrypt all our password fields. In app\User.php add this method: public function setPasswordAttribute($password) { $this->attributes['password'] = bcrypt($password); } This would provide the same functionality as before but now you don't need to write the bcrypt function when dealing with the password field in subsequent controllers. Also in the RegisterController.phpfile. Change the $redirectTo property to: protected $redirectTo = '/'; Do the same thing in the LoginController.phpfile. Since the HomeController has been deleted our users are now redirected to the home page which would contain a list of our blog posts. Next let's edit the resources/views/layouts/app.blade.php file to include: an extra drop-down 'Admin' link to view all users and an errors file which checks if our form produced any error. The 'Admin' link would only be viewed by users with the 'Admin' Role. We would also create a custom styles.css which would have extra styling for our resources/views/posts/index.blade.php view. The styling is just a paragraph in the teaser of our index view, the file should be located in public/css/styles.css {{-- resources/views/layouts/app.blade.php --}} {{ config('app.name', 'Laravel') }} Toggle Navigation {{ config('app.name', 'Laravel') }} Home @if (!Auth::guest()) New Article @endif @if (Auth::guest()) Login Register @else {{ Auth::user()->name }} @role('Admin') {{-- Laravel-permission blade helper --}} Admin @endrole Logout @endif @if(Session::has('flash_message')) {!! session('flash_message') !!} @endif @include ('errors.list') {{-- Including error file --}} @yield('content') The error file is: {{-- resources\views\errors\list.blade.php --}} @if (count($errors) > 0) @foreach ($errors->all() as $error) {{ $error }} @endforeach @endif and the styles.css file is simply: p.teaser { text-indent: 30px; } Post Controller First, let's create the migration and model files for the PostController php artisan make:model Post -m This command generates a migration file in app/database/migrations for generating a new MySQL table named posts in our database and a model file Post.phpin the app directory. Let's edit the migration file to include title and body fields of our post. Add a title and body field so the migration file looks like this: increments('id'); $table->string('title'); $table->text('body'); $table->timestamps(); }); } /** * Reverse the migrations. * * @return void */ public function down() { Schema::dropIfExists('posts'); } } After saving the file, run migration again php artisan migrate You can now check the database for the post table and columns. Next make the title and body field of the Post model mass assignable namespace App; use Illuminate\Database\Eloquent\Model; class Post extends Model { protected $fillable = [ 'title', 'body' ]; } Now let's generate our resource controller. php artisan make:controller PostController --resource This will create our controller with all the stub methods needed. Edit this file to look like this middleware(['auth', 'clearance'])->except('index', 'show'); } /** * Display a listing of the resource. * * @return \Illuminate\Http\Response */ public function index() { $posts = Post::orderby('id', 'desc')->paginate(5); //show only 5 items at a time in descending order return view('posts.index', compact('posts')); } /** * Show the form for creating a new resource. * * @return \Illuminate\Http\Response */ public function create() { return view('posts.create'); } /** * Store a newly created resource in storage. * * @param \Illuminate\Http\Request $request * @return \Illuminate\Http\Response */ public function store(Request $request) { //Validating title and body field $this->validate($request, [ 'title'=>'required|max:100', 'body' =>'required', ]); $title = $request['title']; $body = $request['body']; $post = Post::create($request->only('title', 'body')); //Display a successful message upon save return redirect()->route('posts.index') ->with('flash_message', 'Article, '. $post->title.' created'); } /** * Display the specified resource. * * @param int $id * @return \Illuminate\Http\Response */ public function show($id) { $post = Post::findOrFail($id); //Find post of id = $id return view ('posts.show', compact('post')); } /** * Show the form for editing the specified resource. * * @param int $id * @return \Illuminate\Http\Response */ public function edit($id) { $post = Post::findOrFail($id); return view('posts.edit', compact('post')); } /** * Update the specified resource in storage. * * @param \Illuminate\Http\Request $request * @param int $id * @return \Illuminate\Http\Response */ public function update(Request $request, $id) { $this->validate($request, [ 'title'=>'required|max:100', 'body'=>'required', ]); $post = Post::findOrFail($id); $post->title = $request->input('title'); $post->body = $request->input('body'); $post->save(); return redirect()->route('posts.show', $post->id)->with('flash_message', 'Article, '. $post->title.' updated'); } /** * Remove the specified resource from storage. * * @param int $id * @return \Illuminate\Http\Response */ public function destroy($id) { $post = Post::findOrFail($id); $post->delete(); return redirect()->route('posts.index') ->with('flash_message', 'Article successfully deleted'); } } Here the Post class was imported from our model and the Auth class which was generated with the make:auth command earlier. These were imported so that you would be able to make Eloquent queries on the Post table and so as to be able to have access to authentication information of our users. In the constructor two middlewares were called, one is auth which restricts access to the PostController methods to authenticated users the other is a custom middleware is yet to be created. This would be responsible for our Permissions and Roles system. Next, index and show are passed into the except method to allow all users to be able to view posts. The index() method lists all the available posts. It queries the post table for all posts and passes this information to the view. Paginate() allows us to limit the number of posts in a page, in this case five. The create() method simply returns the posts/create view which would contain a form for creating new posts. The store() method saves the information input from the posts/create view. The information is first validated and after it is saved, a flash message is passed to the view posts/index. Our show() method of the PostController allows us to display a single post. This method takes the post id as an argument and passes it to the method Post::find(). The result of the query is then sent to our posts/show view. The edit() method, similar to the create() method simply returns the posts/edit view which would contain a form for creating editing posts. The update() method takes the information from the posts/edit view and updates the record. The destroy() method let's us delete a post. Now that you have the PostController you need to set up the routes. Edit your app/routes/web.php file to look like this: name('home'); Route::resource('users', 'UserController'); Route::resource('roles', 'RoleController'); Route::resource('permissions', 'PermissionController'); Route::resource('posts', 'PostController'); The / route is the route to our home page, here it was renamed to home The Auth route was generated when you ran the make:auth command. It handles authentication related routes. The other four routes are for resources that would be created later. Post Views Only four views are needed for our PostController. Create the files \resources\views\posts\index.blade.php, \resources\views\posts\create.blade.php, \resources\views\posts\show.blade.php, \resources\views\posts\edit.blade.php Edit the index.blade.phpfile to look like this @extends('layouts.app') @section('content') Posts Page {{ $posts->currentPage() }} of {{ $posts->lastPage() }} @foreach ($posts as $post) {{ $post->title }} {{ str_limit($post->body, 100) }} {{-- Limit teaser to 100 characters --}} @endforeach {!! $posts->links() !!} @endsection Notice that this file extends views\layouts\app.php file, which was generated earlier by the make:auth command. The create.blade.php file looks like this @extends('layouts.app') @section('title', '| Create New Post') @section('content') Create New Post {{-- Using the Laravel HTML Form Collective to create our form --}} {{ Form::open(array('route' => 'posts.store')) }} {{ Form::label('title', 'Title') }} {{ Form::text('title', null, array('class' => 'form-control')) }} {{ Form::label('body', 'Post Body') }} {{ Form::textarea('body', null, array('class' => 'form-control')) }} {{ Form::submit('Create Post', array('class' => 'btn btn-success btn-lg btn-block')) }} {{ Form::close() }} @endsection The show view looks like this: @extends('layouts.app') @section('title', '| View Post') @section('content') {{ $post->title }} {{ $post->body }} {!! Form::open(['method' => 'DELETE', 'route' => ['posts.destroy', $post->id] ]) !!} Back @can('Edit Post') Edit @endcan @can('Delete Post') {!! Form::submit('Delete', ['class' => 'btn btn-danger']) !!} @endcan {!! Form::close() !!} @endsection Here the can directive checks if a user has the permission to Edit or Delete Posts, if so the Edit and Delete button will be displayed. If the user does not have these permissions, only the Back button would be displayed. The edit view just displays a edit form that will be used to update records: @extends('layouts.app') @section('title', '| Edit Post') @section('content') Edit Post {{ Form::model($post, array('route' => array('posts.update', $post->id), 'method' => 'PUT')) }} {{ Form::label('title', 'Title') }} {{ Form::text('title', null, array('class' => 'form-control')) }} {{ Form::label('body', 'Post Body') }} {{ Form::textarea('body', null, array('class' => 'form-control')) }} {{ Form::submit('Save', array('class' => 'btn btn-primary')) }} {{ Form::close() }} @endsection If you visit the home page you would see this User Controller The UserController will handle displaying all users, creating of new users, editing users, assigning roles to users and deleting users. As before generate the controller by running php artisan make:controller UserController --resource Then replace the content of this file with: middleware(['auth', 'isAdmin']); //isAdmin middleware lets only users with a //specific permission permission to access these resources } /** * Display a listing of the resource. * * @return \Illuminate\Http\Response */ public function index() { //Get all users and pass it to the view $users = User::all(); return view('users.index')->with('users', $users); } /** * Show the form for creating a new resource. * * @return \Illuminate\Http\Response */ public function create() { //Get all roles and pass it to the view $roles = Role::get(); return view('users.create', ['roles'=>$roles]); } /** * Store a newly created resource in storage. * * @param \Illuminate\Http\Request $request * @return \Illuminate\Http\Response */ public function store(Request $request) { //Validate name, email and password fields $this->validate($request, [ 'name'=>'required|max:120', 'email'=>'required|email|unique:users', 'password'=>'required|min:6|confirmed' ]); $user = User::create($request->only('email', 'name', 'password')); //Retrieving only the email and password data $roles = $request['roles']; //Retrieving the roles field //Checking if a role was selected if (isset($roles)) { foreach ($roles as $role) { $role_r = Role::where('id', '=', $role)->firstOrFail(); $user->assignRole($role_r); //Assigning role to user } } //Redirect to the users.index view and display message return redirect()->route('users.index') ->with('flash_message', 'User successfully added.'); } /** * Display the specified resource. * * @param int $id * @return \Illuminate\Http\Response */ public function show($id) { return redirect('users'); } /** * Show the form for editing the specified resource. * * @param int $id * @return \Illuminate\Http\Response */ public function edit($id) { $user = User::findOrFail($id); //Get user with specified id $roles = Role::get(); //Get all roles return view('users.edit', compact('user', 'roles')); //pass user and roles data to view } /** * Update the specified resource in storage. * * @param \Illuminate\Http\Request $request * @param int $id * @return \Illuminate\Http\Response */ public function update(Request $request, $id) { $user = User::findOrFail($id); //Get role specified by id //Validate name, email and password fields $this->validate($request, [ 'name'=>'required|max:120', 'email'=>'required|email|unique:users,email,'.$id, 'password'=>'required|min:6|confirmed' ]); $input = $request->only(['name', 'email', 'password']); //Retreive the name, email and password fields $roles = $request['roles']; //Retreive all roles $user->fill($input)->save(); if (isset($roles)) { $user->roles()->sync($roles); //If one or more role is selected associate user to roles } else { $user->roles()->detach(); //If no role is selected remove exisiting role associated to a user } return redirect()->route('users.index') ->with('flash_message', 'User successfully edited.'); } /** * Remove the specified resource from storage. * * @param int $id * @return \Illuminate\Http\Response */ public function destroy($id) { //Find a user with a given id and delete $user = User::findOrFail($id); $user->delete(); return redirect()->route('users.index') ->with('flash_message', 'User successfully deleted.'); } } Here the User class, the Role class, the Permission class and the Auth class are imported. In the constructor the auth middleware is called to make sure only authenticated users have access to the User resource. A custom middleware isAdmin is also called. This checks if the authenticated user has administrator privileges. This middleware will be created later. The index() method gets all users from the Users table and passes it to the index view which will display all users in a table. The create() method first gets all the Roles from the Roles table and passes it to the create view. This is so that Roles can be added when creating a User. The store() method saves the input from the create view, after validating the input, looping through the Roles that was passed in the form and assigning these Roles to the User. The show()method just redirects back to the users page as for this demonstration, we wont need to show each user individually. The edit() method gets the user corresponding to the id passed, then gets all roles and passes it to the edit view. The update() method validates data from the edit view and saves the updated name and password fields. It gets all roles from the roles table and while looping through them, removes any role assign to the user. It then takes the role data inputted from the form, matches them with the values in the databases and assigns these roles to the user. The destroy() method allows us to delete a user along with it's corresponding role. User Views Three views are needed here: index, create and edit views. The index view would contain a table that lists all our users and their roles. {{-- \resources\views\users\index.blade.php --}} @extends('layouts.app') @section('title', '| Users') @section('content') User Administration Roles Permissions Name Email Date/Time Added User Roles Operations @foreach ($users as $user) {{ $user->name }} {{ $user->email }} {{ $user->created_at->format('F d, Y h:ia') }} {{ $user->roles()->pluck('name')->implode(' ') }}{{-- Retrieve array of roles associated to a user and convert to string --}} Edit {!! Form::open(['method' => 'DELETE', 'route' => ['users.destroy', $user->id] ]) !!} {!! Form::submit('Delete', ['class' => 'btn btn-danger']) !!} {!! Form::close() !!} @endforeach Add User @endsection The create view is just a form that allows us to create new users and assign roles to them. {{-- \resources\views\users\create.blade.php --}} @extends('layouts.app') @section('title', '| Add User') @section('content') Add User {{ Form::open(array('url' => 'users')) }} {{ Form::label('name', 'Name') }} {{ Form::text('name', '', array('class' => 'form-control')) }} {{ Form::label('email', 'Email') }} {{ Form::email('email', '', array('class' => 'form-control')) }} @foreach ($roles as $role) {{ Form::checkbox('roles[]', $role->id ) }} {{ Form::label($role->name, ucfirst($role->name)) }} @endforeach {{ Form::label('password', 'Password') }} {{ Form::password('password', array('class' => 'form-control')) }} {{ Form::label('password', 'Confirm Password') }} {{ Form::password('password_confirmation', array('class' => 'form-control')) }} {{ Form::submit('Add', array('class' => 'btn btn-primary')) }} {{ Form::close() }} @endsection The edit view is a form that allows us to edit users and their roles. Using Laravel's form model binding the form is automatically populated with the previous values. {{-- \resources\views\users\edit.blade.php --}} @extends('layouts.app') @section('title', '| Edit User') @section('content') Edit {{$user->name}} {{ Form::model($user, array('route' => array('users.update', $user->id), 'method' => 'PUT')) }}{{-- Form model binding to automatically populate our fields with user data --}} {{ Form::label('name', 'Name') }} {{ Form::text('name', null, array('class' => 'form-control')) }} {{ Form::label('email', 'Email') }} {{ Form::email('email', null, array('class' => 'form-control')) }} Give Role @foreach ($roles as $role) {{ Form::checkbox('roles[]', $role->id, $user->roles ) }} {{ Form::label($role->name, ucfirst($role->name)) }} @endforeach {{ Form::label('password', 'Password') }} {{ Form::password('password', array('class' => 'form-control')) }} {{ Form::label('password', 'Confirm Password') }} {{ Form::password('password_confirmation', array('class' => 'form-control')) }} {{ Form::submit('Add', array('class' => 'btn btn-primary')) }} {{ Form::close() }} @endsection Permission Controller Now let's tackle the PermissionControllerCreate the file and paste the following code: middleware(['auth', 'isAdmin']); //isAdmin middleware lets only users with a //specific permission permission to access these resources } /** * Display a listing of the resource. * * @return \Illuminate\Http\Response */ public function index() { $permissions = Permission::all(); //Get all permissions return view('permissions.index')->with('permissions', $permissions); } /** * Show the form for creating a new resource. * * @return \Illuminate\Http\Response */ public function create() { $roles = Role::get(); //Get all roles return view('permissions.create')->with('roles', $roles); } /** * Store a newly created resource in storage. * * @param \Illuminate\Http\Request $request * @return \Illuminate\Http\Response */ public function store(Request $request) { $this->validate($request, [ 'name'=>'required|max:40', ]); $name = $request['name']; $permission = new Permission(); $permission->name = $name; $roles = $request['roles']; $permission->save(); if (!empty($request['roles'])) { //If one or more role is selected foreach ($roles as $role) { $r = Role::where('id', '=', $role)->firstOrFail(); //Match input role to db record $permission = Permission::where('name', '=', $name)->first(); //Match input //permission to db record $r->givePermissionTo($permission); } } return redirect()->route('permissions.index') ->with('flash_message', 'Permission'. $permission->name.' added!'); } /** * Display the specified resource. * * @param int $id * @return \Illuminate\Http\Response */ public function show($id) { return redirect('permissions'); } /** * Show the form for editing the specified resource. * * @param int $id * @return \Illuminate\Http\Response */ public function edit($id) { $permission = Permission::findOrFail($id); return view('permissions.edit', compact('permission')); } /** * Update the specified resource in storage. * * @param \Illuminate\Http\Request $request * @param int $id * @return \Illuminate\Http\Response */ public function update(Request $request, $id) { $permission = Permission::findOrFail($id); $this->validate($request, [ 'name'=>'required|max:40', ]); $input = $request->all(); $permission->fill($input)->save(); return redirect()->route('permissions.index') ->with('flash_message', 'Permission'. $permission->name.' updated!'); } /** * Remove the specified resource from storage. * * @param int $id * @return \Illuminate\Http\Response */ public function destroy($id) { $permission = Permission::findOrFail($id); //Make it impossible to delete this specific permission if ($permission->name == "Administer roles & permissions") { return redirect()->route('permissions.index') ->with('flash_message', 'Cannot delete this Permission!'); } $permission->delete(); return redirect()->route('permissions.index') ->with('flash_message', 'Permission deleted!'); } } In the store() method, we are making it possible for a role to be selected as a permission is created. After validating and saving the permission name field, a check is done if a role was selected if it was, a permission is assigned to the selected role. Permission View Three views are needed here as well. The index view would list in a table all the available permissions, the create view is a form which would be used to create a new permission and the edit view is a form that let's us edit existing permission. {{-- \resources\views\permissions\index.blade.php --}} @extends('layouts.app') @section('title', '| Permissions') @section('content') Available Permissions Users Roles Permissions Operation @foreach ($permissions as $permission) {{ $permission->name }} Edit {!! Form::open(['method' => 'DELETE', 'route' => ['permissions.destroy', $permission->id] ]) !!} {!! Form::submit('Delete', ['class' => 'btn btn-danger']) !!} {!! Form::close() !!} @endforeach Add Permission @endsection The following is the create view {{-- \resources\views\permissions\create.blade.php --}} @extends('layouts.app') @section('title', '| Create Permission') @section('content') Add Permission {{ Form::open(array('url' => 'permissions')) }} {{ Form::label('name', 'Name') }} {{ Form::text('name', '', array('class' => 'form-control')) }} @if(!$roles->isEmpty()) //If no roles exist yet Assign Permission to Roles @foreach ($roles as $role) {{ Form::checkbox('roles[]', $role->id ) }} {{ Form::label($role->name, ucfirst($role->name)) }} @endforeach @endif {{ Form::submit('Add', array('class' => 'btn btn-primary')) }} {{ Form::close() }} @endsection And finally the edit view: @extends('layouts.app') @section('title', '| Edit Permission') @section('content') Edit {{$permission->name}} {{ Form::model($permission, array('route' => array('permissions.update', $permission->id), 'method' => 'PUT')) }}{{-- Form model binding to automatically populate our fields with permission data --}} {{ Form::label('name', 'Permission Name') }} {{ Form::text('name', null, array('class' => 'form-control')) }} {{ Form::submit('Edit', array('class' => 'btn btn-primary')) }} {{ Form::close() }} @endsection Role Controller The RoleController is quite similar to the UserController. This controller will allow us to create roles and assign one or more permissions to a role. Create the file and paste the following code: middleware(['auth', 'isAdmin']);//isAdmin middleware lets only users with a //specific permission permission to access these resources } /** * Display a listing of the resource. * * @return \Illuminate\Http\Response */ public function index() { $roles = Role::all();//Get all roles return view('roles.index')->with('roles', $roles); } /** * Show the form for creating a new resource. * * @return \Illuminate\Http\Response */ public function create() { $permissions = Permission::all();//Get all permissions return view('roles.create', ['permissions'=>$permissions]); } /** * Store a newly created resource in storage. * * @param \Illuminate\Http\Request $request * @return \Illuminate\Http\Response */ public function store(Request $request) { //Validate name and permissions field $this->validate($request, [ 'name'=>'required|unique:roles|max:10', 'permissions' =>'required', ] ); $name = $request['name']; $role = new Role(); $role->name = $name; $permissions = $request['permissions']; $role->save(); //Looping thru selected permissions foreach ($permissions as $permission) { $p = Permission::where('id', '=', $permission)->firstOrFail(); //Fetch the newly created role and assign permission $role = Role::where('name', '=', $name)->first(); $role->givePermissionTo($p); } return redirect()->route('roles.index') ->with('flash_message', 'Role'. $role->name.' added!'); } /** * Display the specified resource. * * @param int $id * @return \Illuminate\Http\Response */ public function show($id) { return redirect('roles'); } /** * Show the form for editing the specified resource. * * @param int $id * @return \Illuminate\Http\Response */ public function edit($id) { $role = Role::findOrFail($id); $permissions = Permission::all(); return view('roles.edit', compact('role', 'permissions')); } /** * Update the specified resource in storage. * * @param \Illuminate\Http\Request $request * @param int $id * @return \Illuminate\Http\Response */ public function update(Request $request, $id) { $role = Role::findOrFail($id);//Get role with the given id //Validate name and permission fields $this->validate($request, [ 'name'=>'required|max:10|unique:roles,name,'.$id, 'permissions' =>'required', ]); $input = $request->except(['permissions']); $permissions = $request['permissions']; $role->fill($input)->save(); $p_all = Permission::all();//Get all permissions foreach ($p_all as $p) { $role->revokePermissionTo($p); //Remove all permissions associated with role } foreach ($permissions as $permission) { $p = Permission::where('id', '=', $permission)->firstOrFail(); //Get corresponding form //permission in db $role->givePermissionTo($p); //Assign permission to role } return redirect()->route('roles.index') ->with('flash_message', 'Role'. $role->name.' updated!'); } /** * Remove the specified resource from storage. * * @param int $id * @return \Illuminate\Http\Response */ public function destroy($id) { $role = Role::findOrFail($id); $role->delete(); return redirect()->route('roles.index') ->with('flash_message', 'Role deleted!'); } } Roles View Three views are needed here as well. The index view to display available roles and associated permissions, the create view to add a new role and a view to edit an existing role. Create the index.blade.php file and paste the following: {{-- \resources\views\roles\index.blade.php --}} @extends('layouts.app') @section('title', '| Roles') @section('content') Roles Users Permissions Role Permissions Operation @foreach ($roles as $role) {{ $role->name }} {{ str_replace(array('[',']','"'),'', $role->permissions()->pluck('name')) }}{{-- Retrieve array of permissions associated to a role and convert to string --}} Edit {!! Form::open(['method' => 'DELETE', 'route' => ['roles.destroy', $role->id] ]) !!} {!! Form::submit('Delete', ['class' => 'btn btn-danger']) !!} {!! Form::close() !!} @endforeach Add Role @endsection For the create view: @extends('layouts.app') @section('title', '| Add Role') @section('content') Add Role {{ Form::open(array('url' => 'roles')) }} {{ Form::label('name', 'Name') }} {{ Form::text('name', null, array('class' => 'form-control')) }} Assign Permissions @foreach ($permissions as $permission) {{ Form::checkbox('permissions[]', $permission->id ) }} {{ Form::label($permission->name, ucfirst($permission->name)) }} @endforeach {{ Form::submit('Add', array('class' => 'btn btn-primary')) }} {{ Form::close() }} @endsection And for the edit view: @extends('layouts.app') @section('title', '| Edit Role') @section('content') Edit Role: {{$role->name}} {{ Form::model($role, array('route' => array('roles.update', $role->id), 'method' => 'PUT')) }} {{ Form::label('name', 'Role Name') }} {{ Form::text('name', null, array('class' => 'form-control')) }} Assign Permissions @foreach ($permissions as $permission) {{Form::checkbox('permissions[]', $permission->id, $role->permissions ) }} {{Form::label($permission->name, ucfirst($permission->name)) }} @endforeach {{ Form::submit('Edit', array('class' => 'btn btn-primary')) }} {{ Form::close() }} @endsection Middleware To restrict access to the roles and permissions page, a middleware was included called isAdmin in our PermissionController and RoleController. This middleware counts how many users are in the Users table, and if there are more than one users, it checks if the current authenticated User has the permission to 'Administer roles & permissions'. To create a permission visit http://localhost:8000/permissions/create. Then go to http://localhost:8000/roles/create to create a role, to which you can now assign the permission you created. For example you can create a permission called 'Administer roles & permissions' and a 'Admin' role to which you would assign this permission. Create the AdminMiddleware in the directory app/Http/Middleware/ and enter the following code: count(); if (!($user == 1)) { if (!Auth::user()->hasPermissionTo('Administer roles & permissions')) //If user does //not have this permission { abort('401'); } } return $next($request); } } A middleware called clearance was also included in our PostController. This middleware would check if a user has the permissions Administer roles & permissions, Create Post, Edit Post and Delete Post. hasPermissionTo('Administer roles & permissions')) //If user has this //permission { return $next($request); } if ($request->is('posts/create'))//If user is creating a post { if (!Auth::user()->hasPermissionTo('Create Post')) { abort('401'); } else { return $next($request); } } if ($request->is('posts/*/edit')) //If user is editing a post { if (!Auth::user()->hasPermissionTo('Edit Post')) { abort('401'); } else { return $next($request); } } if ($request->isMethod('Delete')) //If user is deleting a post { if (!Auth::user()->hasPermissionTo('Delete Post')) { abort('401'); } else { return $next($request); } } return $next($request); } } Add AdminMiddleware::class and ClearanceMiddleware::class to the $routeMiddleware property of /app/Http/kernel.php like this: protected $routeMiddleware = [ 'auth' => \Illuminate\Auth\Middleware\Authenticate::class, 'auth.basic' => \Illuminate\Auth\Middleware\AuthenticateWithBasicAuth::class, 'bindings' => \Illuminate\Routing\Middleware\SubstituteBindings::class, 'can' => \Illuminate\Auth\Middleware\Authorize::class, 'guest' => \App\Http\Middleware\RedirectIfAuthenticated::class, 'throttle' => \Illuminate\Routing\Middleware\ThrottleRequests::class, 'isAdmin' => \App\Http\Middleware\AdminMiddleware::class, 'clearance' => \App\Http\Middleware\ClearanceMiddleware::class, ]; In both middelwares a 401 exception would be thrown if the conditions are not meet. Let's create a custom 401 error page: {{-- \resources\views\errors\401.blade.php --}} @extends('layouts.app') @section('content') 401 ACCESS DENIED @endsection Wrapping Up First lets create an 'Admin' user and then create the necessary permissions and roles. Click on Register and create a user, then go to http://localhost:8000/permissions and create permissions to Create Post, Edit Post, Delete Post and Administer roles & permissions. After creating these permissions, your permissions page should look like this: Next, you need to create roles to which you would add the Create, Edit and Delete Permissions. Click on Roles and create these roles: Admin- A user assigned to this role would have all permissions Owner- A user assigned to this role would have selected permissions assigned to it by Admin Finally assign the Role of 'Admin' to the currently logged in User. Click on Users and then Edit. Check the Admin box under Give Role: After assigning the 'Admin' role to our user, notice that you now have a new Admin link in the drop of the navigation, this links to our users page. Now create a new user and give it the more restrictive role of Owner. If you login as this user and try to visit the User, Role or Permission pages you get this as expected: The Owner role does not have permission to Administer Roles & Users hence the exception is thrown. To demonstrate how this works for posts, create a post by clicking on New Article. After creating the post, view the post and you would notice you have along with the Back button, an Edit and Delete button as shown below: Now if you logout and view the post only the Back button will be available to us. This also works if you have a logged in user who does not have permissions to Edit or Delete Post. Conclusion The laravel-permission package makes it relatively easy to build a role and permission system. To recap we have considered installation of the laravel-permission package, laravel-permission blade directives, creating a custom middleware and implementing an access control list in a Laravel application. You can look at the final product on Github and if you have any questions or comments, don’t hesitate to post them below. [Less]
Posted 7 days ago by Scotch.io
What We'll Build When building an application, we often need to set up an access control list (ACL). An ACL specifies the level of permission granted to a user of an application. For example a user John may have the permission to read and write to a ... [More] resource while another user Smith may have the permission only to read the resource. In this tutorial, I will teach you how to add access control to a Laravel app using Laravel-permission package. For this tutorial we will build a simple blog application where users can be assigned different levels of permission. Our user admin page will look like this: Why Use Laravel-Permission The Laravel-Permission package is built on top of Laravel's authorization features introduced in the 5.1.1 release. Although there are other packages that claim to offer similar functionalities, none of them have the same level of activity and maintenance as the laravel-permission package. Development Environment and Installation You can get Laravel up and running by first downloading the installer composer global require "laravel/installer" Then add $HOME/.composer/vendor/bin to your $PATH so the laravel executable can be located by your system. Now you can install the latest stable version of Laravel by running laravel new To install the laravel-permission package run composer require spatie/laravel-permission Next include the package to our list of service providers, in config/app.php add Spatie\Permission\PermissionServiceProvider::class so our file looks like this 'providers' => [ ... Spatie\Permission\PermissionServiceProvider::class, ]; Next publish the migration file for this package with the command php artisan vendor:publish --provider="Spatie\Permission\PermissionServiceProvider" --tag="migrations" Database Setup and Migrations Next create the database and update the .env file to include the database information. For example, for this tutorial the database information section of the .env looks like this: DB_CONNECTION=mysql DB_HOST=127.0.0.1 DB_PORT=3306 DB_DATABASE=acl4 DB_USERNAME=root DB_PASSWORD= To build the tables, run php artisan migrate Please note that in Laravel 5.4 the default character set is changed to utf8mb4, therefore if you are running MariaDB or MYSQL version lower than 5.7.7 you may get this error when trying to run migration files [Illuminate\Database\QueryException] SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes (SQL: alter table users add unique users_email_unique(email)) [PDOException] SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes To fix this error edit the app\Providers\AppServiceProvider.php file, setting the default string length in the boot method use Illuminate\Support\Facades\Schema; public function boot() { Schema::defaultStringLength(191); } After that run the migration again. If it works as normal you would find the following tables in your database: migrations: This keeps track of migration process that have ran users: This holds the users data of the application password_resets: Holds token information when users request a new password permissions: This holds the various permissions needed in the application roles: This holds the roles in our application role_has_permission: This is a pivot table that holds relationship information between the permissions table and the role table user_has_roles: Also a pivot table, holds relationship information between the roles and the users table. user_has_permissions: Also a pivot table, holds relationship information between the users table and the permissions table. Publish the configuration file for this package by running php artisan vendor:publish --provider="Spatie\Permission\PermissionServiceProvider" --tag="config" The config file allows us to set the location of the Eloquent model of the permission and role class. You can also manually set the table names that should be used to retrieve your roles and permissions. Next we need to add the HasRoles trait to the User model: use Illuminate\Foundation\Auth\User as Authenticatable; use Spatie\Permission\Traits\HasRoles; class User extends Authenticatable { use HasRoles; // ... } Laravel Collective HTML Form builder Next install Laravel Collective HTML Form builder as this will be useful further on when we are creating our forms: composer require laravelcollective/html Then add your new provider to the providers array of config/app.php: 'providers' => [ ... Collective\Html\HtmlServiceProvider::class, ]; Finally, add two class aliases to the aliases array of config/app.php: 'aliases' => [ // ... 'Form' => Collective\Html\FormFacade::class, 'Html' => Collective\Html\HtmlFacade::class, // ... ], That's all the installation and configuration needed. A role can be created like a regular Eloquent model, like this: use Spatie\Permission\Models\Role; use Spatie\Permission\Models\Permission; $role = Role::create(['name' => 'writer']); $permission = Permission::create(['name' => 'edit articles']); You can also get the permissions associated to a user like this: $permissions = $user->permissions; And using the pluck method, pluck() you can get the role names associated with a user like this: $roles = $user->roles()->pluck('name'); Other methods available to us include: givePermissionTo(): Allows us to give persmission to a user or role revokePermissionTo(): Revoke permission from a user or role hasPermissionTo(): Check if a user or role has a given permission assignRole(): Assigns role to a user removeRole(): Removes role from a user hasRole(): Checks if a user has a role hasAnyRole(Role::all()): Checks if a user has any of a given list of roles hasAllRoles(Role::all()): Checks if a user has all of a given list of role The methods assignRole, hasRole, hasAnyRole, hasAllRoles and removeRole can accept a string, a Spatie\Permission\Models\Role-object or an \Illuminate\Support\Collection object. The givePermissionTo and revokePermissionTo methods can accept a string or a Spatie\Permission\Models\Permission object. Laravel-Permission also allows to use Blade directives to verify if the logged in user has all or any of a given list of roles: @role('writer') I'm a writer! @else I'm not a writer... @endrole @hasrole('writer') I'm a writer! @else I'm not a writer... @endhasrole @hasanyrole(Role::all()) I have one or more of these roles! @else I have none of these roles... @endhasanyrole @hasallroles(Role::all()) I have all of these roles! @else I don't have all of these roles... @endhasallroles The Blade directives above depends on the users role. Sometimes we need to check directly in our view if a user has a certain permission. You can do that using Laravel's native @can directive: @can('Edit Post') I have permission to edit @endcan Controllers, Authentication and Views You will need a total of four controllers for this application. Let's use resource controllers, as this automatically adds stub methods for us. Our controllers will be called PostController UserController RoleController PermissionController Before working on these controllers let's create our authentication system. With one command Laravel provides a quick way to scaffold all of the routes and views needed for authentication. php artisan make:auth After running this command you would notice two new links for user login and registration in the home page. This command also creates a HomeController (you can delete this as it won't be needed), a resources/views/layouts/app.blade.php file which contains markup that would be shared by all our views and an app/Http/Controllers/Auth directory which contains the controllers for registration and login. Switch into this directory and open the RegisterController.phpfile. Remove the bcrypt function in the create method, so the the method looks like this protected function create(array $data) { return User::create([ 'name' => $data['name'], 'email' => $data['email'], 'password' => $data['password'], ]); } Instead let's define a mutator in app\User.php which would encrypt all our password fields. In app\User.php add this method: public function setPasswordAttribute($password) { $this->attributes['password'] = bcrypt($password); } This would provide the same functionality as before but now you don't need to write the bcrypt function when dealing with the password field in subsequent controllers. Also in the RegisterController.phpfile. Change the $redirectTo property to: protected $redirectTo = '/'; Do the same thing in the LoginController.phpfile. Since the HomeController has been deleted our users are now redirected to the home page which would contain a list of our blog posts. Next let's edit the resources/views/layouts/app.blade.php file to include: an extra drop-down 'Admin' link to view all users and an errors file which checks if our form produced any error. The 'Admin' link would only be viewed by users with the 'Admin' Role. We would also create a custom styles.css which would have extra styling for our resources/views/posts/index.blade.php view. The styling is just a paragraph in the teaser of our index view, the file should be located in public/css/styles.css {{-- resources/views/layouts/app.blade.php --}} {{ config('app.name', 'Laravel') }} Toggle Navigation {{ config('app.name', 'Laravel') }} Home @if (!Auth::guest()) New Article @endif @if (Auth::guest()) Login Register @else {{ Auth::user()->name }} @role('Admin') {{-- Laravel-permission blade helper --}} Admin @endrole Logout @endif @if(Session::has('flash_message')) {!! session('flash_message') !!} @endif @include ('errors.list') {{-- Including error file --}} @yield('content') The error file is: {{-- resources\views\errors\list.blade.php --}} @if (count($errors) > 0) @foreach ($errors->all() as $error) {{ $error }} @endforeach @endif and the styles.css file is simply: p.teaser { text-indent: 30px; } Post Controller First, let's create the migration and model files for the PostController php artisan make:model Post -m This command generates a migration file in app/database/migrations for generating a new MySQL table named posts in our database and a model file Post.phpin the app directory. Let's edit the migration file to include title and body fields of our post. Add a title and body field so the migration file looks like this: increments('id'); $table->string('title'); $table->text('body'); $table->timestamps(); }); } /** * Reverse the migrations. * * @return void */ public function down() { Schema::dropIfExists('posts'); } } After saving the file, run migration again php artisan migrate You can now check the database for the post table and columns. Next make the title and body field of the Post model mass assignable namespace App; use Illuminate\Database\Eloquent\Model; class Post extends Model { protected $fillable = [ 'title', 'body' ]; } Now let's generate our resource controller. php artisan make:controller PostController --resource This will create our controller with all the stub methods needed. Edit this file to look like this middleware(['auth', 'clearance'])->except('index', 'show'); } /** * Display a listing of the resource. * * @return \Illuminate\Http\Response */ public function index() { $posts = Post::orderby('id', 'desc')->paginate(5); //show only 5 items at a time in descending order return view('posts.index', compact('posts')); } /** * Show the form for creating a new resource. * * @return \Illuminate\Http\Response */ public function create() { return view('posts.create'); } /** * Store a newly created resource in storage. * * @param \Illuminate\Http\Request $request * @return \Illuminate\Http\Response */ public function store(Request $request) { //Validating title and body field $this->validate($request, [ 'title'=>'required|max:100', 'body' =>'required', ]); $title = $request['title']; $body = $request['body']; $post = Post::create($request->only('title', 'body')); //Display a successful message upon save return redirect()->route('posts.index') ->with('flash_message', 'Article, '. $post->title.' created'); } /** * Display the specified resource. * * @param int $id * @return \Illuminate\Http\Response */ public function show($id) { $post = Post::findOrFail($id); //Find post of id = $id return view ('posts.show', compact('post')); } /** * Show the form for editing the specified resource. * * @param int $id * @return \Illuminate\Http\Response */ public function edit($id) { $post = Post::findOrFail($id); return view('posts.edit', compact('post')); } /** * Update the specified resource in storage. * * @param \Illuminate\Http\Request $request * @param int $id * @return \Illuminate\Http\Response */ public function update(Request $request, $id) { $this->validate($request, [ 'title'=>'required|max:100', 'body'=>'required', ]); $post = Post::findOrFail($id); $post->title = $request->input('title'); $post->body = $request->input('body'); $post->save(); return redirect()->route('posts.show', $post->id)->with('flash_message', 'Article, '. $post->title.' updated'); } /** * Remove the specified resource from storage. * * @param int $id * @return \Illuminate\Http\Response */ public function destroy($id) { $post = Post::findOrFail($id); $post->delete(); return redirect()->route('posts.index') ->with('flash_message', 'Article successfully deleted'); } } Here the Post class was imported from our model and the Auth class which was generated with the make:auth command earlier. These were imported so that you would be able to make Eloquent queries on the Post table and so as to be able to have access to authentication information of our users. In the constructor two middlewares were called, one is auth which restricts access to the PostController methods to authenticated users the other is a custom middleware is yet to be created. This would be responsible for our Permissions and Roles system. Next, index and show are passed into the except method to allow all users to be able to view posts. The index() method lists all the available posts. It queries the post table for all posts and passes this information to the view. Paginate() allows us to limit the number of posts in a page, in this case five. The create() method simply returns the posts/create view which would contain a form for creating new posts. The store() method saves the information input from the posts/create view. The information is first validated and after it is saved, a flash message is passed to the view posts/index. Our show() method of the PostController allows us to display a single post. This method takes the post id as an argument and passes it to the method Post::find(). The result of the query is then sent to our posts/show view. The edit() method, similar to the create() method simply returns the posts/edit view which would contain a form for creating editing posts. The update() method takes the information from the posts/edit view and updates the record. The destroy() method let's us delete a post. Now that you have the PostController you need to set up the routes. Edit your app/routes/web.php file to look like this: name('home'); Route::resource('users', 'UserController'); Route::resource('roles', 'RoleController'); Route::resource('permissions', 'PermissionController'); Route::resource('posts', 'PostController'); The / route is the route to our home page, here it was renamed to home The Auth route was generated when you ran the make:auth command. It handles authentication related routes. The other four routes are for resources that would be created later. Post Views Only four views are needed for our PostController. Create the files \resources\views\posts\index.blade.php, \resources\views\posts\create.blade.php, \resources\views\posts\show.blade.php, \resources\views\posts\edit.blade.php Edit the index.blade.phpfile to look like this @extends('layouts.app') @section('content') Posts Page {{ $posts->currentPage() }} of {{ $posts->lastPage() }} @foreach ($posts as $post) {{ $post->title }} {{ str_limit($post->body, 100) }} {{-- Limit teaser to 100 characters --}} @endforeach {!! $posts->links() !!} @endsection Notice that this file extends views\layouts\app.php file, which was generated earlier by the make:auth command. The create.blade.php file looks like this @extends('layouts.app') @section('title', '| Create New Post') @section('content') Create New Post {{-- Using the Laravel HTML Form Collective to create our form --}} {{ Form::open(array('route' => 'posts.store')) }} {{ Form::label('title', 'Title') }} {{ Form::text('title', null, array('class' => 'form-control')) }} {{ Form::label('body', 'Post Body') }} {{ Form::textarea('body', null, array('class' => 'form-control')) }} {{ Form::submit('Create Post', array('class' => 'btn btn-success btn-lg btn-block')) }} {{ Form::close() }} @endsection The show view looks like this: @extends('layouts.app') @section('title', '| View Post') @section('content') {{ $post->title }} {{ $post->body }} {!! Form::open(['method' => 'DELETE', 'route' => ['posts.destroy', $post->id] ]) !!} Back @can('Edit Post') Edit @endcan @can('Delete Post') {!! Form::submit('Delete', ['class' => 'btn btn-danger']) !!} @endcan {!! Form::close() !!} @endsection Here the can directive checks if a user has the permission to Edit or Delete Posts, if so the Edit and Delete button will be displayed. If the user does not have these permissions, only the Back button would be displayed. The edit view just displays a edit form that will be used to update records: @extends('layouts.app') @section('title', '| Edit Post') @section('content') Edit Post {{ Form::model($post, array('route' => array('posts.update', $post->id), 'method' => 'PUT')) }} {{ Form::label('title', 'Title') }} {{ Form::text('title', null, array('class' => 'form-control')) }} {{ Form::label('body', 'Post Body') }} {{ Form::textarea('body', null, array('class' => 'form-control')) }} {{ Form::submit('Save', array('class' => 'btn btn-primary')) }} {{ Form::close() }} @endsection If you visit the home page you would see this User Controller The UserController will handle displaying all users, creating of new users, editing users, assigning roles to users and deleting users. As before generate the controller by running php artisan make:controller UserController --resource Then replace the content of this file with: middleware(['auth', 'isAdmin']); //isAdmin middleware lets only users with a //specific permission permission to access these resources } /** * Display a listing of the resource. * * @return \Illuminate\Http\Response */ public function index() { //Get all users and pass it to the view $users = User::all(); return view('users.index')->with('users', $users); } /** * Show the form for creating a new resource. * * @return \Illuminate\Http\Response */ public function create() { //Get all roles and pass it to the view $roles = Role::get(); return view('users.create', ['roles'=>$roles]); } /** * Store a newly created resource in storage. * * @param \Illuminate\Http\Request $request * @return \Illuminate\Http\Response */ public function store(Request $request) { //Validate name, email and password fields $this->validate($request, [ 'name'=>'required|max:120', 'email'=>'required|email|unique:users', 'password'=>'required|min:6|confirmed' ]); $user = User::create($request->only('email', 'name', 'password')); //Retrieving only the email and password data $roles = $request['roles']; //Retrieving the roles field //Checking if a role was selected if (isset($roles)) { foreach ($roles as $role) { $role_r = Role::where('id', '=', $role)->firstOrFail(); $user->assignRole($role_r); //Assigning role to user } } //Redirect to the users.index view and display message return redirect()->route('users.index') ->with('flash_message', 'User successfully added.'); } /** * Display the specified resource. * * @param int $id * @return \Illuminate\Http\Response */ public function show($id) { return redirect('users'); } /** * Show the form for editing the specified resource. * * @param int $id * @return \Illuminate\Http\Response */ public function edit($id) { $user = User::findOrFail($id); //Get user with specified id $roles = Role::get(); //Get all roles return view('users.edit', compact('user', 'roles')); //pass user and roles data to view } /** * Update the specified resource in storage. * * @param \Illuminate\Http\Request $request * @param int $id * @return \Illuminate\Http\Response */ public function update(Request $request, $id) { $user = User::findOrFail($id); //Get role specified by id //Validate name, email and password fields $this->validate($request, [ 'name'=>'required|max:120', 'email'=>'required|email|unique:users,email,'.$id, 'password'=>'required|min:6|confirmed' ]); $input = $request->only(['name', 'email', 'password']); //Retreive the name, email and password fields $roles = $request['roles']; //Retreive all roles $user->fill($input)->save(); if (isset($roles)) { $user->roles()->sync($roles); //If one or more role is selected associate user to roles } else { $user->roles()->detach(); //If no role is selected remove exisiting role associated to a user } return redirect()->route('users.index') ->with('flash_message', 'User successfully edited.'); } /** * Remove the specified resource from storage. * * @param int $id * @return \Illuminate\Http\Response */ public function destroy($id) { //Find a user with a given id and delete $user = User::findOrFail($id); $user->delete(); return redirect()->route('users.index') ->with('flash_message', 'User successfully deleted.'); } } Here the User class, the Role class, the Permission class and the Auth class are imported. In the constructor the auth middleware is called to make sure only authenticated users have access to the User resource. A custom middleware isAdmin is also called. This checks if the authenticated user has administrator privileges. This middleware will be created later. The index() method gets all users from the Users table and passes it to the index view which will display all users in a table. The create() method first gets all the Roles from the Roles table and passes it to the create view. This is so that Roles can be added when creating a User. The store() method saves the input from the create view, after validating the input, looping through the Roles that was passed in the form and assigning these Roles to the User. The show()method just redirects back to the users page as for this demonstration, we wont need to show each user individually. The edit() method gets the user corresponding to the id passed, then gets all roles and passes it to the edit view. The update() method validates data from the edit view and saves the updated name and password fields. It gets all roles from the roles table and while looping through them, removes any role assign to the user. It then takes the role data inputted from the form, matches them with the values in the databases and assigns these roles to the user. The destroy() method allows us to delete a user along with it's corresponding role. User Views Three views are needed here: index, create and edit views. The index view would contain a table that lists all our users and their roles. {{-- \resources\views\users\index.blade.php --}} @extends('layouts.app') @section('title', '| Users') @section('content') User Administration Roles Permissions Name Email Date/Time Added User Roles Operations @foreach ($users as $user) {{ $user->name }} {{ $user->email }} {{ $user->created_at->format('F d, Y h:ia') }} {{ $user->roles()->pluck('name')->implode(' ') }}{{-- Retrieve array of roles associated to a user and convert to string --}} Edit {!! Form::open(['method' => 'DELETE', 'route' => ['users.destroy', $user->id] ]) !!} {!! Form::submit('Delete', ['class' => 'btn btn-danger']) !!} {!! Form::close() !!} @endforeach Add User @endsection The create view is just a form that allows us to create new users and assign roles to them. {{-- \resources\views\users\create.blade.php --}} @extends('layouts.app') @section('title', '| Add User') @section('content') Add User {{ Form::open(array('url' => 'users')) }} {{ Form::label('name', 'Name') }} {{ Form::text('name', '', array('class' => 'form-control')) }} {{ Form::label('email', 'Email') }} {{ Form::email('email', '', array('class' => 'form-control')) }} @foreach ($roles as $role) {{ Form::checkbox('roles[]', $role->id ) }} {{ Form::label($role->name, ucfirst($role->name)) }} @endforeach {{ Form::label('password', 'Password') }} {{ Form::password('password', array('class' => 'form-control')) }} {{ Form::label('password', 'Confirm Password') }} {{ Form::password('password_confirmation', array('class' => 'form-control')) }} {{ Form::submit('Add', array('class' => 'btn btn-primary')) }} {{ Form::close() }} @endsection The edit view is a form that allows us to edit users and their roles. Using Laravel's form model binding the form is automatically populated with the previous values. {{-- \resources\views\users\edit.blade.php --}} @extends('layouts.app') @section('title', '| Edit User') @section('content') Edit {{$user->name}} {{ Form::model($user, array('route' => array('users.update', $user->id), 'method' => 'PUT')) }}{{-- Form model binding to automatically populate our fields with user data --}} {{ Form::label('name', 'Name') }} {{ Form::text('name', null, array('class' => 'form-control')) }} {{ Form::label('email', 'Email') }} {{ Form::email('email', null, array('class' => 'form-control')) }} Give Role @foreach ($roles as $role) {{ Form::checkbox('roles[]', $role->id, $user->roles ) }} {{ Form::label($role->name, ucfirst($role->name)) }} @endforeach {{ Form::label('password', 'Password') }} {{ Form::password('password', array('class' => 'form-control')) }} {{ Form::label('password', 'Confirm Password') }} {{ Form::password('password_confirmation', array('class' => 'form-control')) }} {{ Form::submit('Add', array('class' => 'btn btn-primary')) }} {{ Form::close() }} @endsection Permission Controller Now let's tackle the PermissionControllerCreate the file and paste the following code: middleware(['auth', 'isAdmin']); //isAdmin middleware lets only users with a //specific permission permission to access these resources } /** * Display a listing of the resource. * * @return \Illuminate\Http\Response */ public function index() { $permissions = Permission::all(); //Get all permissions return view('permissions.index')->with('permissions', $permissions); } /** * Show the form for creating a new resource. * * @return \Illuminate\Http\Response */ public function create() { $roles = Role::get(); //Get all roles return view('permissions.create')->with('roles', $roles); } /** * Store a newly created resource in storage. * * @param \Illuminate\Http\Request $request * @return \Illuminate\Http\Response */ public function store(Request $request) { $this->validate($request, [ 'name'=>'required|max:40', ]); $name = $request['name']; $permission = new Permission(); $permission->name = $name; $roles = $request['roles']; $permission->save(); if (!empty($request['roles'])) { //If one or more role is selected foreach ($roles as $role) { $r = Role::where('id', '=', $role)->firstOrFail(); //Match input role to db record $permission = Permission::where('name', '=', $name)->first(); //Match input //permission to db record $r->givePermissionTo($permission); } } return redirect()->route('permissions.index') ->with('flash_message', 'Permission'. $permission->name.' added!'); } /** * Display the specified resource. * * @param int $id * @return \Illuminate\Http\Response */ public function show($id) { return redirect('permissions'); } /** * Show the form for editing the specified resource. * * @param int $id * @return \Illuminate\Http\Response */ public function edit($id) { $permission = Permission::findOrFail($id); return view('permissions.edit', compact('permission')); } /** * Update the specified resource in storage. * * @param \Illuminate\Http\Request $request * @param int $id * @return \Illuminate\Http\Response */ public function update(Request $request, $id) { $permission = Permission::findOrFail($id); $this->validate($request, [ 'name'=>'required|max:40', ]); $input = $request->all(); $permission->fill($input)->save(); return redirect()->route('permissions.index') ->with('flash_message', 'Permission'. $permission->name.' updated!'); } /** * Remove the specified resource from storage. * * @param int $id * @return \Illuminate\Http\Response */ public function destroy($id) { $permission = Permission::findOrFail($id); //Make it impossible to delete this specific permission if ($permission->name == "Administer roles & permissions") { return redirect()->route('permissions.index') ->with('flash_message', 'Cannot delete this Permission!'); } $permission->delete(); return redirect()->route('permissions.index') ->with('flash_message', 'Permission deleted!'); } } In the store() method, we are making it possible for a role to be selected as a permission is created. After validating and saving the permission name field, a check is done if a role was selected if it was, a permission is assigned to the selected role. Permission View Three views are needed here as well. The index view would list in a table all the available permissions, the create view is a form which would be used to create a new permission and the edit view is a form that let's us edit existing permission. {{-- \resources\views\permissions\index.blade.php --}} @extends('layouts.app') @section('title', '| Permissions') @section('content') Available Permissions Users Roles Permissions Operation @foreach ($permissions as $permission) {{ $permission->name }} Edit {!! Form::open(['method' => 'DELETE', 'route' => ['permissions.destroy', $permission->id] ]) !!} {!! Form::submit('Delete', ['class' => 'btn btn-danger']) !!} {!! Form::close() !!} @endforeach Add Permission @endsection The following is the create view {{-- \resources\views\permissions\create.blade.php --}} @extends('layouts.app') @section('title', '| Create Permission') @section('content') Add Permission {{ Form::open(array('url' => 'permissions')) }} {{ Form::label('name', 'Name') }} {{ Form::text('name', '', array('class' => 'form-control')) }} @if(!$roles->isEmpty()) //If no roles exist yet Assign Permission to Roles @foreach ($roles as $role) {{ Form::checkbox('roles[]', $role->id ) }} {{ Form::label($role->name, ucfirst($role->name)) }} @endforeach @endif {{ Form::submit('Add', array('class' => 'btn btn-primary')) }} {{ Form::close() }} @endsection And finally the edit view: @extends('layouts.app') @section('title', '| Edit Permission') @section('content') Edit {{$permission->name}} {{ Form::model($permission, array('route' => array('permissions.update', $permission->id), 'method' => 'PUT')) }}{{-- Form model binding to automatically populate our fields with permission data --}} {{ Form::label('name', 'Permission Name') }} {{ Form::text('name', null, array('class' => 'form-control')) }} {{ Form::submit('Edit', array('class' => 'btn btn-primary')) }} {{ Form::close() }} @endsection Role Controller The RoleController is quite similar to the UserController. This controller will allow us to create roles and assign one or more permissions to a role. Create the file and paste the following code: middleware(['auth', 'isAdmin']);//isAdmin middleware lets only users with a //specific permission permission to access these resources } /** * Display a listing of the resource. * * @return \Illuminate\Http\Response */ public function index() { $roles = Role::all();//Get all roles return view('roles.index')->with('roles', $roles); } /** * Show the form for creating a new resource. * * @return \Illuminate\Http\Response */ public function create() { $permissions = Permission::all();//Get all permissions return view('roles.create', ['permissions'=>$permissions]); } /** * Store a newly created resource in storage. * * @param \Illuminate\Http\Request $request * @return \Illuminate\Http\Response */ public function store(Request $request) { //Validate name and permissions field $this->validate($request, [ 'name'=>'required|unique:roles|max:10', 'permissions' =>'required', ] ); $name = $request['name']; $role = new Role(); $role->name = $name; $permissions = $request['permissions']; $role->save(); //Looping thru selected permissions foreach ($permissions as $permission) { $p = Permission::where('id', '=', $permission)->firstOrFail(); //Fetch the newly created role and assign permission $role = Role::where('name', '=', $name)->first(); $role->givePermissionTo($p); } return redirect()->route('roles.index') ->with('flash_message', 'Role'. $role->name.' added!'); } /** * Display the specified resource. * * @param int $id * @return \Illuminate\Http\Response */ public function show($id) { return redirect('roles'); } /** * Show the form for editing the specified resource. * * @param int $id * @return \Illuminate\Http\Response */ public function edit($id) { $role = Role::findOrFail($id); $permissions = Permission::all(); return view('roles.edit', compact('role', 'permissions')); } /** * Update the specified resource in storage. * * @param \Illuminate\Http\Request $request * @param int $id * @return \Illuminate\Http\Response */ public function update(Request $request, $id) { $role = Role::findOrFail($id);//Get role with the given id //Validate name and permission fields $this->validate($request, [ 'name'=>'required|max:10|unique:roles,name,'.$id, 'permissions' =>'required', ]); $input = $request->except(['permissions']); $permissions = $request['permissions']; $role->fill($input)->save(); $p_all = Permission::all();//Get all permissions foreach ($p_all as $p) { $role->revokePermissionTo($p); //Remove all permissions associated with role } foreach ($permissions as $permission) { $p = Permission::where('id', '=', $permission)->firstOrFail(); //Get corresponding form //permission in db $role->givePermissionTo($p); //Assign permission to role } return redirect()->route('roles.index') ->with('flash_message', 'Role'. $role->name.' updated!'); } /** * Remove the specified resource from storage. * * @param int $id * @return \Illuminate\Http\Response */ public function destroy($id) { $role = Role::findOrFail($id); $role->delete(); return redirect()->route('roles.index') ->with('flash_message', 'Role deleted!'); } } Roles View Three views are needed here as well. The index view to display available roles and associated permissions, the create view to add a new role and a view to edit an existing role. Create the index.blade.php file and paste the following: {{-- \resources\views\roles\index.blade.php --}} @extends('layouts.app') @section('title', '| Roles') @section('content') Roles Users Permissions Role Permissions Operation @foreach ($roles as $role) {{ $role->name }} {{ str_replace(array('[',']','"'),'', $role->permissions()->pluck('name')) }}{{-- Retrieve array of permissions associated to a role and convert to string --}} Edit {!! Form::open(['method' => 'DELETE', 'route' => ['roles.destroy', $role->id] ]) !!} {!! Form::submit('Delete', ['class' => 'btn btn-danger']) !!} {!! Form::close() !!} @endforeach Add Role @endsection For the create view: @extends('layouts.app') @section('title', '| Add Role') @section('content') Add Role {{ Form::open(array('url' => 'roles')) }} {{ Form::label('name', 'Name') }} {{ Form::text('name', null, array('class' => 'form-control')) }} Assign Permissions @foreach ($permissions as $permission) {{ Form::checkbox('permissions[]', $permission->id ) }} {{ Form::label($permission->name, ucfirst($permission->name)) }} @endforeach {{ Form::submit('Add', array('class' => 'btn btn-primary')) }} {{ Form::close() }} @endsection And for the edit view: @extends('layouts.app') @section('title', '| Edit Role') @section('content') Edit Role: {{$role->name}} {{ Form::model($role, array('route' => array('roles.update', $role->id), 'method' => 'PUT')) }} {{ Form::label('name', 'Role Name') }} {{ Form::text('name', null, array('class' => 'form-control')) }} Assign Permissions @foreach ($permissions as $permission) {{Form::checkbox('permissions[]', $permission->id, $role->permissions ) }} {{Form::label($permission->name, ucfirst($permission->name)) }} @endforeach {{ Form::submit('Edit', array('class' => 'btn btn-primary')) }} {{ Form::close() }} @endsection Middleware To restrict access to the roles and permissions page, a middleware was included called isAdmin in our PermissionController and RoleController. This middleware counts how many users are in the Users table, and if there are more than one users, it checks if the current authenticated User has the permission to 'Administer roles & permissions'. To create a permission visit http://localhost:8000/permissions/create. Then go to http://localhost:8000/roles/create to create a role, to which you can now assign the permission you created. For example you can create a permission called 'Administer roles & permissions' and a 'Admin' role to which you would assign this permission. Create the AdminMiddleware in the directory app/Http/Middleware/ and enter the following code: count(); if (!($user == 1)) { if (!Auth::user()->hasPermissionTo('Administer roles & permissions')) //If user does //not have this permission { abort('401'); } } return $next($request); } } A middleware called clearance was also included in our PostController. This middleware would check if a user has the permissions Administer roles & permissions, Create Post, Edit Post and Delete Post. hasPermissionTo('Administer roles & permissions')) //If user has this //permission { return $next($request); } if ($request->is('posts/create'))//If user is creating a post { if (!Auth::user()->hasPermissionTo('Create Post')) { abort('401'); } else { return $next($request); } } if ($request->is('posts/*/edit')) //If user is editing a post { if (!Auth::user()->hasPermissionTo('Edit Post')) { abort('401'); } else { return $next($request); } } if ($request->isMethod('Delete')) //If user is deleting a post { if (!Auth::user()->hasPermissionTo('Delete Post')) { abort('401'); } else { return $next($request); } } return $next($request); } } Add AdminMiddleware::class and ClearanceMiddleware::class to the $routeMiddleware property of /app/Http/kernel.php like this: protected $routeMiddleware = [ 'auth' => \Illuminate\Auth\Middleware\Authenticate::class, 'auth.basic' => \Illuminate\Auth\Middleware\AuthenticateWithBasicAuth::class, 'bindings' => \Illuminate\Routing\Middleware\SubstituteBindings::class, 'can' => \Illuminate\Auth\Middleware\Authorize::class, 'guest' => \App\Http\Middleware\RedirectIfAuthenticated::class, 'throttle' => \Illuminate\Routing\Middleware\ThrottleRequests::class, 'isAdmin' => \App\Http\Middleware\AdminMiddleware::class, 'clearance' => \App\Http\Middleware\ClearanceMiddleware::class, ]; In both middelwares a 401 exception would be thrown if the conditions are not meet. Let's create a custom 401 error page: {{-- \resources\views\errors\401.blade.php --}} @extends('layouts.app') @section('content') 401 ACCESS DENIED @endsection Wrapping Up First lets create an 'Admin' user and then create the necessary permissions and roles. Click on Register and create a user, then go to http://localhost:8000/permissions and create permissions to Create Post, Edit Post, Delete Post and Administer roles & permissions. After creating these permissions, your permissions page should look like this: Next, you need to create roles to which you would add the Create, Edit and Delete Permissions. Click on Roles and create these roles: Admin- A user assigned to this role would have all permissions Owner- A user assigned to this role would have selected permissions assigned to it by Admin Finally assign the Role of 'Admin' to the currently logged in User. Click on Users and then Edit. Check the Admin box under Give Role: After assigning the 'Admin' role to our user, notice that you now have a new Admin link in the drop of the navigation, this links to our users page. Now create a new user and give it the more restrictive role of Owner. If you login as this user and try to visit the User, Role or Permission pages you get this as expected: The Owner role does not have permission to Administer Roles & Users hence the exception is thrown. To demonstrate how this works for posts, create a post by clicking on New Article. After creating the post, view the post and you would notice you have along with the Back button, an Edit and Delete button as shown below: Now if you logout and view the post only the Back button will be available to us. This also works if you have a logged in user who does not have permissions to Edit or Delete Post. Conclusion The laravel-permission package makes it relatively easy to build a role and permission system. To recap we have considered installation of the laravel-permission package, laravel-permission blade directives, creating a custom middleware and implementing an access control list in a Laravel application. You can look at the final product on Github and if you have any questions or comments, don’t hesitate to post them below. [Less]
Posted 8 days ago by TwinDB
Usually application do not connect directly to Percona XtraDB Cluster, but go through a proxy – ProxySQL, for instance. However if only one proxy node is used it becomes a single point of failure. Not long ago Marco Tusa wrote about how to ... [More] configure two ProxySQL nodes in front of XtraDB cluster. If deployed on EC2 instances it doesn’t work that way because Amazon doesn’t allow to assign secondary IP address on an interface. This post describes how to configure highly available ProxySQL with keepalived, proxysql-tools and AWS Elastic Network Interface(ENI). The application connects to a single Virtual IP. The VIP is assigned to ENI which is managed by keepalived. proxysql-tools moves ENI between ProxySQL instances and monitor health of XtraDB Cluster nodes. Setup Percona XtraDB Cluster Node #1: 172.31.8.51 Node #2: 172.31.12.128 Node #3: 172.31.3.159 Setting up XtraDB Cluster is pretty straightforward and not different from any other case. # wget https://repo.percona.com/apt/percona-release_0.1-4.$(lsb_release -sc)_all.deb # sudo dpkg -i percona-release_0.1-4.$(lsb_release -sc)_all.deb # sudo apt-get update # sudo apt-get install percona-xtradb-cluster-57 # sudo service mysql stop MySQL configuration file on other nodes differs only in wsrep_node_address. # cat /etc/mysql/my.cnf !includedir /etc/mysql/conf.d/ [mysqld] datadir=/var/lib/mysql user=mysql wsrep_provider=/usr/lib/libgalera_smm.so wsrep_cluster_address=gcomm://172.31.8.51,172.31.12.128,172.31.3.159 binlog_format=ROW default_storage_engine=InnoDB innodb_autoinc_lock_mode=2 wsrep_node_address=172.31.8.51 wsrep_sst_method=xtrabackup-v2 wsrep_cluster_name=cluster_1 wsrep_sst_auth="sstuser:s3cretPass"   After this step, We can bootstrap our cluster. I have made in from first node by next command: # /etc/init.d/mysql bootstrap-pxc When the first node has been started, cluster status can be checked by: mysql> show status like 'wsrep%'; +----------------------------+--------------------------------------+ | Variable_name | Value | +----------------------------+--------------------------------------+ | wsrep_local_state_uuid | cc00ee27-3433-11e7-84e5-4a9beaabe9c9 | ... | wsrep_local_state | 4 | | wsrep_local_state_comment | Synced | ... | wsrep_cluster_size | 1 | | wsrep_cluster_status | Primary | | wsrep_connected | ON | ... | wsrep_ready | ON | +----------------------------+--------------------------------------+ When all nodes are running, wsrep_cluster_size will be equal to number of clusters. Cluster setup is complete at this step. Setup ProxySQL and KeepAlived ProxySQL active: 172.31.24.212 ProxySQL passive: 172.31.19.155 ENI address: 172.31.26.237 It’s worth noting ProxySQL instances and ENI must be on the same subnet. First, We must to configure our ProxySQL instances for proxy request to cluster nodes. # apt install proxysql # apt-get install percona-xtradb-cluster-client-5.7 Now, we’ll setup ProxySQL for work with our Galera nodes. I will use default hostgroup. # service proxysql start # mysql -u admin -p -h 127.0.0.1 -P 6032 mysql@proxysql1> INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (0,'172.31.8.51',3306); mysql@proxysql1> INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (0,'172.31.12.128',3306); mysql@proxysql1> INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (0,'172.31.3.159',3306); Now, we must create user for monitoring Percona XtraDB Cluster nodes in ProxySQL. You can do it on any node of cluster. mysql@node1> CREATE USER 'monitor'@'%' IDENTIFIED BY '*****'; mysql@node1> GRANT USAGE ON *.* TO 'monitor'@'%'; And update settings in proxysql: mysql@proxysql1> UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username'; mysql@proxysql1> UPDATE global_variables SET variable_value='*****' WHERE variable_name='mysql-monitor_password'; mysql@proxysql1> LOAD MYSQL VARIABLES TO RUNTIME; mysql@proxysql1> SAVE MYSQL VARIABLES TO DISK; After this changes, let’s try to see monitoring and ping logs: mysql@proxysql1 SELECT * FROM monitor.mysql_server_ping_log DESC LIMIT 6; +---------------+------+------------------+----------------------+------------+ | hostname | port | time_start_us | ping_success_time_us | ping_error | +---------------+------+------------------+----------------------+------------+ | 172.31.12.128 | 3306 | 1494492166885382 | 627 | NULL | | 172.31.3.159 | 3306 | 1494492166887154 | 586 | NULL | | 172.31.8.51 | 3306 | 1494492166888947 | 501 | NULL | | 172.31.12.128 | 3306 | 1494492176885541 | 596 | NULL | | 172.31.3.159 | 3306 | 1494492176887442 | 599 | NULL | | 172.31.8.51 | 3306 | 1494492176889317 | 527 | NULL | +---------------+------+------------------+----------------------+------------+ 6 rows in set (0.00 sec) The previous examples show that ProxySQL is able to connect and ping the nodes you added. To enable monitoring of these nodes, load them at runtime: mysql@proxysql1> LOAD MYSQL SERVERS TO RUNTIME; Now, we must to create user that will work with our XtraDB Cluster nodes. To add a user, insert credentials into mysql_users table: mysql@proxysql1> INSERT INTO mysql_users (username,password) VALUES ('proxy_user','*****'); Query OK, 1 row affected (0.00 sec) mysql@proxysql1> LOAD MYSQL USERS TO RUNTIME; To provide read/write access to the cluster for ProxySQL, add this user on one of the Percona XtraDB Cluster nodes: mysql@node2> CREATE USER 'proxy_user'@'%' IDENTIFIED BY '*****'; Query OK, 0 rows affected (0.01 sec) mysql@node2> GRANT ALL ON *.* TO 'proxy_user'@'%'; Query OK, 0 rows affected (0.00 sec) proxysql-tools can monitor health of XtraDB Cluster nodes and take out of rotation failed nodes. Install it to your proxysql instances using pip install proxysql-tools  and add it into ProxySQL scheduler: mysql@proxysql1> INSERT INTO scheduler (id,interval_ms,filename,arg1,arg2) VALUES (1, '15000', '/usr/local/bin/proxysql_tools', 'galera', 'register'); mysql@proxysql1> LOAD SCHEDULER TO RUNTIME; To make sure that the script has been loaded, check the runtime_scheduler table: mysql@proxysql1> SELECT * FROM runtime_schedulerG *************************** 1. row *************************** id: 1 active: 1 interval_ms: 15000 filename: /usr/local/bin/proxysql_tools arg1: galera arg2: register arg3: NULL arg4: NULL arg5: NULL comment: 1 row in set (0.00 sec) Repeat steps above for another ProxySQL instance. Now, ProxySQL instances are configured. But we haven’t added high availability yet. The idea is following. keepalived on each of ProxySQL nodes will monitor each other. When one of the nodes becomes active we will attach ENI to the active node and configure VIP on it. The application will use the VIP to connect to MySQL. No configuration changes are needed when the active ProxySQL node changes. So, install KeepAlived on ProxySQL instances and create the network interface in AWS that will be in same subnet with ProxySQL instances. proxysql-tools attaches network interface to an active node. Let’s configure it: Create config file /etc/twindb/proxysql-tools.cfg and add content below: # cat /etc/twindb/proxysql-tools.cfg [proxysql] # ProxySQL admin interface connectivity information admin_host=127.0.0.1 admin_port=6032 admin_username=admin admin_password=*** # MySQL user used by ProxySQL monitoring module to monitor MySQL servers monitor_username=monitor monitor_password=*** # Virtual IP for HA configuration virtual_ip=172.31.26.237 virtual_netmask=255.255.240.0 [aws] aws_access_key_id=*** aws_secret_access_key=*** aws_default_region=*** Virtual IP is address of your ENI. After this, we must configure KeepAlived. # cat /etc/keepalived/keepalived.conf global_defs { notification_email { root@localhost } notification_email_from keepalived@localhost smtp_server localhost smtp_connect_timeout 30 } vrrp_script chk_proxysql { script "/usr/bin/mysqladmin ping -h 127.0.0.1 -P 3306 > /dev/null 2>&1" interval 1 timeout 1 fall 3 rise 3 user root } vrrp_instance proxysql_instance { notify_master "/usr/local/bin/proxysql-tool aws notify_master" virtual_router_id 41 state BACKUP interface eth0 dont_track_primary unicast_peer { 172.31.19.155 } priority 200 authentication { auth_type PASS auth_pass 1066 } track_script { chk_proxysql } nopreempt debug unicast_peer is the IP on eth0 of other ProxySQL node proxysql-tool with arguments aws notify_master attach ENI Virtual IP to the instance. On passive ProxySQL, config is same except unicast_peer: # cat /etc/keepalived/keepalived.conf global_defs { notification_email { root@localhost } notification_email_from keepalived@localhost smtp_server localhost smtp_connect_timeout 30 } vrrp_script chk_proxysql { script "/usr/bin/mysqladmin ping -h 127.0.0.1 -P 3306 > /dev/null 2>&1" interval 1 timeout 1 fall 3 rise 3 user root } vrrp_instance proxysql_instance { notify_master "/usr/local/bin/proxysql-tool aws notify_master" virtual_router_id 41 state BACKUP interface eth0 dont_track_primary unicast_peer { 172.31.19.212 } priority 200 authentication { auth_type PASS auth_pass 1066 } track_script { chk_proxysql } nopreempt debug And now you can start keepalived as service. One of the ProxySQL nodes will become active, /usr/local/bin/proxysql-tool aws notify_master will move the ENI to the active node and assign the VIP to it. The post Setup high availability for ProxySQL via KeepAlived in AWS appeared first on Backup and Data Recovery for MySQL. [Less]
Posted 8 days ago by jocelyn fournier
I) An upgrade which hasn’t gone as planned One of our customer needed help after trying to upgrade their slave from Percona Server 5.6 to Percona Server 5.7. (here 5.7.17 is used) Indeed, from time to time, the replication lag was linearly growing ... [More] , which could be quite annoying… (good guest, 5.7 is the red dashed line!) Their use case is quite interesting : a lot of Queries Per Second (read & write, nearly 10k queries/s on this slave) a lot of tables (about 600k spread across different databases) a lot of writes 24 CPUs, and 386G of RAM A big buffer pool (270G) not that fast SSD disks. Because we have a high QPS but also a lot of writes, we need to fine tune the innodb settings. The idea is to make sure to not flush too fast on disk the dirty pages in the innodb buffer pool, and to have enough room in the redo log. The most important settings to maintain a good balance are: innodb_io_capacity=2000 innodb_io_capacity_max=12000 innodb_log_file_size=32G Other parameters like the innodb_buffer_pool_instances, innodb_flush_neighbors, innodb_lru_scan_depth, innodb_purge_threads or innodb_page_cleaners… have been fine tuned as well, but it doesn’t impact much the flushing speed of the dirty pages. In practical, with those settings, the server has about 24% of dirty pages, and 20% of redo log used in average under a “normal” usage. It seems to be not bad, so why the server is sometimes so slow? II) Let’s investigate! a) The LRU manager I have first investigated if the innodb buffer pool flush was working properly. Although it was not the main cause of the performance regression, I noticed a new threaded LRU (Least Recently Used) manager has been introduced in XtraDB 5.7. Basically, for each buffer pool instance, a list of dirty pages is stored in an LRU queue. The job of this LRU manager is to flush a few dirty pages, when there are not enough free pages available in the buffer pool. However, the mecanism to wake up those threads is not optimal: it’s based on a timer which will wake up the thread every 1s + an extra time which is auto-ajusted depending on the size of the buffer pool free list and what happened during the last thread execution. With this implementation, XtraDB is spending a lot of CPU cycle sleeping (thru the use of the os_thread_sleep function). A better implementation would be perhaps to use an event wait which would trigger a thread wake up once the free list reaches some defined threshold. As a result, I opened a bug report on percona server to report this issue: https://bugs.launchpad.net/percona-server/+bug/1690399 For the record, Mark Callaghan already opened a few years ago a bug report on MySQL asking to try to remove the os_thread_sleep calls : https://bugs.mysql.com/bug.php?id=68588 I’ve just noticed XtraDB 5.7.18 included a few improvement in the LRU Manager, we need to test if it improves things: https://bugs.launchpad.net/percona-server/+bug/1631309   b) ALTER TABLE are slow! Well actually, it’s not always the case, that’s why it was tricky to track it down.It’s worth noting this ALTER TABLE issue in an upstream bug, from MySQL 5.7 (thanks Marko Mäkelä for noticing it). So this one is affecting Percona Server 5.7, MySQL 5.7 and MariaDB 10.2. The workload of this customer can create from time to time a few new tables, fill them, and use an ALTER TABLE to add new indexes. In the introduction, I mentioned the server has been fine tuned to avoid flushing too much the buffer pool. It means the amount of dirty pages is potentially high, depending on the size of the buffer pool. In our case, with 24% of dirty pages, it represents about 4.2m of dirty pages. The issue in 5.7 is that when we rebuild an index, the function FlushObserver:flush is called to flush the dirty pages associated with the modified table. Before doing the flush, it tries to count the number of dirty pages to estimate the amount of time the operation could take. Unfortunately, the implementation of this count is really inefficient : it iterates on all the buffer pool instances, and for each buffer pool, on all the dirty pages, to try to find those one corresponding to the modified table. With 4.2m of dirty pages, it means it iterates on 4.2m dirty pages (even if the table is empty!). Here are the performance results: I created another bug report for this specific issue : https://bugs.launchpad.net/percona-server/+bug/1690588 Let’s hope they will fix those issues quickly! The post Performance regression between Percona Server 5.6 & 5.7 appeared first on Softizy Blog. [Less]
Posted 10 days ago by MySQL Performance Blog
Percona announces the GA release of Percona Server for MySQL 5.7.18-14 on May 12, 2017. Download the latest version from the Percona web site or the Percona Software Repositories. You can also run Docker containers from the images in the Docker Hub ... [More] repository. Based on MySQL 5.7.18, including all the bug fixes in it, Percona Server for MySQL 5.7.18-14 is the current GA release in the Percona Server for MySQL 5.7 series. Percona’s provides completely open-source and free software. Find release details in the 5.7.18-14 milestone at Launchpad. New Features: Percona Server 5.7 packages are now available for Ubuntu 17.04 (Zesty Zapus). Percona Server now supports Prefix Index Queries Optimization. Percona Server has implemented support for Gap locks detection from a Facebook MySQL patch. tokudb_dir_cmd variable can now be used to edit the TokuDB files. Bugs Fixed: Deadlock could occur in I/O-bound workloads when server was using several small buffer pool instances in combination with small redo log files and variable innodb_empty_free_list_algorithm set to backoff algorithm. Bug fixed #1651657. Fixed a memory leak in Percona TokuBackup. Bug fixed #1669005. Compressed columns with dictionaries could not be added to a partitioned table by using ALTER TABLE. Bug fixed #1671492. Fixed a memory leak that happened in case of failure to create a multi-threaded slave worker thread. Bug fixed #1675716. In-place upgrade from Percona Server 5.6 to 5.7 by using standalone packages would fail if /var/lib/mysql wasn’t defined as the datadir. Bug fixed #1687276. Combination of using any audit API-using plugin, like Audit Log Plugin and Response Time Distribution, with multi-byte collation connection and PREPARE statement with a parse error could lead to a server crash. Bug fixed #1688698 (upstream #86209). Fix for a #1433432 bug caused a performance regression due to suboptimal LRU manager thread flushing heuristics. Bug fixed #1631309. Creating Compressed columns with dictionaries in MyISAM tables by specifying partition engines would not result in error. Bug fixed #1631954. It was not possible to configure basedir as a symlink. Bug fixed #1639735. Replication slave did not report Seconds_Behind_Master correctly when running in multi-threaded slave mode. Bug fixed #1654091 (upstream #84415). DROP TEMPORARY TABLE would create a transaction in binary log on a read-only server. Bug fixed #1668602 (upstream #85258). Processing GTIDs in the relay log that were already been executed were causing write/fsync amplification. Bug fixed #1669928 (upstream #85141). Text/BLOB fields were not handling sorting of the empty string consistently between InnoDB and filesort. Bug fixed #1674867 (upstream #81810) by porting a Facebook patch for MySQL. InnoDB adaptive hash index was using a partitioning algorithm which would produce uneven distribution when the server contained many tables with an identical schema. Bug fixed #1679155 (upstream #81814). For plugin variables that are signed numbers, doing a SHOW VARIABLES would always show an unsigned number. Fixed by porting a Facebook patch for MySQL. Other bugs fixed: #1629250 (upstream #83245), #1660828 (upstream #84786), #1664519 (upstream #84940), #1674299, #1670588 (upstream #84173), #1672389, #1674507, #1675623, #1650294, #1659224, #1662908, #1669002, #1671473, #1673800, #1674284, #1676441, #1676705, #1676847 (upstream #85671), #1677130 (upstream #85678), #1677162, #1677943, #1678692, #1680510 (upstream #85838), #1683993, #1684012, #1684078, #1684264, #1687386, #1687432, #1687600, and #1674281. The release notes for Percona Server for MySQL 5.7.18-14 are available in the online documentation. Please report any bugs on the launchpad bug tracker. [Less]
Posted 10 days ago by MySQL Performance Blog
Percona announces the release of Percona Server for MySQL 5.6.36-82.0 on May 12, 2017. Download the latest version from the Percona web site or the Percona Software Repositories. You can also run Docker containers from the images in the Docker Hub ... [More] repository. Based on MySQL 5.6.36, and including all the bug fixes in it, Percona Server for MySQL 5.6.36-82.0 is the current GA release in the Percona Server for MySQL 5.6 series. Percona Server for MySQL is open-source and free – this is the latest release of our enhanced, drop-in replacement for MySQL. Complete details of this release are available in the 5.6.36-82.0 milestone on Launchpad. New Features: Percona Server for MySQL 5.6 packages are now available for Ubuntu 17.04 (Zesty Zapus). Percona Server for MySQL now supports Prefix Index Queries Optimization. tokudb_dir_cmd variable can now be used to edit the TokuDB files. Bugs Fixed: Deadlock could occur in I/O-bound workloads when server was using several small buffer pool instances in combination with small redo log files and variable innodb_empty_free_list_algorithm set to backoff algorithm. Bug fixed #1651657. Querying TABLE_STATISTICS in combination with a stored function could lead to a server crash. Bug fixed #1659992. tokubackup_slave_info file was created for a master server after taking the backup with Percona TokuBackup. Bug fixed #135. Fixed a memory leak in Percona TokuBackup. Bug fixed #1669005. Compressed columns with dictionaries could not be added to a partitioned table by using ALTER TABLE. Bug fixed #1671492. Fixed a memory leak that happened in case of failure to create a multi-threaded slave worker thread. Bug fixed #1675716. The combination of using any audit API-using plugin, like Audit Log Plugin and Response Time Distribution, with multi-byte collation connection and PREPARE statement with a parse error could lead to a server crash. Bug fixed #1688698 (upstream #86209). Fix for a #1433432 bug in Percona Server 5.6.28-76.1 caused a performance regression due to suboptimal LRU manager thread flushing heuristics. Bug fixed #1631309. Creating Compressed columns with dictionaries in MyISAM tables by specifying partition engines would not result in error. Bug fixed #1631954. It was not possible to configure basedir as a symlink. Bug fixed #1639735. Replication slave did not report Seconds_Behind_Master correctly when running in multi-threaded slave mode. Bug fixed #1654091 (upstream #84415). DROP TEMPORARY TABLE would create a transaction in binary log on a read-only server. Bug fixed #1668602 (upstream #85258). Creating a compression dictionary with innodb_fake_changes enabled could lead to a server crash. Bug fixed #1629257. Other bugs fixed: #1660828 (upstream #84786), #1664519 (upstream #84940), #1674299, #1683456, #1670588 (upstream #84173), #1672389, #1674507, #1674867, #1675623, #1650294, #1659224, #1660565, #1662908, #1669002, #1671473, #1673800, #1674284, #1676441, #1676705, #1676847 (upstream #85671), #1677130 (upstream #85678), #1677162, #1678692, #1678792, #1680510 (upstream #85838), #1683993, #1684012, #1684078, #1684264, and #1674281. Release notes for Percona Server for MySQL 5.6.36-82.0 are available in the online documentation. Please report any bugs on the launchpad bug tracker. [Less]