In the database, set up your data schema with data tables, views, and stored procedures to support your application.
That schema owner should only be a trusted data administration person. Let's call that schema APP_DATA.
Then you set up another schema called APP_DATA_USERS with no privileges and no data.
Then you create an Oracle ROLE object called APP_DATA_ROLE.
You grant the app_data_role to the app_data_user account.
You then grant object permissions in the APP_DATA_ROLE that you want the app_data_user to have for the app_data data objects, like Create SESSION, SELECT on TABLE X. or SELECT, INSERT, UPDATE on TABLE Y.
Now the APP_DATA_USERS only has specific permissions needed on specific app_data data objects that you control by the app_data_role.
Then your application only opens connections to Oracle via the single APP_DATA_USERS account
which is constrained by the APP_DATA_ROLE permissions on what can be done in the data schema APP_DATA.
in your web or app config you create the connection information for the single app_data_user account.
Putting the database aside, you now create the application layer with login controls so many users can use the application.
But when they need data, the application only makes a connection to the single APP_DATA_USERS account
Oracle ODP.NET auto handles the multiple session connections by the multiple application users to the single APP_DATA_USERS account.
In summary the multiuser access is controlled by the application
all making connections (sessions) with a single, limited privilege app_data_user account on the database side.
Thank you Lannie..
In your explanation, APP_DATA_ROLE can have one set of permissions. When I want to give different sets of perminssions for different users then what I want to do?
Are the usernames and passwords of users application specific not belongs to the database?
hope some more explanations like first one..
Yes, if you want to go beyond the simple case for intranet office application I described above,
and move into an enterprise internet application with hundreds of user and complex access rules
then you can get more sophisticated
and use the database to store application users (membership provider) and their assigned roles (role provider) in APP_DATA schema.
ODP.NET install has some predefined or shall we say template membership (app users), app roles, tables etc.
and some template stored procedures to manage users, roles, etc. alias Oracle ASP.NET PROVIDERS.
They are a start, and you will likely find they need some modification for your needs.
This link has several documents for .NET development and Oracle.
This book is helpful as well
APP_DATA has your tables in it that contain your data
APP_DATA_API has procedures to add/update/delete data and views to display it
Then you create database users (JSMITH, JDOE) and grant them access to the objects in APP_DATA_API that they need. Have the users log into the database with their own database accounts, if administration is not too much overhead.