Re: Question About Oracle Users

comments embedded

"Daniel" <dhw377_at_usa._NO_SPAM_.net> wrote in message news:ns0U6.17672$e34.5323579_at_typhoon.southeast.rr.com...
> I just describing what is in place at the current moment. Perhaps you did
> not understand what I was really trying to say. My question is, is it
 better
> to have 50,000 Oracle users and validate them when they try to connect to
> the database with their username and password ..... OR ..... connect to
 the
> database with some same master Oracle user and then check the client's
> username and password in a user defined table?
>
> Perhaps I should have phrased my question this way.
>
> I want to know if there are any issues with having 50,000 Oracle users,
> performance-wise, etc.

Having 50,000 users in Oracle has the following disadvantages: 1. security management (users,roles,synonyms,auditing) can be painstaking 2. the system tablespace may become oversized and perfrormance may be affected (though not considerably)

However, this configuration has many advantages over application-managed authentication (with usernames and passwords stored in non-system tables and authentication handled by application code): 1. user authentication is performed by Oracle, therefore security is better and guaranteed to work (in contrast to application authentication which can be buggy)
2. passwords are encrypted at some basic level. 3. with Oracle 8i, advanced secutity options can be employed (e.g. kerberos, radius authentication, etc.)
4. auditing is possible with built-in Oracle mechanisms. 5. session monitoring, tracing and problem resolving is possible (say you have 1,000 users connected and imagine having to identify a session by the username: it is easier when you see 1,000 different usernames in v$session than seeing the common single user 1,000 times!) 6. security policies can be applied with password-expiration, password-complexity verification, account-disabling after unsuccessful connection attempts, etc.
7. data access can be better controlled per-user with built-in features (e.g. synonyms, grants, roles)
8. users can maintain private copies of data if necessary by using private tables, not available in public.
9. resource consumption can be controlled per-user with profiles (and in 8i with resource management).
10. (most important) security is centralised as all of the above apply to any application connecting to the database. On the other hand, if security is delegated to the application, it's very likely that soon or later some knowledgeable person will manage to access the database with some other tool (e.g. sql*plus) and mess around.

It seems your organisation is big, so you would normally want to apply security to the maximum rather than reinventing the wheel with application-managed authentication and risk your head. I believe that having thousands of users is not bad at all; it's probably the best thing you can do.

Though, there is no directive: choose the most appropriate security model for your case, balancing advantages and disadvantages.

>
> Thanks,
> Daniel
>
> PS. When I mean't deleting users, I meant dropping them.
>
> > Please go ahead ruining your already desupported database.
> > You want to delete directly from all_users?
> > Ha!

I thought the purpose of this newsgroup is to help people on Oracle issues, not laugh at them.

> > Know anything about Oracle?
> > Why on earth do you want to do this?
> > Why don't you just drop these user? This is the only way to get rid of
 them
> > in all_users, but they won't be able to connect anymore.
> > 50.000+ users on *1 single* desupported database?

That's irrelevant. Supported or not, the database will work for years with just 2 or as many as 2,000,000 users.

> > You can't be serious, or your department wants to potentially ruin your
> > company.
> >

The department will most probably ruin itself if they allow everyone to connect with the same username/password. Companies usually get ruined by the management, not techies.

> > Regards,
> >
> > Sybrand Bakker, Oracle DBA
> >
> >
> >
>
>

--
Giorgos