Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> Re: Granting a role to a session ?
"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
Received on Thu Jul 01 2004 - 13:38:44 CDT
![]() |
![]() |