Rev 20 | Blame | Compare with Previous | Last modification | View Log | Download | RSS feed
<?php
/*
Copyright (c) 2021, Daily Data, Inc. Redistribution and use in
source and binary forms, with or without modification, are permitted
provided that the following conditions are met:
* Redistributions of source code must retain the above copyright
notice, this list of conditions and the following disclaimer.
* Redistributions in binary form must reproduce the above copyright
notice, this list of conditions and the following disclaimer in the
documentation and/or other materials provided with the distribution.
THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
"AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY
THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
(INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
*/
/*
* UsersDataSourceMySQLi.class.php
*
* Authors: R. W. Rodolico
*
*/
/**
* usersPermissionsDataSource class
*
* usersPermissionsDataSource provides the data access capabilities for
* the usersPermissions class
* class.
*
* To build a data access class for UsersPermissions, the following 5
* methods must exist.
* getPassword(username)
* getAllUsers()
* getARecord
* update
*
* Additionally, where appropriate, the following function is useful
* buildTable()
*
* This particular instance provides an interface to MySQL using
* the mysqli libraries.
*
* Create an instance of this, then pass the variable to several Users
* calls.
*
* @author R. W. Rodolico <rodo@unixservertech.com>
*
* @version 0.9.0 (beta)
* @copyright 2021 Daily Data, Inc.
*
*/
require_once( 'UsersDataSourceMySQLi.class.php' );
class usersPermissionsDataSourceMySQLi extends usersDataSourceMySQLi {
/**
* extends usersDataSource
*
* Adds the new fields needed for permissions. First, creates array
* $permissions, then does a recursive merge into
* usersDataSource::$configuration.
* NOTE: we must merge this before we merge in $dbDef so the
* developer can modify permissions also
*
* @param mysqli $connection Existing mysqli database connection
* @param string[] $customFields Array to be merged with $configuration
* @param string[] $dbLoginInfo Array containing username, hostname, etc.. to make mysqli connection_aborted
*
* @return null
*
*/
public function __construct( $connection, $customFields = array() ) {
$permissions = array(
'tables' => array(
'permissions categories' => array(
'table' => '_permissions_categories', // table name for user records
'id' => '_permission_category_id', // ID column name
'display' => array( // fields which are displayed to select
'description'
),
'fields' => array(
'name' => array(
'dbColumn' => 'name',
'type' => 'varchar',
'size' => 16,
'required' => true,
'unique' => true
)
),
), // permissions categories table
'permissions' => array(
'table' => '_permissions', // table name for user records
'id' => '_permission_id', // ID column name
'display' => array( // fields which are displayed to select
'description'
),
'fields' => array(
'name' => array(
'dbColumn' => 'name',
'type' => 'varchar',
'size' => 16,
'required' => true,
'unique' => true
),
'description' => array(
'dbColumn' => 'description',
'type' => 'varchar',
'size' => 64,
'required' => true
),
'permission category' => array(
'dbColumn' => '_permission_category_id',
'type' => 'int unsigned',
'required' => true,
),
'default' => array(
'dbColumn' => 'default_value',
'type' => 'boolean',
'required' => true
),
)
), // permissions table
'users permissions' => array(
'table' => '_users_permissions', // table name for user records
'id' => '_user_permission_id', // ID column name
'fields' => array(
'user_id' => array(
'dbColumn' => '_user_id',
'type' => 'int unsigned',
'required' => true,
'references' => 'users',
),
'permission_id' => array(
'dbColumn' => '_permission_id',
'type' => 'int unsigned',
'required' => true,
'references' => 'permissions',
),
'value' => array(
'dbColumn' => 'value',
'type' => 'boolean',
'required' => true,
),
)
) // users permissions table
),
'views' => array(
'users permissions' => array(
'name' => '_view_users_permissions'
)
) // users permissions view
);
$this->configuration = array_merge_recursive( $this->configuration, $permissions );
parent::__construct( $connection, $customFields );
} // constructor
/**
* Function will build tables when called
*
* Calls parent::buildTable first, then creates a view. The query
* for the view is created using sprintf so we can allow the developers
* to modify the table names, etc...
*
*/
public function buildTable() {
parent::buildTable();
// add a unique constraint on users permissions for user id and permission id
// we do it this way since there is no way to do it automagically
// without rewriting the $configuration code
$query = sprintf( 'alter table %s add unique key (%s,%s)',
$this->configuration['tables']['users permissions']['table'],
$this->configuration['tables']['users permissions']['fields']['user_id']['dbColumn'],
$this->configuration['tables']['users permissions']['fields']['permission_id']['dbColumn']
);
$this->doSQL( $query );
// create a convenience view
$query = sprintf( "create or replace view %s as
select
%s.%s users_id, /* users id */
%s.%s user, /* users login */
%s.%s permission_id, /* permissions id */
%s.%s permission, /* permissions name */
%s.%s description, /* permissions description */
%s.%s category, /* permissions_categories name */
ifnull(%s.%s,0) value /* actual value */
from
%s /* users */
join %s /* permissions (permissions_id */
left join %s using (%s,%s) /* users_permissions (users_id) */
join %s using (%s) /* permissions_categories( permissions_categories_id) */",
$this->configuration['views']['users permissions']['name'],
$this->configuration['tables']['users']['table'],
$this->configuration['tables']['users']['id'],
$this->configuration['tables']['users']['table'],
$this->configuration['tables']['users']['fields']['login']['dbColumn'],
$this->configuration['tables']['permissions']['table'],
$this->configuration['tables']['permissions']['id'],
$this->configuration['tables']['permissions']['table'],
$this->configuration['tables']['permissions']['fields']['name']['dbColumn'],
$this->configuration['tables']['permissions']['table'],
$this->configuration['tables']['permissions']['fields']['description']['dbColumn'],
$this->configuration['tables']['permissions categories']['table'],
$this->configuration['tables']['permissions categories']['fields']['name']['dbColumn'],
$this->configuration['tables']['users permissions']['table'],
$this->configuration['tables']['users permissions']['fields']['value']['dbColumn'],
$this->configuration['tables']['users']['table'],
$this->configuration['tables']['permissions']['table'],
$this->configuration['tables']['users permissions']['table'],
$this->configuration['tables']['users']['id'],
$this->configuration['tables']['permissions']['id'],
$this->configuration['tables']['permissions categories']['table'],
$this->configuration['tables']['permissions categories']['id']
);
$this->doSQL( $query );
}
/**
* Adds a permission to the permissions table
*
* This simply adds a permission to the permissions table, adding the
* category if it doesn't exist already.
*
* @parameter string $category Category to place the permission into
* @parameter string $name The short name of the permission
* @parameter string $description The long (display) name for the permission
* @parameter boolean $defaultValue The default value for the permission
*/
public function addPermission ( $category, $name, $description, $defaultValue = 0 ) {
$categoryID = $this->getAField(
$this->tableColumnName ( 'permissions categories' ),
$this->tableColumnName ( 'permissions categories', 'id' ),
$this->tableColumnName ( 'permissions categories', 'name' ),
$category
);
if ( ! $categoryID ) { // we did not find the category, so add it
$this->doSQL( sprintf( "insert into %s ( %s ) values ( %s )",
$this->tableColumnName ( 'permissions categories' ),
$this->tableColumnName ( 'permissions categories', 'name' ),
$this->escapeString($category)
)
);
$categoryID = $this->getAField(
$this->tableColumnName ( 'permissions categories' ),
$this->tableColumnName ( 'permissions categories', 'id' ),
$this->tableColumnName ( 'permissions categories', 'name' ),
$category
);
} // if category not found
$query = sprintf( "insert into %s ( %s,%s,%s,%s ) values ( %s,%s,%s,%s )",
$this->tableColumnName ( 'permissions' ),
$this->tableColumnName ( 'permissions', 'name' ),
$this->tableColumnName ( 'permissions', 'description' ),
$this->tableColumnName ( 'permissions', 'permission category' ),
$this->tableColumnName ( 'permissions', 'default' ),
$this->escapeString($name),
$this->escapeString($description),
$this->escapeString($categoryID),
$defaultValue
);
$this->doSQL( $query );
$permissionsID = $this->getAField(
$this->configuration['tables']['permissions']['table'],
$this->configuration['tables']['permissions']['id'],
$this->configuration['tables']['permissions']['fields']['name']['dbColumn'],
$this->escapeString($name)
);
} // addPermission
/**
* Sets any unset user permissions
*
* This will set any missing permissions which have a true value in the
* users permissions join table. This will NOT modify any existing records
* but will add new records if they do not exist in the table.
*
* @parameters string $user_id If set, will limit to only one user (not implemented)
*/
public function setUsersPermissions ( $user_id = null ) {
$query = sprintf(
"insert into %s (%s,%s,%s)
select
%s.%s,
%s.%s,
%s.%s
from
%s
join %s
where
%s.%s
and not exists
(
select 1
from %s
where
%s.%s = %s.%s
and %s.%s = %s.%s
)",
$this->configuration['tables']['users permissions']['table'],
$this->configuration['tables']['users permissions']['fields']['user_id']['dbColumn'],
$this->configuration['tables']['users permissions']['fields']['permission_id']['dbColumn'],
$this->configuration['tables']['users permissions']['fields']['value']['dbColumn'],
$this->configuration['tables']['users']['table'],
$this->configuration['tables']['users']['id'],
$this->configuration['tables']['permissions']['table'],
$this->configuration['tables']['permissions']['id'],
$this->configuration['tables']['permissions']['table'],
$this->configuration['tables']['permissions']['fields']['default']['dbColumn'],
$this->configuration['tables']['users']['table'],
$this->configuration['tables']['permissions']['table'],
$this->configuration['tables']['permissions']['table'],
$this->configuration['tables']['permissions']['fields']['default']['dbColumn'],
$this->configuration['tables']['users permissions']['table'],
$this->configuration['tables']['users permissions']['table'],
$this->configuration['tables']['users permissions']['fields']['user_id']['dbColumn'],
$this->configuration['tables']['users']['table'],
$this->configuration['tables']['users']['id'],
$this->configuration['tables']['users permissions']['table'],
$this->configuration['tables']['users permissions']['fields']['permission_id']['dbColumn'],
$this->configuration['tables']['permissions']['table'],
$this->configuration['tables']['permissions']['id']
);
$this->doSQL($query, true );
} // setUsersPermissions
public function getARecord( $whereFields, $fieldList = null ) {
$id = isset( $whereFields['id'] ) ? $whereFields['id'] : null;
$result = parent::getARecord( $whereFields, $fieldList );
if ( $result && isset( $id ) ) {
$result['permissions'] = $this->getPermissions( $id );
}
return $result;
}
/**
* Gets permissions for one user
*
* NOTE: if $id is -1, indicating a new user, simply retrieves all
* permissions from the permissions table with the default value
*
* WARNING: this uses the view, which is not as flexible as the
* tables as far as modifying programmatically
*
* The return value is an associative array, where the key is
* the permission (short) name and the value is 0 or 1 (boolean
* true/false)
*
* @param integer $id the user id of the record we want.
*
* @returns string[] Array of associative arrays with all data
*/
public function getPermissions ( $id ) {
$query = '';
if ( $id == -1 ) {
$query = "select name permission,default_value value from _permissions";
} else {
$query = "select permission,value from _view_users_permissions where users_id = $id";
}
$results = $this->doSQL( $query );
$values = $results->fetch_all(MYSQLI_ASSOC);
return array_column( $values, 'value', 'permission' );
}
/**
* Gets all permissions with names, group names, etc... from database
*
* NOTE: if $id is -1, indicating a new user, simply retrieves all
* permissions from the permissions table with the default value
*
* WARNING: this uses the view, which is not as flexible as the
* tables as far as modifying programmatically
*
* @param integer $id the user id of the record we want.
* @returns string[] Array of associative arrays with all data
*/
public function getFullPermissions( $id ) {
$query = '';
if ( $id == -1 ) {
$query = "select _permission_id permission_id, _permissions.name permission, description, _permissions_categories.name category, default_value value from _permissions join _permissions_categories using (_permission_category_id)";
} else {
$query = "select permission_id,permission,description,category,value from _view_users_permissions where users_id = $id order by category,description";
}
$results = $this->doSQL( $query );
return $results->fetch_all(MYSQLI_ASSOC);
}
/**
* Adds/Updates a group of permissions for a user
*
* @param integer $userID The user ID to update
* @param string[] $newData An array where the key is the field name and the value is the new value to use
*/
public function updatePermissions ( $userID, $newData ) {
$query = '';
foreach ( $newData as $key => $value ) {
$query = sprintf(
"insert into %s
( %s,%s,%s )
select %s, %s, %s
from %s
where %s = %s
on duplicate key update %s = %s",
$this->tableColumnName ( 'users permissions' ),
$this->tableColumnName ( 'users permissions', 'user_id' ),
$this->tableColumnName ( 'users permissions', 'permission_id' ),
$this->tableColumnName ( 'users permissions', 'value' ),
$userID,
$this->tableColumnName ( 'permissions', 'id', true ),
$value,
$this->tableColumnName ( 'permissions' ),
$this->tableColumnName ( 'permissions', 'name' ),
$this->escapeString($key),
$this->tableColumnName ( 'users permissions', 'value' ),
$value
);
$this->doSQL( $query );
}
} // update
}
?>