Home » SQL & PL/SQL » SQL & PL/SQL » grant unlimited tablespace (Oracle 10g R2)
grant unlimited tablespace [message #405012] Tue, 26 May 2009 01:20 Go to next message
vaibhavi
Messages: 21
Registered: April 2008
Junior Member
Hello,

I am creating new user through stored procedure.
But when I give grants unlimited tablespace to new user I get error as follows :
create user test_usr13 identified by test_usr13 default tablespace users

grant unlimited tablespace to test_usr13
ERROR CODE : -1031
ERROR MSG : ORA-01031: insufficient privileges

Please suggest me on this .
Thanx.

[Updated on: Tue, 26 May 2009 01:24] by Moderator

Report message to a moderator

Re: grant unlimited tablespace [message #405014 is a reply to message #405012] Tue, 26 May 2009 01:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have not the privilege to grant this privilege.
Anyway, you must NOT grant this privilege to any user.

Regards
Michel
Re: grant unlimited tablespace [message #405026 is a reply to message #405014] Tue, 26 May 2009 02:06 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Why does GRANT UNLIMITED TABLESPACE privilege exist if it must NOT be granted to any user?
Re: grant unlimited tablespace [message #405040 is a reply to message #405014] Tue, 26 May 2009 03:05 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Michel Cadot wrote on Tue, 26 May 2009 08:25
Anyway, you must NOT grant this privilege to any user.


If I have a database, dedicated to a single application, why would I not grant unlimited tablespace to the application-owner?
The thing is to shield this user, so others don't mess with it.
Re: grant unlimited tablespace [message #405047 is a reply to message #405040] Tue, 26 May 2009 03:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
why would I not grant unlimited tablespace to the application-owner?

Because it can then fill your SYSTEM tablespace and crashes your database.

@Littlefoot,
Why DBA exists?

Regards
Michel
Re: grant unlimited tablespace [message #405049 is a reply to message #405047] Tue, 26 May 2009 03:28 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Filling the system tablespace is only an isue if your user somehow acquires the ability to create their own tables and write to them, or if you've screwed up and put the application tables into SYSTEM in the first place.
Re: grant unlimited tablespace [message #405051 is a reply to message #405049] Tue, 26 May 2009 03:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Filling the system tablespace is only an isue if your user somehow acquires the ability to create their own tables and write to them,

Which should be the case of an application owner, doesn't it.

Anyway, the basic principle of security (and in this case availibility) is the least privileges one.

Regards
Michel
Re: grant unlimited tablespace [message #405073 is a reply to message #405047] Tue, 26 May 2009 04:28 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Michel Cadot wrote on Tue, 26 May 2009 10:25
@Littlefoot,
Why DBA exists?

Ummmmm ... to do database administration job? Are you saying that @vaibhavi is not a DBA? Well, I can't tell; all we know is that he is trying to create a new user through a stored procedure. He didn't do that as SYS or SYSTEM (good for him), so - maybe he uses another "DBA" user which, unfortunately, doesn't have all required privileges?

I'm sorry, but it appears that I don't understand the question.

My point was: if Oracle (as a corporation) thought that granting unlimited tablespace privilege was a bad idea, it would remove it from list of available privileges.

On the other hand, I guess that your statement was result of experience, documentation you've read, whatever ... therefore, I hoped that you'll explain why should that not be done. There are two options I can think of:
  • you've said that @vaibhavi should not do that (but only his DBA)
  • you've said that granting this privilege should not be done, in any case
So which one is it (and, possibly, why)?
Re: grant unlimited tablespace [message #405089 is a reply to message #405073] Tue, 26 May 2009 05:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
My point was: if Oracle (as a corporation) thought that granting unlimited tablespace privilege was a bad idea, it would remove it from list of available privileges.

What I wanted to say that Oracle also creates DBA role or %ANY% privileges but it is not recommended to give them to any user only to very specific ones (and I don't think "test_usr13" should be one of these).

As I said:
Quote:
the basic principle of security (and in this case availibility) is the least privileges one.


Regards
Michel
Re: grant unlimited tablespace [message #406982 is a reply to message #405012] Mon, 08 June 2009 01:59 Go to previous message
vaibhavi
Messages: 21
Registered: April 2008
Junior Member
Hello all,

Anyways, I am not doing "grant unlimited tablespace to test_usr13" now. And I continued without this.

But thank you so much for all your help.
Previous Topic: Error in Fetching data thru SQLPLUS
Next Topic: DATATYPE HAVING STRING LENGTH MORE THAN 50000
Goto Forum:
  


Current Time: Tue Dec 06 16:04:02 CST 2016

Total time taken to generate the page: 0.23317 seconds