| Confused about the default schema. [message #165980] |
Mon, 03 April 2006 19:30  |
moeen49
Messages: 12
Registered: September 2005
|
Junior Member |
|
|
Hi,
I am a little bit confused about the schema concept.
I want to create a new schema called APP and then create several users and roles based on the schema APP. The default schema for the users should be APP achema.
How can I make the schema APP the default schema for the new users that I am creating?
I feel that there are some schema design concepts that I have to learn. Is there any resource on the internet that I can read and learn more about oracle schema design best practices?
Any help would be appreciated,
Alan
|
|
|
|
|
|
| Re: Confused about the default schema. [message #166131 is a reply to message #166122] |
Tue, 04 April 2006 13:00  |
orausern
Messages: 826
Registered: December 2005
|
Senior Member |
|
|
Please understand that for all practical purposes, in oracle, schema means the same as user(there is a very minute distinction, but practicall it is the same).
so saying:
"How can I make the schema APP the default schema for the new users that I am creating?" is meaningless..each user is a different schema and you can not make one schema , the default for another schema.
for example, the user scott in oracle is same as 'scott schema' , if there is another user scott1 then for this user/schema, the scott schema can not become the default. so how do u get around? by giving grants..eg. grant select on scott.emp to scott1; etc.
see:
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96524/c11schem.htm#31717
"Introduction to Schema Objects
A schema is a collection of logical structures of data, or schema objects. A schema is owned by a database user and has the same name as that user. Each user owns a single schema."
|
|
|
|
| Re: Confused about the default schema. [message #166212 is a reply to message #166131] |
Wed, 05 April 2006 03:17 |
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
|
Senior Member |
|
|
You can however set a schema as being the default schema for 'working with' objects for another user:
Alter session set current_schema = HR;
Providing the user (not HR) has the appropriate permissions the can now create, manipulate objects in the HR schema without requiring the HR scheam prefix.
Jim
|
|
|
|
| Re: Confused about the default schema. [message #166447 is a reply to message #165980] |
Thu, 06 April 2006 06:21 |
SHYJU KT
Messages: 8
Registered: February 2004
|
Junior Member |
|
|
Hi,
A schema in Oracle means a user iteslf.
What U have to do is
1. Create a user APP, and create all the objects under that user.
eg:- Create user APP identified by APP default tablespace App_tablespace temporary tablespace temp;
grant dba to app ; (Hope ur dba will do this or grant u the necesssary permissions to connect and create objects)
connect app/app
create table tab1;
create public synonym tab1 for tab1;
2. create a role , say app_gen.
eg:-Create role app_gen;
3. Grant the select, update , delete privs on the objects U created as APP user.
eg:- grant select,update,delete on Tab1 to app_gen;
4. when ever U create a user, U can grant this role to that particular user so that he can access all objects in HR schema.
eg:-
Create user usr1 identified by usr1 default tablespace users temporary tablespace temp;
grant connect,resource,app_gen to user1;
Sorry for writing so much simple statements. But I Assume that U are a beginner.
Regards,
Shyju
|
|
|
|
| Re: Confused about the default schema. [message #166461 is a reply to message #166447] |
Thu, 06 April 2006 07:09 |
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
|
Senior Member |
|
|
Hi
Just to clarify:
| Quote: |
A schema in Oracle means a user iteslf
|
This is not entirely accurate. A schema is a collection of objects, not a user.
That being said
| Quote: |
Please understand that for all practical purposes, in oracle, schema means the same as user(there is a very minute distinction, but practicall it is the same).
|
(By niravshah) Would be accurate.
i.e. the terms are generally interchangeable, but we must understand that there are subtle distinctions.
In addition, creating lots of public synonyms can have problematic ramifications. e.g.
user a creates public synonym t1 on table t1, grants user b select on that table.
user b create table t1 (in his schema.
When user b issues a select against t1, it will be HIS table that is accessed, not a.t1.
In addition, creating the public synonyms will not alter the fact that user b, creating an object without a schema prefix, will automatically create that object in his/her own schema (i.e. schema b) using the alter session set current_schema resolves this.
However
@OP if the only reason that you want to do this is to so that your users can avoid having to type in the schema name prefix on tables, I would say "Tough, you'll just have to fully reference your objects"
Jim
[Updated on: Thu, 06 April 2006 07:11] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
