Re: Granting a role to a session ?

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Fri, 02 Jul 2004 22:11:18 -0700
Message-ID: <1088831511.886561@yasure>

Paul Brewer wrote:

> "Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message
> news:1088688485.804450_at_yasure...
> 

>>Richard Elliott wrote:
>>
>>
>>>I want to grant an update role to a user when they connect from a
>>>client app.. This is so I can set up users in Oracle with no update
>>>privileges due to the same users using ODBC to run queries. I want the
>>>users to only be able to update the data if they are running a client
>>>that has row level security code in it. If I grant the update role to
>>>the user in the client, what keeps them from running the client, then
>>>connecting ODBC and running an update.
>>>
>>>What I want is to be able to alter the session, not the user. Is this
>>>possible ?
>>>I don't see it in my books. Of course if I'm totally off track, some
>>>direction would be much appreciated. I read in other post where this
>>>was the preferred method, but see a large hole in it as I understand
>>>it.
>>>
>>>Thanks in advance for the help.
>>
>>Use an AFTER-LOGON trigger
>>http://www.psoug.org/reference/ddl_trigger.html
>>and session information about the tool being used to connect to
>>grant the privilege on-the-fly.
>>

> 
> One snag with that approach is I can just rename sqlplus.exe to
> my_official_app.exe. OP might be better off looking at implementing row
> level security in the database, or doing updates via stored
> procedures/packages. Then the DBA doesn't need to worry what tool clients
> are using to connect.
> Alternatively there's the old solution of password protected roles.
> I must say though that in my experience this all becomes somewhat less
> relevant these days, what with everything new being three tier now, rather
> than client/server.
> 
> Regards,
> Paul

One solution to the snag. If they don't log on with the correct tool ... terminate the session.

Problem solved!

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)

Received on Sat Jul 03 2004 - 00:11:18 CDT