38

Suppose a user has full read/write access to MySQL database. Is there any way (some parameter in connection string) to connect to database by the same username and password in read-only mode?

I want this without changing this user's permissions because the same user might require write permission too at some other time. This would be useful (if possible) to prevent accidental modification to database.

Dimitris Papazacharias
  • 661
  • 1
  • 7
  • 21
gtiwari333
  • 24,554
  • 15
  • 75
  • 102
  • Check related questions. http://stackoverflow.com/questions/654656/odbc-5-1-connection-string-for-mysql-with-read-only-access http://stackoverflow.com/questions/10122745/mvc3-read-only-mysql-connection-string – hgulyan Apr 20 '12 at 05:52
  • 1
    According to them, there's no way to specify read-only access via connection string. Actually you should create sql user with read-only permissions or make permissions layer in your software's business logic. – hgulyan Apr 20 '12 at 05:55

3 Answers3

18

The answer to your question is

No, there's no way to specify read-only access in the connection string.

Alternatives are

1. Create sql user with read permission

MVC3 Read-Only MySql Connection String

2. create views or stored procedures with permissions checking logic in them

MS SQL Grant permission to only a view

MySQL Grant a user permission to only view a mysql view

3. Implement permissions layer in your business logic

Good Luck!

hgulyan
  • 8,099
  • 8
  • 50
  • 75
  • On item 3, The link you have supplied is for MS SQL Server. I'm not sure how to ensure similar access in MySQL is readonly. – 700 Software Aug 18 '17 at 20:26
  • 1
    @GeorgeBailey You mean on item 2 about granting permission on view level? Thanks for the catch. I've added a link for MySQL, seems that syntax looks similar. Thanks again! – hgulyan Aug 19 '17 at 17:07
17

The best solution here is to create another account on the mysql server with readonly permissions, and connect using that.

Jon Marnock
  • 3,155
  • 1
  • 20
  • 15
  • I know. But I was wondering if there is any option without this? Thanks. – gtiwari333 Apr 20 '12 at 05:54
  • Sadly, not. I considered doing something clever like immediately locking all tables in your database for read, but (a) that would cause a nightmare for concurrency and (b) it's easy to maliciously break out of if you UNLOCK TABLES, and (c) it prevents you from using locking in your query (though a readonly session probably isn't too likely to use that anyway.) You know more about the target system than we do, so if LOCK TABLES tablename READ will work for you, hooray, otherwise yeah, the best and ~correct~ thing to do is have two users with one being readonly. – Jon Marnock Apr 23 '12 at 08:00
9

Depending on your use case and what control you have you could have the code call "set transaction read only" immediately after connecting, or use the --init-command parameter on connect. This worked for a testing use case we have,

Here's the set transaction doc: https://dev.mysql.com/doc/refman/5.7/en/set-transaction.html, similarly you can also set it as a session variable if that makes a difference https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_transaction_read_only.

Isaac
  • 1,505
  • 15
  • 8
  • This is by far the most useful answer for this question. – mathieu Jan 21 '20 at 08:12
  • 2
    Note that when using the `SET` method, you may want to include `SESSION` (I.e. `SET SESSION TRANSACTION READ ONLY`) otherwise the effect only applies to the very next statement, after which the transaction mode is reverted. – Courtney Miles Nov 18 '21 at 21:46