Re: drop user

Just to add to what Howard has said.

Once you have created all the necessary objects for the schema/user, you can either lock the account as Howard has suggested or simply revoke all privileges from the user/schema. The user is now effectively useless but the schema can still be very much of use.

To simplify access to this "Unique Schema" (Oracle speak) and potentially reduce the requirement to create synonyms, you can ALTER SESSION SET CURRENT_SCHEMA=bowie_schema. You're still "connected" as whatever user (and hence still have the same privilege set) but you access *by default* objects belonging in the unique schema.

The unique schema is hence effectively nothing but a collection of objects which can be accessed automatically by any user with sufficient privileges.

Cheers

Richard

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:xu9l9.41428$g9.119489_at_newsfeeds.bigpond.com...
> Since a schema is 'a collection of objects owned by a user', the answer
must
> be no.
>
> But there are users and then there are users. Oracle itself, for example,
> has a requirement for particular sets of tables/index/other objects to be
> stored in the database, and creates 'dummy' users to be their schema
owners.
> For example, OUTLN owns the stored outline tables; PERFSTAT owns the
tables
> needed for statspack to do its stuff. And when you use OEM to manage your
> databases, you create a 'repository owner' to own the tables needed for
the
> Management Server to do its stuff.
>
> All these users would be visible with a 'select username from dba_users',
so
> they're "real" users -but no-one in their right mind would log on as
OUTLN,
> or expect to do any meaningful database work whilst connected as REPOWNER.
> Hence, they are at the end of the day, just 'schema placeholders'.
>
> You can take advantage of much the same sort of approach for your own
> problem: create a user to be the schema holder, create all the requisite
> objects (tables, etc), grant permissions on those objects to "real" users,
> and then simply 'alter user <SCHEMA_HOLDER> account lock'. The fact that
the
> schema owner himself is locked out of the database doesn't stop anyone
else
> querying or modifying the contents of the schema owner's tables (provided
> you did the permission grants properly in the first place).
>
> Regards
> HJR
>
>
> "xx32" <i.dont.want.sp_at_m.com> wrote in message
> news:3D9495CB.6060305_at_m.com...
> > is it possible to have a schema without a user?
> >
>
>
Received on Sun Sep 29 2002 - 08:22:32 CDT