Home » SQL & PL/SQL » SQL & PL/SQL » Create a user and give full previlages only for that schema (Oracle 10.2.0.1)
Create a user and give full previlages only for that schema [message #428560] Wed, 28 October 2009 19:23 Go to next message
Ricky_1362002
Messages: 111
Registered: February 2009
Senior Member
Hello everyone,
I have a strange or i should say a dumb question.

I need to create a user A who should be able to create and access whatever he wants but ONLY in its own schema, and in tablespace T, but should NOT have access to any object in other schemas.

I did create the user A gave unlimited quota on tablespace T, is that what i should do, please let me know what i should do.


Thanks,
Munna
Re: Create a user and give full previlages only for that schema [message #428561 is a reply to message #428560] Wed, 28 October 2009 19:31 Go to previous messageGo to next message
BlackSwan
Messages: 24903
Registered: January 2009
Senior Member
>I need to create a user A who should be able to create and access whatever he wants but ONLY in its own schema,

CREATE USER WIMP IDENTIFIED BY SILLY DEFAULT TABLESPACE WIMPIES TEMPORARY TABLESPACE TEMP;


user WIMP does not have ability to login & does not have ability to do anything!
Any Oracle USER only has ability to do what has been explicitly GRANTed to that USER.

Default behavior is that user WIMP can only access objects owned by WIMP; assuming WIMP has been GRANT CREATE on those object(s).

[Updated on: Wed, 28 October 2009 19:39]

Report message to a moderator

Re: Create a user and give full previlages only for that schema [message #428562 is a reply to message #428560] Wed, 28 October 2009 19:36 Go to previous messageGo to next message
Ricky_1362002
Messages: 111
Registered: February 2009
Senior Member
Thank you for your reply.

so do i have to grant "create table" like previleges to the user after creating the user?

will the following work?

create user WIMP identified by "SILLY" password expire default tablespace DEFAULT
temporary tablespace TEMP
QUOTA unlimited on T;

grant create session to WIMP;

grant connect to WIMP;

grant create table to wimp;

Do i have to grant for all objects?
Or is there anything like grant all to the user????

Also, the user doesnt have any tables now, he will be creating the objects in the near future, his previleges should be such that he should be able to create and do whatever he wants to the objects under his schema, which you answered already, i got it, and also he should have access ONLY to one tablespace T



Regards,
Munna

[Updated on: Wed, 28 October 2009 19:54]

Report message to a moderator

Re: Create a user and give full previlages only for that schema [message #428591 is a reply to message #428562] Thu, 29 October 2009 01:21 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
RESOURCE role will satisfy your almost every required grants.

regards,
Delna

[Updated on: Thu, 29 October 2009 01:22]

Report message to a moderator

Re: Create a user and give full previlages only for that schema [message #428597 is a reply to message #428562] Thu, 29 October 2009 01:57 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
grant connect to WIMP;

Do not grant any predefined role, ask your DBA to create appropriate ones with the privileges you need.

Regards
Michel
Re: Create a user and give full previlages only for that schema [message #428598 is a reply to message #428591] Thu, 29 October 2009 01:58 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
delna.sexy wrote on Thu, 29 October 2009 07:21
RESOURCE role will satisfy your almost every required grants.

regards,
Delna

NO, never use predefined roles. They are for Oracle only.
ALWAYS create your own roles.
In addition, RESOURCE grants you the privilege to break the database filling the SYSTEM tablespace. Not a good advice.

Regards
Michel

[Updated on: Thu, 29 October 2009 01:59]

Report message to a moderator

Re: Create a user and give full previlages only for that schema [message #428620 is a reply to message #428598] Thu, 29 October 2009 03:13 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Quote:
NO, never use predefined roles. They are for Oracle only.
ALWAYS create your own roles.
In addition, RESOURCE grants you the privilege to break the database filling the SYSTEM tablespace. Not a good advice.


Thanks for these useful comments...

regards,
Delna
Re: Create a user and give full previlages only for that schema [message #428765 is a reply to message #428560] Thu, 29 October 2009 21:38 Go to previous messageGo to next message
Ricky_1362002
Messages: 111
Registered: February 2009
Senior Member
So, do i need to create a role with all the previleges needed?

The user shud be able to do whatever he wants BUT in his own schema only, and shud only have the access to the tablespace T,
please give me the SQL for that, or if i need to create a role and then assign it to the user, what cud be that role????

This is what i did...

create user Tim identified by 'SILLY'
default tablespace T
password expire;


grant connect,resource to Tim;
grant create session to Tim;


Will the above fullfill my needs???

[Updated on: Thu, 29 October 2009 21:41]

Report message to a moderator

Re: Create a user and give full previlages only for that schema [message #428766 is a reply to message #428765] Thu, 29 October 2009 22:07 Go to previous messageGo to next message
BlackSwan
Messages: 24903
Registered: January 2009
Senior Member
>Will the above fullfill my needs???
What are your needs?

Which object_types does user TIM need to be able to CREATE & manage?
Re: Create a user and give full previlages only for that schema [message #428770 is a reply to message #428560] Thu, 29 October 2009 23:34 Go to previous messageGo to next message
Ricky_1362002
Messages: 111
Registered: February 2009
Senior Member
The user TIM should be able to create all objects and create everything but should not be able to do on other schema objects, ofcourse should also be able to connect to the database.
Re: Create a user and give full previlages only for that schema [message #428771 is a reply to message #428770] Thu, 29 October 2009 23:39 Go to previous messageGo to next message
BlackSwan
Messages: 24903
Registered: January 2009
Senior Member
>The user TIM should be able to create all objects
enumerate "all"

How will you know that "all" is complete & correct?
Re: Create a user and give full previlages only for that schema [message #428779 is a reply to message #428560] Fri, 30 October 2009 00:29 Go to previous messageGo to next message
Ricky_1362002
Messages: 111
Registered: February 2009
Senior Member
i mean to say the user might be able to create tables,indexes,sequences,stored procedures,execute the procedures,connect to the database
shud be able to delete,insert into and update tables,indexes.
Re: Create a user and give full previlages only for that schema [message #428783 is a reply to message #428779] Fri, 30 October 2009 01:18 Go to previous messageGo to next message
BlackSwan
Messages: 24903
Registered: January 2009
Senior Member
munna94 wrote on Thu, 29 October 2009 22:29
i mean to say the user might be able to create tables,indexes,sequences,stored procedures,execute the procedures,connect to the database
shud be able to delete,insert into and update tables,indexes.


Then do you not care about objects you have not listed?
Re: Create a user and give full previlages only for that schema [message #428789 is a reply to message #428765] Fri, 30 October 2009 01:55 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
grant connect,resource to Tim;
Will the above fullfill my needs???

Michel Cadot wrote on Thu, 29 October 2009 07:58
NO, never use predefined roles. They are for Oracle only.
ALWAYS create your own roles.
In addition, RESOURCE grants you the privilege to break the database filling the SYSTEM tablespace. Not a good advice.

Regards
Michel

Does my anticipated not answer your question?

Regards
Michel

Re: Create a user and give full previlages only for that schema [message #428791 is a reply to message #428779] Fri, 30 October 2009 01:57 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
munna94 wrote on Fri, 30 October 2009 06:29
i mean to say the user might be able to create tables,indexes,sequences,stored procedures,execute the procedures,connect to the database
shud be able to delete,insert into and update tables,indexes.

See GRANT and choose the appropriate privileges.

Regards
Michel

[Updated on: Fri, 30 October 2009 01:57]

Report message to a moderator

Re: Create a user and give full previlages only for that schema [message #428993 is a reply to message #428560] Sat, 31 October 2009 12:24 Go to previous message
Ricky_1362002
Messages: 111
Registered: February 2009
Senior Member
Thank you very much for all your help guys....!!!
Previous Topic: SQL text using SID
Next Topic: Error logging SQLERRM, FORMAT_ERROR_STACK
Goto Forum:
  


Current Time: Sat Sep 24 21:30:20 CDT 2016

Total time taken to generate the page: 0.07419 seconds