Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 221
  • Last Modified:

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.
0
crishna1
Asked:
crishna1
  • 2
1 Solution
 
aelatikCommented:
Within the VB application when a database command occures just insert to a new table the name of the user and the executed SQL statement.
To fetch the windows authenticated username use :

Environ("USERNAME")
0
 
crishna1Author Commented:
Is this T-SQL or PL/SQL?
0
 
fatalXceptionCommented:
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.
0
 
crishna1Author Commented:
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)
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now