Set user id for Oracle of logged in user of Rails application

[mikev]

We have triggers need to get the user by doing something like 'select
user from dual'. Instead of having this return the user id specified
in database.yml is there a way to set the user id seen by Oracle to
the id of the person logged into the Rails application?

[Raimonds Simanovskis]

Just wanted to mention that if you need to populate user id in some
tables from your Rails application then I would recommend to use
before / after save callbacks and not to use database triggers - it is
always easier if all business logic is in one place and in my opinion
Ruby is much better than PL/SQL for programming business logic.

But if for some reason you need to use database triggers (e.g. you are
accessing legacy schema where you cannot directly update from Rails)
then you can do the following
- Create / use some PL/SQL package and define global variable in this
package which will store current user id. Create some procedure in
this package which will be used for setting current user id.
- During Rails request processing call this PL/SQL package procedure
to set current user id (use ruby-plsql gem for calling PL/SQL package
procedure)
- In database trigger query PL/SQL package global variable to get
current user id

We use this approach with Oracle E-Business Suite to initialize
current database session with current Oracle E-Business Suite user id
so that API procedures would correctly set created by and updated by
table fields.

Raimonds

[mikev]

The stored procedures are doing auditing plus I'd like to have the
Oracle VPD features work and this requires we set 'USER' inside Oracle
(select USER from dual;) I've started by setting the
client_identifier in the session, as described at:
http://download.oracle.com/docs/cd/B13789_01/network.101/b10773/apdvprxy.htm#1010357.
See code below which I've put as a before filter on my
application_controller.rb

def set_oracle_session_identifier
plsql.activerecord_class = ActiveRecord::Base
plsql.dbms_session.set_identifier(current_user.login)
end

The stored procedures would then do the following to either select the
client_identifier if it was specified or the 'USER'.

select decode(sys_context
('userenv','client_identifier'),NULL,USER,sys_context
('userenv','client_identifier')) FROM DUAL

This works but doesn't seem very secure so I'd like to use proxy
authentication, as described at:
http://download.oracle.com/docs/cd/B13789_01/network.101/b10773/apdvprxy.htm#1010289.
How would I make the openProxySession call to switch my connection to
the logged on user if using the Oracle Enhanced Adapter? Could I
simply put this openProxySession call as a before filter on
application_controller or are there complexities to using proxy
authentication combined with Rails and the enhanced adapter?

[Raimonds Simanovskis]

At first I wanted to tell that you need to put
plsql.activerecord_class = ActiveRecord::Base
in some Rails initializer file - this should be set up just once. Then
you can use plsql.... calls in your controllers / models and it will
use current ActiveRecord connection.

I haven't used proxy authentication and I think that it will not work
very well with Rails application model where one database session is
used for many different user requests. And what are your concerns
regarding security? End users can access database just through your
Rails application and therefore I think it is OK to implement all
security controls in Rails application. And if someone can manage to
change Rails application code then no database proxy authentication
will help as in this case "the bad guy" could remove it from
application code :)

Raimonds

On Nov 10, 11:21 pm, mikev <globallyuni...@gmail.com> wrote:
> The stored procedures are doing auditing plus I'd like to have the
> Oracle VPD features work and this requires we set 'USER' inside Oracle
> (select USER from dual;)  I've started by setting the

> client_identifier in the session, as described at:http://download.oracle.com/docs/cd/B13789_01/network.101/b10773/apdvp....

> See code below which I've put as a before filter on my
> application_controller.rb
>
>   def set_oracle_session_identifier
>     plsql.activerecord_class = ActiveRecord::Base
>     plsql.dbms_session.set_identifier(current_user.login)
>   end
>
> The stored procedures would then do the following to either select the
> client_identifier if it was specified or the 'USER'.
>
> select decode(sys_context
> ('userenv','client_identifier'),NULL,USER,sys_context
> ('userenv','client_identifier')) FROM DUAL
>
> This works but doesn't seem very secure so I'd like to use proxy

> authentication, as described at:http://download.oracle.com/docs/cd/B13789_01/network.101/b10773/apdvp....

[mikev]

I'll move the plsql.activerecord_class = ActiveRecord::Base assignment
to an initializer. Thanks for the correction.

re: Security concerns - I'm no security expert. I just want to
implement something that the corporate security and DBA guys will
approve. Rails is only one of the ways our database is accessed,
others include BusinessObjects and SAS SQLPassThrough. So we need a
consistent way to enforce security and to create audit trails.
Security has been accomplished via Oracle VPD. It looked like if I
could figure out how to make the JDBC openProxySession call to change
the user that security would work easily inside of rails and would
allow all the standard oracle security to work, especially the Oracle
VPD features. My understanding is that via openProxySession I can
change the connection to be for a different user than what is in
database.yml and that all database operations would then be done as
that user. This doesn't seem any harder or lower performance than
setting the user into 'client_identifier'. Using 'client_identifier'
seems like a variation on the global variable in a procedure package
you recommended. Won't openProxySession work with Rails pooled
connections and be 'more secure'?

Is there a way to call openProxySession via the
oracle_enhanced_adapter?

[Raimonds Simanovskis]

I'm afraid that as I do not have need for that and as I have not
investigated this proxy authentication and will not spend time on this
in nearest future.
But if someone can investigate and make a patch than that would be
welcome.

Raimonds