如何检查mysql数据库是否是用户的一部分 [英] How to check mysql db is user is part of a group

问题描述

这可能是一个简单的问题，但是我有一个登录页面，我希望用户在该页面上根据LDAP进行身份验证，并且工作正常.

This might be a simple question but I have a login page where I want users to authenticate against LDAP and that's working fine.

我创建了一个mysql数据库，在其中创建了一个具有用户登录名的表，并希望在成功登录后进行检查，以在数据库中验证试图进行身份验证的用户是否属于Administrator组(在数据库表中定义) ).

I created a mysql database where I created a table with the user's logon name and would like a check after the successful logon to verify in my DB that the user trying to authenticate is part of the Administrator group (defined in my database table).

如果这是有效的，则将它们重定向到url1，否则将它们重定向到URL2.此时，我只需要SQL查询部分的帮助，因为我不太熟悉它.

If this is valid then redirect them to url1 and if not redirect them to URL2. At this point I just need help with the SQL query portion as im not too familiar with it.

Localhost
DB name=imc.directory.tool
Table name=tbl_staff
------------------------------------------------
| ID    | username           | group            |
------------------------------------------------
| 1     | username1          | Administrator    |
------------------------------------------------
| 2     | username2          | Guest            |
------------------------------------------------
| 3     | username3          | Guest            |
------------------------------------------------

推荐答案

您可以在任何SQL语句以及您选择的API中使用以下内容.

You can use the following inside any SQL statement, and the API of your choosing.

SELECT ID FROM tbl_staff 
WHERE username='username1' 
AND `group`='Administrator'

或(旁注:column_x，column_y是列示例名称):

or (sidenote: column_x, column_y are column example names):

SELECT column_x, column_y FROM tbl_staff 
WHERE username='username1' 
AND `group`='Administrator'

您还可以执行SELECT *选择所有列，但这通常是许多SQL开发人员不喜欢使用的方法.

You can also do SELECT * to select all columns, but that is often a method many SQL developers do not like to use.

• 以下是有关该主题的文章:为什么SELECT *被认为有害?

但是，您确实说过这是针对ASP.net的，对此我一无所知.

However, you did say this was for ASP.net which is something I do not know anything about.

边注: group是

Sidenote: group is a MySQL reserved word which requires special attention.

要么将单词包装在刻度中，要么使用其他名称(例如组").

Either by wrapping the word in ticks, or using another name such as "groups" for instance.

以下是使用准备好的语句的PDO方法:

Here is a PDO method using prepared statements:

$db = new PDO("mysql:host=localhost;dbname=db_name", $user, $pass);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

$username = "username1";
$group = "Administrator";

$query = "SELECT COUNT(*) FROM tbl_staff 
         WHERE username = :username 
         AND `group` = :username";

$statement = $db->prepare($query);
$statement->bindValue(':username', $username);
$statement->bindValue(':password', $group);
$statement->execute();
$count = $statement->fetchColumn();
if ($count === 1)
{
    return TRUE;
}
else 
{
    return FALSE;   
}

---

参考文献:

---

References:

• http://php.net/manual/en/book.pdo.php
• http://php.net/pdostatement.fetchcolumn.php
• http://dev.mysql.com/doc/refman/5.1/en/counting-rows.html

• http://php.net/manual/en/book.pdo.php
• http://php.net/pdostatement.fetchcolumn.php
• http://dev.mysql.com/doc/refman/5.1/en/counting-rows.html

这篇关于如何检查mysql数据库是否是用户的一部分的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！