Re: How to limit USERS ability to Create Tables
From: Michael Serbanescu <mserban>
Date: 1996/11/23
Message-ID: <576hcc$877_at_mtinsc01-mgt.ops.worldnet.att.net>#1/1
janet <janet_at_telesph.com> wrote:
>I would be really curious if he was able to INSERT a row
>into the table...
>just a guess...
>he created the table...
>put rows the data dictionary...
>but nothing is actually in the tablespace yet...
>
>janet
>
>
>Vikas Agnihotri wrote:
>>
>> You are right. But even if the user has CREATE TABLE privilege, he also
>> needs space quota on that tablespace, right? Martin said that he
>> has set 0 quota for that user on all tablespaces.
>> So how come he was able to create a table in the DEFAULT or any other
>> tablespace?
>>
>> --Vikas
>>
>> On Wed, 20 Nov 1996 19:15:16 +1100, Sridhar Subramaniam <avion_at_ozemail.com.au> wrote:
>> >Martin Douglas wrote:
>> >>
>> >> Howdy,
>> >>
>> >> I have just run into a problem on my inquiry only system. I have a large
>> >> Data Warehouse (100Gig) in which I have a user that just created his own table
>> >> in his default tablespace "USER_TBS". I have granted all users one ROLE that
>> >> I defined. This ROLE has 'connect' and 'select *' on views I developed. This
>> >> user does have a default tablespace 'USER_TBS' and a default temporary
>> >> tablespace 'TEMP', But he has '0k quota' on each of these tablespaces.
>> >>
>> >> How did he create this user defined table without me giving 'create table'
>> >> privileges and how do prevent this in the future.
>> >>
>> >> Thanks for you time
>> >>
>> >> Martin Douglas
>> >Martin,
>> >
>> >In oracle 7.x, connect is a role having, among other privs, create
>> >ssession, create table .. system privs. If all you intend giving the
>> >user is a 'connection' privilege - revoke connect role and grant create
>> >session priv separately.
Received on Sat Nov 23 1996 - 00:00:00 CET
Date: 1996/11/23
Message-ID: <576hcc$877_at_mtinsc01-mgt.ops.worldnet.att.net>#1/1
I suspect that your user has UNLIMITED TABLESPACE system privilege. This privilege can be granted _only_to_users, not roles, and overrides any specific quotas granted.
To check if this is the case, try this:
SELECT * FROM sys.dba_sys_privs
WHERE grantee='<user_name>';
I hope this helps.
Michael Serbanescu
janet <janet_at_telesph.com> wrote:
>I would be really curious if he was able to INSERT a row
>into the table...
>just a guess...
>he created the table...
>put rows the data dictionary...
>but nothing is actually in the tablespace yet...
>
>janet
>
>
>Vikas Agnihotri wrote:
>>
>> You are right. But even if the user has CREATE TABLE privilege, he also
>> needs space quota on that tablespace, right? Martin said that he
>> has set 0 quota for that user on all tablespaces.
>> So how come he was able to create a table in the DEFAULT or any other
>> tablespace?
>>
>> --Vikas
>>
>> On Wed, 20 Nov 1996 19:15:16 +1100, Sridhar Subramaniam <avion_at_ozemail.com.au> wrote:
>> >Martin Douglas wrote:
>> >>
>> >> Howdy,
>> >>
>> >> I have just run into a problem on my inquiry only system. I have a large
>> >> Data Warehouse (100Gig) in which I have a user that just created his own table
>> >> in his default tablespace "USER_TBS". I have granted all users one ROLE that
>> >> I defined. This ROLE has 'connect' and 'select *' on views I developed. This
>> >> user does have a default tablespace 'USER_TBS' and a default temporary
>> >> tablespace 'TEMP', But he has '0k quota' on each of these tablespaces.
>> >>
>> >> How did he create this user defined table without me giving 'create table'
>> >> privileges and how do prevent this in the future.
>> >>
>> >> Thanks for you time
>> >>
>> >> Martin Douglas
>> >Martin,
>> >
>> >In oracle 7.x, connect is a role having, among other privs, create
>> >ssession, create table .. system privs. If all you intend giving the
>> >user is a 'connection' privilege - revoke connect role and grant create
>> >session priv separately.
Received on Sat Nov 23 1996 - 00:00:00 CET