Hi Roger,
> With that authority, the stored procedure will place job
information, a processing code and the data to insert/update on to a
generic queue.
Okay, I think this might be where I misunderstood you. I thought the
TRIGGER (not the stored procedure) was to put the data on the data queue.
I thought your original problem was that the trigger couldn't open the
file (because it lacked adopted authority) and therefore the trigger was
going to put an entry on a data queue, and let the background job write
to the file. In this scenario, accessing the data queue becomes
equivalent to accessing the file -- since anyone who can write to the
data queue can also write to the file -- and therefore the data queue
has to be locked down at the same level as the file. If that were the
case, the trigger couldn't write to the data queue anymore than it could
write to the file, and you'd be back to square one.
However, I now understand that you won't be writing to the queue from
the trigger. Instead, you'll write to the queue from the stored
procedure directly. Since the stored procedure CAN adopt authority,
there's no problem.
Though, if you're going to write the queue directly from the stored
procedure, one must wonder why you couldn't write to the database file
directly from the stored procedure. (Instead of writing from the trigger
where adoption doesn't work.) So I guess there's something that's
unclear in my mind...
If I was bright enough to write a generic stored procedure that would
work for a variety of files, I can see how it might provide unacceptable
access to the database.
No, I didn't think you were going to make it generic enough to work on
any file. That never even crossed my mind.
My point was very simple: If access to the data queue provides the same
function as access to the file, then the data queue must be secured the
same way as the file. Otherwise, whichever one is protected with less
security is the weak link in the chain. (And a chain is only as strong
as it's weakest link.)
LMTUSR won't know the data queue name, won't have access to the library
> it is in even if LMTUSR did know the name, and the queue doesn't
> answer requests for data. At my level of understanding, it appears
> that LMTUSR can only see the data we explicitly allow. LMTUSR cannot
> see data the stored procedure does not explicitly include in the
> return values.
Securing the object by hiding the data queue name from the user is
"security by obscurity". In that case there's nothing physically
preventing the user from accessing the data, it's just a lack of
knowledge on the user's part. If that's an acceptable risk to your
company, then you're fine. Many organizations don't believe in security
by obscurity, however.
You say that the user won't have access to the library that the data
queue is in, which leads us back to square 1. The trigger won't be able
to update it, because the user doesn't have authority to it. If you
move the "add to queue" operation to the program that can adopt
authority, then you may as well have movied the "add to file" operation
to a program that can adopt authority, since the queue and the file are
basically equivalent. If you grant the user access to the queue, then
the user DOES have access to the queue, so you're back to security by
obscurity.
Does that make sense? Obviously, I'm not in your shoes, and I don't
know very much about the business task at hand or the client, or what's
considered an "acceptable risk'. I just recognized the pattern of an
"equivalent object" to the secured one, and with that it mind, came up
with a reply...
midrange.com
