Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Revoke Create and Alter Table
I want to create a user, and allow him to create a table. Once the table is
established, I want to revoke the system privilege of creating, modifying,
or dropping an object in his schema. Can I do that? In the following
script, "sam" can still modify the table states after revoking create table
from him. I no longer want him to do any alters. Fortunately, he can no
longer create tables, which is what I want.
SQL> connect buck/passwd
Connected.
SQL> create user sam identified by uncle default tablespace users quota unlimited on users;
User created.
SQL> grant create session to sam;
Grant succeeded.
SQL> grant create session to sam;
Grant succeeded.
SQL> grant create table to sam;
Grant succeeded.
SQL> connect sam/uncle
Connected.
SQL> create table states (state_name varchar2(2));
Table created.
SQL> insert into states (state_name) values ('NY');
1 row created.
SQL> commit;
Commit complete.
SQL> connect buck/passwd
Connected.
SQL> revoke create table from sam;
Revoke succeeded.
SQL> connect sam/uncle;
Connected.
SQL> alter table states add zip varchar2(5);
Table altered.
SQL>
SQL> create table countries (country_name varchar2(40));
create table countries (country_name varchar2(40))
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL>
SQL> select * from countries;
select * from countries
*
ERROR at line 1:
ORA-00942: table or view does not exist
Received on Tue Feb 15 2005 - 20:38:54 CST