You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@struts.apache.org by Dan Allen <da...@mojavelinux.com> on 2003/03/09 20:14:14 UTC
[OT] JDBCRealm question
This is a simple one, pardon the request for advice.
In the specs for JDBCRealm it talks about a username column being in
the users table and in the user_role table. Does this mean that the
username column has to be the primary key in the users table and not
a user_id?? I thought it was bad design to make a text field a
primary key since it means that it would have to be updated in two
places if the user changed usernames, or are we sticking with the
requirment that users can't change usernames or that it is an
uncommon task?
Dan
--
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Daniel Allen, <da...@mojavelinux.com>
http://www.mojavelinux.com/
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
[Frodo]: "He deserves death."
[Gandalf]: "Deserves it! I daresay he does. Many that live
deserve death. And some that die deserve life. Can you give
it to them? Then do not be too eager to deal out death in
judgement. For even the very wise cannot see all ends."
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
---------------------------------------------------------------------
To unsubscribe, e-mail: struts-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: struts-user-help@jakarta.apache.org
Re: [OT] JDBCRealm question
Posted by Sloan Seaman <sl...@sgi.net>.
I had the same issue and from what I could find, you had to stick to using a
text field.
I ended up writing my own security system that was just an extension off of
the ideas supported by Struts. It only takes a few hours to do..
Just put a User "bean" in the session when the person logs on and then write
your own isUserInRole() tag to check the bean in memory...
Pretty simple...
----- Original Message -----
From: "Dan Allen" <da...@mojavelinux.com>
To: "Struts-User List" <st...@jakarta.apache.org>
Sent: Sunday, March 09, 2003 2:14 PM
Subject: [OT] JDBCRealm question
> This is a simple one, pardon the request for advice.
>
> In the specs for JDBCRealm it talks about a username column being in
> the users table and in the user_role table. Does this mean that the
> username column has to be the primary key in the users table and not
> a user_id?? I thought it was bad design to make a text field a
> primary key since it means that it would have to be updated in two
> places if the user changed usernames, or are we sticking with the
> requirment that users can't change usernames or that it is an
> uncommon task?
>
> Dan
>
> --
> - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
> Daniel Allen, <da...@mojavelinux.com>
> http://www.mojavelinux.com/
> - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
> [Frodo]: "He deserves death."
> [Gandalf]: "Deserves it! I daresay he does. Many that live
> deserve death. And some that die deserve life. Can you give
> it to them? Then do not be too eager to deal out death in
> judgement. For even the very wise cannot see all ends."
> - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: struts-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: struts-user-help@jakarta.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: struts-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: struts-user-help@jakarta.apache.org
Re: [OT] JDBCRealm question
Posted by Dan Allen <da...@mojavelinux.com>.
> This whole thing is really a TOMCAT-USER issue, but there's lots of Tomcat
> users here.
>
> JDBCRealm does *not* require that the username be the primary key -- you
> can still use an autogenerated sequence number (or whatever) for that
> purpose. You'll want to ensure that the username column is indexed,
> however, for faster performance.
>
> If you're using a USER_ID column as the primary key for your users and
> users_roles tables, the easiest thing to do is create a view...
I just received an e-mail mirroring this solution...and I was afraid
of it being that, since MySQL (my current RDBMS) does not support
views. The nice part is, now I understand why it should, SOON!
Anyway, I can just update the non-normalized data manually and it
will work. Don't get me wrong, I realize that you can work yourself
into a nice configuration. I just thought it would have been more
natural had the behind the scenes query been a join on the user_id
rather than the username. Either way, it will definitely work out.
Dan
--
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Daniel Allen, <da...@mojavelinux.com>
http://www.mojavelinux.com/
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
It is not enough to succeed. Others must fail.
-- Gore Vidal
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
---------------------------------------------------------------------
To unsubscribe, e-mail: struts-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: struts-user-help@jakarta.apache.org
Re: [OT] JDBCRealm question
Posted by "Craig R. McClanahan" <cr...@apache.org>.
> Dan Allen wrote:
>
> >This is a simple one, pardon the request for advice.
> >
> >In the specs for JDBCRealm it talks about a username column being in
> >the users table and in the user_role table. Does this mean that the
> >username column has to be the primary key in the users table and not
> >a user_id?? I thought it was bad design to make a text field a
> >primary key since it means that it would have to be updated in two
> >places if the user changed usernames, or are we sticking with the
> >requirment that users can't change usernames or that it is an
> >uncommon task?
> >
This whole thing is really a TOMCAT-USER issue, but there's lots of Tomcat
users here.
JDBCRealm does *not* require that the username be the primary key -- you
can still use an autogenerated sequence number (or whatever) for that
purpose. You'll want to ensure that the username column is indexed,
however, for faster performance.
If you're using a USER_ID column as the primary key for your users and
users_roles tables, the easiest thing to do is create a view that includes
the columns you need for Tomcat's purpose. Consider the scenario where
you've got fully normalized tables like this (datatypes modified as
needed for your database):
Table USERS:
USER_ID Primary key, auto-generated sequence number
USER_NAME String
Table ROLES:
ROLE_ID Primary key, auto-generated sequence number
ROLE_NAME String
Table USERS_ROLES:
USER_ID
ROLE_ID
Then you can create a simulation of what Tomcat needs with something like
this (assuming you've got a database that supports views):
create view users_roles_view as
select u.user_name, r.role_name
from users u, roles r, users_roles ur
where (ur.user_id = u.user_id) and
(ur.role_id = r.role_id);
Now, your administrative apps can continue to use USER_ID and ROLE_ID as
the primary key (in the usual way), allowing changes in the actual user
name and role name values, but Tomcat is still happy with the
USERS_ROLES_VIEW for matching users and roles by name.
> >Dan
Craig
---------------------------------------------------------------------
To unsubscribe, e-mail: struts-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: struts-user-help@jakarta.apache.org
Re: [OT] JDBCRealm question
Posted by Eddie Bush <ek...@swbell.net>.
I do realize it takes additonal processor and time to do (an extra ten
seconds, perhaps), but, assuming you're using a database which supports
triggers, it really doesn't take long to have all of the maintenance
associated with adding users and roles be automated. So far as changing
username goes, I don't see why you couldn't. You would, of course, want
to ensure everything stayed in sync, but triggers really are a useful
tool for doing things such as this.
Username being a primary key demands that it be unique. Referrential
integrity demands that, when used as a foreign key, the value of the
field be the same as where it is the primary key. Neither of these two
constraints demand that username be unchanging. Just allow updates to
cascade when you create the table and you shouldn't have to contend with
any RI issues.
Dan Allen wrote:
>This is a simple one, pardon the request for advice.
>
>In the specs for JDBCRealm it talks about a username column being in
>the users table and in the user_role table. Does this mean that the
>username column has to be the primary key in the users table and not
>a user_id?? I thought it was bad design to make a text field a
>primary key since it means that it would have to be updated in two
>places if the user changed usernames, or are we sticking with the
>requirment that users can't change usernames or that it is an
>uncommon task?
>
>Dan
>
--
Eddie Bush
---------------------------------------------------------------------
To unsubscribe, e-mail: struts-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: struts-user-help@jakarta.apache.org
SV: [OT] JDBCRealm question
Posted by Søren Blidorf <so...@nolas.dk>.
Yes, username is primary key in both
-----Oprindelig meddelelse-----
Fra: Dan Allen [mailto:dan@mojavelinux.com]
Sendt: 9. marts 2003 20:14
Til: Struts-User List
Emne: [OT] JDBCRealm question
This is a simple one, pardon the request for advice.
In the specs for JDBCRealm it talks about a username column being in
the users table and in the user_role table. Does this mean that the
username column has to be the primary key in the users table and not
a user_id?? I thought it was bad design to make a text field a
primary key since it means that it would have to be updated in two
places if the user changed usernames, or are we sticking with the
requirment that users can't change usernames or that it is an
uncommon task?
Dan
--
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Daniel Allen, <da...@mojavelinux.com>
http://www.mojavelinux.com/
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
[Frodo]: "He deserves death."
[Gandalf]: "Deserves it! I daresay he does. Many that live
deserve death. And some that die deserve life. Can you give
it to them? Then do not be too eager to deal out death in
judgement. For even the very wise cannot see all ends."
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
---------------------------------------------------------------------
To unsubscribe, e-mail: struts-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: struts-user-help@jakarta.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: struts-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: struts-user-help@jakarta.apache.org