You cannot select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
238 lines
8.6 KiB
PHP
238 lines
8.6 KiB
PHP
<?php
|
|
|
|
class User_Manager
|
|
{
|
|
|
|
private static $table_users = 'users';
|
|
private static $table_groups = 'groups';
|
|
|
|
|
|
//could append 'ldap', 'cas':
|
|
private static $available_auth_methods = array('local');
|
|
|
|
|
|
public static function get_table_users_str()
|
|
{
|
|
return self::$table_users;
|
|
}
|
|
public static function get_table_groups_str()
|
|
{
|
|
return self::$table_groups;
|
|
}
|
|
public static function get_table_users_groups_rel_str()
|
|
{
|
|
return self::get_table_users_str() . "_" . self::get_table_groups_str() ."_rel";
|
|
}
|
|
|
|
const QUERY_CREATE_TABLE_USERS = "
|
|
CREATE TABLE %table_users% (
|
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`login` varchar(16) NOT NULL,
|
|
`display_name` text NOT NULL,
|
|
`auth_method` enum('local','ldap','cas','none') NOT NULL,
|
|
`password` char(128) NOT NULL,
|
|
`external_uid` char(45) NOT NULL,
|
|
`admin` tinyint(1) NOT NULL,
|
|
`active` tinyint(1) NOT NULL DEFAULT '1',
|
|
`created_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`created_by` int(11) NOT NULL,
|
|
`updated_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
|
|
`updated_by` int(11) NOT NULL,
|
|
PRIMARY KEY (`id`)
|
|
);
|
|
|
|
";
|
|
const QUERY_CREATE_SYSTEM_USER = "INSERT INTO %table_users%
|
|
(id,login,display_name,auth_method,active,created_time,created_by)
|
|
VALUES (0,'','SYSTEM','none',0, NOW(),0);";
|
|
|
|
const QUERY_CREATE_TABLE_GROUPS = "
|
|
CREATE TABLE %table_groups% (
|
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`name` text NOT NULL,
|
|
`active` tinyint(1) NOT NULL DEFAULT '1',
|
|
`created_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`created_by` int(11) NOT NULL,
|
|
`updated_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
|
|
`updated_by` int(11) NOT NULL,
|
|
PRIMARY KEY (`id`)
|
|
);
|
|
";
|
|
|
|
const QUERY_CREATE_DEFAULT_GROUPS = "
|
|
INSERT INTO %table_groups% (id,name,active,created_time,created_by,updated_time,updated_by)
|
|
VALUES ('0','Administrateurs','1',NOW(),'0',NOW(),'0'),
|
|
('1','Responsables sécurité','1',NOW(),'0',NOW(),'0'),
|
|
('2','Agent de prévention HSE','1', NOW(),'0',NOW(),'0')
|
|
;
|
|
";
|
|
|
|
const QUERY_CREATE_REL_USERS_GROUPS = "
|
|
CREATE TABLE `%table_users%_%table_groups%_rel` (
|
|
`user_id` int(11) NOT NULL,
|
|
`group_id` int(11) NOT NULL,
|
|
PRIMARY KEY (`user_id`,`group_id`),
|
|
KEY `users_id` (`user_id`),
|
|
KEY `group_id` (`group_id`),
|
|
CONSTRAINT `%table_users%_%table_groups%_rel_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `%table_users%` (`id`),
|
|
CONSTRAINT `%table_users%_%table_groups%_rel_ibfk_2` FOREIGN KEY (`group_id`) REFERENCES `%table_groups%` (`id`)
|
|
);
|
|
";
|
|
|
|
|
|
/*
|
|
* Erase the list of avaliable methods
|
|
* @ input methods: Array (non associative)
|
|
* return true
|
|
*/
|
|
public static function set_available_auth_methods($methods)
|
|
{
|
|
self::$available_auth_methods = $methods;
|
|
}
|
|
public static function create_local_tables(PDO $db)
|
|
{
|
|
$searched = array('%table_users%','%table_groups%');
|
|
$replace = array(self::$table_users,self::$table_groups);
|
|
|
|
$queries = array(
|
|
str_replace($searched,$replace,self::QUERY_CREATE_TABLE_USERS),
|
|
str_replace($searched,$replace,self::QUERY_CREATE_SYSTEM_USER),
|
|
str_replace($searched,$replace,self::QUERY_CREATE_TABLE_GROUPS),
|
|
str_replace($searched,$replace,self::QUERY_CREATE_REL_USERS_GROUPS),
|
|
str_replace($searched,$replace,self::QUERY_CREATE_DEFAULT_GROUPS)
|
|
);
|
|
foreach($queries as $query)
|
|
{
|
|
$rs = $db->query($query);
|
|
if($rs === false){
|
|
throw new \UnexpectedValueException("SQL ERROR ON QUERY " . $query );
|
|
}
|
|
}
|
|
}
|
|
|
|
|
|
public function authentificate(PDO $db, STRING $login, STRING $password){
|
|
|
|
foreach(self::$available_auth_methods as $method){
|
|
|
|
switch($method)
|
|
{
|
|
case "local":
|
|
case "sql'":
|
|
$user = new User_Sql($db);
|
|
if($user->authentificate($login,$password)){
|
|
return $user;
|
|
}
|
|
break;
|
|
default:
|
|
return false;
|
|
}
|
|
}
|
|
|
|
$user = new User($db);
|
|
|
|
return $user;
|
|
}
|
|
public static function add_user_to_group(PDO $db, $userId,$groupId)
|
|
{
|
|
$tableRel = self::get_table_users_groups_rel_str();
|
|
$sql = "INSERT INTO $tableRel (user_id,group_id) VALUES (:userid, :groupid)";
|
|
$rs = $db->prepare($sql);
|
|
|
|
$rs->execute( array(
|
|
":userid" => $userId,
|
|
":groupid" => $groupId
|
|
));
|
|
|
|
}
|
|
public static function del_user_from_group(PDO $db, $userId,$groupId)
|
|
{
|
|
$tableRel = self::get_table_users_groups_rel_str();
|
|
$sql = "DELETE FROM $tableRel WHERE user_id=:userid AND group_id=:groupid;";
|
|
$rs = $db->prepare($sql);
|
|
$rs->execute( array(
|
|
":userid" => $userId,
|
|
":groupid" => $groupId
|
|
));
|
|
}
|
|
/*
|
|
* Return an array of users objects.
|
|
* is_connected for each ones is set to false
|
|
*/
|
|
public static function get_users_list(PDO $db, $activesOnly = true, $groups = null)
|
|
{
|
|
$tableUsers = self::get_table_users_str();
|
|
$tableGroups = self::get_table_groups_str();
|
|
$tableRel = $tableUsers . "_" . $tableGroups ."_rel";
|
|
|
|
//conditions
|
|
$conditions = array();
|
|
if($activesOnly){
|
|
$conditions[] = "$tableUsers.active='1'";
|
|
}
|
|
if(!is_null($groups)){
|
|
//$ groups is an untrusted entry; check it before using it on a non a query
|
|
foreach($groups as $group){
|
|
if (!preg_match('/^[0-9]+$/',$group)){
|
|
throw new \UnexpectedValueException('$groups must be a list containing only digits'. $group);
|
|
}
|
|
}
|
|
$conditions[] = "$tableUsers.id IN(
|
|
SELECT $tableUsers.id
|
|
FROM $tableUsers, $tableRel
|
|
WHERE $tableRel.user_id = $tableUsers.id
|
|
AND $tableRel.group_id IN ('" . implode("','",$groups) ."')
|
|
)";
|
|
}
|
|
|
|
|
|
|
|
$sql = "
|
|
SELECT
|
|
$tableUsers.id as id,
|
|
$tableUsers.login as login,
|
|
$tableUsers.display_name as display_name,
|
|
$tableUsers.auth_method as auth_method,
|
|
$tableUsers.external_uid as external_id,
|
|
$tableUsers.admin as is_admin,
|
|
$tableUsers.active as active,
|
|
GROUP_CONCAT(groupsrel.group_id SEPARATOR \",\") as groups_ids,
|
|
GROUP_CONCAT(groups.name SEPARATOR \",\") as groups_name
|
|
FROM
|
|
$tableUsers LEFT JOIN $tableRel as groupsrel ON groupsrel.user_id = $tableUsers.id
|
|
LEFT JOIN $tableGroups as groups ON groups.id = groupsrel.group_id"
|
|
.(empty($conditions) ? "" : " WHERE " . implode(" AND ", $conditions))
|
|
." GROUP BY $tableUsers.id; ";
|
|
|
|
$rs = $db->query($sql);
|
|
|
|
$list = array();
|
|
while ($r = $rs->fetch())
|
|
{
|
|
|
|
$groups_id = explode(",", $r["groups_ids"]);
|
|
$groups_names = explode(",", $r["groups_name"]);
|
|
$groups = array();
|
|
for($i = 0; $i < count($groups_id); $i++){
|
|
$groups[ $groups_id[$i] ] = $groups_names[$i];
|
|
}
|
|
|
|
$user = new User($db);
|
|
|
|
// array("login","external_id","id","display_name","auth_method","is_admin","group");
|
|
$user-> set_properties(
|
|
array(
|
|
"login" => $r["login"],
|
|
"id" => $r["id"],
|
|
"display_name" => $r["display_name"],
|
|
"external_id" => $r["external_id"],
|
|
"auth_method" => $r["auth_method"],
|
|
"is_admin" => ($r["is_admin"] == "1"),
|
|
"groups" => $groups
|
|
)
|
|
);
|
|
$list[] = $user;
|
|
}
|
|
return $list;
|
|
}
|
|
} |