PHP and RBAC design ideas, data table design and source code

Small knowledge, big challenge! This article is participating in the creation activity of “Essential Tips for Programmers”.

This article has participated in the “Digitalstar Project” and won a creative gift package to challenge the creative incentive money.

The permission system module is a very important function for Internet products, which can control different roles to access different resources reasonably so as to achieve safe access

What models are available for permission control

ACL RBAC Indicates role-based access control

Acls are directly related to users and permissions, while RBAC is indirectly related to users and permissions through roles. So we noticed that roles are an important attribute of the RBAC system.

What is the RBAC model

Role-based Access Control (RBAC) is used to associate users with rights Based on roles. Simply put, a user has several roles, and each role has several permissions. In this way, a user – role – permission authorization model is constructed. In this model, the relationship between users and roles, roles and permissions is generally many-to-many.

Why the RBAC model

Here’s why:

Convenient user grouping

Facilitate permission assignment and reclamation

The expansion is convenient and can meet most business requirements

Before we talk about permission management, we should know that permission management has functions.

The five attributes of RBAC model are as follows:

1 User Attributes (Zhang SAN, Li Si, Wang Wu)

2 Role Attributes (Sales manager, salesperson, receptionist)

3 Relationship between users and roles (Zhang SAN is sales manager, Li Si and Wang Wu are sales)

4 Permissions (Add a customer, edit a customer, delete a customer, view a customer)

5 Relationship between Rights and Roles (The sales manager can view, add, delete, and edit customers, while the sales manager can view, add, delete, and edit customers)

An RBAC permission module must realize three functions

User management

List of users

Add user

Edit the user

Setting user Roles

Role management

The role list

Adding roles

Editing the role

Setting Role Rights

Rights management

Permissions list

Additional permissions

Edit permissions

Data table design

The users table

CREATE TABLE `user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT, 
  `name` varchar(20) NOT NULL DEFAULT ' ' COMMENT 'name'.`email` varchar(30) NOT NULL DEFAULT ' ' COMMENT 'email'.`is_admin` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Is it super administrator? 1: Yes, 0: No'.`status` tinyint(1) NOT NULL DEFAULT '1' COMMENT 'Status 1: valid 0: invalid'.`updated_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Last Update Time'.`created_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Insertion time',
  PRIMARY KEY (`id`),
  KEY `idx_email` (`email`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='User table';
Copy the code

Character sheet

CREATE TABLE `role` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL DEFAULT ' ' COMMENT 'Role Name'.`status` tinyint(1) NOT NULL DEFAULT '1' COMMENT 'Status 1: valid 0: invalid'.`updated_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Last Update Time'.`created_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Insertion time',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Role table';
Copy the code

User role table

CREATE TABLE `user_role` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `uid` int(11) NOT NULL DEFAULT '0' COMMENT 'user id'.`role_id` int(11) NOT NULL DEFAULT '0' COMMENT 'character ID'.`created_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Insertion time',
  PRIMARY KEY (`id`),
  KEY `idx_uid` (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='User Role Table';
Copy the code

Permission Details Table

CREATE TABLE `access` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(50) NOT NULL DEFAULT ' ' COMMENT 'Permission Name'.`urls` varchar(1000) NOT NULL DEFAULT ' ' COMMENT 'the json array'.`status` tinyint(1) NOT NULL DEFAULT '1' COMMENT 'Status 1: valid 0: invalid'.`updated_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Last Update Time'.`created_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Insertion time',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Permission Details Table';
Copy the code

Role permission table

CREATE TABLE `role_access` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `role_id` int(11) NOT NULL DEFAULT '0' COMMENT 'character id'.`access_id` int(11) NOT NULL DEFAULT '0' COMMENT 'authorization id'.`created_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Insertion time',
  PRIMARY KEY (`id`),
  KEY `idx_role_id` (`role_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Role Permission Table';
Copy the code

User operation record form

CREATE TABLE `app_access_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uid` bigint(20) NOT NULL DEFAULT '0' COMMENT 'brand UID'.`target_url` varchar(255) NOT NULL DEFAULT ' ' COMMENT 'Url accessed'.`query_params` longtext NOT NULL COMMENT 'GET and POST parameters'.`ua` varchar(255) NOT NULL DEFAULT ' ' COMMENT 'access ua'.`ip` varchar(32) NOT NULL DEFAULT ' ' COMMENT 'access IP'.`note` varchar(1000) NOT NULL DEFAULT ' ' COMMENT 'JSON format remarks field'.`created_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_uid` (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='User Operation Record';
Copy the code

Code implementation

All pages of this system are required to log in to access, in the framework to join the unified verification method

public function beforeAction($action) {
	$login_status = $this->checkLoginStatus();
	if(!$login_status && !in_array( $action->uniqueId,$this->allowAllAction )  ) {
		if(Yii::$app->request->isAjax){
			$this->renderJSON([],"Not logged in, please return to user center", -302);
		}else{
			$this->redirect( UrlService::buildUrl("/user/login"));// Return to the login page
		}
		return false;
	}
	// Save all accesses to the database
	$get_params = $this->get( null );
	$post_params = $this->post( null );
	$model_log = new AppAccessLog();
	$model_log->uid = $this->current_user?$this->current_user['id'] :0;
	$model_log->target_url = isset( $_SERVER['REQUEST_URI'])?$_SERVER['REQUEST_URI'] :' ';
	$model_log->query_params = json_encode( array_merge( $post_params.$get_params));$model_log->ua = isset( $_SERVER['HTTP_USER_AGENT'])?$_SERVER['HTTP_USER_AGENT'] :' ';
	$model_log->ip = isset( $_SERVER['REMOTE_ADDR'])?$_SERVER['REMOTE_ADDR'] :' ';
	$model_log->created_time = date("Y-m-d H:i:s");
	$model_log->save( 0 );
	/** * The logic for checking permissions is * To retrieve the role of the current login user, * to retrieve the permission relationship based on the role * to retrieve all permission links from the permission table * to determine whether the currently accessed link is in the permission list */
	// Determine whether the currently accessed link is in the permissions list
	if(!$this->checkPrivilege( $action->getUniqueId() ) ){
		$this->redirect( UrlService::buildUrl( "/error/forbidden"));return false;
	}
	return true;
}
Copy the code

Checks whether you have permission to access the specified link

public function checkPrivilege( $url ){
	// If you are the super administrator, you do not need permission judgment
	if( $this->current_user && $this->current_user['is_admin'] ){
		return true;
	}
 
	// There are some pages that do not require permission judgment
	if( in_array( $url.$this->ignore_url ) ){
		return true;
	}
 
	return in_array( $url.$this->getRolePrivilege( ) );
}
Copy the code

Obtain all the permissions of a user, retrieve the role of the specified user, obtain the permission relationship based on the role, and retrieve all permission links from the permission table

public function getRolePrivilege($uid = 0){
	if(!$uid && $this->current_user ){
		$uid = $this->current_user->id;
	}
 
	if(!$this->privilege_urls ){
		$role_ids = UserRole::find()->where([ 'uid'= >$uid ])->select('role_id')->asArray()->column();
		if( $role_ids) {// Access the permission relationship by role
			$access_ids = RoleAccess::find()->where([ 'role_id'= >$role_ids ])->select('access_id')->asArray()->column();
			// Retrieve all permission links in the permission table
			$list = Access::find()->where([ 'id'= >$access_ids ])->all();
			if( $list) {foreach( $list as $_item) {$tmp_urls = @json_decode(  $_item['urls'].true );
					$this->privilege_urls = array_merge( $this->privilege_urls,$tmp_urls); }}}}return $this->privilege_urls ;

Copy the code