<

Enabling and Administering Apache's HTTPD authn_dbd MySQL authentication, through PHP, on Suse

Published on
28,939 Points
16,939 Views
Last Modified:
Approved
Community Pick

Introduction

As you’re probably aware the HTTP protocol offers basic / weak authentication, which in combination with the relevant configuration on your web server, provides the ability to password protect all or part of your host.  If you were not aware and before you get to excited, note the HTTP protocol offers little more than the ability to request and transmit a user-id and password, in a non human readable manner, with every page request (the Authentication Basic header). So this approach shouldn’t be regarded as a secure solution or efficient, but may be useful in providing a deterrent to the uninitiated.

Anyway I hope the following either helps you enable MySQL based authentication, on an Apache host, or possibly points you elsewhere for your authentication solution:

Overview

Apache offers the ability, within its <DirectoryMatch>, <Directory >, <LocationMatch >, <Location > <FilesMatch > or <Files > blocks, to restrict access to:
• A Valid User             [via “Require valid-user “  or “AuthDBDUserPWQuery xxxxxx”]
• A Specific User        [via “Require user xxxx”   or “AuthDBDUserPWQuery xxxxxx”]
• A Group of Users     [via “Require group yyyy”   or “AuthDBDUserPWQuery xxxxxx”]

Credentials can be stored in local configuration (flat text), pseudo databases (.dbm file) or a database of your choice {MySQL, Postgress, ldap (Including a Windows DC), NIS...} (see documentation here).

As the text configuration and .dbm file mechanisms are a pain to manage, for anything other than the most trivial of static set-ups, not to mention already being well documented, I’ll ignore them and have a quick look at using a DB solution.  In particular,  Mysql through the Apache “authn_dbd” module. Primarily as it's one of the few options not already detailed in the excellent article  here.
 
To make life very slightly harder for myself I’ve used the vanilla Apache build bundled with [Open]SuSE.  Due to an Apache/GPL license mixing restriction and SuSe’s sysconfig utility, which dynamically re-generates configuration files, that will add a couple of steps to the set-up.  

Note: The contents of SuSe /etc/apache2/sysconfig.d/* is re-built every time Apache starts, so any direct manual changes applied here will be lost.

Assumptions:

You already have a working Apache HTTPD daemon running, on an OS of your choice, as mentioned above I’ve used SuSE Linux.
You already have a Mysql daemon up and running.
You have root / administrator access to both the OS and MySQL database.
Your Apache modules directory or your HTTPD binary includes the “mod_dbd” and “mod_authn_dbd” modules.
You have the Apache Portable Runtime (APR) libraries, for Mysql, installed or have access to the package.
You have PHP installed and configured.
Your aware of the SuSE Yast tool.

The Set-up:

[STEP title="Check and install Dependent packages "]
[/STEP]Apache's DB driver module requires the APR library, so let's check to see if it's installed: Open Yast, and select the Software -> Package Manager, and search for "apr".  If you don't have a: libapr1-util1-dbd-mysql installed (or if you’re planning to use Postgress ...-pgsql), install the package. While you're in the package manager, also ensure you have the “apache-utils” package.

[STEP title="Load the necessary Apache modules"][/STEP]
Ensure the “dbd” and “authn_dbd “ modules are loaded into Apache. As mentioned before SuSE dynamically re-builds a sub-set of the Apache configuration every time the server starts, this includes the module loads. Still within Yast Open the: System ->"/etc/Sysconfig Editor" and select: Network -> WWW -> Apache2 -> APACHE_MODULES.

If not already present, append "dbd  authb_dbd" to the line and hit OK.
Then in a console window, as root:
#  /etc/init.d/apache2 test

Open in new window

Check for errors, and if OK:
#  /etc/init.d/apache2 restart

Open in new window

Then:
# grep dbd /etc/apache2/sysconfig.d/loadmodule.conf

Open in new window

And you should see:
LoadModule dbd_module                     /usr/lib64/apache2-prefork/mod_dbd.so
LoadModule authn_dbd_module               /usr/lib64/apache2-prefork/mod_authn_dbd.so

Open in new window

If you’re not using SuSe, check your httpd.conf for similar entries, and add if lacking.

[STEP title="Create the Database"]
[/STEP]To create a basic database schema, to store the credentials, return to your console Window and type:
# mysql -uroot -p
Passowrd: xxxxx  #Replace with you own MySQL root password
SHOW databases;
CREATE database apache;
SHOW databases;
USE apache;
CREATE TABLE USRS (UsrID VARCHAR(16), Passwd VARCHAR(64), PRIMARY KEY (UsrID));
CREATE TABLE GRPS (Grp VARCHAR(16), PRIMARY KEY (Grp));
CREATE TABLE USRS_GRPS (Grp VARCHAR(16),  UsrID VARCHAR(16), PRIMARY KEY (Grp, UsrID));
SHOW tables;
CREATE USER apacheBR@localhost IDENTIFIED BY 'some_pass';
GRANT SELECT 
   ON apache.* 
   TO apacheBR@localhost;

CREATE USER maintScript@localhost IDENTIFIED BY 'other_pass';
GRANT SELECT,INSERT,UPDATE,DELETE
   ON apache.* 
   TO maintScript@localhost;
FLUSH PRIVILEGES;
QUIT

Open in new window

1. Populate the database


It is time to test the MySQL User-ID’s and Database we've just created,.  We'll do this by simply adding and selecting a few dummy values.  For this test, we shall insert and query rows with the following keys:
USRS:
  • aTestUser
  • aDeadUser
  • imTheAdmin  
GRPS:
  • Administrator
  • ActiveUser
USRS-GRPS:
  • Administrator + imTheAdmin
  • ActiveUser + aTestUser
Before we start pasting the values into INSERT statements, the USRS entries will require a Password that we will first need to encode (convert) into a suitable format.  Several options are available for this and detailed here, but we’ll use just the suggested SHA scheme for this exercise.  So a couple of:
htpasswd2 –bns <username> <password>

Open in new window

calls should suffice e.g.
# htpasswd2 -bns aTestUser  aPassW0rd
aTestUser:{SHA}z2DI9nDATSY62mOI/GS4kz4VC10=

# htpasswd2 -bns aDeadUser anOthrPW
imTheAdmin:{SHA}2Vwc0dzvxZ8mblHnBQ3E5nWDxcc=

# htpasswd2 -bns imTheAdmin  theAdminPasswd
imTheAdmin:{SHA}+LwfoB15Qfql+EP/WQp9/vzPN9s=

Open in new window

Note: The SuSe supplied script is htpasswd2 not htpasswd

Next, we need to plug the passwords above into some DDL and update the database, via our new maintScript user-id.  At a command prompt type:
# mysql -u maintScript -p'other_pass'
USE apache;
INSERT INTO  USRS (UsrID, Passwd) VALUES ('aTestUser', '{SHA}z2DI9nDATSY62mOI/GS4kz4VC10=');
INSERT INTO  USRS (UsrID, Passwd) VALUES ('aDeadUser', '{SHA}2Vwc0dzvxZ8mblHnBQ3E5nWDxcc=');
INSERT INTO  USRS (UsrID, Passwd) VALUES ('imTheAdmin',  '{SHA}+LwfoB15Qfql+EP/WQp9/vzPN9s=');
INSERT INTO  GRPS (GRP) VALUES ('Administrator');
INSERT INTO  GRPS (GRP) VALUES ('ActiveUser');
INSERT USRS_GRPS (GRP, UsrID) VALUES ('Administrator', 'imTheAdmin');
INSERT USRS_GRPS (GRP, UsrID) VALUES ('ActiveUser', 'aTestUser');
COMMIT;
QUIT;

Open in new window

Next, test the read-only apache user-id by typing;
# mysql -u apacheBR -p'some_pass';
USE apache;
SELECT * from USRS, USRS_GRPS where USRS.UsrID=USRS_GRPS.UsrID and GRP='Administrator';
QUIT:

Open in new window


[STEP title="Prepare Test cases"]
[/STEP]Let’s now create a few directories and files, that we can use to test the Authentication.  At a command prompt type:
# mkdir -p /srv/www/htdocs/RegisterdUser/ActiveMembers
# mkdir -p /srv/www/htdocs/RegisterdUser/AdminOnly
# echo "<HTML><BODY>I'm a Registered user!!!</BODY></HTML>" > /srv/www/htdocs/RegisterdUser/index.html
# echo "<HTML><BODY>I'm an ACTIVE registered user!!!</BODY></HTML>" > /srv/www/htdocs/RegisterdUser/ActiveMembers/index.html
# echo "<HTML><BODY>I'm the Admin :)</BODY></HTML>" > /srv/www/htdocs/RegisterdUser/AdminOnly/index.htm

Open in new window

Then verify that you can get to each URL, via a browser e.g.

  • http://127.0.0.1/RegisterdUser/index.html
  • http://127.0.0.1/RegisterdUser/ActiveMembers/index.html
  • http://127.0.0.1/RegisterdUser/AdminOnly/index.html

Note: My internal / Play Apache Virtual host listens on 127.0.0.1 (localhost).  I'm guessing your Apche host will probably not, so change the IP's in the example URL's, above / below, to suit.
[STEP title="Update the Apache Configuration"]
[/STEP]Next we need to backup the existing HTTPD configuration and add the necessary entries, to enable DBD authentication.  My own test Host is called “Playsite” and its specific configuration resides in /etc/apache2/conf.d /playsite.conf, but you can append the DBD entries below to your own existing *.conf (but not a .htaccess file).   In a console window:
# cd /etc/apache2/conf.d
# cp playsite.conf playsite.conf.OLD

Open in new window

Next, edit the file, in a text editor of your choice, and insert the following:
<IfModule mod_dbd.c>
  DBDriver mysql
  DBDParams "host=127.0.0.1 dbname=apache user=apacheBR pass=some_pass"
  DBDMin 1
  DBDKeep 8
  DBDMax 20
  DBDExptime 300
</IfModule>

Open in new window

Note: the Following can be included in the same *.conf file, which I have done, or appended into a .htaccess file in the respective directory:
<Directory "/srv/www/htdocs/RegisterdUser">
  Options FollowSymLinks Indexes MultiViews
  AuthType Basic
  AuthName "Registered User"
  AuthBasicProvider dbd
  AuthDBDUserPWQuery "SELECT Passwd FROM USRS WHERE UsrID = %s"
  Require valid-user
  AllowOverride None
  Order allow,deny
  Allow from all
</Directory>

<Directory "/srv/www/htdocs/RegisterdUser/ActiveMembers">
  Options FollowSymLinks Indexes MultiViews
  AuthType Basic
  AuthName "Active users Only"
  AuthBasicProvider dbd
  AuthDBDUserPWQuery "SELECT Passwd from USRS, USRS_GRPS where USRS.UsrID=%s AND USRS.UsrID=USRS_GRPS.UsrID"
  Require valid-user
  AllowOverride None
  Order allow,deny
  Allow from all
</Directory>

<Directory "/srv/www/htdocs/RegisterdUser/AdminOnly">
  Options FollowSymLinks Indexes MultiViews
  AuthType Basic
  AuthName "Admin users Only"
  AuthBasicProvider dbd
  AuthDBDUserPWQuery "SELECT Passwd from USRS, USRS_GRPS where USRS.UsrID=%s AND USRS.UsrID=USRS_GRPS.UsrID AND GRP='Administrator'"
  Require valid-user
  AllowOverride None
  Order allow,deny
  Allow from all
</Directory>

Open in new window


Once that is saved, it’s time to test the syntax and re-start apache, so in the console window and as root type:
#  /etc/init.d/apache2 test

Open in new window

Check for / resolve any errors then type:
#  /etc/init.d/apache2 restart

Open in new window

[STEP title="Test the Authentication"][/STEP]
Now in a browser of your choice, try to access the following URL:

  • http://127.0.0.1/RegisterdUser/index.html

You should see a dialog box, asking you for your credentials. If you do enter: aDeadUser / anOthrPW
Else check the Apache ErrorLog for mysql connection errors e.g.
# tail /var/log/apache2/error_log 
... Internal error: DBD: Can't connect to mysql

Open in new window

If an error is present check you can connection to mysql using the credentials in the DBDParams entry e.g.
# mysql -h 127.0.0.1 -u apacheBR -psome_pass

Open in new window

7.1) Once logged in as ‘aDeadUser’ attempt to access the following URL’s:
  • http://127.0.0.1/RegisterdUser/ActiveMembers/index.html
  • http://127.0.0.1/RegisterdUser/AdminOnly/index.html
You should be prompted for your credentials again and see the following appear in the ErrorLog:
[Tue Jun 15 16:11:45 2010] [error] [client 127.0.0.1] user aDeadUser not found: /RegisterdUser/ActiveMembers/index.html
[Tue Jun 15 16:11:51 2010] [error] [client 127.0.0.1] user aDeadUser not found: /RegisterdUser/ActiveMembers/index.html

Open in new window

Close the Browser, re-open it and browse back to:
  • http://127.0.0.1/RegisterdUser/index.html
This time log in as: aTestUser /aPassW0rd
You should now be able to access:
  • http://127.0.0.1/RegisterdUser/ActiveMembers/index.html
but not:
  • http://127.0.0.1/RegisterdUser/AdminOnly/index.html
Again you’ll see the following entry in the ErrorLog:
[Tue Jun 15 16:10:14 2010] [error] [client 127.0.0.1] user aTestUser not found: /RegisterdUser/AdminOnly/index.html

Open in new window

Finally close and re-open the browser and browse back to the first URL and log in as:
    imTheAdmin / theAdminPasswd
You should now be able to browse to all 3 URL’s in turn.
[STEP title="Create a Mechanism to maintain the Database"]
[/STEP]We now have a working, MySQL based, authentication mechanism, but as this solution lacks the command line utilities bundled with Apache to maintain text or .dbm based files, or the existing command line and GUI tools to maintain a  NIS or LDAP (including Windows DC) based schemas we’ll need a mechanism to maintain the database, other than via mysql DDL. So I’ll briefly provide a few skeleton PHP examples to handle:

  •  User Registration – Accessible by anyone
  •  Password Change – Accessible to the User.
  • User / Group membership – Accessible to the Administrators only
  • Group Maintenance  – Accessible to the Administrators only
  • User Reporting and Maintenance – Accessible to the Administrators only

I apologize in advance for the following examples, they're only designed to provide a quick, dirty and not too cryptic hint of what can be achieved in a few lines.

We'll start by creating a  MySQL access function library, this will include the common functionality needed to connect to and manipulate our database.  We'll later copy (included) this library in all our subsequent examples.  In a editor of your choice create the file:
    /srv/www/htdocs/RegisterdUser/AdminOnly/dbConn.inc.php as:
<?php
// Connect to the Apache Authentication DB
$dbhost="localhost";
$username="maintScript";
$password="other_pass";
$database="apache";

mysql_connect($dbhost,$username,$password) or die(mysql_error());
mysql_select_db($database) or die( "Unable to select database: $database".mysql_error());

// ----------------------------
// functions
// ----------------------------

// Insert the New User into the DB
function addUser ($userID, $passWrd){
        $sql = "INSERT INTO USRS (UsrID, Passwd) VALUES ('$userID','{SHA}".base64_encode(sha1($passWrd, TRUE))."')";
        $result = mysql_query($sql) or die("<BR><P>Error:User Creation failed -".mysql_error()."</P>");
        echo "<BR><P>User created:<B>$userID</B></P> ";
        return $result;
}
// update the Users Password
function changePass ($userID, $passWrd){
        $sql="UPDATE USRS set Passwd='{SHA}".base64_encode(sha1($passWrd, TRUE))."' WHERE UsrID='$userID'";
        $result = mysql_query($sql) or die("<BR><P>Error:Update failed -".mysql_error()."</P>");
        echo "<BR><P>Password changed for: <B>$userID</B></P>";
        return $result;
}
// Add a new Group
function addGroup ($grpID){
        $sql = "INSERT INTO GRPS (GRP) VALUES ('$grpID')";
        $result = mysql_query($sql) or die("<BR><P>Error:Group ADD failed -$sql:".mysql_error()."</P>");
        return $result;
}
// Delete a new Group
function deleteGroup ($grpID){
        $sql = "DELETE FROM USRS_GRPS WHERE Grp='$grpID'";
        $result = mysql_query($sql) or die("<BR><P>Error:USRS_GRPS DELETE failed -".mysql_error()."</P>");
        $sql = "DELETE FROM GRPS WHERE Grp='$grpID'";
        $result = mysql_query($sql) or die("<BR><P>Error:Group DELETE failed -".mysql_error()."</P>");
        return $result;
}
// Add a User to a Group
function joinGroup ($userID, $grpID){
        $sql = "INSERT INTO USRS_GRPS (UsrID, Grp) VALUES ('$userID','$grpID')";
        $result = mysql_query($sql) or die("<BR><P>Error:Group join failed -".mysql_error()."</P>");
        return $result;
}
// Remove a User from a Group
function leaveGroup ($userID, $grpID){
        $sql = "DELETE FROM USRS_GRPS WHERE UsrID='$userID' AND Grp= '$grpID'";
        $result = mysql_query($sql) or die("<BR><P>Error:Group exit failed -".mysql_error()."</P>");
        return $result;
}
// Delete a user from the DB
function deleteUser ($userID){
        $sql = " DELETE FROM USRS_GRPS where UsrID='$userID'";
        $result = mysql_query($sql) or die("<BR><P>Error:USRS_GRPS Deletion failed -".mysql_error()."</P>");
        $sql = "DELETE FROM USRS where UsrID='$userID'";
        $result = mysql_query($sql) or die("<BR><P>Error:User Deletion failed -".mysql_error()."</P>");
        return $result;
}
?>

Open in new window

Now let’s create a password change form, so in an editor of your choice create the file:
    /srv/www/htdocs/RegisterdUser/chngPass.php as:
<HTML>
<HEAD><TITLE>Password Change Example</TITLE></HEAD>
<BODY>
<SCRIPT LANGUAGE="JavaScript">
function checkPass(theForm) {
  if (theForm.usrPass.value.length == 0) {
   alert("Enter a new password");
   return false;
  }
  if (theForm.usrPass.value != theForm.chkPass.value) {
   alert("Passwords differ, please try again");
   return false;
  }
  if (theForm.usrPass.value.indexOf(" ") > -1){
   alert("Passwords contains spaces, please try again");
   return false;
  }  
  return true;
}// end function checkPass
</SCRIPT>
<h4>Change your Password:</h4>
<?php function show_form ($usrPass="", $chkPass=""){ ?>
<form name="changePass" onSubmit="return checkPass(this)" action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
<B>User: </B><?php echo htmlspecialchars($_SERVER['REMOTE_USER'])?><br>
New Password: <input type="password" name="usrPass" VALUE="<?php echo $usrPass?>"><br>
Re-Enter New Password: <input type="password" name="chkPass" VALUE="<?php echo $chkPass?>"<br>
<input type="submit" VALUE="Change">
</form>
<?php } //End show_form ()

if (!isset($_POST['usrPass'])) {
        show_form();
}
else {
        if ($_POST['usrPass'] != $_POST['chkPass']) {
                echo "Passwords differ, please try again";
                show_form();
        }
        else {
                // connect to the apache DB
                require("/srv/www/htdocs/RegisterdUser/AdminOnly/dbConn.inc.php");
                // update the Users Password
                echo changePass($_SERVER['REMOTE_USER'], $_POST['usrPass']);
                mysql_close();
        }
}
?>
</BODY>
</HTML>

Open in new window

Then give it a go.  Browser to http://127.0.0.1/RegisterdUser/chngPass.php
A New User Registration form would be handy, this will have to sit outside of the authenticated area, so in an editor create:
    /srv/www/htdocs/regUser.php
<HTML>
<HEAD><TITLE>User Registration Example</TITLE></HEAD>
<BODY>
<SCRIPT LANGUAGE="JavaScript">
function checkFrom(theForm) {
  if (theForm.usrName.value.length == 0) {
   alert("Enter a new Username");
   return false;
  }
  if (theForm.usrPass.value.length == 0) {
   alert("Enter a new password");
   return false;
  }
  if (theForm.usrPass.value != theForm.chkPass.value) {
   alert("Passwords differ, please try again");
   return false;
  }
  if (theForm.usrName.value.indexOf(" ") > -1) {
   alert("UserName contains spaces, please try again");
   return false;
  }
  if (theForm.usrPass.value.indexOf(" ") > -1){
   alert("Passwords contains spaces, please try again");
   return false;
  }
  return true;
}// end function checkPass
</SCRIPT>

<h4>Create a User-ID:</h4>
<?php function show_form ($usrName="", $usrPass="", $chkPass=""){ ?>
<form name="regUser" onSubmit="return checkFrom(this)" action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
User Name: <input type="text" name="usrName" VALUE="<?php echo $usrName?>"><br>
New Password: <input type="password" name="usrPass" VALUE="<?php echo $usrPass?>"><br>
Re-Enter New Password: <input type="password" name="chkPass" VALUE="<?php echo $chkPass?>"><br>
<input type="submit" VALUE="Submit">
</form>
<?php } //End show_form ()

if (!isset($_POST['usrName'])) {
        show_form();
}
else {
        if ($_POST['usrPass'] != $_POST['chkPass']) {
                echo "Passwords differ, please try again";
                show_form();
        }
        else {
                // connect to the apache DB
                require("/srv/www/htdocs/RegisterdUser/AdminOnly/dbConn.inc.php");
                // Insert the New User into the DB
                echo addUser($_POST['usrName'], $_POST['usrPass']);
                mysql_close();
        }
}
?>
</BODY>
</HTML>

Open in new window

Give it a go.  Browse to  http://127.0.0.1/regUser.php
We’ll add a very simple page to display and optionally delete registered users, and any group membership.  Create:
    /srv/www/htdocs/AdminOnly/maintUSRS.php
<HTML>
<HEAD><TITLE>User Maintenance</TITLE></HEAD>
<BODY>
<H1>Registered Users:</H1>
<BR>
<?php function show_form (){
  // Query the Registered users and their groups
  $query="SELECT USRS.UsrID, Grp FROM USRS LEFT JOIN USRS_GRPS ON USRS.UsrID=USRS_GRPS.UsrID";
  $result=mysql_query($query) or die(mysql_error());

  // Print out resulta
  echo '<form name="maintUsr" action="'.$_SERVER['PHP_SELF'].'" method="post">';
  echo "\n<TABLE>\n";
  for($i = 0; $i < mysql_num_fields($result); $i++){
    echo "<th>".htmlspecialchars(mysql_field_name($result, $i))."</th>";
  }
  echo "<th>Delete</th></tr>\n";
  $rows=0;
  while($row = mysql_fetch_array($result)){
    echo "<tr>";
    for($i = 0; $i < mysql_num_fields($result); $i++){
        echo "<td>".htmlspecialchars($row[$i])."</td>";
    }
    $rows++;
    echo "<td><input type=\"checkbox\" name=\"delUsr[]\" value=\"$row[0]\" ></td></tr>\n";
  }
 echo "</TABLE>\n<BR>\n<input type=\"submit\" VALUE=\"Delete\">\n";
 echo "</form>\n<BR>\n<HR>\n";
 echo "<P><B>Your: </B>".$_SERVER['REMOTE_USER']."</p>";
} //End show_form ()

// connect to the apache DB
require("/srv/www/htdocs/RegisterdUser/AdminOnly/dbConn.inc.php");

// Get the array of users to delete
$delList=$_POST['delUsr'];

if (empty($delList)) {
        show_form();
}
else {
        $toDel = count($delList);
        for($i=0; $i < $toDel; $i++) {
          // Delete the user from the DB
          echo deleteUser($delList[$i]);
        }
}
mysql_close();
?>
</BODY>
</HTML>

Open in new window

Give it a go.  Browse to: http://127.0.0.1/RegisterdUser/AdminOnly/maintUSRS.php
Next create a page to display and maintain the GRPS table: /srv/www/htdocs/AdminOnly/maintGRPS.php as:
<HTML>
<HEAD><TITLE>Group Maintenance Example</TITLE></HEAD>
<BODY>

<h4>Group Maintenance</h4>
<u>Existing Groups:</u>
<?php function show_form ($grpID=""){
  // Query the Registered users and their groups
  $query="SELECT Grp FROM GRPS";
  $result=mysql_query($query) or die(mysql_error());

  // Print out the results
  echo '<form name="maintGrp" action="'.$_SERVER['PHP_SELF'].'" method="post">';
  echo "\n<TABLE>\n";
  for($i = 0; $i < mysql_num_fields($result); $i++){
    echo "<th>".htmlspecialchars(mysql_field_name($result, $i))."</th>";
  }
  echo "<th>Delete</th></tr>\n";
  $rows=0;
  while($row = mysql_fetch_array($result)){
    echo "<tr>";
    for($i = 0; $i < mysql_num_fields($result); $i++){
        echo "<td>".htmlspecialchars($row[$i])."</td>";
    }
    $rows++;
    echo "<td><input type='checkbox' name='delGrp[]' value='$row[0]' ></td></tr>\n";
  }
  echo "</TABLE>\n<BR>\nGroup Name: <input type=\"text\" name=\"grpID\" VALUE=\"$grpID\"><input type=\"submit\" VALUE=\"Submit\">\n";
  echo "</form>\n<BR>\n<HR>\n";
  echo "<P><B>Your: </B>".$_SERVER['REMOTE_USER']."</p>";
} //End show_form ()

require("/srv/www/htdocs/RegisterdUser/AdminOnly/dbConn.inc.php");

// Get the array of Groups to delete and any new Group name
$delList=$_POST['delGrp'];
$newGrp=$_POST['grpID'];

if ( ($newGrp == "") && empty($delList)) {
        show_form();
}
else {
        if ($newGrp != ""){
            echo addGroup($newGrp);
        }
        if (!empty($delList)) {
          $toDel = count($delList);
          for($i=0; $i < $toDel; $i++) {
            // Delete the user from the DB
            echo deleteGroup($delList[$i]);
          }
        }
}
mysql_close();
?>
</BODY>
</HTML>

Open in new window

Give it a go.  Browse to: http://127.0.0.1/RegisterdUser/AdminOnly/maintGRPS.php

Finally create a page to maintain the Group membership:
    /srv/www/htdocs/AdminOnly/maintMBRS.php as:
<HTML>
<HEAD><TITLE>Group Membership Maintenance</TITLE></HEAD>
<BODY>
<H1>Membership:</H1>
<BR>
<?php function show_form (){
  // Build the Group Option lists
  $query="SELECT Grp FROM GRPS";
  $result=mysql_query($query) or die(mysql_error());
  $optionList=array();
  while($row = mysql_fetch_array($result)){
         $optionList[]=$row[0];
  }

  // Query the Registered users and their groups
  $query="SELECT USRS.UsrID, Grp FROM USRS LEFT JOIN USRS_GRPS ON USRS.UsrID=USRS_GRPS.UsrID";
  $result=mysql_query($query) or die(mysql_error());

  // Print out the results
  echo '<form name="maintUsr" action="'.$_SERVER['PHP_SELF'].'" method="post">';
  echo "\n<TABLE>\n";
  for($i = 0; $i < mysql_num_fields($result); $i++){
    echo "<th>".htmlspecialchars(mysql_field_name($result, $i))."</th>";
  }
  echo "<th>Delete Membership</th><th>Join Group</th></tr>\n";
  $lastUsr="";
  while($row = mysql_fetch_array($result)){
    if ($lastUsr == $row[0]){
        $theUSR="";
    }
    else {
        $theUSR=htmlspecialchars($row[0]);
    }
    echo "<tr><td>$theUSR</td><td>".htmlspecialchars($row[1])."</td>";
    $lastUsr="$row[0]";
    $delBox="";
    if ($row[1] != ""){
        $delBox="<input type=\"checkbox\" name=\"delUsrGRP[]\" value=\"$row[1] $row[0]\" >";
    }
    echo "<td>$delBox</td><td><select name=\"joinGRP[]\">\n";
    reset($optionList);
    while(list($ind,$grp)=each($optionList)){
        echo "    <option value=\"$row[0] $grp\">$grp</option>\n";
    }
    echo "</select></tr>\n";
  }
 echo "</TABLE>\n<BR>\n<input type=\"submit\" VALUE=\"Update\">\n";
 echo "</form>\n<BR>\n<HR>\n";
 echo "<P><B>Your: </B>".$_SERVER['REMOTE_USER']."</p>";
} //End show_form ()

// connect to the apache DB
require("/srv/www/htdocs/RegisterdUser/AdminOnly/dbConn.inc.php");

// Get the array of users to delete
$delList=$_POST['delUsrGRP'];
$joinList=array();
while(list($ind,$selection)=each($_POST['joinGRP'])){
        $toJoin=explode(" ",$selection);
        if ($toJoin[1] != "") {
          $joinList[]=$selection;
        }
}

if (empty($delList) && empty($joinList)) {
        show_form();
}
else {
        //Delete the marked Associations
        $toDel = count($delList);
        for($i=0; $i < $toDel; $i++) {
          $mbrs=explode(" ",$delList[$i]);
          // Delete the user from the DB
          echo leaveGroup($mbrs[1], $mbrs[0]);
        }
        //Add the Selected Associations
        $toJoin = count($joinList);
        for($i=0; $i < $toJoin; $i++) {
          $mbrs=explode(" ",$joinList[$i]);
          // Delete the user from the DB
          echo joinGroup($mbrs[0], $mbrs[1]);
        }
}
mysql_close();
?>
</BODY>
</HTML>

Open in new window

Give it a go.  Browse to: http://127.0.0.1/RegisterdUser/AdminOnly/maintMBRS.php

[STEP title="Wash Up"]
[/STEP]So we don't forget about / loose these example PHP scripts next add a few links to the /AdminOnly/index.html, e.g. Edit /srv/www/htdocs/RegisterdUser/AdminOnly/index.htm to look something like:
<HTML><BODY>
<H3>Apache DBD MySQL Admin pages</H3>
<ul>
<li><A HREF="maintUSRS.php">User Maintenance</A></li>
<li><A HREF="maintGRPS.php">Group Maintenance</A></li>
<li><A HREF="maintMBRS.php">User / Group Association</A></li>
<li><A HREF="../chngPass.php">Change your password</A></li>
<li><A HREF="../../regUser.php">Register a New user</A></li>
</ul>
</BODY></HTML>

Open in new window


Check it via: http://127.0.0.1/RegisterdUser/AdminOnly/

And that’s all for now.

(C) Andrew Roberts – 2010-06-16
0
Comment
Author:arober11
0 Comments

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Join & Write a Comment

Key to your CPU's ability to stay cool is to use the right amount of thermal paste and apply it correctly. In other words you want as much thermal conductivity between CPU and the cooling block. Use a quality thermal paste and apply it in a manner…
Please check the video also in regards to recovery of deleted emails from office 365 admin center and through the MFCMAPI tool. I have mentioned each and every step with the proper steps that need to be taken care of.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month