This content has been marked as final. Show 9 replies
"grant dba to <user>" do implicit a "grant unlimited tablespace to <user>"
"revoke dba from <user>" do implicit a "revoke unlimited tablespace from <user>"
"grant unlimited tablespace to <user>"
again: "grant resource to <user>", in resource is a implicit grant to...
yes you are right.
the workaround is
(1) uncheck RESOURCE !AND! DBA -> ALTER USER
(2) check RESOURCE -> ALTER USER
So we should revoke also the checkbox-entry for RESOURCE if SYSTEM unchecked DBA. So the easy-user see this and knows what he has to do.
Otherwise he looks at the screen and see a checked RESOURCE-right, but haven't any.
It was a customer problem. And it takes me some time to find out why resource was missing, even after some discussion he told me that this user has had an DBA-role before.
No bug, the error message returned is rather telling
[tkyte@dellpe ~]$ oerr ora 1950
01950, 00000, "no privileges on tablespace '%s'"
// *Cause: User does not have privileges to allocate an extent in the
// specified tablespace.
// *Action: Grant the user the appropriate system privileges or grant the user
// space resource on the tablespace.
when you revoked dba, you revoked unlimited tablespace. It is working the way it should. create table is insufficient, you also need the ability to allocate space in tablespaces.
But ;-) :
I a user has the dba and resource role (both roles have "unlimited tablespace") and you revoke the dba role -> "unlimited tablespace" is revoked too.
Although resource is still granted.
But it's the same since oracle 6 (or longer?)
yup, that is the way it works.
sorry for rising such an ancient topic.
i just can not find any information about explicit granting of `unlimited tablespace' system privilege when granting DBA or RESOURCE (and may be some other roles too?). could you please point me any?
granting of `unlimited tablespace' system privilegeDid you know that UNLIMITED TABLESPACE system privilege, making able user to create any table anywhere in the database – including the SYSTEM tablespace? Obviously, this is not what you want. You would want to restrict the tablespace access to specific tablespaces only...?
when granting DBA or RESOURCE (and may be some other
roles too?). could you please point me any?
Message was edited by:
"it" - who is "it"? DBA (or RESOURCE) role? but UNLIMITED TABLESPACE can't be granted to a role.
granting of `unlimited tablespace' systemprivilege
when granting DBA or RESOURCE (and may be someother
roles too?). could you please point me any?Did you know that it has UNLIMITED TABLESPACE system
privilege, making it able to create any table
anywhere in the database – including the SYSTEM
Obviously, this is not what you want. Youi want to find where this issue (or feature) is documented.
would want to restrict the tablespaces to specific
PS ouch. in my previous post i ment implicit granting of that system privilege.
"it" - who is "it"?'It' is a user, of course....
Sorry - English is not my native language, even not my second language.