[SQL Server|.NET] Application roles in ASP.NET

[whoami]

Ik ben een beetje aan het spelen met Application Roles in SQL Server en ASP.NET, maar het wil niet zo goed werken.

Ik heb dus een Sql Server databank, met een aantal tabellen en stored procedures.
Ik heb de rechten zo ingesteld dat er niemand rechten heeft op de tabellen, en dat alle toegang via stored procedures verloopt. Dat loopt perfect, ik kan de SP's uitvoeren om de inhoud van de tabellen te zien etc....

Nu wilde ik nog een stap verder gaan: ik wil dat niemand rechtstreeks toegang heeft tot de DB, en dat alle toegang moet verlopen via een applicatie.
Ik heb dan dus alle toegang (uitvoerrechten) ontkent voor alle SP's. Je kan dus nu niet zomaar een SP gaan uitvoeren.
Vervolgens heb ik een application role gemaakt in SQL Server, die de toegangsrechten controleert:

code:

sp_addapprole 'libapp', 'test'

Ik heb execute rechten toegekend op m'n stored procedures aan die application role:

code:

GRANT EXECUTE on LIB_GETUSERS TO libapp

Tot daar geen probleem.

Als ik nu in Query Analyzer de Application Role actief wil maken, dan lukt dat ook:

code:

sp_setapprole 'libapp', 'test

Dit lukt ook: ik krijg de melding: 'The application role libapp is now active'.

Echter, wil ik nu vanaf een ASP.NET site toegang krijgen tot m'n db dan loopt het mis.
Een application role is geldig voor de duur van een connectie, dus ik heb bv volgende code:

code:

conn = new SqlConnection (connectionString);
            
conn.Open();
            
SqlCommand cmdAppRole = new SqlCommand();
cmdAppRole.CommandType = CommandType.StoredProcedure;
cmdAppRole.Connection = conn;
cmdAppRole.CommandText = "sp_setapprole";
cmdAppRole.Parameters.Add ("@rolename", "libapp");
cmdAppRole.Parameters.Add ("@password", "test");

cmdAppRole.ExecuteNonQuery();
                        
cmdSearch = new SqlCommand();
cmdSearch.Connection    = conn;
cmdSearch.CommandType   = CommandType.StoredProcedure;
cmdSearch.CommandText   = "LIB_SEARCHCONTACTS";
...
daSearch.Fill (dsContacts);

Dan loopt het mis op de regel: daSearch.Fill.
Ik krijg een security-exception dat ik geen toegang heb op de procedure LIB_SEARCHCONTACTS.
Dit is zeer vreemd, want ik ben zeker dat ik de toegang verleend heb aan de application-role, en ik heb ook de application-role actief gemaakt. (Zie de cmdAppRole Command). Daar krijg ik geen fout op, dus ik vermoed dat dit goed gaat.
Heeft er iemand een ideetje aan wat het zou kunnen liggen, of heeft er al iemand anders ervaring met application-roles in sql-server en web-apps in ASP.NET en daar al succesvol mee getest?

[whoami]

Oké, ondertussen heb ik het opgelost.
't Is wel zeer vreemd.....

Ik had dus -om er zeker van te zijn dat ik het goed kon testen- ook alle EXECUTE permissies op m'n stored procedures aan de PUBLIC groep gedenied.

code:

DENY ALL on LIB_SEARCHCONTACTS TO public

Daar zat hem dus het probleem, als ik dat revoke, dan werkt het wel.
Waarom vind ik dat nu vreemd?
In de doc's staat er, dat, als er gebruik gemaakt wordt van een application-role, dat het DBMS (Sql Server dus), enkel naar de rechten kijkt die die application role heeft, en de andere rechten van de user buiten beschouwing laat. Dit om ervoor te zorgen dat de applicatie wel goed kan werken.
Stel, nl. dat een user connect naar de DB via die applicatie (en dus met de daarbij horende app. role), maar zelf geen rechten heeft op de DB (of bepaalde delen van de DB), hij toch de applicatie naar behoren moet kunnen gebruiken.
Blijkbaar geldt dit niet voor de PUBLIC built - in role. Vreemd.
Iemand met meer informatie daaromtrent?

Daarnaast is het wel zeer vervelend dat je iedere keer die app. role moet gaan zetten bij het openen van een connectie. Om je applicatie zo schaalbaar mogelijk te maken, en gebruik te kunnen maken van connection-pooling, moet je nl. je connectie sluiten van zodra je ze niet meer nodig hebt.
Eigenlijk zou het dus beter zijn dat ik een custom-object maakt die m'n sp's uitvoert, en weet of ik een application-role moet actief maken, en welke dat dan is, en dat m'n object dat dan ook gaat doen voor mij..... (Weet ik weer wat gedaan..... )

[EfBe]

Ik heb het wel eens met ADO geprobeerd icm ASP, maar dat verliep ook al voor geen meter. Deze message: http://groups.google.com/...2MSFTNGP12.phx.gbl&rnum=6 heeft zowat dezelfde code als jij... weird.

Btw, je voorbeeld praat over sp LIB_GETUSERS, je code ropet LIB_SEARCHCONTACTS aan...

[whoami]

EfBe schreef op 09 August 2003 @ 13:13:
Btw, je voorbeeld praat over sp LIB_GETUSERS, je code ropet LIB_SEARCHCONTACTS aan...

Gewoon ervan uitgaan dat alle SP's dezelfde rechten hebben enzo.

[whoami]

Toch vreemd.
Als ik dus de PUBLIC role geen execute rechten geef, maar die execute rechten ook niet expliciet deny, dan werkt het als een trein. Als ik de execute permissie voor die SP aan de public role deny, dan werkt het niet.
Als ik echter geen rechten toeken aan die SP voor de public role (dus geen grant execute, maar ook geen deny execute), maar wel expliciet execute permissie deny voor de ASPNET user account, dan werkt het ook.

Toch ff verder kijken wat MS zegt over die Application roles en de rechten van de public group.

[EfBe]

als public geen rechten heeft, kun je geen verbinding maken met de catalog afaik. dus als public iets geweigerd wordt, houdt het op.

[whoami]

EfBe schreef op 09 augustus 2003 @ 14:44:
als public geen rechten heeft, kun je geen verbinding maken met de catalog afaik. dus als public iets geweigerd wordt, houdt het op.

Rechten hebben op iets, en log-in auth. is toch iets anders?

Eerst moet je kunnen inloggen: dit was geen probleem, zelfs als public nergens rechten op had.
Het uitvoeren van de sp_setapprole stored procedure verliep zonder probleem, pas als ik een eigen SP wou uitvoeren, dan ging het mis. Die sp_setapprole is natuurlijk wel een system SP die zich in de master DB bevindt.

Trouwens, ik log in mbhv de aspnet user account. Sql Server neemt altijd de meest beperkende rechten als je tot verschillende groepen behoort. Aangezien de ASPNET account de minste rechten heeft (ik heb nl. expliciet deny execute on ... to aspnet gezet), heeft dat er toch niets mee te maken?
Trouwens, bij application roles worden enkel de rechten bekeken van die application role. Andere rechten die de ASPNET user heeft (en ook de groepen waartoe hij behoort) worden buiten beschouwing gelaten.

Ik denk echt niet dat het te maken heeft met het verbinden met de catalog, want m'n exception was echt wel:

EXECUTE PERMISSION on LIB_.... DENIED

[Annie]

whoami schreef op 09 augustus 2003 @ 15:20:
Eerst moet je kunnen inloggen: dit was geen probleem, zelfs als public nergens rechten op had.
Het uitvoeren van de sp_setapprole stored procedure verliep zonder probleem, pas als ik een eigen SP wou uitvoeren, dan ging het mis. Die sp_setapprole is natuurlijk wel een system SP die zich in de master DB bevindt.

Inloggen heeft hier inderdaad weinig mee te maken. Je probleem ligt bij de rechten in de database. Zoals EfBe al aangaf houdt het snel op als je de public role rechten ontneemt. De reden is dat alle users, groups en roles automatisch aan deze role toebehoren (en dus ook een application role lijkt me).

Uitvoeren van een system sproc geeft natuurlijk geen probleem aangezien je daarvoor naar een andere database gaat waar de restricties op public niet gelden.

[whoami]

Hmmm...... Sounds acceptable.

Ik had er nooit meer aan gedacht dat alle users (en ook roles) automatisch aan de public role toebehoren.
Daarbij komt eigenlijk ook nog het feit dat in de docs staat dat; bij toepassing van een application role, alle andere rechten gesuspended worden en er enkel naar de rechten van de app. role gekeken wordt.

[EfBe]

precies. Inloggen in sqlserver gaat in 2 stappen:
1) inloggen in sqlserver zelf (dit lukte je)
2) inloggen in de database zelf (dit lukte je niet). Dit wordt gedaan met de security ID die je gekregen hebt na het inloggen in sqlserver en er wordt gekeken of je toegang hebt tot de database, dat wordt gedaan aan de hand van het kijken in de public role, of je daar in zit en zo ja wat die mag.

[Annie]

Puntje 2 klopt niet helemaal EfBe. Misschien bedoel je het goed, maar staat het een beetje krom uitgelegd of ik begrijp je gewoon verkeerd (het is nog vroeg ).

Het 'inloggen' op de database lukt wel, dat heeft namelijk niets te maken met de rechten die je hebt in de database (je hebt alleen db-access nodig, m.a.w. sta je in de sysusers). Om daarna iets te kunnen doen binnen de database wordt gekeken naar rechten die je bezit op user-, groups- en role niveau en daarbij worden dus ook de rechten in de public role meegenomen (aangezien je daar altijd lid van bent).

[ Voor 5% gewijzigd door Annie op 10-08-2003 12:21 ]

[whoami]

Ok.

ff recapituleren: de application-role heeft dus rechten toegekend gekregen. Die application role behoort ook tot de public groep, en die heeft ook rechten.
Aangezien in mijn geval die rechten van de public groep het meest gerestricteerd waren, werden die rechten toegepast en had ik geen access tot die functie/procedure.

Ikke snap.

[whoami]

Damnit, SQL Server en ASP.NET is toch nog niet zaligmakend.

Door de manier waarop die app. roles momenteel geimplementeerd zijn, is het niet mogelijk er gebruik van te maken als je gebruik maakt van connection pooling.

Je mag nl. slechts 1x in de connectie een application role zetten. Doe je het 2x binnen dezelfde connectie, dan krijg je een foutmelding.
Nu, wat is het probleem:
Stel, je opent een connectie naar de databank; je maakt een application role actief, je rommelt wat met die database, en je sluit je connectie opnieuw.
Een beetje later moet je opnieuw wat doen met de DB, dus: je opent een nieuwe connectie. Echter, ASP.NET zal een connectie van de pool nemen.
Aangezien in die connectie de app. role reeds geset werd, maar jij dat niet (kunt) weten, zet je ze opnieuw, met als gevolg dat je in je ASP.NET pagina een mooie , duidelijke error krijgt (niet dus): object reference not set to an instance.
Na wat zoeken en debuggen bleek dat ik nergens een null Exception kon hebben, en had ik het probleem herleid tot bij de application-roles.

De enige manier waarop het probleem (momenteel) kan opgelost worden, is geen gebruik maken van connection pooling.

Het zou toch mooi zijn moest MS het mogelijk maken om een application - role te deactiveren binnen een connectie, want momenteel is dat niet mogelijk. Dit zou mijn probleem (en dat van vele anderen) mooi oplossen.

Zie ook dit document van Microsoft.

[whoami]

Ik kan het natuurlijk ook oplossen door geen gebruik te maken van application-roles.

Wat ik dan zou moeten doen, is al mijn access regelen via Stored Procedures. Toegang tot alle tabellen ed aan iedereen weigeren, en enkel de ASPNET account EXECUTE rechten geven op al mijn SP's.

Op die manier kan ik wel gebruik maken van connection-pooling.

Wil ik dat doen voor een Windows-applicatie, dan moet ik,
- ofwel gebruik maken van een SQL Server user account , en met die account inloggen in de DB
- ofwel een groep aanmaken in Sql Server en alle Windows accounts aan die group adden, en enkel die groep EXECUTE rechten geven op m'n SP's.

[ Voor 41% gewijzigd door whoami op 14-08-2003 21:16 ]