0

We have multiple sources from where transactions are coming into the database. All channels are using a common db user, which are getting logged into the database. I would like to segregate all source channels with different user id.

However we don't want to create a separate db user for same. Is there any way to create alias of existing database user so that same can be used?

The objective to identify from which channels connections are coming.

  • Ref: https://stackoverflow.com/questions/9381638/oracle-db-user-alias – Vebbie Feb 05 '19 at 10:04
  • How about proxy user? https://oracle-base.com/articles/misc/proxy-users-and-connect-through – Ted at ORCL.Pro Feb 05 '19 at 10:08
  • Do you want to be able to identify which channel a given user is connecting through? – APC Feb 05 '19 at 10:20
  • @APC Yes...thats the objective to identify from which channels connections are coming. in current scenario, all connections are getting logged into db using one user. –  Feb 05 '19 at 12:21
  • @TedatORCL.Pro Have tried using "alter user scott grant connect through midtier;" but seems to be having privileges issue. Then its falling to same process of giving all rights to new user as same as earlier user. –  Feb 05 '19 at 12:25
  • 2
    *"the objective to identify from which channels connections are coming."* That's the sort of pertinent detail you should include in the question. It prevents people wasting their time providing solutions which won't help you. For instance, proxy users are just a way of connecting to the same user with a different set of credentials so probably not what you're looking for, – APC Feb 05 '19 at 13:18
  • This may or may not work, which is why I'm posting it as a comment rather than an answer. You could try creating different schemas for each "channel", and then develop a trigger on login (perhaps based on OSUSER or MACHINE from v$session?) that issues an `alter session set current_schema=X` command. E.G. If a connection comes from Channel 1, the trigger runs on successful login and alters the session to run under a specific schema that you can associate with that channel. Please disregard if this doesn't quite meet your requirements. – 1991DBA Feb 05 '19 at 15:08
  • @APC I think the question very well clarified...anyway thanks for revert...it did not help what i am looking for. –  Feb 07 '19 at 07:42
  • @cdb_dba I find this is bit complex than the creating new user and provided all privileges as existing user has. Many Thanks for revert... –  Feb 07 '19 at 07:44

0 Answers0