Tom:

My understanding is you create a schema per application not user. If it is the same then how can you use the DB to manage hundreds of users instead of creating a user table in the application.

managing hundreds of schemas or users in oracle would not be easy to do i assume.

can you tell us how you implement an inventory application with 500 users in the DB. One schema and 500 user accounts.

This is not that big of a deal. Lots of applications are setup like this. However, I would try to implement something where all 500 users didn't have direct access to the database unless there was some compelling reason. This is just to give you more control over the number of sessions going on in the database.

TOm:

I am little confused. When I think "schema" I think "db application" and not "user in the outside world"

Can you provide a small example of how you set this up in the DB versus an application table.

Let us say you have a database application where you sell skin supplies.

I set up a schema in oracle called "skin" and create all my packages, tables, triggers, etc. in it.

Now, if i want to open this to the web to get orders (no authentication), i assume that is the only schema i need.

however, if i want to only allow certain number of users to log in and order supplies, i have to create 500 additional schemas and these schemas will be empty. so i go to "skin" and create roles and privileges and assign to those other 500 schemas?

2. It seems to me (not sure) that most applications use table for authentication and authorization. do you agree? and why is that common instead of creating DB accounts.

Can you imagine amazon.com with using database accounts instead of applciation table. They would need many many dbas probably.

Tom:

so you are saying that you can create and manage accounts as simple as creating a custom table and inserting userids/passwords into it. And oracle has written all the API code for us to manage this?

If you are designing a system now for an enterprise, would you be creating DB accounts instead of custom table?

2. If you are writing an app for yahoo mail accounts in oracle, you would let your API create a DB account, and then drop it if I decided to close the account. would not you lose all of the historical records.

How about if i want to store personal information for the user with the account and create flags or whatever. Can you do this with a database account or it would a separate table that references the DB account.

As much as I used to like this website, more recently your answers seem to be angry....

May be time to take a vacation :)

Cheers!

I do not detect any anger in Tom's answers. In fact these are as good as it gets for the questions that are being asked.

Tom,
I have the following issue with database user accounts and need your suggestions.

We have production database where users can register on the web and application creates a database account for them. There are other user accounts which are created for various other purposes.

Now that we have 10k+ user accounts, it is almost impossible to tell which accounts were created by the web registration part and which were created manually. Oracle doesn't provide any feature by which I can put a comment with a user account to distinguish what kind of account it is. Is there any workaround?

Thanks

There is no direct way, however, you can just create a table and insert a row with the username when it is created by the application.

If you're trying to differentiate between different groups of users then this would be an appropriate use of a role. The role doesn't necessarily need privileges, but would serve as a marker for that user. Of course, this would also allow a user to be in two (conflicting) groups. Why is it significant how the user was created? If it's a matter of rights then use roles. If we "just want to know" then create a simple table.

The application i have been asked to work on always use one or two users, and user management is in the application.

I think there are two reasons for that. One, letting a programmer design the database. Programmers seem to like control, and that is in the application, not that database that just stores stuff. Two, to create a user there is a process and other groups in the company, making it too cumbersome to get anything done. Such as waiting a couple days to change a user's right is usually not acceptable.

There's also the program logic that is defined outside the DB, making it nearly impossible to put together a ROLE with just some rights. Which means, user rights would be in the application anyway.

I don't think people realize that the database is not just a place to store data, but is an application for the storage, retrieval, and management of data. Together with stored PROCEDUREs, a fully functional, reliable, and secure API can be created. Even if they did know that could be done, actually creating an API--and not just a bunch of PROCEDUREs that does what the application needs right now--takes planning, another thing which many times is unfortunately lacking.

I love reading replies by Tom where he states the obvious. Hopefully, people will "get it" after a while, and start actually using the DB for more than just a data store.

Hi Tom,

On a client-server project that I worked on, we used the database to manage the users, their privileges etc. and it was the best solution that I can think about for a client-server situation.

Coming to web based applications, typically a J2EE architecture, how would the database authentication work, more particularly when applications are designed to work in a time-share mode. I mean, when applications work in a connect-authenticate-getyourjobdone-disconnect mode, ie. every database activity from the application layer involves an authentication, whereas the actual application users could be different and more in number.

Thanks & Regards
Rama Subramanian G

Its an interesting thread

How many users can the Oracle database hold?

If we were designing a Hosted web application that in theory could have hundreds of thousands of users then we'd be better going the table application user route? Or would we?

I would try to summirize a relation between user and schema:

1. "User" is authentificable entity.

2. Each database object is owned by somebody ("user"), no unowned objects exist.

3. Ownership relation is immutable (i.e. owneship of DB object can not be changed).

4. Set (probably, empty) of all objects owned by specific user is known as users's "schema".

5. "create schema" command name is somewhat misleading, this statement does not create schema but just provides a way to create a lot of objects inside single schema in "one shot" (single transaction).

I've been doing Oracle for about 15 years or so mostly in the application architecture/development roles, but also do quite a bit of DBA work, too.

In almost all circumstances, each one of our applications has an owner account which we call, [APPLICATION]_OWNER where [APPLICATION] is the application name. For the above example, we would create a user called 'SKIN_OWNER'. That user would own all of the objects, do all of the grants, create and grant roles, create public synonyms (if necessary) and so on and so on.

From there, we would grant appropriate ROLES to users. It's simple to maintain and very clear.

http://www.oracle-base.com/articles/misc/schema-owners-and-application-users.php

how to create multiple user in group and how to apply the privelges of group in mysql pls send me any one


Thanks
Srinivas

I wanted to know how best we can assign a privilege or role to a group of users, say there are some 100 or 1000 users etc. The comma separated list doesn't look elegant.

Can we create a user-group just like roles are for privs, thanks.

I would say for Oracle, 'user' and 'schema' are same thing.
If you can't digest this please shift to MySQL.