Re: Create user with dba role - no schema object in that user's name

Unfortunately Oracle mixes the Schema and User metaphor in Oracle 8 & 8i. We are seeing this get resolved around Oracle9i R2 starting with the 'grant any privilege' capability.

A schema is an ID that has the capability to define persistant resource consumption (as compared to things like defining & using temp space for intermittent tables, etc.). So a schema will end up with 'resource' privs - create table, create index, create ..... An Oracle schema can be equated to a 'database' in other dbms's such as dBase and some rdbms competitors. (An Oracle database can contain many schemas.) A schema can make data definition requests (DDL). And it will show up under the Schema section in OEM as soon as it 'owns' at least one object.

A userid is an ID generally associated with an end user and is primarily given the ability to create a session. A userid gets the capability to make data manipulation and query requests (DML & query).

Years ago, before Oracle had the refined roles they have now, there were 3 roles available - Connect, Resource and DBA. For the sake of upgradability (and laziness) these roles are still available with the base required system privs as well as a bunch of extra privs.

In general we should not be using the roles names 'DBA', 'CONNECT' or 'RESOURCE' as they usually provide an 'incorrect' set of privs. Of course, that means reading through the SQL Language manual and deciding exactly what priv's are required by each group of users - tedious to say the least.

In your case, you gave a userid 'connect' prov - create a session - as well as define persistant resources - create table. This last defined the userid as a potential 'schema'.

For your DBA user, I recommend reviewing the role definiotion carefully - that is an all-powerful role that can do anything to anyone, anytime. Very definitely recommend reducing the privs for most 'database admins'.

Interestingly I just tested the DBA situation on a 9iR2 database. Using SQL Plus, I created userid "tst", and granted it DBA role. Default tablespace was 'USERS". I then created a table under this userid - OEM added the userid to the schema section and the table was created in tablespace USERS as expected. I can not reproduce the situation you describe (at least not using SQL Plus).

Are you perhaps using a different tool to create your table? Or using the DBA's power to create the object in the SYS schema - eg: create sys.table ( col col_type);

/Hans
BTW - please restrict to one newsgroup.

Hugo Bouckaert wrote:

> Hi
>
> I am new to Oracle but something is puzzling me: in Oracle 9i I created an
> ordinary user with role "connect" and system privileges to create a session,
> create a table and select any dictionary. I also gave that user a default
> tablespace to use and a quota on that and some other tablespaces.
>
> When I create a table in that user's name, in the Oracle 9i Enterprise
> Manager, the user name is listed under Schema and under that schema name for
> that user I can find a heading "tables" and the table that user created.
> This all seems very logical.
>
> Subsequently I decided to create another user with dba privileges (role
> "dba"). This user does NOT appear as an object in the Schema, and one of the
> tables I created with this user ended up in the Schema user name SYS
> although the default tablespace for that new users was set to tablespace
> USERS. I had a look at the new user's object privileges as there was
> something in there about execute on sys.sys_group - perhaps this came in by
> default when I assigned role "dba" to that user. I have taken this privilege
> away but still no schema object is created in that user's name.
>
> Would someone be able to explain under what circumstances a Schema object
> appears / does not appear in a user's name in the Oracle 9i Enterprise
> Manager, how I can restore "execute on sys.sys_group" for the dba users and
> precisely what that means and whether it is necessary for this user to have
> dba privileges?
>
> Thanks very much.
>
> Hugo
Received on Tue Jun 17 2003 - 23:13:06 CDT