Skip to content

Instantly share code, notes, and snippets.

@rusln

rusln/gist:6021413

Created July 17, 2013 15:05
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save rusln/6021413 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save rusln/6021413 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. rusln created this gist Jul 17, 2013.
    415 changes: 415 additions & 0 deletions gistfile1.txt
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,415 @@
    <?php

    /**
    * Role Based Access Control :
    * @link http://phpmaster.com/role-based-access-control-in-php/ Een uitgebrenamee versie van ACL
    * Role Based Access List: de sql queries die in deze tutorial
    * worden gebruikt zijn te ingewikkeld, 1 join voor 3 tabels ?
    * Daarom heb ik een veel simpelere oplossing die minder elegant is maar
    * veel dunameelijker.
    *
    * @link http://net.tutsplus.com/tutorials/php/a-better-login-system/
    * Access Control List (ACL)
    * Een simpelere tutorial zonder queries "voor gevorderden" :/.
    *
    * @link https://www.security.nl/artikel/22885/1/Zet_RBAC_bij_het_grof_vuil.html en voila
    * @link http://en.wikipedia.org/wiki/Role-based_access_control#Relation_to_other_models geen enkele list is compleet zonder wikipedia
    */

    namespace Rbac;

    use Rbac\Models;
    use \Rbac\Db\PdoWrapper as PdoWrapper;

    class Rbac {

    /**
    * @todo Maak hier een repository, schuif alle db naar een nieuwe classe
    * @todo verplaats alle user specifieke taken naar een user repository
    * @todo verplaats alle permission specifieke taken naar een perm repo
    * @var \Rbac\Db\PdoWrapper $db zou in principe hier versch db's aan kunnen kopellen
    */
    protected $db;

    public function __construct() {

    }

    public function initDb() {
    $this->db = new Db\PdoWrapper();
    }

    public function createRole($roleName) {
    $sql = 'insert into roles (role_name) values (:role_name)';

    $this->initDb();
    $result = $this->db
    ->connect()
    ->prepareStmt($sql)
    ->executeStmt(array(':role_name' => $roleName))
    ->getRowCount();
    var_dump($result);
    }

    public function getUserRolePermissions() {
    $sql = 'SELECT u.user_name,
    r.role_name,
    p.perm_desc

    FROM users AS u, roles as r,permissions as p
    JOIN user_role AS ur ON u.user_name = ur.user_name
    JOIN r ON ur.role_name = r.role_name
    JOIN role_perm AS rp ON r.role_name = rp.role_name
    JOIN p ON rp.perm_name = p.perm_name
    ';
    $this->initDb();
    $result = $this->db
    ->connect()
    ->prepareStmt($sql)
    ->executeStmt()
    ->fetchAll();
    return $result;
    }

    public function getPermissions() {
    $sql = "select * from permissions";

    $this->initDb();

    $result = $this->db
    ->connect()
    ->prepareStmt($sql)
    ->executeStmt()
    ->fetchAll();
    return $result;
    }

    public function getRoles() {
    $sql = 'select * from roles';

    $result = $this->db
    ->connect()
    ->prepareStmt($sql)
    ->executeStmt()
    ->fetchAll();
    return $result;
    }

    public function getRolesOfUser($userName = "") {

    $sql =
    'select u.user_name, r.role_name
    from users as u
    join user_role as ur on u.user_name = ur.user_name
    join roles as r on ur.role_name = r.role_name';
    $sqlO =
    'select users.user_name, roles.role_name
    from users
    join user_role on users.user_name = user_role.user_name
    join roles on user_role.role_name = roles.role_name
    where users.user_name = :user_name';

    $result = $this->db
    ->connect()
    ->prepareStmt($sql)
    ->executeStmt(array('user_name' => $userName))
    ->fetchAll();
    return $result;
    }

    public function getPermissionsOfRole($role = "") {

    $sql =
    '
    select permissions.perm_desc, roles.role_name
    from permissions
    join role_perm on permissions.perm_name = role_perm.perm_name
    join roles on role_perm.role_name = roles.role_name
    where roles.role_name = :role_name
    ';

    $result = $this->db
    ->connect()
    ->prepareStmt($sql)
    ->executeStmt(array(':role_name' => $role))
    ->fetchAll();
    return $result;
    }

    public function getPermOfUsers() {
    $sql = 'select u.user_name, r.role_name , p.perm_desc
    from users as u

    join user_role as ur on u.user_name = ur.user_name
    join roles as r on ur.role_name = r.role_name
    join role_perm as rp on r.role_name = rp.role_name
    join permissions as p on rp.perm_name = p.perm_name
    ';
    $result = $this->db
    ->connect()
    ->prepareStmt($sql)
    ->executeStmt()
    ->fetchAll();
    return $result;
    }

    public function getUsersOfPerm() {
    $sql = 'select distinct p.perm_desc, u.user_name
    from permissions as p
    join role_perm as rp on p.perm_name = rp.perm_name
    join roles as r on rp.role_name = r.role_name
    join user_role as ur on r.role_name = ur.role_name
    join users as u on ur.user_name = u.user_name';

    $result = $this->db
    ->connect()
    ->prepareStmt($sql)
    ->executeStmt()
    ->fetchAll();
    return $result;
    }

    public function getPermissionsRolesOfUser($userName = "") {

    $sql1 = 'select distinct u.user_name,r.role_name,p.perm_desc
    from users as u
    join user_role as ur on u.user_name = ur.user_name
    join roles as r on ur.role_name = r.role_name

    join role_perm as rp on r.role_name = rp.role_name
    join permissions as p on rp.perm_name = p.perm_name
    where u.user_name = :user_name
    order by r.role_name
    ';
    }

    public function asignPermissionToRole($role_name, $perm_name) {

    $sql = 'insert into role_perm (role_name,perm_name) values(:role_name,:perm_name)';

    $db = new \PDO('mysql:host=localhost;dbname=rbac', 'root', 'root');

    $stmt = $db->prepare($sql);

    $stmt->execute(array(
    ':role_name' => $role_name,
    ':perm_name' => $perm_name
    ));

    $result = $stmt->rowCount();

    var_dump($result);
    }

    public function createPermission($permDesc) {
    $sql = 'insert into permissions (perm_desc) values (:perm_desc)';

    $db = new \PDO('mysql:host=localhost;dbname=rbac', 'root', 'root');

    $stmt = $db->prepare($sql);

    $stmt->execute(array(
    ':perm_desc' => $permDesc
    ));

    $result = $stmt->rowCount();

    var_dump($result);
    }

    public function deletePermission($permId) {
    $sql = 'delete from permissions where perm_name = :perm_name';

    $db = new \PDO('mysql:host=localhost;dbname=rbac', 'root', 'root');

    $stmt = $db->prepare($sql);

    $stmt->execute(array(
    ':perm_name' => $permId
    ));

    $result = $stmt->rowCount();

    var_dump($result);
    }

    public function removePermissionFromRole($permId) {

    $sql = "delete from role_perm where perm_name = :perm_name";

    $db = new \PDO('mysql:host=localhost;dbname=rbac', 'root', 'root');

    $stmt = $db->prepare($sql);

    $stmt->execute(array(
    ':perm_name' => $permId
    ));
    }

    public function updatePermissionDescription($permId, $newDesc) {

    $sql = "update permissions set perm_desc = :perm_desc where perm_name = :perm_name ";

    $db = new \PDO('mysql:host=localhost;dbname=rbac', 'root', 'root');

    $stmt = $db->prepare($sql);

    $stmt->execute(array(
    ':perm_name' => $permId,
    ':perm_desc' => $newDesc
    ));

    $result = $stmt->rowCount();

    var_dump($result);
    }

    /**
    * Deze haalt alle gebruikers op, met hun roles en daaraan verbonden
    * permissions,de array die terug komt heeft voor elke permission een gebruiker
    * array + voor elke role een gebruiker array.
    * dus wanneer een user 5 permissions heeft, krijgen we 1 user 5 keer,
    * maar telkens met andere permissions :/
    * @return array $user
    */
    public function getCompleteUsers() {
    $sql = "
    select u.*,r.role_name as role ,p.perm_desc as permission
    from permissions as p
    join role_perm as rp on p.perm_name = rp.perm_name
    join roles as r on rp.role_name = r.role_name
    join user_role as ur on r.role_name = ur.role_name
    join users as u on ur.user_name = u.user_name
    where u.user_name = 'ruslan'
    order by u.user_name, role";

    $ql ="

    ";
    $this->initDb();
    $result = $this->db
    ->connect()
    ->prepareStmt($sql)
    ->executeStmt()
    ->fetchAll();
    // var_dump($result);
    $userArrayAll = $this->prepareUsers($result);
    var_dump($this->makeUserArrRolesPermForUser($userArrayAll[0]));
    //var_dump($this->makeUserArrRolesPermForUser($userArrayAll[1]));
    }

    /**
    * Deze werkt samen met getCompleteUsers()
    * @param array $result EEN gebruiker, die met duplicate waarden binnekomt
    * @return array een "zuivere" gebruiker
    */
    public function prepareUsers($result) {
    $userIds = array();
    $userIdAll = array();
    $userArrayAll = array();
    /**
    * http://stackoverflow.com/questions/6750290/php-array-search-returning-0-for-the-first-element
    *
    */
    foreach ($result as $key => $value) {
    if (array_search($value['user_name'], $userIds) !== false) {

    } else {

    $userIds[] = $value['user_name'];
    }
    }
    /*
    * haal ALLE name's
    */
    foreach ($result as $key => $value) {

    $userIdAll[] = $value['user_name'];
    }
    /*
    * splits elke gebruiker in zijn aparte subarray
    */
    foreach ($userIds as $value) {
    // maak een nieuwe user, met duplicate values
    $oneUser = array();
    // haal die uit de volledige array
    foreach ($result as $k => $v) {
    if ($v['user_name'] == $value) {
    $oneUser[] = $v;
    }
    }
    $userArrayAll[] = $oneUser;
    }
    return $userArrayAll;
    }

    /**
    * alleen voor gebruik met prepareUsers
    *
    * @param type $result
    * @return type
    */
    public function makeUserArrRolesPermForUser($result) {

    $user = array();
    foreach ($result as $key => $value) {
    foreach ($value as $k => $v) {
    if (isset($user[$k])) {
    if ($user[$k] !== $v) {

    if (is_array($user[$k])) {
    if (is_int(array_search($v, $user[$k]))) {

    } else {
    $user[$k][] = $v;
    }
    } else {
    $first = $user[$k];
    $user[$k] = array($first, $v);
    }
    }
    } else {
    $user[$k] = $v;
    }
    }
    }
    return $user;
    }

    public function getRoleById($name) {
    $sql = "select role_name from roles where role_name = :name";

    $this->initDb();
    $result = $this->db->connect()
    ->prepareStmt($sql)
    ->executeStmt(array(':name'=>$name))
    ->fetch();
    return $result;
    }

    public function updateRole($name) {
    $sql = "UPDATE roles SET(roles.roles_name) VALUES (:name)";

    $this->initDb();
    $result = $this->db->connect()
    ->prepareStmt($sql)
    ->executeStmt(array(':name'=>$name))
    ->getRowCount();
    return $result;
    }

    public function deleteRole($id) {
    $sql = "delete roles where roles.role_id = :id";

    $this->initDb();
    $result = $this->db->connect()
    ->prepareStmt($sql)
    ->executeStmt(array(':id'=>$id))
    ->getRowCount();
    return $result;

    }

    }

    ?>
    45 changes: 45 additions & 0 deletions gistfile2.txt
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,45 @@
    <?php
    namespace Rbac\Db;
    class PdoWrapper {
    /**
    *
    * @var \Pdo
    */
    protected $pdo;
    /**
    *
    * @var \PdoStatement
    */
    protected $stmt;

    function __construct() {

    }
    public function connect(){
    $this->pdo = new \PDO('mysql:host=localhost;dbname=rbac','root','root');
    $this->pdo->setAttribute(\PDO::ERRMODE_EXCEPTION, \PDO::ERRMODE_WARNING);
    return $this;
    }
    public function prepareStmt($sql){
    $this->stmt = $this->pdo->prepare($sql);

    return $this;
    }
    public function executeStmt($bindParams=array()){
    $this->stmt->execute($bindParams);
    return $this;
    }
    public function fetch(){
    $result = $this->stmt->fetch(\PDO::FETCH_ASSOC);
    return $result;

    }
    public function fetchAll(){
    $result =$this->stmt->fetchAll(\PDO::FETCH_ASSOC);
    return $result;
    }
    public function getRowCount(){
    $this->stmt->rowCount();
    }
    }
    ?>