Home » SQL & PL/SQL » SQL & PL/SQL » User and schema (Oracle 10g)
User and schema [message #289008] Wed, 19 December 2007 04:53 Go to next message
hidnana
Messages: 87
Registered: November 2007
Location: chennai
Member
Can a user own more than one schema ?

Also which is the better method to create schema(users),
like for every schema do we need to always create 3 users,
useradmin - schema owner,
usernormal - isert/updte,delete/select privileges,
userviewonly - select (readonly) privilege.
Can you suggest better way to handles schema, user and privieges.
Re: User and schema [message #289034 is a reply to message #289008] Wed, 19 December 2007 05:37 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
A schema is collection of database objects, is owned by user and has the same name as user. Relation between user and schema is 1 : 1.

3 users you mention are, actually roles. You can create as many users as you wish (for example, if there are 23 persons working with the application, create 23 Oracle users). Those users should be granted required privileges. Roles provide a way to do it easily.

This is the Concepts book; pay attention to chapter 20 which covers security issues.
Re: User and schema [message #289035 is a reply to message #289034] Wed, 19 December 2007 05:42 Go to previous messageGo to next message
hidnana
Messages: 87
Registered: November 2007
Location: chennai
Member
Thanks for the reply.
Do we need to create a separate role for admin as already the schema owner himself has all the prvileges doesn't he ?
Also is it necessary to have a tablespace assiged specifically to each user. And can one schema span across multiple tablespaces ...?

Re: User and schema [message #289037 is a reply to message #289035] Wed, 19 December 2007 05:54 Go to previous message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

Also is it necessary to have a tablespace assiged specifically to each user

No.

Quote:

And can one schema span across multiple tablespaces ...?

Yes.

You should really read Database Concepts, 99.99% of your questions are answered there.

Regards
Michel
Previous Topic: External table
Next Topic: selecting only duplicate records
Goto Forum:
  


Current Time: Wed Dec 07 03:11:10 CST 2016

Total time taken to generate the page: 0.16835 seconds