Re: Enterprise manager user creation. Whats wrong?

Sherlock wrote:

> Well, actually the earlier ones that i created had only SELECT,UPDATE
> etc. privelages which were absolutely necessary.

An honest question here - Are you familiar with the documentation library? It is available on http://docs.oracle.com - part of Oracle's Technet (http://otn.oracle.com is the official access).

If you haven't done so, you eventually need to spend some time looking through the Concepts manual and then the Database Administrator's Guide. The challenges you encountered are answered in these two manuals, but they can be difficult to read.

I recommend OReilly's "Oracle Essentials" book (http://oracle.oreilly.com) for people starting out to give sufficient background to start digging into the Oracle manuals properly. Others have had good success with the "Oracle 101" series.

> I understand that no one should give the DBA role to others as it may
> not be supported in later versions. DBA role allows users to tinker with
> the SYS schema which should not be done at all. There are views
> available to avoid this.

Good start.

Learn the views - the names, the columns & the reasons. That will make your time much more valuable than trying to learn the schema of SYS.

If you really want to learn the SYS dictionary stuff (which I advise against) - look at the $ORACLE_HOME/rdbms/admin/ for the scripts. You might also look for the bootstrap sql file.

> I was just trying the thing out whether i can see the SYS schema if i
> create another user with the DBA role. The earlier users i created had
> specific priveleges (SELECT etc) that were absolutely necessary to them
> (No unnecesary priveleges were granted).

Sounds like you are starting roughly in the right direction. Now you also want to start reading about "ROLES". There are a number of 'roles of roles', including CONNECT, RESOURCE and DBA.

Theew are two distinct types of GRANTS. You are learning about the one, now you need to learn the other, so read the SQL Language manual under GRANT role ...

> But i dont still understand why a schema is not added when i add a user.

Specifically look up GRANT RESOURCE ....

In the bigger scheme of things - in an enterprise or company that has multiple databases - you will (eventually) want to avoid adding any users to any database. Instead, you will add the users' info and their base priviledges to a central LDAP (or LDAP-like) directory.

However, each database does need the object definitions and that is where a schema comes in - a schemaId looks very much like a userid but noone logs on to the schema. Instead, a schema only consumes resources and provides accesses.

So one way to look at this - a "user" is an id that has the CONNECT set of roles but not the RESOURCE set; a "schema" is an id that has the RESOURCE set of roles but not (ideally) the CONNECT set. (And a DBA has a different set of roles which does not necessarily overlap with either of the other two).

For the short term you will find that a userId and a schemaId seem like (and in fact can be) the same thing and you will need to keep the functionality separated on your own. And in the short term - and answering your original question - when you create a userId in OEM (Oracle Enterprise Manager), you need to grant ***appropriate*** resource priviledges to make it capable of behaving like a schema - and then add at least one object to make it show up under the schema section. Received on Wed May 21 2003 - 23:32:47 CDT