Re: SYSTEM and SYSAUX Tablespace Opinions Requested

From: Frank van Bortel <frank.van.bortel_at_gmail.com>
Date: Sun, 09 Sep 2007 11:56:27 +0200
Message-ID: <fc0g08$1n4$1@news2.zwoll1.ov.home.nl>

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

DA Morgan wrote:
> Frank van Bortel wrote:

>> -----BEGIN PGP SIGNED MESSAGE-----
>> Hash: SHA1
>>
>> Dereck L. Dietz wrote:
>>> In the database I work on at work it appears that there are many user
>>> accounts which have been created with the system privilege UNLIMITED
>>> TABLESPACE.
>>
>> Those account probably have the RESOURCE role granted.
>> In previous releases (it started with 8.0, iirc), RESOURCE had
>> unlimited tablespace.
>>
>>> I brought this to the attention of my manager mentioning that no user
>>> account should have any tablespace quota in those two tablespaces. 
>>> He had me write it up and then forwarded it to the off-site DBA to
>>> see what he thought of my suggestions.
>> SYSTEM - OK, SYSAUX, why not? A TS just as any other.

>
> Ouch. That hurt. SYSAUX is not just another tablespace. There are very
> specific rules as to what can move into and out of SYSAUX.
>
> col occupant_name format a30
> col schema_name format a30
> col move_procedure format a50
>
> SELECT occupant_name, schema_name, move_procedure
> FROM v_$sysaux_occupants
> ORDER BY 1;
>
> It might not hurt ... but neither does locking down security and
> putting schemas where they can do no harm.

undoubtedly Oracle had a cunning plan while making up sysaux, so your query will work (hopefully in real life versions, too)... But even then - what does it show?

The point was: should users have quota on sysaux. My response was why not, (in that respect) sysaux is an ordinary tablespace - au contraire to system, which will cause an instance to behave very strange indeed when filled up and an extend is needed.

Any thoughts on granting quota to users on SYSAUX - --
Regards,
Frank van Bortel

Top-posting is one way to shut me up...
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (MingW32)

iD8DBQFG48NLLw8L4IAs830RAgGtAJ0UKNEUtrYB8L67tvYPX1sH73SS9ACgmICT KFN4wxgs0QmDRfowyBIBOd8=
=JhBd
-----END PGP SIGNATURE----- Received on Sun Sep 09 2007 - 04:56:27 CDT