Syntax/Sql
We have an application where users login using their Windows Authentication user name , but the connection goes to the database using a generic userid "ABC123" for all the users. My question is , when a user makes some changes to the data , like insert , update,delete ...is there a way to find out who did the change.
Right now when i use "sp_who" or DBArtisan for Oracle ...alll i can see is "ABC123" ....i want to know which exact user did the transaction based on his/her login.
I need the syntax and the way it can be incorporated in code using VB.6
Any help in this matter is greatly appreciated , please let me know in detail .
many thanks.
Right now when i use "sp_who" or DBArtisan for Oracle ...alll i can see is "ABC123" ....i want to know which exact user did the transaction based on his/her login.
I need the syntax and the way it can be incorporated in code using VB.6
Any help in this matter is greatly appreciated , please let me know in detail .
many thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If the actual database service is being run by this "ABC123" user then I'm not sure that you can get it automatically - I do know that Oracle stores the OS username for inserts, etc, but I'm failry sure (don't qoute me) that it's the username submitted by the service
The command
select osuser from v$session;
will give you back the connected users, so for example you could in a Stored procedure determine the username from Windows, but I'm not sure if this will work for you.
The command
select osuser from v$session;
will give you back the connected users, so for example you could in a Stored procedure determine the username from Windows, but I'm not sure if this will work for you.
ASKER
may be the following will make it simpler.
For instance,
I have a column ( say MODIFIER) in every table in the database
which is deafulted to SUSER_SNAME( ) for Sql Server and USER for Oracle.
When i do a sp_who2 , it returs me the user as ABC and the hostname.
Instead of defaulting this coulmn to SUSER_SNAME or USER , how can i default it to the Hostname?
(I am guessing it can be done through a user defined function or something like that)
For instance,
I have a column ( say MODIFIER) in every table in the database
which is deafulted to SUSER_SNAME( ) for Sql Server and USER for Oracle.
When i do a sp_who2 , it returs me the user as ABC and the hostname.
Instead of defaulting this coulmn to SUSER_SNAME or USER , how can i default it to the Hostname?
(I am guessing it can be done through a user defined function or something like that)
ASKER