| Computers Forum Index » Computer - Databases - Oracle (Server) » How to trigger which program updated a table ?... |
|
Page 1 of 1 |
|
| Author |
Message |
| Maija-Leena... |
 Posted: Wed Sep 23, 2009 10:50 am |
|
|
|
Guest
|
Hi,
I need to log some updates and I would like to do it using a trigger which
inserts a line to a logtable. I need to know which application (sqlplus,
java, toad etc) was used to make the update. I've studied sys_context but
didn't find that information. Toad's Session Browser shows programs that are
connected to the database, so I think it's possible. Do You know how ?
Thanks !
Maija-Leena |
|
|
| Back to top |
|
|
|
| yossarian... |
| Posted: Wed Sep 23, 2009 11:25 am |
|
|
|
Guest
|
Maija-Leena wrote:
Quote: I need to log some updates and I would like to do it using a trigger which
inserts a line to a logtable. I need to know which application (sqlplus,
java, toad etc) was used to make the update. I've studied sys_context but
didn't find that information. Toad's Session Browser shows programs that are
connected to the database, so I think it's possible. Do You know how ?
dbms_application_info.READ_module(:MODULE_NAME,:ACTION_NAME) works for
sqlplus and popular Oracle tools like Toad I believe, but for custom
Java applications you must hardcode a call to
dbms_application_info.SET_MODULE at the beginning of the session.
Y. |
|
|
| Back to top |
|
|
|
| Mladen Gogala... |
| Posted: Wed Sep 23, 2009 12:44 pm |
|
|
|
Guest
|
Na Wed, 23 Sep 2009 06:50:07 +0000, Maija-Leena napisao:
Quote: Hi,
I need to log some updates and I would like to do it using a trigger
which inserts a line to a logtable. I need to know which application
(sqlplus, java, toad etc) was used to make the update.
Application sensitive DML, what a novel idea! So, your business rules are
application sensitive? Might I inquire what business is your company in?
--
http://mgogala.freehostia.com |
|
|
| Back to top |
|
|
|
| gazzag... |
| Posted: Wed Sep 23, 2009 1:02 pm |
|
|
|
Guest
|
On 23 Sep, 07:50, "Maija-Leena" <kangasm... at (no spam) netti.fi.removethis>
wrote:
Quote: Hi,
I need to log some updates and I would like to do it using a trigger which
inserts a line to a logtable. I need to know which application (sqlplus,
java, toad etc) was used to make the update. I've studied sys_context but
didn't find that information. Toad's Session Browser shows programs that are
connected to the database, so I think it's possible. Do You know how ?
Thanks !
Maija-Leena
Oacle version?
Some ideas:
- AFTER LOGON ON DATABASE trigger and V$SESSION view.
- Oracle Fine Grain Auditing (may be overkill depending on your
requirement)
HTH
-g |
|
|
| Back to top |
|
|
|
| joel garry... |
| Posted: Wed Sep 23, 2009 4:01 pm |
|
|
|
Guest
|
On Sep 22, 11:50 pm, "Maija-Leena" <kangasm... at (no spam) netti.fi.removethis>
wrote:
Quote: Hi,
I need to log some updates and I would like to do it using a trigger which
inserts a line to a logtable. I need to know which application (sqlplus,
java, toad etc) was used to make the update. I've studied sys_context but
didn't find that information. Toad's Session Browser shows programs that are
connected to the database, so I think it's possible. Do You know how ?
Thanks !
Maija-Leena
Note that all that information is easily spoofable, so I echo Palooka
and Mladen: Why?
jg
--
at (no spam) home.com is bogus.
Checkers speech day. |
|
|
| Back to top |
|
|
|
| Palooka... |
| Posted: Wed Sep 23, 2009 4:34 pm |
|
|
|
Guest
|
On 23/09/09 07:50, Maija-Leena wrote:
Quote: I need to know which application (sqlplus,
java, toad etc) was used to make the update.
Why?
Palooka |
|
|
| Back to top |
|
|
|
| Mladen Gogala... |
| Posted: Wed Sep 23, 2009 6:59 pm |
|
|
|
Guest
|
On Wed, 23 Sep 2009 09:01:53 -0700, joel garry wrote:
Quote: Note that all that information is easily spoofable, so I echo Palooka
and Mladen: Why?
That was Palooka's question. I have my very own and uniquely obnoxious
way of asking such questions. I've been a computer geek for more than 20
years now and my personality is beyond repair now.
--
http://mgogala.freehostia.com |
|
|
| Back to top |
|
|
|
| joel garry... |
| Posted: Thu Sep 24, 2009 12:36 am |
|
|
|
Guest
|
On Sep 23, 3:23 pm, Palooka <nob... at (no spam) nowhere.com> wrote:
Quote: On 23/09/09 19:59, Mladen Gogala wrote:> On Wed, 23 Sep 2009 09:01:53 -0700, joel garry wrote:
Note that all that information is easily spoofable, so I echo Palooka
and Mladen: Why?
That was Palooka's question. I have my very own and uniquely obnoxious
way of asking such questions. I've been a computer geek for more than 20
years now and my personality is beyond repair now.
I first discovered computers in the early '70s, but didn't really get
serious about it until '80. I may be less serious now. I think the
turning point may have been when I couldn't find the free thumb drive
from Oracle amid the detritus in my home office, and realized I didn't
really care.
Quote:
Your question and mine were similar. Mine was just more succinct.
(gloat), 
Questions like that will break the computer!
jg
--
at (no spam) home.com is bogus.
http://translate.google.ru/translate?hl=ru&sl=ru&tl=en&u=http%3A%2F%2Fwww.oracle.com%2Fglobal%2Fru%2Foramag%2Fmayjune2007%2Frussia_oracle_facts1.html |
|
|
| Back to top |
|
|
|
| Palooka... |
| Posted: Thu Sep 24, 2009 2:23 am |
|
|
|
Guest
|
On 23/09/09 19:59, Mladen Gogala wrote:
Quote: On Wed, 23 Sep 2009 09:01:53 -0700, joel garry wrote:
Note that all that information is easily spoofable, so I echo Palooka
and Mladen: Why?
That was Palooka's question. I have my very own and uniquely obnoxious
way of asking such questions. I've been a computer geek for more than 20
years now and my personality is beyond repair now.
Your question and mine were similar. Mine was just more succinct.
(gloat), ;-)
Palooka |
|
|
| Back to top |
|
|
|
| Jaakko... |
| Posted: Fri Oct 30, 2009 8:12 pm |
|
|
|
Guest
|
Maija-Leena kirjoitti:
Quote: Hi,
I need to log some updates and I would like to do it using a trigger which
inserts a line to a logtable. I need to know which application (sqlplus,
java, toad etc) was used to make the update. I've studied sys_context but
didn't find that information. Toad's Session Browser shows programs that are
connected to the database, so I think it's possible. Do You know how ?
Thanks !
Maija-Leena
Hello Maija-Leena!
You can read the information from the V$SESSION view.
For example:
select u.program,u.machine,u.terminal
into p_program,p_machine,p_terminal
from v$session u
where u.audsid = userenv('sessionid') ;
This of course requires extended permission, but there is a work-around:
put this query in a package which has the permission and call it from
the trigger.
HTH,
Jaakko |
|
|
| Back to top |
|
|
|
|
