SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


View and User-Rights (Application Role)


View and User-Rights (Application Role)

Author
Message
stryk
stryk
SSC-Enthusiastic
SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)

Group: General Forum Members
Points: 178 Visits: 1223
Hi all,

I'm not sure how to explain my problem properly, so I'll start from the scratch:

I have a database - call it "DB_A". All users connect to this DB via an application (MS Dynamics NAV) which uses an internal "Application Role" (in "DB_A") called "$ndo$shadow".

I have another database - call it "DB_B" which is on the sames SQL Server.

Now I want to create a "View" (call it "View_A") in "DB_A" looking at a table (call it "Tab_B") in "DB_B".
I grant ALL rights on this View to "public".

I include this "View" in the NAV application. When trying to read from this View the users get an error message ("Invalid User or Password").
I guess this is because the "$ndo$shadow" role ("DB_A") somehow does not get access to "DB_B".
In SSMS, when using the same Login-Account - in this case only "public" - it works OK.

The current workaround is to use a Linked Server to "DB_B", here mapping the User-Rights.

So the actual question is: Is there another possibility? Without Linked Server? Can I give "$ndo$shadow" access to another database "DB_B"?

Any ideas welcome, appreciate your feedback. Thanks in avance.

Kind regards,
Jörg

Jörg A. Stryk
MVP - MS Dynamics NAV
dmoldovan
dmoldovan
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1588 Visits: 1479
You'll need the "guest" user in the other database. See BOL ---> Application Roles and http://www.sqlservercentral.com/articles/Security/sqlserversecurityprosandconsofapplicationroles/1116/ ("Con: Crossing Databases Requires the guest User ")
stryk
stryk
SSC-Enthusiastic
SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)

Group: General Forum Members
Points: 178 Visits: 1223
Yeah ... you made my day :-P
This works!

Small thing with tremendous impact ...

THANKS a lot!

Best regards,
Jörg

Jörg A. Stryk
MVP - MS Dynamics NAV
dmoldovan
dmoldovan
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1588 Visits: 1479
You're welcome, Jörg :-)
However take care of your data when using "guest", see http://www.mssqltips.com/tip.asp?tip=1172.
RBarryYoung
RBarryYoung
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35534 Visits: 9518
Right, the problem with enabling "guest" is that you are allowing everyone else who has any kind of access to your SQL Server to get into DB_B also, not just the NAV app users from DB_A.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
stryk
stryk
SSC-Enthusiastic
SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)

Group: General Forum Members
Points: 178 Visits: 1223
Thanks for the hint!

Well, "hardening" a NAV database outside the internal NAV security system is actually a really mean issue. Once this "$ndo$shadow" role was bypassed - e.g. simply using MS Excel to connect to the SQL Server - the only role applied is "public", which grants them way too many rights ...

My customer is quite aware of this and we have started a global "database security" project. And thanks to your advice we will consider the "guest" issue, too, of course.

But the primary goal was to get easy access to these remote Views, and that's solved (again, thanks a bunch).
Now we will dig into the "hardening" stuff ...

Best regards,
Jörg

Jörg A. Stryk
MVP - MS Dynamics NAV
stryk
stryk
SSC-Enthusiastic
SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)

Group: General Forum Members
Points: 178 Visits: 1223
OK, I'm currently looking into various issues about "hardening" (createing roles, etc.).
According to that I would like to evaluate features to monitor the user access. So far I have created SQL Profiler Traces to monitor the Audit Login/Logout and I enabled the logging to the SQL Errorlog.

It would be convenient, if an Operator could be informed whenever a "permission issue" occurs. Therefore I created an Alert for events with "severity level" 014 (insufficient permission). Well, this Alert triggers whenever a login fails, but NOT if e.g. a SELECT permission on a table was denied.

But what I'd like to do is to monitor all those queries which fail due to this "permission denial" - obviously 014 is not sufficient.

Is there a chance to accomplish this?

Jörg A. Stryk
MVP - MS Dynamics NAV
dmoldovan
dmoldovan
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1588 Visits: 1479
Try to add to a SQL trace the Security Audit ---> Audit Statement Permission Event
stryk
stryk
SSC-Enthusiastic
SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)

Group: General Forum Members
Points: 178 Visits: 1223
Hmm ... this does not work as I hoped ...

But: I found out that the standard sys.messages of severity "14" are not logged by default. So I set "WITH_LOG" to TRUE for message 229 - et voila, it works! Now the Alert triggers whenever e.g. a SELECT permission was denied, etc.!

Thanks & best regards,
Jörg

Jörg A. Stryk
MVP - MS Dynamics NAV
dmoldovan
dmoldovan
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1588 Visits: 1479
Demands too many resources, isn't it? :-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search