0

I met strange security result on my Delphi 10 Seattle using SQL Server 2008.

I need read-only DB access, so made dedicated r/o user; ADO connection string in TADOConnection uses its credentials. Test query

select system_user

shows its username.

T-SQL code looks like:

select SValue 
from Table 
where SValue = '1'; 

update Table 
set SValue = '1';

In SQL Server Management Studio this code shows error if I login with r/o user. When I use this code in Delphi with TADOQuery.ExecSQL, it also shows exception with r/o access error. But when I use it as TADOQuery.Open it works fine and successfully updates the database.

What it could be a cause of such behavior and how I could prevent further write access on Open?

RRUZ
  • 134,889
  • 20
  • 356
  • 483
Sergey Ryvkin
  • 81
  • 7
  • If it is not allowed in SQL, there is no way any kind of component can do that. please provide your connectionstring. Maybe ADOConnection is connecting using any other user. Use SQL Profiler to see which user name connects to database when you use TADOQuery.Open – FLICKER Feb 03 '17 at 22:31
  • I created MS SQL user as datareader only. After ADO.Connect I open test query to show actual user: "select system_user", it shows this r/o user. Connection string contains MSSQL.1 and ID and password of r/o user. And I see this behavior on TADOQuery.Open only. In my case I see record with updated field, and I'm sure that this field was empty before open. Try to reproduce. – Sergey Ryvkin Feb 03 '17 at 22:40
  • And pay attention that the query string contains two sql commands Select and Update simultaneously. This is unusual but possible. – Sergey Ryvkin Feb 03 '17 at 22:43
  • It is very normal. you can run multiple query using ADO. The point is any component, at the end of the operation sends the query to database. so if SQL rejects a query, it's impossible you can do that using any component. don't trust your code. I suggest to use profiler and see what is actually executing and which user is doing that. I agree that this is weird but I'm sure your ADOQuery is using a different username. I'm curious to see the reason :) – FLICKER Feb 03 '17 at 22:47
  • Please provide your connectionstring – FLICKER Feb 03 '17 at 22:51
  • Sorry, I'll have access to my laptop on Monday only, now write code from my mind. – Sergey Ryvkin Feb 03 '17 at 22:51
  • ADO.Close; ADO.ConnectionString := 'string'; ADO.Open: ADOQuery := TADOQuery.Create(nil); ADOQuery.SQL.text := 'select system_user as S'; ADOQuery.Open; ShowMessage(ADOQuery.FieldByName('S').AsString); This code shows my r/o user name. If I set SQL.Text with my complex select/update query, it returns updated record. I'll try Profiler on Monday, but it would be strange if these two query items will be executed on behalf of another user. – Sergey Ryvkin Feb 03 '17 at 23:01
  • @FLICKER Connection String is: _Provider=SQLOLEDB;Password=*****;Persist Security Info=True;User ID=****;Initial Catalog=****;Data Source=XXXX_ I also set ADO.Mode := cmRead; It does not fix the issue. – Sergey Ryvkin Feb 06 '17 at 07:32

0 Answers0