Re: User w/only slct,cnnct,cr session can create tables?

On Mon, 05 Mar 2001 11:08:09 -0500, Rob Williamson <robw_at_physics.umd.edu> wrote:

>I have recently created a user and role in the following way:
>
>
>-- CONNECT AS SYS
>DROP ROLE QUERY;
>CREATE ROLE QUERY;
>grant connect, create session to QUERY
>/
>
>--CONNECT AS SYSTEM
>CREATE USER rds IDENTIFIED by &password
> DEFAULT TABLESPACE userdata
> TEMPORARY TABLESPACE temporary
> QUOTA 10M ON userdata;
>
>GRANT QUERY TO rds;
>
>--CONNECT AS TableOwner
>grant select on oehead to QUERY;
>grant select on oedetl to QUERY;
>grant select on PO_HEADER to QUERY;
>grant select on PO_DETAIL to QUERY;
>grant select on VENDOR to QUERY;
>SPOOL OFF
>/
>
>This user can now create tables and drop them which I would not like him
>to do.
>I tried removing create session but then Oracle will not let me log in
>via sqlplus.
>Am I missing something here, I thought you had to explicitly give these
>permissions?
>
>Thanks
>Rob

Connect is a role! It consists amongst others of - create session privilege
- create table privilege
etc.

So here is your solution:
revoke connect from query

and you should be OK
The independent create session priv is necessary though.

Hth,

Sybrand Bakker, Oracle DBA Received on Mon Mar 05 2001 - 10:30:57 CST