Thread: Create a cache DB between web portal and internal DB?
I have a requirement from my security manager but I can't seem to find a good solution. So I wondered if someone had donesomething similar. We have a web portal and a DB in PostgreSQL (obviously) which contains user data. The portal is accessed by account managerswho have access to only specific user accounts. This all works fine however the concern is that if you ever got accessmore directly into the DB through a hack, or poorly designed site code, you could potentially access information thatyou shouldn't. So the idea is that he is floating is we create a cache DB between the portal and the main DB which will only keep the informationcurrently being worked on by the person logged in, and that any inserts/updates/deletes are passed on to the mainDB through additional layers of security. Any ideas? Rick Dearman ----------------------------------------------------------------------- The information in this email is confidential and may be legally privileged. It is intended solely for the addressee(s). Access to this email by anyone else is unauthorised. If you are not the intended recipient, please delete this e-mail and notify Telstra. Any disclosure of the contents or recipients, distribution of, copying of or decisions based on this email by unauthorised persons is prohibited and may be unlawful. Telstra Europe Ltd 3 Finsbury Square London EC2A 1AE
* Dearman, Rick <rick.dearman@teamuk.telstra.com> [1245 05:45]:
> I have a requirement from my security manager but I can't seem to find a good solution.
> So I wondered if someone had done something similar.
>
> We have a web portal and a DB in PostgreSQL (obviously) which contains user data.
> The portal is accessed by account managers who have access to only specific user accounts.
> This all works fine however the concern is that if you ever got access more directly
> into the DB through a hack, or poorly designed site code,
> you could potentially access information that you shouldn't.
>
> So the idea is that he is floating is we create a cache DB between the portal and the
> main DB which will only keep the information currently being worked on by the person logged in,
If I was you, I'd have major concerns and have a chat with the manager in question.
How is that going to help anything - surely the cache DB would have to do a
query to populate itself anyway, which you have to check to gain any security benefit?
You're in a similar boat for updates.
If you're going to check the queries it makes somehow, just do those checks on the queries
you get in the first place.
This sounds like a pain in the arse to implement, maintain and debug with no benefits.
Far better to spend that time cleaning up your application code and implementing a decent backup
policy.
--
'A little rudeness and disrespect can elevate a meaningless interaction
into a battle of wills and add drama to an otherwise dull day.'
-- Calvin discovers Usenet
Rasputin :: Jack of All Trades - Master of Nuns
On Mon, Dec 20, 2004 at 17:21:02 -0000, "Dearman, Rick" <rick.dearman@teamuk.telstra.com> wrote: > I have a requirement from my security manager but I can't seem to find a good solution. So I wondered if someone had donesomething similar. > > We have a web portal and a DB in PostgreSQL (obviously) which contains user data. The portal is accessed by account managerswho have access to only specific user accounts. This all works fine however the concern is that if you ever got accessmore directly into the DB through a hack, or poorly designed site code, you could potentially access information thatyou shouldn't. If the managers access the web site using their DB credentials then you can enforce the proper access in the DB using views and/or security definer functions. If the managers don't have their own DB accounts, then the web application needs to be trusted and should can spend your effort securing that. Implementing a cache inbetween doesn't seem help in solving your problem.
A much better way to do this, assuming that the database login uses the
actual username/password of the manager in question (if not, see if
this can be arranged securely):
Assume you have a table with the restricted information. Call it
real_data:
CREATE TABLE real_data
(
accountID INTEGER,
...
)
Then you have another table storing the user id's of the managers:
CREATE TABLE managers
(
managerUserID TEXT,
managerID SERIAL
)
Now keep one more table, this one mapping which managers may access
which accounts:
CREATE TABLE access_control
(
managerID INTEGER,
accountID INTEGER
)
Rather than granting access to the tables, create a view which the user
software has privileges on:
CREATE VIEW data AS
SELECT * FROM real_data
WHERE accountID IN
(SELECT accountID FROM access_control WHERE managerID =
(SELECT managerID FROM managers WHERE managerUserID =
$CURRENT_USER))
Note: this code is off the top of my head and untested, so it may need
to be tweaked.
Assuming I got all of that right, this should set up a view, which you
would then GRANT privileges on, so that managers would only be able to
see data from the accounts the access_control table says they may see.
You can set up RULEs to permit UPDATEs and DELETEs, if these are
desired.
Note that for the security to work, any such RULEs you create will also
need to check access rights, and you will need to grant access to the
view (data), but *not* to the table (real_data), otherwise this makes
no real difference.
On Dec 21, 2004, at 5:31 AM, Dick Davies wrote:
> * Dearman, Rick <rick.dearman@teamuk.telstra.com> [1245 05:45]:
>> I have a requirement from my security manager but I can't seem to
>> find a good solution.
>> So I wondered if someone had done something similar.
>>
>> We have a web portal and a DB in PostgreSQL (obviously) which
>> contains user data.
>> The portal is accessed by account managers who have access to only
>> specific user accounts.
>> This all works fine however the concern is that if you ever got
>> access more directly
>> into the DB through a hack, or poorly designed site code,
>> you could potentially access information that you shouldn't.
>>
>> So the idea is that he is floating is we create a cache DB between
>> the portal and the
>> main DB which will only keep the information currently being worked
>> on by the person logged in,
>
> If I was you, I'd have major concerns and have a chat with the manager
> in question.
>
> How is that going to help anything - surely the cache DB would have to
> do a
> query to populate itself anyway, which you have to check to gain any
> security benefit?
> You're in a similar boat for updates.
>
> If you're going to check the queries it makes somehow, just do those
> checks on the queries
> you get in the first place.
>
> This sounds like a pain in the arse to implement, maintain and debug
> with no benefits.
> Far better to spend that time cleaning up your application code and
> implementing a decent backup
> policy.
>
> --
> 'A little rudeness and disrespect can elevate a meaningless interaction
> into a battle of wills and add drama to an otherwise dull day.'
> -- Calvin discovers Usenet
> Rasputin :: Jack of All Trades - Master of Nuns
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 8: explain analyze is your friend
>
>
-----------------------------------------------------------
Frank D. Engel, Jr. <fde101@fjrhome.net>
$ ln -s /usr/share/kjvbible /usr/manual
$ true | cat /usr/manual | grep "John 3:16"
John 3:16 For God so loved the world, that he gave his only begotten
Son, that whosoever believeth in him should not perish, but have
everlasting life.
$
___________________________________________________________
$0 Web Hosting with up to 120MB web space, 1000 MB Transfer
10 Personalized POP and Web E-mail Accounts, and much more.
Signup at www.doteasy.com