Hello Mr Tom,

I use the database completly for managing users, but over the last one week, I have noticed in this site that a good number of users are using tables to manage users (including passwords) for various reasons.

That had me thinking about any limitation on Oracle's end (except clear text password, I could not think of any). The solutions that you presented for using secure passwords have also been excellent...

FGAC is a boon to people like us!

Hi,

I have such puzzle because many programmers claim that by using table(s) and coding themselves, they have the freedom to control up to very detail level -- access control to some operations in the form & report level, i.e. update/query/delete of certain fields in the Form, etc. And they also want to allow application administrator to manipulate users without knowing how Oracle manages users.

I would be pleased to hear more about the Fine Grained Control Access and its examples...

Thank you!

Hi, Tom
I'm also think about changing to use database user in my application. But I found in Oracle 11i, there is a table about application users, following is the description of this table i found on metalink.
"
FND_USER stores information about application users. Each row includes the user's username (what a user types in at the sign-on screen), password, and information on when the user should change the password. Each row also contains information on when the user last signed on, start and end dates for when a username is valid and a description of the user. Oracle Application Object Library does not use the USER_PASSWORD and LOCAL_PRINTER columns. You need one row for each application user at your site. Oracle Application
Object Library uses this information to allow users to sign on to an application.
"
It's seems 11i also manages user in a table. Thus can system use oracle "Profile" to limit resources used by each application user?

Hay Friend!

I have been Oracle DBA since 1997....and i never encountered any kind of problem in managing user through database instead of using my own tables.

Yes, Oracle also do manage users access through tables but the method(s) applied for that are well-tested and debugged as well as much more efficient than that of managing users through our own tables. If some utility has been designed to facilitate you, then it seems quite awakward to look for own...thus waste resources and time in managing that. The rule is "If milk is available from shop then it is foolish to have a cow".

If someone has any problems regarding managing users through database, then do tell me. I will try to solve that problem.

Regards

Hi Tom,
I am working on an application in Oracle9i.
In this, I have to give user management i.e.,
1) Guest :-> has only Select access on data.
2) User :-> has all the rights which are available to a guest and he can update the data.
3) Owner :-> has all the right which available to User and Insertion rights
4) Administrator :-> has all the rights which available to a owner and User creation/deletion rights

For user management, I am doing following things :->
1) I have created 4 different roles for all the categories of user.
2) I am creating a schema for each user i.e,
If I have 20 guests then I am creating 20 new users on database level (i.e., 20 new schemas)
And if I have 30 administrators then I am creating 30 new users (i.e. 30 new schemas). And so on...

In this application, I am also providing check in- checkout rights (data level) to the user, owner and Administrator. So creating individual schema for each user is very helpful for me to provide check in - checkout rights.
(Ie, a single user can checkout the latest data from tables and he can only work on checkout data and after modification he can check in the data.)

Could you please let me know :->?

What is the overhead of creating each schema?

Another question :-> Suppose I have 50/100 schemas, then is it so that we will have to get license of 50/100 users from oracle Corporation? i.e, there any cost related overhead by creating separate database user?

Tom,

Would you please explain how this kind of database based access control works when the front end is a java application using connection pooling, rather than the front end application making a direct connection using the oracle login details supplied by the user? If every user has their own Oracle user, what user(s) is the connection pool connecting to?

Thanks for a great site by the way.

The access control that I find lacking is; How to give a developer control over another schema without giving away rights to the whole database? I can give a developer the rights to create objects in his/her own schema (eg, create table, etc) or I can give him/her the rights to create objects anywhere in the database (eg, create any table). What I can't seem to do is give him/her the privilege to create objects in a specific, other, non-system, schema.

If I give a developer the CREATE ANY PROCEDURE privilege then it would be trivial for them to create a procedure in the SYS schema that would execute any sql statement that they send it and they would, for all effective purposes, be able to do anything on the database that they wanted.

What would be nice would be to have statements like these:
GRANT CREATE ANY TABLE IN APPLICATION_SCHEMA TO DEVELOPER;
or
GRANT CREATE ANY PROCEDURE IN APPLICATION_SCHEMA TO DEVELOPER;
or
GRANT SELECT, INSERT, UPDATE, DELETE ON ANY TABLE IN APPLICATION_SCHEMA TO DEVELOPER;

So that a developer could login to the database as his/her self and still be able to manipulate objects in the application schema but not be able to alter objects in sys or system, or another application schema that he/she shouldn't be mucking about with.