Cannot Revoke System Privilige

[Horace]

Hi All,

I have a user, and I don't want her to CREATE TABLEs in her schema.

In order for her to connect to the DataBase, I've granted her the CONNECT role. Within the CONNECT role, there is an IMPLICIT system privilige for CREATE TABLE.

So I tried 'REVOKE CREATE TABLE FROM MY_USER';

and I got the error ORA-01952: system privileges not granted to 'MY_USER'

She can still create tables, what do I do...........

[julian]

Revoke connect and grant only create session. Would this be fine?

[Horace]

That seems to work, just have to wait for the fallout.........

Cheers.

[Tarry]

You cannot revoke the system priv in a role if it had a grant option, you must revoke the role and regrant the role without the priv. But since you don't need him/her to have create table priv....
like julian said the create session will give him/her the ability to connect and then grant select on .. etc for specific access to select, or more as you want him/her to engage in the db.