Read only queries Using ADO.NET












0















I want to limit the application to read only queries. In other words, I want the application to process only those queries which are not changing the state of the database. I am using ADO.NET. I do not want to create a new user against the database with read only permissions. Any suggestions are welcome.






c# sql sql-server ado.net







share|improve this question

























  • Why don't you want to create a new user? That's the real way to do this, unless you have 100% control of the application code and can ensure that no data altering statements are executed through it.

    – squillman
    Jan 18 at 19:04











  • The best, most secure, easiest way to do that is to limit the database user the application is using to readonly. There's even a built in role called db_denydatawriter which will automatically deny writing privileges for any database object from the user that has it.

    – Zohar Peled
    Jan 20 at 10:13











  • The database is an external db and it does not allow to create a new database user (We don't have privileges to create a new database user). I am using Windows Authentication.

    – Programmer
    Jan 21 at 7:05
















0















I want to limit the application to read only queries. In other words, I want the application to process only those queries which are not changing the state of the database. I am using ADO.NET. I do not want to create a new user against the database with read only permissions. Any suggestions are welcome.






c# sql sql-server ado.net







share|improve this question

























  • Why don't you want to create a new user? That's the real way to do this, unless you have 100% control of the application code and can ensure that no data altering statements are executed through it.

    – squillman
    Jan 18 at 19:04











  • The best, most secure, easiest way to do that is to limit the database user the application is using to readonly. There's even a built in role called db_denydatawriter which will automatically deny writing privileges for any database object from the user that has it.

    – Zohar Peled
    Jan 20 at 10:13











  • The database is an external db and it does not allow to create a new database user (We don't have privileges to create a new database user). I am using Windows Authentication.

    – Programmer
    Jan 21 at 7:05














0












0








0








I want to limit the application to read only queries. In other words, I want the application to process only those queries which are not changing the state of the database. I am using ADO.NET. I do not want to create a new user against the database with read only permissions. Any suggestions are welcome.






c# sql sql-server ado.net







share|improve this question
















I want to limit the application to read only queries. In other words, I want the application to process only those queries which are not changing the state of the database. I am using ADO.NET. I do not want to create a new user against the database with read only permissions. Any suggestions are welcome.






c# sql sql-server ado.net




c# sql sql-server ado.net






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Jan 18 at 19:08









marc_s

574k12811091256




574k12811091256










asked Jan 18 at 18:57









ProgrammerProgrammer

197




197













  • Why don't you want to create a new user? That's the real way to do this, unless you have 100% control of the application code and can ensure that no data altering statements are executed through it.

    – squillman
    Jan 18 at 19:04











  • The best, most secure, easiest way to do that is to limit the database user the application is using to readonly. There's even a built in role called db_denydatawriter which will automatically deny writing privileges for any database object from the user that has it.

    – Zohar Peled
    Jan 20 at 10:13











  • The database is an external db and it does not allow to create a new database user (We don't have privileges to create a new database user). I am using Windows Authentication.

    – Programmer
    Jan 21 at 7:05



















  • Why don't you want to create a new user? That's the real way to do this, unless you have 100% control of the application code and can ensure that no data altering statements are executed through it.

    – squillman
    Jan 18 at 19:04











  • The best, most secure, easiest way to do that is to limit the database user the application is using to readonly. There's even a built in role called db_denydatawriter which will automatically deny writing privileges for any database object from the user that has it.

    – Zohar Peled
    Jan 20 at 10:13











  • The database is an external db and it does not allow to create a new database user (We don't have privileges to create a new database user). I am using Windows Authentication.

    – Programmer
    Jan 21 at 7:05

















Why don't you want to create a new user? That's the real way to do this, unless you have 100% control of the application code and can ensure that no data altering statements are executed through it.

– squillman
Jan 18 at 19:04





Why don't you want to create a new user? That's the real way to do this, unless you have 100% control of the application code and can ensure that no data altering statements are executed through it.

– squillman
Jan 18 at 19:04













The best, most secure, easiest way to do that is to limit the database user the application is using to readonly. There's even a built in role called db_denydatawriter which will automatically deny writing privileges for any database object from the user that has it.

– Zohar Peled
Jan 20 at 10:13





The best, most secure, easiest way to do that is to limit the database user the application is using to readonly. There's even a built in role called db_denydatawriter which will automatically deny writing privileges for any database object from the user that has it.

– Zohar Peled
Jan 20 at 10:13













The database is an external db and it does not allow to create a new database user (We don't have privileges to create a new database user). I am using Windows Authentication.

– Programmer
Jan 21 at 7:05





The database is an external db and it does not allow to create a new database user (We don't have privileges to create a new database user). I am using Windows Authentication.

– Programmer
Jan 21 at 7:05












2 Answers
2






active

oldest

votes


















1














Option 1: SQL Authentication



You can use connections as shown below:



Server ={serverName}; Initial Catalog = {DB_Name}; User Id={uid}; Password={pwd};


Use the uid which has only read access in database.



Option 2: Windows Authentication



If you want to use Integrated Security = True; (i.e. windows authentication) then you will have to grant readonly access to the windows user (under which the program runs).



Hope this helps.






share|improve this answer
























  • Thanks for the reply. Application is using Windows Authentication and is running under the scope of an administrator (which is a Windows User). Administrator has all the privileges and we cannot change the permissions level. What if, the database even don't allow us to create a new database user with read only rights. Any thoughts?

    – Programmer
    Jan 21 at 7:11





















0














You can create triggers to cancel any insert update or delete through a trigger at the database level. The trigger would end with a rollback to cancel the transaction. You would have to figure out who kicked off the trigger so other users can update the db.



I would not do it - I would take away any permission (except select) from the account being used for the application. I have created many, many triggers but I have never heard anyone using database triggers to enforce read only.






share|improve this answer


























  • Application is using Windows Authentication and is running under the scope of an administrator. So, it has all the priveleges and can execute all the queries. The database is an external db and it doesn't allow us to create a new database user with read only permissions. Any thoughts on doing this without creating a new db user, changing the permission level of the existing Windows user.

    – Programmer
    Jan 21 at 7:33











Your Answer






StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});














draft saved

draft discarded


















StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54259947%2fread-only-queries-using-ado-net%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown

























2 Answers
2






active

oldest

votes








2 Answers
2






active

oldest

votes









active

oldest

votes






active

oldest

votes









1














Option 1: SQL Authentication



You can use connections as shown below:



Server ={serverName}; Initial Catalog = {DB_Name}; User Id={uid}; Password={pwd};


Use the uid which has only read access in database.



Option 2: Windows Authentication



If you want to use Integrated Security = True; (i.e. windows authentication) then you will have to grant readonly access to the windows user (under which the program runs).



Hope this helps.






share|improve this answer
























  • Thanks for the reply. Application is using Windows Authentication and is running under the scope of an administrator (which is a Windows User). Administrator has all the privileges and we cannot change the permissions level. What if, the database even don't allow us to create a new database user with read only rights. Any thoughts?

    – Programmer
    Jan 21 at 7:11


















1














Option 1: SQL Authentication



You can use connections as shown below:



Server ={serverName}; Initial Catalog = {DB_Name}; User Id={uid}; Password={pwd};


Use the uid which has only read access in database.



Option 2: Windows Authentication



If you want to use Integrated Security = True; (i.e. windows authentication) then you will have to grant readonly access to the windows user (under which the program runs).



Hope this helps.






share|improve this answer
























  • Thanks for the reply. Application is using Windows Authentication and is running under the scope of an administrator (which is a Windows User). Administrator has all the privileges and we cannot change the permissions level. What if, the database even don't allow us to create a new database user with read only rights. Any thoughts?

    – Programmer
    Jan 21 at 7:11
















1












1








1







Option 1: SQL Authentication



You can use connections as shown below:



Server ={serverName}; Initial Catalog = {DB_Name}; User Id={uid}; Password={pwd};


Use the uid which has only read access in database.



Option 2: Windows Authentication



If you want to use Integrated Security = True; (i.e. windows authentication) then you will have to grant readonly access to the windows user (under which the program runs).



Hope this helps.






share|improve this answer













Option 1: SQL Authentication



You can use connections as shown below:



Server ={serverName}; Initial Catalog = {DB_Name}; User Id={uid}; Password={pwd};


Use the uid which has only read access in database.



Option 2: Windows Authentication



If you want to use Integrated Security = True; (i.e. windows authentication) then you will have to grant readonly access to the windows user (under which the program runs).



Hope this helps.







share|improve this answer












share|improve this answer



share|improve this answer










answered Jan 18 at 19:04









Manoj ChoudhariManoj Choudhari

1,096115




1,096115













  • Thanks for the reply. Application is using Windows Authentication and is running under the scope of an administrator (which is a Windows User). Administrator has all the privileges and we cannot change the permissions level. What if, the database even don't allow us to create a new database user with read only rights. Any thoughts?

    – Programmer
    Jan 21 at 7:11





















  • Thanks for the reply. Application is using Windows Authentication and is running under the scope of an administrator (which is a Windows User). Administrator has all the privileges and we cannot change the permissions level. What if, the database even don't allow us to create a new database user with read only rights. Any thoughts?

    – Programmer
    Jan 21 at 7:11



















Thanks for the reply. Application is using Windows Authentication and is running under the scope of an administrator (which is a Windows User). Administrator has all the privileges and we cannot change the permissions level. What if, the database even don't allow us to create a new database user with read only rights. Any thoughts?

– Programmer
Jan 21 at 7:11







Thanks for the reply. Application is using Windows Authentication and is running under the scope of an administrator (which is a Windows User). Administrator has all the privileges and we cannot change the permissions level. What if, the database even don't allow us to create a new database user with read only rights. Any thoughts?

– Programmer
Jan 21 at 7:11















0














You can create triggers to cancel any insert update or delete through a trigger at the database level. The trigger would end with a rollback to cancel the transaction. You would have to figure out who kicked off the trigger so other users can update the db.



I would not do it - I would take away any permission (except select) from the account being used for the application. I have created many, many triggers but I have never heard anyone using database triggers to enforce read only.






share|improve this answer


























  • Application is using Windows Authentication and is running under the scope of an administrator. So, it has all the priveleges and can execute all the queries. The database is an external db and it doesn't allow us to create a new database user with read only permissions. Any thoughts on doing this without creating a new db user, changing the permission level of the existing Windows user.

    – Programmer
    Jan 21 at 7:33
















0














You can create triggers to cancel any insert update or delete through a trigger at the database level. The trigger would end with a rollback to cancel the transaction. You would have to figure out who kicked off the trigger so other users can update the db.



I would not do it - I would take away any permission (except select) from the account being used for the application. I have created many, many triggers but I have never heard anyone using database triggers to enforce read only.






share|improve this answer


























  • Application is using Windows Authentication and is running under the scope of an administrator. So, it has all the priveleges and can execute all the queries. The database is an external db and it doesn't allow us to create a new database user with read only permissions. Any thoughts on doing this without creating a new db user, changing the permission level of the existing Windows user.

    – Programmer
    Jan 21 at 7:33














0












0








0







You can create triggers to cancel any insert update or delete through a trigger at the database level. The trigger would end with a rollback to cancel the transaction. You would have to figure out who kicked off the trigger so other users can update the db.



I would not do it - I would take away any permission (except select) from the account being used for the application. I have created many, many triggers but I have never heard anyone using database triggers to enforce read only.






share|improve this answer















You can create triggers to cancel any insert update or delete through a trigger at the database level. The trigger would end with a rollback to cancel the transaction. You would have to figure out who kicked off the trigger so other users can update the db.



I would not do it - I would take away any permission (except select) from the account being used for the application. I have created many, many triggers but I have never heard anyone using database triggers to enforce read only.







share|improve this answer














share|improve this answer



share|improve this answer








edited Jan 18 at 19:14

























answered Jan 18 at 19:08









benjamin moskovitsbenjamin moskovits

3,7011516




3,7011516













  • Application is using Windows Authentication and is running under the scope of an administrator. So, it has all the priveleges and can execute all the queries. The database is an external db and it doesn't allow us to create a new database user with read only permissions. Any thoughts on doing this without creating a new db user, changing the permission level of the existing Windows user.

    – Programmer
    Jan 21 at 7:33



















  • Application is using Windows Authentication and is running under the scope of an administrator. So, it has all the priveleges and can execute all the queries. The database is an external db and it doesn't allow us to create a new database user with read only permissions. Any thoughts on doing this without creating a new db user, changing the permission level of the existing Windows user.

    – Programmer
    Jan 21 at 7:33

















Application is using Windows Authentication and is running under the scope of an administrator. So, it has all the priveleges and can execute all the queries. The database is an external db and it doesn't allow us to create a new database user with read only permissions. Any thoughts on doing this without creating a new db user, changing the permission level of the existing Windows user.

– Programmer
Jan 21 at 7:33





Application is using Windows Authentication and is running under the scope of an administrator. So, it has all the priveleges and can execute all the queries. The database is an external db and it doesn't allow us to create a new database user with read only permissions. Any thoughts on doing this without creating a new db user, changing the permission level of the existing Windows user.

– Programmer
Jan 21 at 7:33


















draft saved

draft discarded




















































Thanks for contributing an answer to Stack Overflow!


  • Please be sure to answer the question. Provide details and share your research!

But avoid …



  • Asking for help, clarification, or responding to other answers.

  • Making statements based on opinion; back them up with references or personal experience.


To learn more, see our tips on writing great answers.




draft saved


draft discarded














StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54259947%2fread-only-queries-using-ado-net%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown





















































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown

































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown







-

Popular posts from this blog

Liquibase includeAll doesn't find base path

Image
0 while migrating a project from maven to gradle I ran in the following problem. My file structure looks like this: The changelog-master.xml includes the changelog-v1-0.xml, which then should include the directory v1-0 with following line: <includeAll path="src/main/resources/liquibase/changelogs/v1-0/" errorIfMissingOrEmpty="true" /> . This works just fine. This: <includeAll path="/liquibase/changelogs/v1-0/" errorIfMissingOrEmpty="true" relativeToChangelogFile="true" /> doesn't. I get following errormessage: Cannot find base path 'src/main/resources/liquibase/changelogs/changelog-v1-0.xml' with liquibase in version 3.6.2. I also tried the versions 3.6.0, 3.6.1 and 3.5.5. I am using gradle to build my project in version 4.9
Read more

How to use setInterval in EJS file?

Image
0 I have a problem with using setInterval in my EJS file, which is part of my nodejs app (with Express). I have created function getRandomSubarray , which chooses randomly subset of images from array. I want to change this subset every three seconds. This is problematic piece of my code: <%setInterval(function(){%> <%RandomSubrecipesImg=tools.getRandomSubarray(recipesImg,4)%> <div class="navbar-image-box col-sm-3 d-none d-sm-block"><img class="navbar-image" src="<%=RandomSubrecipesImg[0].replace('public',"")%>"></div> <div class="navbar-image-box col-sm-3 d-none d-sm-block"><img class="navbar-image" src="<%=RandomSubrecipesImg[1].replace('public',"")%>">&
Read more

Petrus Granier-Deferre

Image
Petrus Granier-Deferre (Lutetiae die 27 Iulii 1927 natus; ibidem die 16 Novembris 2007 mortuus) fuit moderator cinematographicus et dux scaenarum Francicus. Pelliculae selectae | 1961 : Le Petit Garçon de l'ascenseur 1962 : Les Aventures de Salavin (sous-titré Confession de minuit) 1965 : La Métamorphose des cloportes 1965 : Paris au mois d'août 1967 : Le Grand Dadais 1970 : La Horse 1971 : Le Chat 1971 : La Veuve Couderc 1973 : Le Fils 1973 : Le Train 1974 : La Race des seigneurs 1975 : La Cage 1975 : Adieu poulet 1976 : Une femme à sa fenêtre 1976 : Le Toubib 1981 : Une étrange affaire 1982 : L'Étoile du Nord 1983 : L'Ami de Vincent 1985 : L'Homme aux yeux d'argent 1986 : Cours privé 1987 : Noyade interdite 1988 : La Couleur du vent 1990 : L'Autrichienne 1995 : Le Petit Garçon 1992 : La Voix 1993 : Archipel 1995 : Maigret et la vente à la bougie (TV) 1996 : La Dernière Fête (TV) 1997 : Maigret et l'enfant de c
Read more