Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Revoke Create and Alter Table

Revoke Create and Alter Table

From: Buck Turgidson <jc_va_at_hotmail.com>
Date: Tue, 15 Feb 2005 21:38:54 -0500
Message-ID: <5nyQd.42130$EG1.33178@lakeread04>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US