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

Homophylophilia

Image
Haec pagina ad tempus protecta est ob maleficia perpetua. Quaesemus, emendationes suggere in pagina disputationis vel magistratum ad hanc deprotegendam pete. Homophylophilia Hyacinthus complexus ab amante suo Apolline, in pictura Mors Hyacinthi quam Jean Broc pinxit. Homophylophilia est sui sexus vel generis amor vel mores romantici vel sexuales, res venerea masculina in masculos, aut mulier in mulieres appetentes. Hoc ingenium sexuale est "exemplar permanens vel inclinatio ad experiendam sexualem, adfectionalem, vel romanticam vim attrahendi," praecipue vel solum ex hominibus eiusdem sexús; notio "etiam ad hominis iudicium identitatis privatae socialisque in illis attractionibus conditum refert, in moribus quoque qui illos exprimant, et in societate communitatis aliorum qui illos communicent." [1] [2] Per aevos alii aliter rem definiverunt, sed Occidentali nostrae aetatis cultu civili non tantum ad ipsa acta erotica spectat, sed etiam igitur
Read more

Updating UILabel text programmatically using a function

Image
0 so I am trying to update the UIlabel text using a func. I have seen few other questions like mine and I tried their method but it doesn't work. fun returns no value when used in the UIlabel. I have shown my code below.It has to be noted that when I use the function in viewdidload I can retrieve the data. lazy var name: UILabel = { let lb = UILabel() lb.text? = getthename() lb.lineBreakMode = NSLineBreakMode.byWordWrapping lb.textColor = .mainGreen lb.textAlignment = .left lb.font = UIFont.boldSystemFont(ofSize:12.0) return lb }() func getthename() -> String { var wins : String = "" let ud = (Auth.auth().currentUser?.uid) self.ref = Database.database().reference() self.ref.child("Users").child(ud!).observeSingleEvent(of: .value, with: {(snapshot) in
Read more

Cloud Functions - OpenCV Videocapture Read method fails for larger files from cloud storage

Image
0 I created a cloud function to read a video file from cloud storage and read frames and create an image for each frame. This function works for small files which is 1-2 MB, However, this method fails while reading larger file 130 MB. import cv2 from google.cloud import storage vidcap = cv2.VideoCapture("https://storage.googleapis.com/[bucker_name]/[filename.mp4]") if vidcap.isOpened(): vidcap.open("https://storage.googleapis.com/[bucker_name]/[filename.mp4]") success,image = vidcap.read() print(success) Expected the print will return true and an image will hold an image of a first frame. python-3.x function opencv cloud share | improve this question
Read more