what about development database
rati todua, February 08, 2017 - 6:12 am UTC
i see. that will be hard to achive because our developers are addicted to go in production db as dbas and do whatever they like. but it will be right thing to do.
and what if this is development database?
if i want developers to have access on object they create in another schema(without giving them permission on this object after it is created)
is there workaround for this? or am i forced to give permissions again and again on newly created object in another schema?
February 10, 2017 - 3:37 pm UTC
In a *development* database, the dba's job ( in my opinion) is to facilitate as much productivity for developers as possible.
I'd be much more lenient with the privilieges you give them, and develop an understanding with them that they also now have an increased responsibility to "play nicely with others" as part of that arrangement.
Another possible option
J. Laurindo Chiappa, February 10, 2017 - 4:18 pm UTC
In a development environment to avoid catfights and conflicts I think that some rule like this maybe could be applied : a schema owner of the application (say, APP_OWNER) exists and contains all the application tables, sized and populated with faked data but in volumes and types very very similar to prod. Each developer receives their own database schema/user, containing one private synonym pointi to each table in APP_OWNER schema, and he/she have too the right to SELECT APP_OWNER and to create his/her own tables in his/her schema: so, if he/she want to test a new column in table X, he/she simply delete his/her synonym X and create a new private table called X including the new column, populating it with a SELECT FROM APP_OWNER.X....
Only when the alteration is validated/fully tested the same DDLs he/she did in their own schema is applied in APP_OWNER (and thus the other developres using that table X must adapt their code to the new column, say), and later in Homo and finally in PROD...
Regards,
Chiappa
pmdba, March 12, 2017 - 4:55 am UTC
In a production database Connor is 100% right: always start with virtually nothing and grant only what is absolutely necessary. In general, development and admin responsibilities should be completely separate in production and any changes they want to make should go through a real DBA. A production system with no accountability for its developers won't last very long, in my opinion.
For development systems, there are a couple of ways to go: each developer should have an individual account to start. Depending on how you want to audit their activity you can either grant some of the "any" privileges to them so that they can work across schemas (easy to audit individual actions that way), or grant them proxy connect privileges to the application schemas they need. That way they connect to shared schemas with their personal credentials (no password sharing) and can only work on the schemas you allow them to. It's a little harder to audit individual actions this way, but not impossible.
I do not recommend creating tons of synonyms in personal schemas, as you would need to create and maintain potentially large numbers of individual object privileges and synonyms.
March 13, 2017 - 2:47 am UTC
nice input
Users get addicted too
Chuck Jolley, March 13, 2017 - 1:19 pm UTC
"that will be hard to achive because our developers are addicted to go in production db as dbas and do whatever they like. but it will be right thing to do."
We had a developer on a module of our accounting software who did this and it's the USERS who became addicted to being casual with mistakes because they knew he would go in and fix things for them after just a phone call.
It took literally 10 years to break them of it after he left.
The trick was to pile them with an onerous amount of paperwork if they wanted a data fix.
March 14, 2017 - 3:46 am UTC
"that will be hard to achive because our developers are addicted to go in production db as dbas and do whatever they like"
Only one thing is certain with that policy..... you're gonna lose your database one day :-)