Re: Granting sysdba to a user

From: Hans Forbrich <forbrich_at_yahoo.net>
Date: Fri, 02 Jan 2004 20:51:44 GMT
Message-ID: <3FF5D976.53B64C6B_at_yahoo.net>

DC_DBA wrote:
>
> I want to 'grant sysdba to a user' for an application. (At least that
> is what I believe so far after having had this problem dumped on me 2
> days ago.) The support we get from the application vendor is lame, to
> say the least. Our application users dont know anything.
>
> 1. The Oracle 9i server is installed on a Sun Solaris system.
> 2. I work with Sybase and hence don't know any Oracle.
> 3. I can start sqlplus but dont know where to go after that.
> 4. Nobody knows the 'sa' password. (If sa is indeed the proper Oracle
> term.)
> 5. Nobody here can tell me if the Oracle server is running or not.
>
> Am I SOL or is there someone who can tell me:
>
> 1. How to check if the server is running and if not how to start it?

First thing: do a 'set | grep ORA' to get a list of environment variables related to Oracle. Of interest: ORACLE_HOME (where the s/w is) and ORACLE_SID (the system identifier for the database the s/w is to control).

An Oracle DATABASE is controlled by an INSTANCE (or multiple instances in a clustered database world). Basically the INSTANCE is the set of processes that manage a database; the DATABASE is the set of files containing the data to be managed. (As comparted to some systems, an Oracle database can have many schemas. You can think of a schema as an application, roughly equivalent to a Sybase 'database').

Assuming the software was installed under user 'oracle', check ps for that user (eg: 'ps -ef | grep oracle') and see whether there are between 7 and 10 processes displaying things like "???_LGWR" "???_PMON", "???_SMON" and so on. The ??? will be the 'SID'. If there, likely the database is up (but could be broken).

Access to the database is usually via a LISTENER which generally listens on TCP/IP port 1521. To check whether that's up - 'lsnrctl status' If not up, try 'lsnrctl start'.

If the listener is up, you should be able to access the database in admin mode without additional passwords using the userid that installed Oracle. Try

SQLPLUS /NOLOG
CONNECT / AS SYSDBA (or CONNECT /_at_??? AS SYSDBA ... ??? = SID)

STARTUP (for a normal startup)
SHUTDOWN (for a normal shutdown)

> 2. How to grant sysdba to the user? (I at least hope this user already
> exists on the server, but in case not I should also like to know how
> to add this user to the system.)

If you have a started instance from above, you can change any user's password by

ALTER USER {user_xyz} IDENTIFIED BY {new_passwd};

>
> The answers to the above 2 questions should get me started, although I
> dont see at this point how I do anything without the 'sa' password.
> Thanks.

The role you want is 'DBA', not 'SA'. Right idea though.

You need to dive into the docco fairly quickly. That is available at http://docs.oracle.com, then go to your version, list the books, and access the "Concepts" and the "Database Administration" manuals. I find that OReilly's "Oracle Essentials"
(http://www.oreilly.com/catalog/oressentials2/index.html) helps get people up to speed.

In the mean time:

Let's start with some basics. You might want to think of the Oracle user/role environment as follows:

Database:
A set of files that can be managed by one or more instances (more in a DB cluster environment, called Parallel Server, Real Application Cluster, or Grid - when released). Includes datafiles, control files, parameter files.

The bulk of the files have file suffix ".ora" or ".dbf" and are frequently found in a "oradata/{sid}/" directory or mount point.

Instance:
A set of software that interacts with the SYS schema in a database to manage that database. There are at least 5 and frequently up to 10 processes with names like 'SMON, PMON, LGWR, DBWR, ARCH' and so on. Details in the manuals.

Listener:
- A set of software that listens on the attached network(s) for database requests. When one is received, the listener 'attaches' the request to the instance. The instance provides a proxy on behalf of the user, either by spawning a new one or by using a shared proxy, that will interact directly with the user.

Super User:

- A schema is a collection of database objects 'owned' by a single user;
- The schema that defines the data dictionary is owned by user SYS;
- An owner generally has unlimited authority over it's objects;
- To avoid potential issues, SYS should not be used as a DBA;
- User SYSTEM is always created as a 'master DBA';
- SYSTEM has a few additional objects that are powerful;
- SYSTEM should not generally be used for day to day DBA work;
- Default Passwords:  SYS = CHANGE_ON_INSTALL, SYSTEM = MANAGER
- A competent DBA will immediately change the above default passwords;
- Using the UNIX userid that installed/owns the database, 
	access w/o passwords using

SQLPLUS /NOLOG

CONNECT / AS SYSDBA (or ...
CONNECT /_at_service AS SYSDBA

Role:
- There are over 100 database priviledges; - Priv's can be equated to operations against the SYS schema;

        eg: SELECT TABLE, CREATE VIEW, etc

- Most privs have an 'ANY' counterpart (eg: SELECT ANY TABLE);
- The ANY priv is considered a DBA capability;
- A role is a stored collection of priv's;
- Some roles are predefined, including CONNECT, RESOURCE and DBA;
- These 3 are for convenience, defined historically;
- Generally better to create new roles based on needs;
- Userids that have permission to do so can GRANT {priv | role} TO

{user}; Received on Fri Jan 02 2004 - 21:51:44 CET