Geek asks and answers

Permissions: bitwise operations or many-to-many child table?

I'm trying to figure out how best to work with permissions, and as far as I know, there are two main options.

The first option is to use bitwise operations, and for this I would use the following database structure:

of users

user_id | user_permission
---------------------
1       | 15
2       | 1

permissions

permission_id | permission_name
-----------------------
1             | Read
2             | Write
4             | Execute
8             | Delete

And then, to verify that the user has permission, I would use the operation:

$user_permission & $permission_id

The main advantages that I see in this:

  • Trivial for setting, obtaining and checking permissions
  • Less memory (no child database, no extra rows for user permission)

The main disadvantages that I see in this are:

  • The list of user rights is a bit more complicated.
  • Cannot use foreign key constraints
  • Limited permissions (64 when using BIGINT)

" ", :

user_id
-------
1      
2      


permission_id | permission_name
-----------------------
1             | Read
2             | Write
3             | Execute
4             | Delete

user_permissions

user_id | permission_id
-----------------------
1       | 1
1       | 2
1       | 3
1       | 4
2       | 1

, , , ( $user_permission - permission_id s):

in_array($permission_id, $user_permission);

, :

, :

  • ( , )
  • .

? , , . , , , ; , ? , ?

" " , , - ; , -, , , .

+5
2

. , , . , , FK . , , . , , , " ", , ( , ).

+2

, - . , Mysql.

:

1: , , 1,2,4,8..etc( 2)

CREATE TABLE IF NOT EXISTS `permission` (
  `bit` int(11) NOT NULL,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`bit`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

.

INSERT INTO `permission` (`bit`, `name`) VALUES
(1, 'User-Add'),
(2, 'User-Edit'),
(4, 'User-Delete'),
(8, 'User-View'),
(16, 'Blog-Add'),
(32, 'Blog-Edit'),
(64, 'Blog-Delete'),
(128, 'Blog-View');

2. , . .
:
"Ketan" "User-Add" ( = 1) "Blog-Delete" (-64), 65 (1 + 64).
"Mehata" "Blog-View" ( = 128) "User-Delete" (-4), 132 (128 + 4).

CREATE TABLE IF NOT EXISTS `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `role` int(11) NOT NULL,
  `created_date` datetime NOT NULL
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

-

INSERT INTO `user` (`id`, `name`, `role`, `created_date`)
   VALUES (NULL, 'Ketan', '65', '2013-01-09 00:00:00'),
   (NULL, 'Mehata', '132', '2013-01-09 00:00:00');

Loding , , , :

SELECT permission.bit,permission.name  
   FROM user LEFT JOIN permission ON user.role & permission.bit
 WHERE user.id = 1

user.role "&" permission.bit - , -

User-Add - 1
Blog-Delete - 64

, -

  SELECT * FROM `user` 
     WHERE role & (select bit from permission where name='user-edit')

= .

: http://goo.gl/ATnj6j

+6

More articles:


All Articles