Permissions for Read-Only user in SQL

Strange I will need to double check this, might be a bug in v14. Read only users should have read-only access to most tables. Why most? User will still need write to the log table for example.

As with any client-server type application, preventing the user from manipulating the data directly in the database can very between tricky. Once a user has any sort of authentication on the server (integrated authentication or SQL authentication) it is very difficult to prevent them from malicious actions like deleting or modifying data.

With RDM, one way around this is to not use SQL Authentication but rather use our own "Custom login". With “Custom Login” you configure the data source to connect to the database with a secret database login (say "RDMUser") and then lock the data source. Then you create “custom logins” that users will then use to authenticate and work within RDM. The custom login doesn’t grant them any direct access to the underlying database, all connections to the database are done using the “RDMUser” login.

Outlined here: https://blog.devolutions.net/2016/10/new-sql-server-custom-authentication-mode

Another work around is to use DPS (Devolutions Password Server) to act as a middle man between RDM and the database, preventing users direct access to the underlying database.

With all that said, I will investigate the possible issue on Monday and get back to you. Read-only users should still have a somewhat limited read/write access to the database.

Best regards,

I had a look at the grant/deny/revoke statements issued via RDM for a read-only user in SQL Server.

Here is the output of such a user
Schema Object username permission_name state_desc column_name
NULL DATABASE ReadOnlyUser CONNECT GRANT NULL
NULL DATABASE ReadOnlyUser DELETE GRANT NULL
NULL DATABASE ReadOnlyUser INSERT GRANT NULL
NULL DATABASE ReadOnlyUser SELECT GRANT NULL
NULL DATABASE ReadOnlyUser UPDATE GRANT NULL
dbo Attachment ReadOnlyUser DELETE DENY NULL
dbo Attachment ReadOnlyUser INSERT DENY NULL
dbo Attachment ReadOnlyUser UPDATE DENY NULL
dbo Connections ReadOnlyUser DELETE DENY NULL
dbo Connections ReadOnlyUser INSERT DENY NULL
dbo Connections ReadOnlyUser UPDATE DENY NULL
dbo Connections ReadOnlyUser UPDATE REVOKE ModifiedDate
dbo Connections ReadOnlyUser UPDATE REVOKE ModifiedUsername
dbo Connections ReadOnlyUser UPDATE REVOKE ModifiedLoggedUserName
dbo DatabaseInfo ReadOnlyUser DELETE DENY NULL
dbo DatabaseInfo ReadOnlyUser INSERT DENY NULL
dbo DatabaseInfo ReadOnlyUser UPDATE DENY NULL
dbo GroupInfo ReadOnlyUser DELETE DENY NULL
dbo GroupInfo ReadOnlyUser INSERT DENY NULL
dbo GroupInfo ReadOnlyUser UPDATE DENY NULL
dbo UserGroupInfo ReadOnlyUser DELETE DENY NULL
dbo UserGroupInfo ReadOnlyUser INSERT DENY NULL
dbo UserGroupInfo ReadOnlyUser UPDATE DENY NULL
dbo UserSecurity ReadOnlyUser DELETE DENY NULL
dbo UserSecurity ReadOnlyUser INSERT DENY NULL
As you can see all* the major tables (connection & security tables) have proper DENY for the read-only users.

Statements such as the following will fail:delete dbo.Connections where ID = '68CC5D41-67EE-440B-BA71-0015E4EFE07D'
update dbo.Connections set ID = '68CC5D41-67EE-440B-BA71-0015E4EFE07D' where ID = '68CC5D41-67EE-440B-BA71-0015E4EFE07D'
insert into dbo.Connections(ID) values (newid());

Msg 229, Level 14, State 5, Line 21
The DELETE permission was denied on the object 'Connections', database 'Std10', schema 'dbo'.
Msg 229, Level 14, State 5, Line 22
The UPDATE permission was denied on the object 'Connections', database 'Std10', schema 'dbo'.
Msg 229, Level 14, State 5, Line 23
The INSERT permission was denied on the object 'Connections', database 'Std10', schema 'dbo'.

* There are, however, a few satellite tables that could benefit from a more restrictive permission grant. I will add it to our to-do list.

Best regards,