Read only ADO connection still allows writing to database?
-
Sorry for the cross/re post, but I really need a solution to this problem. So far I have had the question posted for two days with only 50 views and no hints or answers. I really do not want to have to go through the effort of sanitizing out any possible writes. I can not set the database to be readonly at the file level as there is another app that has to have read-write access. I would think there should be a way for SQLServer to simply refuse to do any write operations for a readonly connection, but I have not been able to figure it out so far. http://www.codeproject.com/Questions/164065/Read-only-ADO-connection-still-allows-writing-to-d.aspx[^]
I wrote:
I have connected to my SQL Server 2005 database by using ADO (through A set of ADO classes - version 2.20 by Carlos Antollini[^]). Now I have set the connection mode to be ReadOnly
CADODatabase *pDatabase = NULL;
pDatabase = new CADODatabase();
pDatabase->SetConnectionString(MyConnectionString);
pDatabase->SetConnectionMode(CADODatabase::connectModeRead);
pDatabase->Open();SetConnectMode() calls ADODB::Connection15::PutMode and CADODatabase::connectModeRead is adModeRead. Now my understanding is that setting the adModeRead mode should make the database connection read-only. But a simple test of the connection shows that I can use the connection to create and drop tables, as well as insert and update data. How can I make the connection truely read-only?
You may be right I may be crazy -- Billy Joel -- Within you lies the power for good - Use it!
-
Sorry for the cross/re post, but I really need a solution to this problem. So far I have had the question posted for two days with only 50 views and no hints or answers. I really do not want to have to go through the effort of sanitizing out any possible writes. I can not set the database to be readonly at the file level as there is another app that has to have read-write access. I would think there should be a way for SQLServer to simply refuse to do any write operations for a readonly connection, but I have not been able to figure it out so far. http://www.codeproject.com/Questions/164065/Read-only-ADO-connection-still-allows-writing-to-d.aspx[^]
I wrote:
I have connected to my SQL Server 2005 database by using ADO (through A set of ADO classes - version 2.20 by Carlos Antollini[^]). Now I have set the connection mode to be ReadOnly
CADODatabase *pDatabase = NULL;
pDatabase = new CADODatabase();
pDatabase->SetConnectionString(MyConnectionString);
pDatabase->SetConnectionMode(CADODatabase::connectModeRead);
pDatabase->Open();SetConnectMode() calls ADODB::Connection15::PutMode and CADODatabase::connectModeRead is adModeRead. Now my understanding is that setting the adModeRead mode should make the database connection read-only. But a simple test of the connection shows that I can use the connection to create and drop tables, as well as insert and update data. How can I make the connection truely read-only?
You may be right I may be crazy -- Billy Joel -- Within you lies the power for good - Use it!
Ok so thats odd, it never occurred to me that you could have a read only connection, read only credentials used by a connection but then it is the creds that are controlled, not the connection. What is wrong with setting up a set of creds that have RO access and your app uses them.
Never underestimate the power of human stupidity RAH
-
Ok so thats odd, it never occurred to me that you could have a read only connection, read only credentials used by a connection but then it is the creds that are controlled, not the connection. What is wrong with setting up a set of creds that have RO access and your app uses them.
Never underestimate the power of human stupidity RAH
The problem is that I do not own the database (as in I did not write the app that created it), and if (or more likely when) I distribute my app to other users I do not want to have to add users and permissions etc to their copy of the DB. I want just a simple read only connection from my app into the database, I do not want to modify the database in any way.
You may be right I may be crazy -- Billy Joel -- Within you lies the power for good - Use it!
-
The problem is that I do not own the database (as in I did not write the app that created it), and if (or more likely when) I distribute my app to other users I do not want to have to add users and permissions etc to their copy of the DB. I want just a simple read only connection from my app into the database, I do not want to modify the database in any way.
You may be right I may be crazy -- Billy Joel -- Within you lies the power for good - Use it!
Some nasty concepts are creeping in here
PJ Arends wrote:
I do not want to have to add users and permissions etc to their copy of the DB
Implies an embedded style database. Just which database are you using.
PJ Arends wrote:
I do not want to modify the database in any way.
Then control your code, you own the client you control it! If your users are writing sql script then there is no hope for you.
Never underestimate the power of human stupidity RAH
-
Some nasty concepts are creeping in here
PJ Arends wrote:
I do not want to have to add users and permissions etc to their copy of the DB
Implies an embedded style database. Just which database are you using.
PJ Arends wrote:
I do not want to modify the database in any way.
Then control your code, you own the client you control it! If your users are writing sql script then there is no hope for you.
Never underestimate the power of human stupidity RAH
Mycroft Holmes wrote:
Implies an embedded style database. Just which database are you using.
The DB is a local copy of SQL Server Express 2005
Mycroft Holmes wrote:
Then control your code, you own the client you control it! If your users are writing sql script then there is no hope for you.
I have read about SQL injection attacks. While it is not likely that users of my app would want to screw up their own data, having a truely readonly connection would prevent it.
You may be right I may be crazy -- Billy Joel -- Within you lies the power for good - Use it!
-
Mycroft Holmes wrote:
Implies an embedded style database. Just which database are you using.
The DB is a local copy of SQL Server Express 2005
Mycroft Holmes wrote:
Then control your code, you own the client you control it! If your users are writing sql script then there is no hope for you.
I have read about SQL injection attacks. While it is not likely that users of my app would want to screw up their own data, having a truely readonly connection would prevent it.
You may be right I may be crazy -- Billy Joel -- Within you lies the power for good - Use it!
Here is what I would do. From your app I would query the users/logins of the database, if your functional id (this describes the identity your app is going to use to communicate with the database. create the user with user name and password using sql authentication. Give the user RO rights on all the objects (views and tables) change your connection to use the RO credentials. I seriously don't know if a RO connection is valid, I have certainly never heard of anyone using one.
Never underestimate the power of human stupidity RAH
