Skip to Main Content

Breadcrumb

Dev Live Dev Intro

We are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August and September. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Tom Kyte

Thanks for the question, Karteek.

Asked: June 26, 2008 - 12:53 pm UTC

Answered by: Tom Kyte - Last updated: February 18, 2016 - 3:25 am UTC

Category: Database - Version: 9.2.0.6.0

Viewed 10K+ times! This question is

You Asked

Hi Tom,

When an user is created using "create user" it will create a user as well as a schema with the same name as user. Also, there is a way to create only schema as well, using "create schema". But how do I create a user without and associated schema.

Like I want to create a user "user1_readonly", and give select only permission to all objects in "user1" schema. ("user1" schema got created when "user1" user was created)

I've seen in our environment that there 5 schemas, and more than 50 users. But don't know how those 50-5 = 45 users were created.

Thanks!
Karteek

and we said...

A user and a schema are pretty much synonymous.


If you do not grant user1 the ability to create things, things will not be created unless someone with really big privileges (create any privileges) does it.



http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_6014.htm#SQLRF01313

...
Note:
This statement does not actually create a schema. Oracle Database automatically creates a schema when you create a user (see CREATE USER). This statement lets you populate your schema with tables and views and grant privileges on those objects without having to issue multiple SQL statements in multiple transactions.

......


in your environment there are 50 users created via create user. They are all schemas.

and you rated our response

  (17 ratings)

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

Reviews

schema

June 27, 2008 - 12:46 am UTC

Reviewer: A reader

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.


Tom Kyte

Followup  

June 27, 2008 - 8:52 am UTC

Your understanding is wrong then.


There are databases that have thousands of users - users have privileges.

Your assumption needs to be re-thought.


You have many tools to manage users and privileges. Tell me, without the concept of a user - how would you even begin to manage privileges, roles, grants, auditing and the like?



It is true that some applications use a single user and manage users themselves - I'm not a fan of that - you are just re-implementing a feature the database has had for over 30 years - grants, roles, auditing...

Hundreds of Schemas

June 27, 2008 - 7:59 am UTC

Reviewer: Joseph D'Antoni from Malvern PA

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 Kyte

Followup  

June 27, 2008 - 9:26 am UTC

I would try to implement them as database users myself

so I don't have to reinvent the wheel

and I'd have the same number of sessions regardless of whether I use a single application user or 500 separate users


http://www.oracle.com/pls/db102/search?remark=quick_search&word=proxy+authentication&tab_id=&format=ranked


you need as many sessions as you have active users at least - and each session can in fact have a separate user_id associated with it, giving you the ability to use auditing, grant, roles - all of that database stuff - so you don't have to re-implement that which you already bought.

schema

June 27, 2008 - 5:54 pm UTC

Reviewer: A reader

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 Kyte

Followup  

June 28, 2008 - 1:25 pm UTC

so, yes, you would go to "skin" and create a set of roles, to the 500 users (the other schemas if you will) you would assign the needed role.

2) why - why would or should they (hey, WE use tables, so the answer is probably technically yes, but you need to explain to me why you would want to redo what is already provided. I can only tell you what we provide

the ability to easily create and manage users
to create and managed roles
to enforce the privileges associated with said roles
to do so even if your application is bypassed
to audit anything they do if you want

all without writing code (and the list of what we can do is longer than that, that is just the beginning)

and why, why would you want to write that code again?



are you building amazon.com?

users

June 28, 2008 - 3:52 pm UTC

Reviewer: A reader

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.
Tom Kyte

Followup  

July 01, 2008 - 6:45 am UTC

1) yes.

click on the link up two or three - for ntier proxy authentication - sure, I'd use the database. There are many ways to create a user, you have database users, you have "enterprise users" (single account, centrally managed, available to ANY database or application)

2) what historical records? dropping an account would not "delete" anything else. You would just be revoking their access.

It would either be extended attributes in a ldap repository or your own table.

Please take a vacation

June 30, 2008 - 4:02 pm UTC

Reviewer: A reader

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!

To: A reader

July 01, 2008 - 10:54 am UTC

Reviewer: A reader

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.

Problem with database user accounts

July 01, 2008 - 11:04 am UTC

Reviewer: A reader

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
Tom Kyte

Followup  

July 06, 2008 - 7:18 pm UTC

an account is - well - an account. There are no special accounts - beyond what you consider special (and that would mean - you wanted to store metadata about your accounts when you created them yourself). We absolutely allow you to create tables, store related information, manage it, query it (eg: please don't say 'because I cannot comment on an account, this isn't something I did to myself - Oracle did it to me', that just isn't so)

why don't you look at the roles granted to said accounts? I would presume that the set of roles/privs you assigned to your "web accounts" would tend to set them apart of "other accounts"?

No direct way..

July 01, 2008 - 2:03 pm UTC

Reviewer: A reader

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.

Re: Problem with database user accounts

July 01, 2008 - 4:01 pm UTC

Reviewer: APHawkes from Miami, FL USA

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.

July 03, 2008 - 8:18 am UTC

Reviewer: Brian Tkatch

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.

Having users at the database

July 03, 2008 - 9:32 am UTC

Reviewer: Rama Subramanian G from Bangalore, India

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
Tom Kyte

Followup  

July 07, 2008 - 10:05 am UTC

sigh... I'll say it again

...
click on the link up two or three - for ntier proxy authentication - sure, I'd use the database. There are many ways to create a user, you have database users, you have "enterprise users" (single account, centrally managed, available to ANY database or application)
......

It is now more than two or three up, so I'll repeat it

http://www.oracle.com/pls/db102/search?remark=quick_search&word=proxy+authentication&tab_id=&format=ranked


How many users

July 04, 2008 - 7:06 am UTC

Reviewer: Trevor from England

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?


Tom Kyte

Followup  

July 07, 2008 - 11:10 am UTC

you know where we store our users?

we can store them in a table
in an ldap repository (which we store in .... tables )


http://docs.oracle.com/docs/cd/B19306_01/server.102/b14237/limits003.htm#i288032

we currently max out at 2.1 billion users if you use just database users via "create user"


July 07, 2008 - 4:52 am UTC

Reviewer: Oleksandr Alesinskyy from Germany

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).

More info...

July 14, 2008 - 10:55 pm UTC

Reviewer: R. Vernick from Philadelphia, PA

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.

July 02, 2013 - 4:11 am UTC

Reviewer: A reader

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


Thanks
Srinivas
Tom Kyte

Followup  

July 02, 2013 - 4:53 pm UTC

huh?

one to many; priv to users

February 17, 2016 - 11:41 am UTC

Reviewer: Praveen Ray from Kolkata, India

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.
Chris Saxon

Followup  

February 18, 2016 - 3:25 am UTC

You can use roles themselves to in effect "group" the users.

eg You could have a role called "HUMAN_RESOURCE_DEPT" and assign the HR users to that role. Then you assign privs just to the role. Similarly, can you build a hierarchy of roles, representing (say) business activities that HR people are allowed to do, and assign them to the HUMAN_RESOURCE_DEPT role, then assign privs to those activities.

But ultimately, at the "end of the chain" you are going to be assigning *something* (either a role or priv) to each user.

Hope this helps.