Granting sysdba to a user

**Hans Forbrich** · Jun 27 '08, 05:45 PM

Re: Granting sysdba to a user

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 /@??? 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_INSTA LL, 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 /@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};

Granting sysdba to a user

Granting sysdba to a user

Comment