Re: Question About Oracle Users

On Fri, 08 Jun 2001 11:38:49 GMT, you wrote:

>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.
>

well, i'll disagree:

ops$tkyte_at_ORA8I.WORLD> select count(*) from all_users;

  COUNT(*)

     43363

ops$tkyte_at_ORA8I.WORLD>

thats my production instance. Having the ability to use roles, revoke privs, having accountability, etc -- its great.

logging in happens just as fast as it did when I had 10 users.

If you have a system with 50k users registered already, and it works, it would not be a wise choice to drop them -- change the security of the apps and do a table based method (IMHO)

>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

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://asktom.oracle.com/ http://asktom.oracle.com/~tkyte/
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp