Skip to Main Content
  • Questions

  • When to create different schema for the users?

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Abu Tarek.

Asked: July 21, 2002 - 10:25 am UTC

Last updated: July 22, 2002 - 3:41 pm UTC

Version: 8.1.7

Viewed 1000+ times

You Asked

Dear Tom,

When do you create schemas for users like customers or employees?

My company is a small one. There are only 30 employees. I have developed a e-timesheet for our use. I have used two schemas one (named mis) to hold the tables and other (named Timesheet) as the gateway to those tables through synonyms and procedures. The employees are authenticated by a password table in the mis schema through a procedure. One of my friend was telling me to create 30 schemas for the employees only with create session and some other privileges.

If we follow his instruction what would happen if our company gets 5000 employees?

Again think about banking system. Do we need to keep different schemas of different clients here?

Actually I want to know when we should use different schema method and when the password table.

Have a nice time,
Tarek

and Tom said...

Personally, I like to let the database do what databases can do -- like authenticate users, provide security, do the auditing and so on.

Using this common username for everyone and replicating the functionality of the database as you are doing is something I've never been very fond of. How do you audit what people are doing? How do you give different sets of privs to different users (without writing a TON of code)? And so on.

Me, I have this many schemas created in my database:

ops$tkyte@ORA8I.WORLD> select count(*) from all_users;

COUNT(*)
----------
48706


and let the database do all of the work for me (authentication, authorization, auditing, access control).




Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Good sanity check

Mike, July 21, 2002 - 11:25 am UTC

Tom: it's reassuring to see your 48K+ user count as I've worried that thousands of user accounts might not be appropriate but you dispelled that worry.

A reader, July 21, 2002 - 4:38 pm UTC

Dear Tom,

Thanks a lot. I'll follow your way in future.

My users can't select the data of other users. And can't perform DML on others activity records. They can't connect to the database even with sql*plus. Only my application allows them to connect to the database.

Take care,
Tarek



Tom Kyte
July 21, 2002 - 11:53 pm UTC

And how pray tell did you accomplish that in version 817?

Very high number of database user

Abhishek Sinha, July 22, 2002 - 6:55 am UTC

If i have an application with oracle database and say there are 1 lakhs of clients (say in banking system). Then would u still suggest to go for that many Oracle users??

Tom Kyte
July 22, 2002 - 9:50 am UTC

As long as a lakh is less then 2 billion, sure.

It is just data in a database

A reader, July 22, 2002 - 3:41 pm UTC

Lakh is the Indian term for 100,000. And Crore stands for 100 lakhs (10 million).

Bill Clinton learned these terms when he visited India.

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library