If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

I dont think Giani was being smart. If you want users to create other users then you should insist that they follow your rules.
Perhaps you could supply them with a creation script that they MUST use when creating users.
Something like

I don't think it's possible. You can restrict default temporary tablespace (only on Oracle 9i), but not default tablespace. You can use a trigger as a workaround (AFTER CREATE ON DATABASE) and change the default tablespace just after the user creation.

However, that workaround seems disgusting (at least for me). What do you think about changing the policy? Is it really necessary that application users have the right to create users on database?

Originally posted by adrianomp Is it really necessary that application users have the right to create users on database?

In the best security model I know ("'tis my own invention" said the White Knight), the IT dept should not be creating users - it's the job of HR and/or line-management. OF COURSE they shouldn't be using a "create user" statement! {descends from soap-box}

"The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

In the best security model I know ("'tis my own invention" said the White Knight), the IT dept should not be creating users - it's the job of HR and/or line-management. OF COURSE they shouldn't be using a "create user" statement! {descends from soap-box}

In these terms, I completely agree with you. However, take care of the database health is a duty of the DBA. If it's possible to embed the correct operation on an application, fine. Otherwise it's better to restrict access than let others make a mess on the database.

There's absolutely nothing smart in that, it's just how users should be created. And if you had been smart enough you would have realised that the above command creates a user that can't create any object in any tablespace whatsoever - not in TBS_NAME (the default tablespace for that user), nor in any other tablespace!

It's not the assigned defaut tablespace that gives user the ability to create objects - it's what privileges and tablespace quotas you assign to that user.

Jurij ModicASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?