473,320 Members | 2,202 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Granting sysdba to a user

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?
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.)

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.
Jul 19 '05 #1
1 34018
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_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 /@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};
Jul 19 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: lionking | last post by:
oracle 9.2 on redhat 7.3 I can't login as system/password as sysdba to dbastudio (I started it by typing oemapp dbastudio). I can login as normal though... what might be the problem?
2
by: Ted | last post by:
How do I grant all privileges for a schema that has a large number of existing tables, procedures, functions, etc to a newly created role, without having to issue a grant statement for each object...
4
by: Wade Chy | last post by:
Hi All I am in a situation where I have to grant select privileges on tables owned by a different owner to a specific role. I am logged in as system/sysdba. I have created a new role...
3
by: Tim Werth | last post by:
I have a Windows Service that is running as an administrator account. Some of it methods create files for a COM+ client to digest and puts them in C:\Documents and Settings\All Users\Application...
2
by: Prasad Patil | last post by:
hi, I have a ASP.NET / C# web based intranet application, i need to create excel files on the server in the folder. I always get a access denied error from the web application on the server. The...
2
by: LazyAnt | last post by:
Hello, I have DB2 v8.1 for Linux for a class environment; each user has his/her own database as dbadm and they are suppose to study queries from another database. They have the right...
0
by: Charles A. Lackman | last post by:
Hello, I have a web site that is supose to create an Excel file out of a dataset. In my code where: "Dim Excel as new Excel.Application" is placed I receive the following exception: ...
1
by: tpawona | last post by:
I'm writing a script that would be run by user oracle, that would logon to all the databases on the server. Thus I'll need to logon as sysdba. In Sqlplus, this would be sqlplus / as sysdba. How do...
1
by: DC_DBA | last post by:
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...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.