Re: Question About Oracle Users

On Fri, 08 Jun 2001 00:48:23 GMT, "Daniel" <dhw377_at_usa._NO_SPAM_.net> wrote:

>Currently, our system has about 50,000+ Oracle users. That is, there are
>50,000+ entries in the system table, "all_users". I am trying to determine
>if this is the best and most efficient way to do this. Would it take less
>resources and make the database go faster if I deleted all these Oracle
>users and instead just put their info into a new user created table with
>their username and password?
>
>So, I delete all the Oracle users, create a new table called Users, with two
>columns (username, password), and insert 50,000+ rows into this table.
>
>I believe I am using Oracle 8.04 (not 8i).
>

I have never hit a situation like this. Mostly because I've never had more than about 10000 registered users and even then 3/4 of them needed cleanup. But assuming you really need the uid/pwd of that small nation:

I'd look into doing what you just said: login as a common user, get a peek at a uid/pwd table after asking the users for their respective and check against that.

If you ever need to create private synonyms or individually add all those users to roles for authorisations, odds are something will slow you down inside the ORACLE dictionary, section "security clearance". Maybe Jonathan will have some experience with this, he regularly plonks tens of thousands of entries in ORACLE where nobody would do it? Part of his testing and darn useful it is too.

I'd say go for it: put all that mob in a table, use a single logon and check them against the table. It will be faster for sure than ORACLE having to pour through a 50000 entry USER$.

Cheers
Nuno Souto
nsouto_at_bigpond.net.au.nospam
http://www.users.bigpond.net.au/the_Den/index.html Received on Fri Jun 08 2001 - 06:38:49 CDT