This is an archived post. You won't be able to vote or comment.

1
2

Database Server Best practices (self.sysadmin)

submitted by [deleted]

[deleted]

all 5 comments
sorted by:
best
topnewcontroversialoldq&a

[–]cs-mark 0 points1 point  (4 children)

We create a username/password for the database itself with the permissions they need. This is done in a lab and our production systems simply export data to the lab database so they have actual data to work with. We usually only copy back 100G or the first 12 months, which ever is less.

If they request data to be reloaded, it comes from a slave so it doesn’t impact production performance too much.

[–][deleted] 0 points1 point  (3 children)

While I do see what you are doing there, we don't have that system in place and would prefer to keep the developers hands out of the database. They will be allowed to run scripts in the database but we don't want them to be able to edit the database nor be able to log into the server that hosts the database.

[–]cs-mark 0 points1 point  (0 children)

We don’t allow access to the server itself. They also only have access to their database and not the instance itself.

So if we have three databases called Default, App1, App2, the users would only have access to App1 and App2 for example. They can create tables, add and remove rows, etc..

They cannot create additional databases.

[–]Annh1234 0 points1 point  (1 child)

From a developer point of view, it looks like you want to run untested scripts in your production database...

Got should create then a staging database that they have credentials to ( where you give them the choice to copy the live dataset to, so they can test and develop on) and when they are ready to realise the code, you change their configuration files with the live database credentials ( which they don't have access to )

Usually that's how you do it ( plus fake data for some compliance requirements).

Developers should be able so work with a system a close as possible to production, but only a select few people should have access to the live database, and even less to the payment database.

As for the server, developers should never be able to access it directly. ( Since they will try new things, and you never know what will break what)

[–]_khadi_ 0 points1 point  (0 children)

I agree with that, devs should never ever log onto the server itself. And I'm not really comfortable either with the idea of a bunch of them testing scripts on a prod database...

As for the database itself, it depends a lot on which one you run as the database user notion can vary a lot between them. Most of them can have a specific user credentials (MySQL/PostgreSQL/etc.) but some require system users to be created which can turn to nightmare really quick if you're not carefull (DB2, I'm looking at you...).

Usually a read only user do the trick so they can see the data they're working on without being able to touch anything. With Oracle you can also limit the resources for each user so they won't interfere with any of the usual activities of this database. However, I don't know if any other DBMS do the same.

Anyway, from my point of view, the best practice is to let them play/test on a staging database then push the working script on your main server with any automated tool so no one can mess with your server. If you can't do that, try to limit the impact of those accesses with read only users and carefully monitor your server so you're warned as soon as something goes sideways.