If you can modify the configuration of the slave, then the solution would be to fairly straightforward: turn on log_slave_updates on the slave, which will result in the writing everything changed on the slave via replication events into the slave's own binlog, which you could then "sniff" and decode every event as it occurs, by connecting to the slave as "your" master and reading from the replication stream that it generates.
Further, changing the slave's binlog_format to ROW will cause the slave to translate any statement-based events from the master so that they will be written to the slave's binlog (and replicated to you) as row-based events, which are dramatically simpler to process from the replication stream.
Once you have that, there are numerous projects in various languages that can spoof a slave connection and decode a replication stream, although in my case, I ended up writing my own parser because I wanted to genuinely understand the low-level nuts and bolts of the implementation well enough that I could thoroughly troubleshoot it as well as extend it... because with each major version iteration from MySQL Server 5.1 → 5.5 → 5.6 (I don't have anything older in my networks), new capabilities in replication have been introduced that will break older code that fails to understand the new structures. This, incidentally, is why you always upgrade the slave first, when doing version upgrades, since new slaves understand everything an old master can send, but not the other way around. Some of the open source projects that accomplish this will not work with newer versions of MySQL or will require non-default configurations, because of these changes.
I also wanted support for the compressed client/server protocol, which can significantly reduce the number of bytes sent over the wire in replication events, but which didn't often seem to be as much of a priority to some of the other projects.
Sniffing the replication stream has been very useful in my operation, since I really hate polling. The one caveat I would point out that you might not expect is the fact that foreign key cascade events are not explicitly replicated, ever. MySQL itself is apparently completely unaware when InnoDB performs foreign key operations on the child tables as a result of updates or deletes on the parent table. I never thought about precisely which "layer" was doing this before, but it makes sense... if foreign keys weren't happening strictly internal to InnoDB, then there's no reason why MyISAM wouldn't have them by now... and there would also be no reason why foreign keys cascades couldn't activate triggers, but that too makes sense in this light, since triggers happen at the MySQL layer, not the storage engine layer... and this also explains why cascade actions are not reflected in the "rows affected" count. The rows changing on the parent table are replicated, and on a normal slave, the foreign key cascades are handled internally by InnoDB magic... but that's the only unexpected thing I've encountered in the last couple of months of using this in live (back-end, not end-user-facing) systems.
In SQL Server Management Studio, expand the 'Security' node under the server instance in question.
Add the Windows Group in question to the Logins node.
Under the Server Roles section, leave the restricted groups, such as DEVS, QA, etc, as Public, and assign them access to the desired databases via the User Mapping node.
For the DBA group, assign them the sysadmin server role.
This will prevent databases being dropped / replaced by everyone except members of the DBA group.
Look at SQL Server Books Online for further details: http://msdn.microsoft.com/en-us/library/aa337552.aspx
In order to allow users to create databases, you could add them to the dbcreator role. Unfortunately, this role also allows members to DROP DATABASE, etc, as documented at http://msdn.microsoft.com/en-us/library/ms188659.aspx
A server-level DDL trigger could be created to prevent users performing DROP DATABASE such as:
USE master;
GO
CREATE TRIGGER DropDB ON ALL SERVER
FOR DROP_DATABASE
AS
BEGIN
IF COALESCE(IS_MEMBER('dba'),0) = 0 ROLLBACK;
END
GO
ENABLE TRIGGER DropDB ON ALL SERVER;
However, this does not prevent the user from executing RESTORE DATABASE commands.
Additional Info
Your requirements can be resolved by creating a stored procedure that allows users to create databases, whilst preventing them from performing the other actions allowed by membership in the dbcreator server role.
Details about how to create the stored procedure in a secure manner have been provided by @PaulWhite in his detailed answer here.
Best Answer
Always just give the grants necessary. If you want to restrict on the row number, you have to create views like
Then just grant select on this view, not the table the view is reading from. Read more about views here. With
DEFINERandSQL SECURITYyou can also select on things, that the user usually has no right to. Whatever you need.As a last note, I included
ORDER BYin the query, because aLIMITwithoutORDER BYusually doesn't make much sense, cause there's no order in a relational database unless you specify it with saidORDER BY.