App Roles in SQL Server

[earniedyke]

Greetings all,

Our DBAs have our SQL Server databases configured so we connect with a user that has not privileges then we must switch to application role using sp_setapprole stored procedure. How can I get this to work with a datasource? I can connect with the user name but getting the sp_setapprole executed is proving more of an issue.

I have thought about using it in the SQL that is called when a connection is created but the whole reason for using app roles is so no one knows they are being used. Make any sense?

Any and all help is appreciated.

Earnie!

[darranl]

Which version of JBoss are you using?

"earniedyke" wrote:

I have thought about using it in the SQL that is called when a connection is created but the whole reason for using app roles is so no one knows they are being used. Make any sense?

It is possible in the -ds.xml to specify some SQL to be executed when a new connection is created, is this what you are looking for or what you are trying to avoid?

[earniedyke]

The problem is we connect to the database with one id with no privileges then switch to an app role which has all of the table privileges. I could execute a stored procedure in the

<new-connection-sql>

but the fear among our security people is if someone hacks our server, decryts the connection user password then all they have to do is execute the stored procedure to gain full access to the table.

I know, this is a long stretch but our security people view it as a real threat. :-( I think it's a load of bs!

Earnie!

[earniedyke]

And we are on 4.0.1sp1

Earnie!

[adrian.brock]

Moved to the security forum.

A basic understanding of security should tell you that unless you force
the user booting JBoss to enter a password or some other credential
your problem is unsolvable.

Static preconfiguration of credentials can always be hacked.
It depends who you trust with the credential.

"Security is a process, not a technology".

[adrian.brock]

Static preconfiguration of credentials can always be hacked.

Adding complicated protocols based on store procedures or some other
mechanism is just "Security by Obfuscation".

[earniedyke]

I could not agree more. I am just trying to satisfy people who think you can create systems that are unbreechable.

Earnie!