Re: application roles
- From: Franky <Franky@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Wed, 5 Mar 2008 07:50:01 -0800
I 've made a trigger on a SQL2000 database to test your solution with the
triggers.
But it seems that this did not work.
so Ive created a logtable
create table dbo.TB_SecurityLog
(
nsSystemUser nvarchar(128),
sUser varchar(128),
sdatTime smalldatetime default getdate(),
sTableName varchar(128)
)
go
and then created a trigger
CREATE trigger dbo.TR_InsUpd
on dbo.[Project Log] for insert,update as
begin
insert dbo.TB_SecurityLog (nsSystemUser,sUser,sTableName)
select system_user, user, 'dbo.TB_Test'
end
go
But my login-account and database user appear in my logtable instead of the
application role I am working under.
I also tried your suggestion with the DMV on a test server of our client but
was not able to detect the applic role. Like you mentioned
'The catch is that there is only a request, only if the connection is
executing a command, so it may somewhat difficult to track'
Thx for your response
"Erland Sommarskog" wrote:
Franky (Franky@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:.
We have 3rd party software which makes application roles and assigns
specific rights to these application roles defined by it 's own internal
security mechanism. But it happens that the synchronisation between it
own internal security-system and the rights it assigned to the
application roles is not in sync. Even after a resynchronisation the
problem still occurs.
That application makes 1 application role for each user. But I cannot
know which application role belongs to a specific user (or vice versa)
In sql profiler you can see which user executes the statements but you
cannot see which application role is active.
Is there a way to detect the active application role for a particular user
?
Yes, if you are on SQL 2005. In that case, you find the value in the
column user_id in sys.dm_exec_request, and you can look up the name in
sys.database_principals. The user id will the one of the application role.
The catch is that there is only a request, only if the connection is
executing a command, so it may somewhat difficult to track.
Another possibility is to have triggers to write the value of SYSTEM_USER
and USER to a log table. This requires that the users actually perform
updates. This would work on both SQL 2000 and SQL 2005.
--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
- Follow-Ups:
- Re: application roles
- From: Erland Sommarskog
- Re: application roles
- References:
- Re: application roles
- From: Erland Sommarskog
- Re: application roles
- Prev by Date: Re: LOGON trigger
- Next by Date: Re: application roles
- Previous by thread: Re: application roles
- Next by thread: Re: application roles
- Index(es):
Relevant Pages
|
