查询分配角色(重新发布) [英] Query for assigning roles (repost)

问题描述

我有三个桌子


用户:
USERID(1,2)，USERNAME(ALI，USER)，PASSWORD(1B23C)

组:
GROUPID(1,2)，GROUPNAME(ADMINISTRATOR，USER)

角色:
USERID(1，2)，GROUPID(1,2)

所以我想为表user
中具有用户名的用户分配一个角色
将角色分配给新用户的查询将是什么.我很困惑.我们只需插入用户名并将角色分配给该用户名.如何对userid和groupid的值进行管理?

请任何人帮助我.

此外，是否可以创建一个具有用户名，密码和角色值的表的替代项?这有什么缺点吗?

(删除的喊叫声和语速不佳的人说话)

解决方案

如果我正确理解了您的问题，则表名令人困惑，我认为这部分是您尚未拥有的原因一个回应.如果您可以将Roles重命名为UserGroups，或者更好地将Roles重命名为UserRoles，并且将Groups重命名为Roles，那么这将提高表的可理解性，目前看来您想要能够将用户分配给组和角色(很多人实际上想做的事情).

在进行更改之前，SQL将类似于:

  INSERT   INTO 
  角色(UserId，GroupId)
选择
   用户身份，
   群组编号
 FROM 
  用户，
  团体
位置
  UserName = '  Ali'
 AND 
  GroupName = ' 管理员' 

您应该对该查询进行参数化，以避免SQL注入 [ ^ ]，因此"Ali"和"Administrators"成为参数.

至于将其汇总到一张桌子中:不要.正如Christain在他的[已删除]回复中所说，这将导致您只能为每个用户分配一个角色，这几乎肯定不是必需的，因为用户通常具有多个角色.

I have three tables


USERS:
USERID(1,2), USERNAME(ALI, USER) , PASSWORD(1B23C)

GROUPS:
GROUPID(1,2) , GROUPNAME(ADMINISTRATOR,USER)

ROLES:
USERID(1 ,2), GROUPID(1,2)

So I want to assign a role to a user having username in table user

What will be the query to assign the role to the new user. I am confused. We just insert the username and assign role to that user name. How are the userid and groupid managed in their values ?

Please anyone help me.

Moreover, can I create an alternative of one table having the values username, password and role ? Is there any drawback to this ?

(removed shouting and retarded text speak)

解决方案

If I understand your question correctly, your table names are confusing, I think this is partially why you haven''t had a response. If you can re-name Roles to UserGroups, or better yet rename Roles to UserRoles and Groups to Roles then this will improve the understandibility of your tables, it current looks like you want to be able to assign users to both groups and roles (which is something a lot of people actually want to do).

Before you make the changes, the SQL will be something like:

INSERT INTO 
  ROLES (UserId,GroupId)
SELECT 
   UserId,
   GroupId
FROM 
  Users,
  Groups
WHERE
  UserName= 'Ali'
AND
  GroupName='Administrator'

You should parameterise this query to avoid SQL injection[^] so ''Ali'' and ''Administrators'' become parameters.

As for rolling it up into one table: don''t. As Christain said in his [deleted] reply it will result in you only being able to assign one role to each user, this is almost certainly not what is needed as users normally have multiple roles.

这篇关于查询分配角色(重新发布)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！