[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Negative restrictions in MySQL 4.1.22?

Posted on 2007-10-16
6
Medium Priority
?
451 Views
Last Modified: 2013-12-13
Hi

This might be a though nut to crack. . .

Our <application>, mostly PHP based, uses a single MySQL user account to both read and write data to our master database server, which in turn sends it to its slaves, you know, your standard replication setup.

Now, I have some tables I want to restrict write access to, but the MySQL user automatically has access to everything because of its GRANT ALL ON *.*  TO user@host  privilege.

I have thought about chmod-ing the table, but it isn't very practical since it is on a different server.

The other option, defining privileges of each of the 20K+ tables in our databases is not only very daunting, but we'd need to employ an extra hand just to maintain those privs, that, and they don't work very well with temporary tables (with uniqid names)

So, is there a way to do in MySQL

GRANT ALL ON *.* EXCEPT thisorthadatabas TO user@...

Or another way you can think of revoking write privilege to the user for this particular database.


Thank you.
0
Comment
Question by:x_terminat_or_3
  • 3
  • 3
6 Comments
 
LVL 17

Expert Comment

by:Aleksandar Bradarić
ID: 20091193
0
 
LVL 2

Author Comment

by:x_terminat_or_3
ID: 20091335
All REVOKE does is take privileges away, what I want is exceptions to global privileges, like if a user has a global update priv, I want it to exclude access to a certain database.
0
 
LVL 17

Expert Comment

by:Aleksandar Bradarić
ID: 20091678
I'm afraid this can't be done as far as I know. Global privileges (table: mysql.user) take precedence over the DB privileges (table: mysql.db)...
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 2

Author Comment

by:x_terminat_or_3
ID: 20095163
Any other ideas on thow to do this?, maybe not with SQL language, but with another means?
0
 
LVL 17

Accepted Solution

by:
Aleksandar Bradarić earned 1500 total points
ID: 20095184
Well, it's not an SQL problem - it's just the way MySQL privileges work :( Sorry :(
0
 
LVL 2

Author Comment

by:x_terminat_or_3
ID: 20114865
Thank you for your input.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses
Course of the Month18 days, 15 hours left to enroll

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question