Creating a SYSDBA user

[heprox]

We have a production database Oracle 8.1.7 the is running on AIX 4.3.3. This database was setup using a series of scripts by application provider. Apparently they do not use the SYSADMIN or SYSDBA functions of Oracle, so as far as I can tell these users do not exist. All management of tablespaces/indexes is done through using the admin password for that schema (i.e. sys/syswpd). How would I go about creating a SYSADMIN and SYSDBA user, and/or verifying that these users do not exist. I'm attempting to install Quest Spotlight on Oracle and this tool needs some type of SYSDBA level privileges.

 

[sem]

SYSADMIN and SYSDBA are not users

Regards, Dima

http://seminihin.narod.ru

 

[SantaMufasa]

Heprox,

"SYSDBA" and "SYSOPER" are Oracle roles, but they are special roles: we do not define them as we do other Oracle roles, and they do not reside in the data dictionary as do other roles such as "CONNECT", "RESOURCE", et cetera. They are "de facto roles" that exist when an Oracle database exists. You cannot drop "SYSDBA" or "SYSOPER", nor can you explicitly create or grant privileges to these de facto roles.

You can, however, optionally grant "SYSDBA" or "SYSOPER" privileges/role membership to an Oracle user if you have properly attended to the "Oracle password file" (orapwd). The reason this is optional (and I, in fact, do not grant such privileges to Oracle users), is because one can become a "SYSDBA" or "SYSOPER" by simply logging into the operating system with an account that is a member of the group which Oracle knows is the "DBA" group.

On a Unix machine (and for the AIX environment you are using), you can confirm the Unix group name by using Unix's "cat" or "tail" commands to view the last few lines of this file: $ORACLE_HOME/rdbms/lib/config.s. The "DBA" group name appears following the '. string "<group name>"' entries. (On other Unix systems, the entry appears following the '. ascii "<group name>"' entries.

Then, for any user, you can confirm their group membership by the Unix o/s command, "id". If the user is a member of the group which name appears in the "string" or "ascii" entries of the "config.s" file, then that user is, by default, a member of the "SYSDBA"/"SYSOPER" groups and should be able to successfully issue the command "connect / as sysdba" or "connect / as sysoper".

Let us know if this helps resolve your need.

Mufasa
(aka Dave of Sandy, Utah, USA @ 07:29 (07Apr04) UTC (aka "GMT" and "Zulu"), 00:29 (07Apr04) Mountain Time)

 

[heprox]

I apologize for the confusion, I understand the concept of the SYSDBA/SYSADMIN being Oracle roles, my problem is that these "roles" have never been set up in the database. Our provider informed that if we wanted to use performance software like Quest Spotlight on Oracle, that would be fine and did not violate any EULA that we have with them, however they ONLY manage the database at the schema level. Luckily we are capable of connecting as internal but my DBA and I are trying to piece together where to begin when the SYSDBA/SYSADMIN roles were never established. Both of us have always had those roles, with users under them previously in place.

 

[Scunningham99]

logon via

sqlplus "/ as sysdba"

then grant sysdba or sysopr to whicher user u wish.

to view which users have these privs. use:-

select * from v$pwfile_users

hth

Sy UK

 

[SantaMufasa]

And again, Heprox, may I re-direct your attention to my comment in my that these roles (SYSDBA and SYSOPER) 'are "de facto roles" that exist when an Oracle database exists'. When you say, above, "my problem is that these "roles" have never been set up in the database", I cannot emphasise enough that you do not set up these roles explicitly. They exist, by default, with any working Oracle database.

So, as I suggested in my previous post, if you connect to the operating system as a DBA-privileged user, then you should be able to connect as Sy suggests in his just-previous post, in this fashion from your o/s prompt:

sqlplus /nolog
SQL> connect / as sysdba
connected.

Let us know your results.

Mufasa
(aka Dave of Sandy, Utah, USA @ 17:09 (08Apr04) UTC (aka "GMT" and "Zulu"), 10:09 (08Apr04) Mountain Time)

 

[Turkbear]

Hi,
You need to read , at least, the Oracle Administrator's guide or the Concepts manual
( try at

http://tahiti.oracle.com

)

Without more knowledge you are likely to be misled by your application provider -
For instance, that is no such thing as 'managing at the schema level using that schema's admin password ( sys/syspwd)' - if, in fact, they are using sys as a user id, they have complete control over the database instance not just some schema.