RE: Become User privilege

Hi Kimberly,

I think our respective usage of "users" may differ slightly. We have a few different 3rd-party packages that use Oracle. Each one has it's own schema, and with some forethought, it's own database. I guess that's what you referred to as "application accounts". Makes sense to me. I'll add that one to my vocabulary. :)

Anyway, because I didn't have a whole lot of expertise in Oracle at the time (like I do now? -- HA!), I didn't realize that perhaps the setup parameters in the various databases, instances, and schemas were not particularly best suited to our environment/workload/etc. So, now we have the enviable task of making everything run faster and better. This, of course, includes reorg'ing tables that have hundreds of chained rows and hundreds (even thousands!) of extents due to things like PCTFREE of 5 on heavy-traffic tables and NEXT_EXTENT of 28K on audit tables.

Now, if I could do this using a SYSDBA account, there would be no problem. But since I can't, I now need to make sure that I don't lose any security info about the reorg'd table, since one not granted privs to that table may not be able to see all the privs granted to other users. This generally means I need to do the reorgs while logged into the application account, which normally is not afforded all the privs I'd like when reorg'ing (e.g. UNLIMITED TABLESPACE). Without trying to sound too whiny, I guess my point is that when I login as SYSDBA, I want to be god (note the small "g"). I want to be "DBg", instead of just "DBA". It would help me immensely in an environment that I'm not always able to change.

Thanks for listening,

Rich Jesse                          System/Database Administrator
Rich.Jesse_at_qtiworld.com             QTI -- Sussex, WI USA

> -----Original Message-----
> From: Kimberly Smith [mailto:kimberly.smith_at_gmd.fujitsu.com]
> Sent: Tuesday, August 29, 2000 12:39
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Become User privilege
>
>
> I normally would not even want to reorganize user tables. So I guess
> I never had a reason to complain about that. For the most part I deal
> only with the application accounts and I sure don't have 60 of those.
> I would then grant to roles and grant the roles to users. If joe blow
> developer needs a table in his own account they know how to
> create them
> or I tell them how (or point them to the proper manual). They do not
> need them tuned in that environment. They are not on my
> production system
> so its not affecting anything else. That is the way its worked on all
> enviroments I have been in. Now granted there could be
> someone out there
> that has 60 application accounts but if it should still be
> the dba touching
> the production accounts so consolidate the passwords.
>
> -----Original Message-----
> Sent: Tuesday, August 29, 2000 9:48 AM
> To: Multiple recipients of list ORACLE-L
>
>
> That'll work fine and dandy until you try and GRANT privs on
> those objects
> to other users.
>
> <soapbox on>
>
> No one has yet been able to satisfactorily explain to me, the
> "A" in "DBA",
> why I don't have full control of database security! I can
> create objects on
> behalf of users, but I can't grant security on those objects
> to others???
> So now I have to have a list of 60 different Oracle accounts
> and passwords
> that I need to use and maintain in order to reorg user
> tables. Why, why,
> WHY??? <fume, fume>
>
> <soapbox off>
>
> Rich Jesse System/Database Administrator
> Rich.Jesse_at_qtiworld.com QTI -- Sussex, WI USA