A Web of Roles

Last night I completed the move of a production database that was running Oracle 9i on Windows 2003. It is now an Oracle 10g database running on RHEL. Things went pretty smoothly – because I was able to practice and refine the move multiple times before we did the real thing. It’s nice when one has the time and resources to do something the right way.

I don’t know if the way I did the move was the best way to do it or not – but it worked. My point here though is somewhat irrelevant to how I did the move – it’s just that I figured it all out getting ready. So I want to give the full picture for context. Since I was moving from 9i to 10g I decided to do an export from the old database and an import into the new database. Doing anything less than a full export left out things I wanted. Doing a full import brought things over that I didn’t want. In the end I did a full export, imported only the users I wanted from that export, and used some scripts to create the things I needed to make all that work. My scripts created tablespaces, users, and roles. They also did a lot of the grants needed. This is where I learned something interesting.

The application this db supports is all home grown. They use a lot of roles. Probably close to a couple hundred. When I would create all these roles in the new database – I would get errors because SYS would get too many active roles. (Oracle has a hard limit of 148 enabled roles for a user.) So as I created them, I revoked them from SYS.

I used the data dictionary to figure out all the user privileges and role privileges. As SYS – in the 9i database I checked role_role_privs to see if any roles were assigned to other roles for the application stuff. None showed up. (If you know Oracle you probably already see the problem.)

As I was testing, things were not matching up. As far as I could see everything matched up in both databases, yet when I logged on as some user in both databases, I obviously had access to more objects in the 9i database than I did in my new database. And after a really long time of banging away at it, I finally realized that qerying role_role_privs only shows results for active roles for the user running the query – even if that is SYS. There is no table dba_role_role_privs – though I really think there ought to be. Now more than anything this story makes me look stupid – but I was so used to being able to see everything as sys.

All the roles I was interested in for the application had been created with a common naming scheme. The all were named beginning with “R_” so they were easy to find, that was nice. So I just created a user and granted it all of those roles. Then I queried role_role_privs as that user – and pow – there it was. Tons of roles being granted other roles. So I wrote some scripts – and that fixed things.

This brings up a bigger point though. Based on this experience and just thinking about it from a developer and dba perspective – I’m thinking that assigning roles to roles is something that ought to be done with great caution. It is the equivalent of a goto in my mind. There can be layers and layers of these relationships – roles granted to roles granted to roles, etc. And with each layer it becomes increasingly difficult to know just what privileges are being given to a user.

I’m not sure why this application has so many roles or why roles are then granted to one another. I wasn’t there as this thing was developed, and I’m not going to second guess. And there may be someone out there who knows a really good reason on why one would want to do this. (I’d love to hear about it.) But I think right now that this is a capability that should be used with great caution and thorough documentation.

CRS is Forever

Here is what seems to be true to me based on recent experience. If you ever add anything to CRS – by any means (dbca, crs_register, srvctl add, whatever) you can’t ever really take it out. Ever. Oh you can remove and whatnot all day long. Run crs_stat and nothing shows. But just try and add whatever it was again later with the same name. It wont work.

This leaves an Oracle dba with a couple options. 1) Use another name. Not a big deal in some cases. Say with a service nobody is using yet anyway. But with a database? Not so simple. 2) Restore an old ocr bacup. That’s what I did today – still fighting something but seems to have helped by and large. 3) Wipe out crs and build it again. This is usually the RAC option for anything. Rip it all out, wipe it clean and start all over. Problem is – it gets a might bit old.

oh and a bonus bit of information. srvct/crs error messages? worthless. look up in the docs know what most of them say? ‘Call support.’ Yeah – thanks.

In the office

Well, I’m pretty fortunate that I don’t have to work outside of ‘normal’ hours too often.  Today I’m in patching a test server and I hit a couple snags so it’s taken a bit longer than I thought it would, but it still hasn’t been bad.  Right now I’m just watching a sql script run – which I get to do 2 more times and then run another one 3 times.  After that I can go.

Each time I do one of these I learn something new.  So it is a worthwhile exercise aside from the benefit to the organization.

Next week is the biggie.  Patch the production RAC cluster.  That ought to be fun.