Forum / Remote Desktop Manager - Support

SQL-permissions for RDM

  • Create an Issue
  • Cancel

Hi,

I'm having a small task making sure, that our database used for RDM (not RDM server), is set up properly.

The way we're accessing it is with "integrated authentication", where a AD-group membership is granting access to the database.
We don't use SQL-logins (and are not allow to create SQL-users - that's a task for our DBA).

I know that security (role-membership) has to be handled inside RDM, and cannot be handled by AD-groups.
I have tried to find some information in your knowledge base, regarding "best practices" for permissions on the database.

I prefer to have (at least) two different level of permissions. One with "full admin" and are allowed to handle upgrades of DB/users/permissions and other administrative tasks (assume it should be a DBO?).
Another level meant for "normal users" where they can create/edit/delete sessions/credentials/etc. and of cause write log-entries.

Can you help me with some information on how to grant the right way. smile

Best Regards,
Anders

Clock2 yrs

Hello,

We recommend using the Role Based Security system to apply security inside RDM.

For more information, please consult https://help.remotedesktopmanager.com/rolebasedsecuritysystem_simplifiedsecurity.htm

Best regards,



Jeff Dagenais
signaturesignature

Clock2 yrs

Hi Jeff,

That's also what we're doing, but I want the DB to be as secure as possible. Eg. there's no reason for at "normal user" to have CREATE/UPDATE/DELETE access to the table contains the security-setting for RDM. I don't see a need for a normal user to be able to update EVERY table in the database for RDM.
So I can also ask the question in another way.
See from a normal user, which tables should the user have CREATE/UPDATE/DELETE access to for the client to work. I assume that eg. the table for log is only needed to have CREATE-access for the "normal users"?

Br,
Anders

Clock2 yrs

Hi Anders,

First some background information. Since you are granting user login rights via SQL Servers Integrated Authentication, your user has direct access to the database and therefore could circumvent RDM security by accessing the RDM database directly. So the approach of validating who has access to what is very important.

We suggest you go with the "secure by default" approach. That is to deny (almost) everything and grant only what is actually needed.

Disclaimer: If you found landed here via search, be warned do not venture down this road unless you are well versed in SQL Server security.

It might look something like this (draft):

--revoke insert/update/delete to all objects in the database
REVOKE INSERT, UPDATE, DELETE TO <database_principal>;

-- grant insert/update to history/log tables
GRANT INSERT, UPDATE ON dbo.ConnectionHandbookHistory TO <database_principal>;
GRANT INSERT, UPDATE ON dbo.ConnectionHistory TO <database_principal>;
GRANT INSERT, UPDATE ON dbo.DataSourceSettingHistory TO <database_principal>;
GRANT INSERT, UPDATE ON dbo.ConnectionLog TO <database_principal>;
GRANT INSERT, UPDATE ON dbo.GroupInfoHistory TO <database_principal>;
GRANT INSERT, UPDATE ON dbo.LoginAttempt TO <database_principal>;
GRANT INSERT, UPDATE ON dbo.LoginHistory TO <database_principal>;
GRANT INSERT, UPDATE ON dbo.RepositoryHistory TO <database_principal>;
GRANT INSERT, UPDATE ON dbo.TodoHistory TO <database_principal>;
GRANT INSERT, UPDATE ON dbo.UserInfoHistory TO <database_principal>;
GRANT INSERT, UPDATE ON dbo.LogMessage TO <database_principal>;

-- grant insert/update/delete to the main tables
GRANT INSERT, UPDATE, DELETE ON dbo.Attachment TO <database_principal>;
GRANT INSERT, UPDATE, DELETE ON dbo.ConnectionGroup TO <database_principal>;
GRANT INSERT, UPDATE, DELETE ON dbo.ConnectionHandbook TO <database_principal>;
GRANT INSERT, UPDATE, DELETE ON dbo.Connections TO <database_principal>;
GRANT INSERT, UPDATE, DELETE ON dbo.ConnectionState TO <database_principal>;
GRANT INSERT, UPDATE, DELETE ON dbo.CustomImage TO <database_principal>;
GRANT INSERT, UPDATE, DELETE ON dbo.Subscription TO <database_principal>;
GRANT INSERT, UPDATE, DELETE ON dbo.SubscriptionEvent TO <database_principal>;
GRANT INSERT, UPDATE, DELETE ON dbo.Todo TO <database_principal>;
GRANT INSERT, UPDATE, DELETE ON dbo.TodoUsers TO <database_principal>;
GRANT INSERT, UPDATE, DELETE ON dbo.UserRoamingSettings TO <database_principal>;

-- grant for security tables
GRANT UPDATE ON dbo.DatabaseInfo (ConnectionCacheID, IntelligentCacheID) TO <database_principal>;
GRANT UPDATE ON dbo.UserInfo TO <database_principal>;
DENY UPDATE ON dbo.UserInfo (ID, Name, FullName, IsAdministrator, CanAdd, CanDelete, CanEdit, CustomSecurity) TO <database_principal>;
GRANT UPDATE ON dbo.UserProfile TO <database_principal>;
GRANT UPDATE ON dbo.UserAccount TO <database_principal>;


<database_principal> ::=
Database_user
| Database_role
| Application_role
| Database_user_mapped_to_Windows_User
| Database_user_mapped_to_Windows_Group <== you will most likely be using this
| Database_user_mapped_to_certificate
| Database_user_mapped_to_asymmetric_key
| Database_user_with_no_login

Note: If permissions are missing RDM will throw an error that looks something like this:2018+03+19+14+33+53
In this case you will need to add the missing GRANT statement.

Best regards,

Stefane Lavergne

signaturesignature

2018-03-19_14-33-53.png
Clock2 yrs

I know this is an old thread, but I have the same questions but with Azure SQL instead of on-premise SQL. I set the users up using O365 with MFA support (we are using free MFA with O365), but the users have way too high a level of permissions on the data source. Why not just use O365 to validate the username/identity of the user, but then still use an RDM service account (like custom login option does) to actually make the database connection? I don't want to have to switch to custom login and give users another username/password to remember and also lose our MFA with O365. In other words, I think it would be a good idea to split RDM and data source credentials into two settings, the same way custom login works, but allow other auth mechanisms instead of just a username/password for user authentication.

Clock2 yrs

@Tony

Very good suggestion. I will add it to our to-do list.

Best regards,

Stefane Lavergne

signaturesignature

Clock2 yrs

We actually already support it this with our DPS product. https://server.devolutions.net/

Stefane Lavergne

signaturesignature

Clock2 yrs

@Stefane, thanks for the quick response and info. It does make sense that DPS supports it since its a server product and doesn't have the added challenge of distributing service account credentials to a client side application in a secure fashion.

Clock2 yrs