Re: grant creates users?

From: Daniel A. Morgan <dmorgan_at_exesolutions.com>
Date: Sun, 21 Jan 2001 01:05:22 -0800
Message-ID: <3A6AA651.EC21C33D@exesolutions.com>

> I'm slowly and steadily working my way through the Oracle dba handbook &
> oracle DBA 101 books. One of the things suggested is keeping all users
> out of the system tablespace. When I look at where each segment is, I
> noticed that user scott has segments in the system tablespace.
>
> Being relatively new to this, I'm not sure how to move scott's tables to
> the correct location - yet - so the easiest resolution (for me) would be
> to edit the scripts that created him to use the proper tablespaces, drop
> scott, and simply regenerate him (and his cohorts) from scratch.
>
> So, I find the utlsampl.sql in ${ORACLE_HOME}/rdbms/admin and edit it.
> These are the lines that have me perplexed:
>
> DROP USER SCOTT CASCADE;
> [[snip - no create user lines though ]]
> GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE TO SCOTT IDENTIFIED BY TIGER;
>
> Curious, I say to myself. So I try it:
>
> SQL> grant connect to biteme identified by biteme;
>
> Sure enough, there's a user named biteme in the database now. Looking on
> OTN, though, the documentation for grant says nothing about the ability
> to create new users with it. Is this one of those undocumented features
> or did I hit the wrong documentation?
>
> Anybody actually using this feature? I'm suspecting I should avoid it...
>
> Any info greatly appreciated...

Anytime you create a user you should always use a more complete syntax. For example:

CREATE USER biteme
IDENTIFIED BY biteme
DEFAULT TABLESPACE data
TEMPORARY TABLESPACE temp
QUOTA 100M ON data.

Failure to do so will create problems.

And with respect to old Scott Tiger. Do what every DBA I know does as soon as the database is installed.

Change the password for SYS
Change the password for SYSTEM
DROP USER SCOTT CASCADE

In other words, get rid of him immediately.

Daniel A. Morgan Received on Sun Jan 21 2001 - 03:05:22 CST