Ideally, for an operational production database, you don't want developers having any access to the server or any database on it at all. That sort of thing is one of the first things you'll have to do for SOX compliance.
For the sort of rights that userIDs run under, the only rights they really should have are db_datareader
, db_datawriter
and explicit GRANT EXECUTE ON x TO y
(for each stored proc and user defined function x
for the userID y
).
If you need to be running traces in production, you have some problems and it will take a Great Wall of Textâ„¢ to explain it all. My first recommendation is to have a QA environment that is locked down just like production and if traces need to be run, restore a back-up of the prod db to QA and run the traces there. Again, if you have SOX, HIPAA or PCI-DSS requirements, then you better sanitize the prod data before restoring it to QA.
We currently have a windows group for DBAs that has rights to all of our servers and databases.
Give them logon and view data rights; however to perform DBAly duties, use a separate login with elevated privileges. I know one financial customer that does this - the regular windows authentication based logins were limited in the damage they could inadvertantly do. Restores and running DML required running with the separate SQL authentication login.
One government agency I worked with used 2 separate logins for each server/db admin. So if Tangurena
was my domain login (this login would have regular User
privileges), then TangurenaAdmin
would be my separate Administrator
login. You get into trouble if you use your admin account all the time, but then it lacks permissions to other things (like no email. Oh, you say that like it is a bad thing...).
The current government agency I'm working with has each server/db admins having privileges elevated above the standard user, but not quite admin (think of it as the PowerUser
group). Domain admin functions are performed with a shared domain admin account.
A common error is restoring the wrong database (like QA restored over the production server), and this isn't going to be solved via restricted rights or multiple logins. Doing potentially destructive things in pairs is one way to minimize the risks.
I'm guessing we'd need elevated privs to run traces as sa
No. You only need ALTER TRACE permissions:
http://msdn.microsoft.com/en-us/library/ms187611.aspx
As long as the traveling user will be adding, updating, and deleting records and can guarantee that no one else is touching in the master, there is something you can try.
What you want to do is setup MySQL Circular Replication (Master/Master) between Master and LapTop. Once you have this esatblished and the time has come for the traveling user (TVU) to leave for the week, here is what to do:
Step 1 : Run STOP SLAVE; on the Master
Step 2 : Run STOP SLAVE; on the LapTop
Step 3 : TVU inserts, updates, and deletes on the LapTop
Step 4 : Make sure TVU does not delete or update data that the Master will have worked on during the week.
Step 5 : When the TVU returns, run START SLAVE; on the Master to reconnect the Master to the LapTop. All inserts, updates, and deletes that occurred on the LapTop during the week will transmit over to the Master. Run SHOW SLAVE STATUS\G every couple of minutes on the Master and look for Seconds_Behind_Master going to 0. When it does, the data from the LapTop will be sync'd over to the Master.
Step 6 : (OPTIONAL) Run START SLAVE; on the LapTop to bring all the inserts, updates, and deletes the Master did during the week. Run SHOW SLAVE STATUS\G every couple of minutes on the LapTop and look for Seconds_Behind_Master going to 0. When it does, the data from the Master will be sync'd over to the LapTop.
The only real danger is if you delete or update data on the LapTop that still needs to be on the Master. If you forget that, step 5 will make that data change or disappear unintentionally. As long as you work with a new set of data on the LapTop only, there should be no problem.
You should test this between a DevServer and a LapTop you are using for Development only.
UPDATE 2011-07-24 21:42 EDT
Here is another thing you can try:
Step 1 : Setup mysql on the LapTop with no initial data on the laptop and no binary logging
Step 2 : mysqldump the data out of the Master and into the LapTop
Step 3 : Add this to my.ini on the LapTop
[mysqld]
log-bin=mysql-bin
Step 4 : Activate binary logging without a mysql restart on the LapTop
SET GLOBAL SQL_LOG_BIN = 0;
or just restart mysql
Step 5 : Perform inserts, updates, and deletes on the LapTop (Make sure they do not conflict with needed data on the Master)
Step 6 : run this
mysqlbinlog mysql-bin.0* > ThisWeeksChanges.sql
Step 7 : Run this on the Master
mysql> source ThisWeeksChanges.sql
Best Answer
When it comes to securing mysql, as sson as you install mysql, you must run the script mysql_secure_installation. If mysql is already in production for a while, you can perform the steps of the script manually with a lot of caveats.
For example, you have to erase all entries in mysql.db whose db column's first four(40 characters are 'test'. : Please see MySQL : Why are there "test" entries in mysql.db? (February 17, 2012)
You must drop anonymous users
Here are my other posts on securing mysql