RBAC Database Design

51472208

1 RBAC Database Design

RBAC resource-based access control (resource-based access control) is a resource-centric access controls share cow original, share cattle series, share cattle. How is the RBAC User Role Permissions Resource table designed? The table below starts the design. The RBAC table structure.

1.1 User Tables

[SQL]View PlainCopy

2. CREATE TABLE ' Sys_user ' (
4. ' ID ' varchar ($) not NULL COMMENT ' primary key ',
6. ' Usercode ' varchar (+) not NULL COMMENT ' account ',
8. ' username ' varchar (+) not NULL COMMENT ' name ',
10. 'password ' varchar (+) not NULL COMMENT ' password ',
12. ' Salt ' varchar (+) DEFAULT NULL COMMENT ' salts ',
14. ' Locked ' char (1) DEFAULT NULL COMMENT ' account is locked, 1: Locked, 0 unlocked ',
16. PRIMARY KEY (' id ')
18. ) Engine=innodb DEFAULT Charset=utf8;

1.2 Character table

[SQL]View PlainCopy

1. create table   ' Sys_role '   (  
2.    ' id '  varchar (  not null,  
3.    ' name '  varchar ($)   not null,  
4.    ' available '  char (1)  default  null comment  is available, 1: Available, 0 unavailable ',   
5.   primary key  (' id ')   
6. )  engine=innodb default charset=utf8;   

1.3 User Role Intermediate table

[SQL]View PlainCopy

1. CREATE TABLE ' sys_user_role ' (
2. ' ID ' varchar ($) not NULL,
3. ' sys_user_id ' varchar (+) not NULL,
4. ' sys_role_id ' varchar (+) not NULL,
5. PRIMARY KEY (' id ')
6. ) Engine=innodb DEFAULT Charset=utf8;

1.4 Permissions Table

[SQL]View PlainCopy

2. CREATE TABLE ' sys_permission ' (
4. ' ID ' bigint (a) not NULL COMMENT ' primary key ',
6. 'name ' varchar (+) not NULL COMMENT ' resource name ',
8. ' Type ' varchar (+) not NULL COMMENT ' resource type: Menu,button, ',
10. ' URL ' varchar (+) DEFAULT NULL COMMENT ' Access URL address ',
12. ' Percode ' varchar (+) DEFAULT NULL COMMENT ' permission code string ',
14. ' ParentID ' bigint () DEFAULT NULL COMMENT ' parent Node ID ',
16. ' Parentids ' varchar (+) DEFAULT NULL COMMENT ' parent Node ID list string ',
18. ' Sortstring ' varchar (+) DEFAULT NULL COMMENT ' sort number ',
20. ' Available ' char (1) DEFAULT NULL COMMENT ' is available, 1: Available, 0 unavailable ',
22. PRIMARY KEY (' id ')
24. ) Engine=innodb DEFAULT Charset=utf8;

1.5 Role Permissions Table

[SQL]View PlainCopy

2. CREATE TABLE ' sys_role_permission ' (
4. ' ID ' varchar ($) not NULL,
6. ' sys_role_id ' varchar (+) not NULL COMMENT ' role id ',
8. ' sys_permission_id ' varchar (+) not NULL COMMENT ' permission id ',
10. PRIMARY KEY (' id ')
12. ) Engine=innodb DEFAULT Charset=utf8;

1.6 Initializing data

/*

SQLyog v10.2

Mysql-5.1.72-community:database-shiro

*********************************************************************

*/

/*!40101 SET NAMES UTF8 */;

/*!40101 SET sql_mode= ' * *;

/*!40014 SET @[email protected] @UNIQUE_CHECKS, unique_checks=0 */;

/*!40014 SET @[email protected] @FOREIGN_KEY_CHECKS, foreign_key_checks=0 */;

/*!40101 SET @[email protected] @SQL_MODE, sql_mode= ' no_auto_value_on_zero ' */;

/*!40111 SET @[email protected] @SQL_NOTES, sql_notes=0 */;

/*data for the table ' sys_permission ' */

Insert INTO ' sys_permission ' (' id ', ' name ', ' type ', ' url ', ' percode ', ' parentid ', ' parentids ', ' sortstring ', ' available ') VALUES (1, ' Permissions ', ' ', ' ', null,0, ' 0/', ' 0 ', ' 1 '), (11, ' Commodity Management ', ' menu ', '/item/queryitem.action ', null,1, ' 0/1/', ' 1 ', ' 1 '), ( 12, ' Product additions ', ' permission ', '/item/add.action ', ' item:create ', 11, ' 0/1/11/', ' ', ' 1 '), (13, ' product modification ', ' permission ', '/item/ Edititem.action ', ' item:update ', 11, ' 0/1/11/', ', ' 1 '), (14, ' product delete ', ' permission ', ' ', ' item:delete ', 11, ' 0/1/11/', ', ' 1 '), (15, ' Commodity search ', ' permission ', '/item/queryitem.action ', ' item:query ', one, ' 0/1/15/', NULL, ' 1 '), (21, ' User Management ', ' menu ', '/ User/query.action ', ' user:query ', 1, ' 0/1/', ' 2 ', ' 1 '), (22, ' User new ', ' permission ', ' ', ' user:create ', 21, ' 0/1/21/', ', ' 1 '), (23, ' User modified ', ' permission ', ' ', ' user:update ', 21, ' 0/1/21/', ' ', ' 1 '), (24, ' user delete ', ' permission ', ' ', ' user:delete ', 21, ' 0/1/21/', ', ' 1 ');

/*data for the table ' sys_role ' */

Insert INTO ' sys_role ' (' id ', ' name ', ' available ') VALUES (' ebc8a441-c6f9-11e4-b137-0adc305c3f28 ', ' Commodity manager ', ' 1 '), (' Ebc9d647-c6f9-11e4-b137-0adc305c3f28 ', ' user admin ', ' 1 ');

/*data for the table ' sys_role_permission ' */

Insert INTO ' sys_role_permission ' (' id ', ' sys_role_id ', ' sys_permission_id ') VALUES (' Ebc8a441-c6f9-11e4-b137-0adc305c3f21 ', ' ebc8a441-c6f9-11e4-b137-0adc305c ', ' 12 '), (' Ebc8a441-c6f9-11e4-b137-0adc305c3f22 ', ' ebc8a441-c6f9-11e4-b137-0adc305c ', ' 11 '), (' Ebc8a441-c6f9-11e4-b137-0adc305c3f24 ', ' ebc9d647-c6f9-11e4-b137-0adc305c ', ' 21 '), (' Ebc8a441-c6f9-11e4-b137-0adc305c3f25 ', ' ebc8a441-c6f9-11e4-b137-0adc305c ', ' 15 '), (' Ebc9d647-c6f9-11e4-b137-0adc305c3f23 ', ' ebc9d647-c6f9-11e4-b137-0adc305c ', ' 22 '), (' Ebc9d647-c6f9-11e4-b137-0adc305c3f26 ', ' ebc8a441-c6f9-11e4-b137-0adc305c ', ' 13 ');

/*data for the table ' sys_user ' */

Insert INTO ' sys_user ' (' id ', ' usercode ', ' username ', ' password ', ' salt ', ' locked ') VALUES (' Lisi ', ' Lisi ', ' John Doe ', ' bf07fd8bbc73b6f70b8319f2ebb87483 ', ' uiwueylm ', ' 0 '), (' Zhangsan ', ' Zhangsan ', ' Zhang San ', ' Cb571f7bd7a6f73ab004a70322b963d5 ', ' eteokues ', ' 0 ');

/*data for the table ' sys_user_role ' */

Insert INTO ' sys_user_role ' (' id ', ' sys_user_id ', ' sys_role_id ') VALUES (' Ebc8a441-c6f9-11e4-b137-0adc305c3f28 ', ' Zhangsan ', ' ebc8a441-c6f9-11e4-b137-0adc305c '), (' Ebc9d647-c6f9-11e4-b137-0adc305c3f28 ', ' Lisi ', ' Ebc9d647-c6f9-11e4-b137-0adc305c ');

/*!40101 SET [Email protected]_sql_mode */;

/*!40014 SET [Email protected]_foreign_key_checks */;

/*!40014 SET [Email protected]_unique_checks */;

/*!40111 SET [Email protected]_sql_notes */;

Share the original (respecting the original reprint right when the first line please specify, reproduced from the share of the source of cattle http://blog.csdn.net/qq_30739519)

RBAC Database Design