Questions to any dba's

[MrNorth]

Hi!

I just started working as a database developer (oracle) at a medium sized company. And I was very puzzled with how the oracle dba's had things organized. This company have an environment as following:

2 oracle clusters
8 prod instances
6 test instances
3 dev instances

Each instance have about 10 databases each.

There are about 40 people at the IT dept that work with oracle, either as developers or just reading data. And about 40 external people at sales offices etc that mostly read views into external systems for analysis. Out of the 40 ppl in the it department 30 % are consultants.


That puzzles me the most is that the dba's have no role based security. WHen I need access to something I ahve to specify exactly the tables, objects, privilegies I need and then they add this to my db user. You can imagine when there are about 6 parallell development projects that all need access to different things on a running basis. I have waited for grants to a database for about 3 weeks now... And another colleague have waited 2 weeks for a database user.

What I can't understand is why they dont create and use role based security?????? You should be able to solve about 85 % of all security issues by using role grants, if you have a somewhat decent role tree. And only use explicit grants when there is no other way... What do you think about this? How would you have organized the security around this environment as a dba?

I can understand that they are over worked when they never use roles but do explicit grants to every user/object. Just think about how much work they can reuse if they create the roles once and then just add ppl to these roles..

/Henrik

[Ecniv]

I think that the organisation I work for uses a similar set up - perhaps it is the way oracle works on a default basis and no one wanted to change it (or that the front end needs it that way?).

I think Oracle is great, but I also think there are a lot of ill implemented solutions for it. Patches come and get added too

Roles sounds like a good idea, have you requested whether you can add them to test databases as a practice run?

[GaryMazzone]

I always set roles in the database and assign users to the role. Security is much easier that way. When some needs some other level of access you just assign them the required role. No should have direct rights to the tables or procedures just the role has acccess. It sounds to me that the DBA are lazy or don't understand the proper way to impliment the security issues.

[MrNorth]

Hi!

Im not sure why the dbas do it the way they do, I asked them about it and they claimed that role based security caused problems to them in the production environment... I got no further explanation. And since Im only a consultant for this company, I dont want to get involved too much. But I have worked as a supporting dba a few years ago, and I was mearly curious why they didnt handle security in line with best practices... Since they are all overworked with administrative issues. And the communication between dba and develoeprs are not the best, since they have no templates on how to order grants and stuff the devs write just a plain letter, and very often they forget to ask for grants to certain roles/objects, and usually it takes 3-4 times before the dba has given them the full access they really need, basically due to communication issues.

/Henrik

[Dnereb]

I suspect this practice is old (maybe from the ages roles weren't invented yet) and grown into the compagny way of doing things.
Change always creates resistance. One off the problems would be learning how to use and restructure into a role based security.
This can imply several weeks of lower productivity so especcially if some employees sabotage the change, this can happen if personal emotions are involved in the decision to change process or there are already tensions between groups of employees or permissions reflect some kind of status in the compagny.

All in all it can be a simple change but it can be a disaster as well.

[Ecniv]

...I asked them about it and they claimed that role based security caused problems to them in the production environment... I got no further explanation
...

Probably someone tried a while ago and didn't manage to set it up right, but in the process caused some downtime (or corruption ?). Hence they would be hesitant to try again.

Possibly, the front end relies on user rights rather than roles... and it would be a pain to recode all. They know, if they don't want to tell, up to them unless they pay you to check