Thread: General Security-Question
Hi, I am currently developing a software that should replace our hated excel-time- sheets. My problem is the following: I have an javaapplet for dataentry that connects to the database via jdbc. There is a table that holds the data (who worked what when). So far so good, but: Since I must grant update/insert/delete access to this table to everybody that can use this application, how can I stop people from updating the data of the others. The one thing that came to my mind was not creating database- users but instead use a static user, and let the application handle the logic who can access which lines in the database (its also a matter of dataprivacy, one should be allowed to watch one's own data but not of the others, the team- manager should see the data of the team etc). The read-access can be implemen- ted using views but I don't see much other way for data-entry. Somebody has an idea? Konstantin -- Dipl-Inf. Konstantin Agouros aka Elwood Blues. Internet: elwood@agouros.de Otkerstr. 28, 81547 Muenchen, Germany. Tel +49 89 69370185 ---------------------------------------------------------------------------- "Captain, this ship will not sustain the forming of the cosmos." B'Elana Torres
elwood@agouros.de (Konstantinos Agouros) writes: > I am currently developing a software that should replace our hated > excel-timesheets. My problem is the following: I have an > javaapplet for dataentry that connects to the database via > jdbc. There is a table that holds the data (who worked what > when). So far so good, but: Since I must grant update/insert/delete > access to this table to everybody that can use this application, how > can I stop people from updating the data of the others. The one > thing that came to my mind was not creating database-users but > instead use a static user, and let the application handle the logic > who can access which lines in the database (its also a matter of > dataprivacy, one should be allowed to watch one's own data but not > of the others, the team-manager should see the data of the team > etc). The read-access can be implemented using views but I don't > see much other way for data-entry. Somebody has an idea? Honestly, I think the best way to do this is to create a Java class (or classes) that implements all your business logic on the server side, then have the applet make RMI calls into that API. You can pass the applet a random cookie when it's created, and have the applet pass that back as part of the RMI call, and then check in the server logic to see whether the user is trying any funny stuff (like trying to see or modify someone else's data). Make sense? -Doug -- The rain man gave me two cures; he said jump right in, The first was Texas medicine--the second was just railroad gin, And like a fool I mixed them, and it strangled up my mind, Now people just get uglier, and I got no sense of time... --Dylan
On Mon, Jun 18, 2001 at 05:02:45PM -0400, Doug McNaught wrote: > elwood@agouros.de (Konstantinos Agouros) writes: > > > can I stop people from updating the data of the others. The one > > thing that came to my mind was not creating database-users but > > instead use a static user, and let the application handle the logic > > who can access which lines in the database (its also a matter of > > dataprivacy, one should be allowed to watch one's own data but not > > of the others, the team-manager should see the data of the team > > etc). The read-access can be implemented using views but I don't > > see much other way for data-entry. Somebody has an idea? > > Honestly, I think the best way to do this is to create a Java class > (or classes) that implements all your business logic on the server > side, then have the applet make RMI calls into that API. You can pass > the applet a random cookie when it's created, and have the applet pass > that back as part of the RMI call, and then check in the server logic > to see whether the user is trying any funny stuff (like trying to see > or modify someone else's data). > > Make sense? That way I could bind the postgres-master zu 127.0.0.1 that might work though. My Problem is, that if I would use a regular applet/jdbc-connection the post- master has to listen on the real network address and if I create normal db- accounts, everybody could call psql and that is what I want to avoid. But I guess I go with the Static-ID-Part since it is easier to implement \:) Konstantin -- Dipl-Inf. Konstantin Agouros aka Elwood Blues. Internet: elwood@agouros.de Otkerstr. 28, 81547 Muenchen, Germany. Tel +49 89 69370185 ---------------------------------------------------------------------------- "Captain, this ship will not sustain the forming of the cosmos." B'Elana Torres
Konstantinos Agouros <elwood@agouros.de> writes: > On Mon, Jun 18, 2001 at 05:02:45PM -0400, Doug McNaught wrote: > > Honestly, I think the best way to do this is to create a Java class > > (or classes) that implements all your business logic on the server > > side, then have the applet make RMI calls into that API. You can pass > > the applet a random cookie when it's created, and have the applet pass > > that back as part of the RMI call, and then check in the server logic > > to see whether the user is trying any funny stuff (like trying to see > > or modify someone else's data). > > > > Make sense? > That way I could bind the postgres-master zu 127.0.0.1 that might > work though. My Problem is, that if I would use a regular > applet/jdbc-connection the postmaster has to listen on the real > network address and if I create normal dbaccounts, everybody could > call psql and that is what I want to avoid. But I guess I go with > the Static-ID-Part since it is easier to implement \:) I'm not totally sure what you mean by the above, but good luck... ;) -Doug -- The rain man gave me two cures; he said jump right in, The first was Texas medicine--the second was just railroad gin, And like a fool I mixed them, and it strangled up my mind, Now people just get uglier, and I got no sense of time... --Dylan
elwood@agouros.de (Konstantinos Agouros) writes:
> Since I must grant update/insert/delete access to this table to everybody
> that can use this application, how can I stop people from updating the data
> of the others.
Triggers that compare current_user to the userid column of the table,
perhaps?
You might well be better off with the other solution (one database
userid, enforce restrictions in application), however. If most of the
people involved here have no reason to be accessing the database for
other purposes, I'd be inclined not to set up a database userid for each
of them.
regards, tom lane
If you're going to prevent people from entering other people's accounts, you're going to have to give them a way to get into their own - so you're going to have to manage passwords or connect to something that manages them for you. If these users have system IDs (on the platform you're running postgresql on) than perhaps a C or Java function could authenticate them against that. There should be code you can beg borrow or steal that talks to the authentication they already use. Hopefully. On Mon, Jun 18, 2001 at 10:26:22PM +0200, Konstantinos Agouros wrote: > Hi, > > I am currently developing a software that should replace our hated excel-time- > sheets. My problem is the following: I have an javaapplet for dataentry that > connects to the database via jdbc. There is a table that holds the data (who > worked what when). So far so good, but: > Since I must grant update/insert/delete access to this table to everybody > that can use this application, how can I stop people from updating the data > of the others. The one thing that came to my mind was not creating database- > users but instead use a static user, and let the application handle the logic > who can access which lines in the database (its also a matter of dataprivacy, > one should be allowed to watch one's own data but not of the others, the team- > manager should see the data of the team etc). The read-access can be implemen- > ted using views but I don't see much other way for data-entry. Somebody has an > idea? > > Konstantin > -- > Dipl-Inf. Konstantin Agouros aka Elwood Blues. Internet: elwood@agouros.de > Otkerstr. 28, 81547 Muenchen, Germany. Tel +49 89 69370185 > ---------------------------------------------------------------------------- > "Captain, this ship will not sustain the forming of the cosmos." B'Elana Torres > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly