Solved

system, sys, internal, dba, sysdba and sysoper

Posted on 2002-05-04
9
1,931 Views
Last Modified: 2008-02-01
1) What is exactly internal. Why password of internal is oracle and sys change_on_install. Can we change their passwords and if by changing one does the other's password also set to same.

2) system and sys are users. Is sys higher than system. If so, in what all ways. Is DBA the only role granted to system and what all are granted to sys.

3) sysdba and then sysoper are the top most authorities in oracle. Does this mean they have been assigned roles and privileges more than those granted to any user.

4) We can grant dba to any user if connected as sys or system. Can we grant sysdba/sysoper to any user and if so connected as what user.

5) connect / as sysdba is the highest connection?

6) Can we do exactly the same things when connected as internal/oracle@mydb and sys/mypwd@mydb OR internal/oracle as sysdba and sys/mypwd as sysdba.

7) connect system/manager as sysdba. Does this mean system is a sysdba now?

8) If we grant roles and privileges to a user and if a particular role is having some privileges separately granted. Now, if we revoke the role do we revoke the privileges that were granted separately also.

--- k_murli_krishna
0
Comment
Question by:k_murli_krishna
  • 3
  • 3
  • 3
9 Comments
 
LVL 1

Accepted Solution

by:
Bashar earned 125 total points
Comment Utility
Hi,
1) Internal and sys are both built-in users with default passwords. In fact, the internal user is sys user from the database perspective.
You can change the sys password with an "Alter user .." statement.
Changing the internal's password could be more complicated.
If you are using a password file (which stores the passwords of the sysdba's and sysoper's outside the database), it will be changed when you change the sys password by issuing "alter user" command. i.e. "Alter user SYS identified by SYS" will synchronize the password of sys and internal in both the password file and in the database.

2) The SYS user holds the Data Dictionary for the database and he has a higher responsibility than system. You may not be able to startup and shutdown the database as system, but when connected as sys you could do it.
You could get all the roles granted to a user
by querying the following view "USER_ROLE_PRIVS".
The SYS and SYSTEM have the DBA role granted by default.

3) The SYSOPER and SYSDBA are both privileges granted for SYS and INTERNAL by default. They could be assigned to others if you are using password file marked as Exclusive.
A user with SYSDBA or SYSOPER will be authenticated against the password file, not the database (requires "AS SYSDBA" or "AS SYSOPER" specified at login).

4) You can grant DBA to any user as long you are connected as a DBA.
You may grant SYSOPER or SYSDBA to a user only when you are connected as SYSDBA or SYSOPER.

5) Connect / as sysdba utilizes the Operating System level Authentication. It requires a setup at the operating system side.

6) You can do anything when connected with "AS SYSDBA" specified. But, you don't have all the privileges without it (i.e. connect sys/mypwd@mydb).

7) If the SYSTEM user is listed in the V$PWFILE_USERS, then this means he has been already added and can be logged in successfully. If not, it will generate an invalid username/password message.

8) Revoking a role from a user takes all the privileges in that role from that user, unless he/she was granted some or all of that privileges directly.
Example: grant connect to user10;
 This grants the "create session" privileges to the user10 implicitly.
But, if you do " grant create session to user10;" and then "revoke connect from user10", he/she will be able to log in since he/she has that privilege granted.

Regards,
Bashar.
0
 
LVL 17

Author Comment

by:k_murli_krishna
Comment Utility
Bashar, I have the following doubts in your list:

1) The internal user is sys user from the database perspective. Everything is in the database perspective and I read somewhere that internal is an alias for sys and not actually an user.
i) Changing the internal's password could be more complicated.
ii) If you are using a password file (which stores the passwords of the sysdba's and sysoper's outside the
database)
Can you explain these 2 points in more detail for me.

3) The SYSOPER and SYSDBA are both privileges granted for SYS and INTERNAL by default. I did not see this even by doing a select * from dba_role_privs. They could be
assigned to others if you are using password file marked as Exclusive. Where and how can we find the status of password file.

5) Connect / as sysdba utilizes the Operating System level Authentication. It requires a setup at the
operating system side. What kind of set up is required. Can you please elaborate on password file, database level and operating system level authentication.

7) If the SYSTEM user is listed in the V$PWFILE_USERS, then this means he has been already added and
can be logged in successfully. If not, it will generate an invalid username/password message.
                                   This particular point is wrong. When I did select * from V$PWFILE_USERS, I got the following:
USERNAME                       SYSDB SYSOP
------------------------------ ----- -----
INTERNAL                       TRUE  TRUE
SYS                            TRUE  TRUE

SYSTEM is not there anywhere but I was able to connect system/mypwd as sysdba. This also indicates that sys and internal are the only ones using the password file. If I change password of sys, do I have to recreate the password file OR it's okay. How do I explicitly change password of internal.

--- k_murli_krishna




0
 
LVL 1

Expert Comment

by:Bashar
Comment Utility
Hi,
Please provide me with the Oracle database version and the Platform you are running.
Also, mention any special setup issues that might have taken place.

Regards,
Bashar.
0
 
LVL 5

Expert Comment

by:sora
Comment Utility
NOTE: The password file is NOT to control who can login as SYSDBA etc. It is solely meant for remote database administration. If you are logged in as SYSTEM or SYS etc, which have the DBA privilege automatically grnated to them, you can whatever you like LOCALLY. But for remote database administration, the password file comes into play

Yes, for an INTERNAL password, ORAPWD or the like on different o/s is used for the purpose of changing the password. The orapwd is applicable only for the INTERNAL password.

This is different on differernt o/s's. On Windows, it is called orapwd80 (I think)

see below (copy-pasted) from metalink.


    A password file is used to authenticate a user prior to database shutdown.
    When creating this file, a password for SYS or INTERNAL must be supplied.  
    After the file is created, you can add other accounts/password by assigning
    the SYSDBA privilege to the account.  When you logon to the database as
    SYSDBA or SYSOPER, you are logging into the SYS schema.

    To Create the password file;
    On Unix:  orapwd   file=<fname> password=<password> entries=<number>
    On NT:    orapwd80 file=<fname> password=<password> entries=<number>

    where  file     = name of the password file (mandatory)
           password = password for SYS or INTERNAL (mandatory)
           entries  = maximum number of distinct DBA and OPERs (optional)
 
   

Password file:
-----------------
When the "init.ora file" parameter remote_login_passwordfile is set to EXCLUSIVE
or SHARED, Oracle can have a password file to maintain

 - the password of internal
 - the list of users and their passwords that have been granted administrator privileges


ORAPWDxx:
------------
ORAPWDxx is a specific command-line utility for password file creation.
ORAPWDxx is automatically installed with the RDBMS utilities



C:\> ORAPWDxx
 Usage: orapwd file=<fname> password=<password> entries=<users>

  where
    file - name of password file (mand),
    password - password for SYS and INTERNAL (mand),
    entries - maximum number of distinct DBA and OPERs (opt),
  There are no spaces around the equal-to (=) character.

  File:
   this option lets you specify the name of the password file. It should
   containt the full path, <ORACLE_HOME>\DATABASE, and be of this format
   PWD<SID>.ORA. Its content is encrypted.

  Password:
   sets the password for internal.
 
  Entries:
   specifies the maximum number of distinct SYSDBA and SYSOPER USERS
 
Example (8.1):
D:\> ORAPWD file=D:\ORANT\DATABASE\PWDORCL.ORA password=ORACLE


Adding & Removing users to the password file:
------------------------------------------------
Adding users to password file is done by granting them the sysdba or sysoper
system privilege.

  SVRMGR> grant sysdba to system;
  SVRMGR> grant sysoper to test_user;
  SVRMGR> grant sysoper to test_user2;

Removing users from the password file is done by revoking the corresponding privileges
from the specific users;

  SVRMGR> revoke sysoper from test_user2;


e) Listing users recorded in the password file:
-----------------------------------------------
There is a view defined in the database that will give you the list of the users
recorded in the password file: v$pwfile_users.


  SVRMGR> describe v$pwfile_users;
  Column Name                    Null?    Type
  ------------------------------ -------- ----
  USERNAME                                VARCHAR2(30)
  SYSDBA                                  VARCHAR2(5)
  SYSOPER                                 VARCHAR2(5)

  SVRMGR> select * from v$pwfile_users;
  USERNAME                       SYSDB SYSOP
  ------------------------------ ----- -----
  INTERNAL                       TRUE  TRUE
  SYSTEM                         TRUE  FALSE
  TEST_USER                   FALSE TRUE
 
       

f) Resetting/changing the password of the user internal:
--------------------------------------------------------
There are 3 common ways to change to password of internal:

1) Recreate the passwordfile (Read Warning 1 & 2):

You can recreate the password file using the ORAPWDxx command by which you can
specify the password for internal.
Before running this command to create the new password file, make sure to delete
the old password file.
Otherwise, the ORAPWDxx utility will give an error message.

  D:\> ORAPWD file=D:\ORANT\DATABASE\PWDORCL.ORA password=<password> entries=5

  Verify if the new file has been created.

The passwordfile might be hidden. Hidden files can be seen:
 on the DOS-prompt by type:
    c:\> dir /A:H

 in Windows NT explorer:
    use the Windows Explorer View/Option 'show all files' to see the file.




sora
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 17

Author Comment

by:k_murli_krishna
Comment Utility
Thanx sora and bashar: I have enterprise oracle running on winNT 4 and it was a typical installation. The only problem is management server service is not running and hence no enterprise oracle.

--- k_murli_krishna
0
 
LVL 5

Expert Comment

by:sora
Comment Utility
If you are done, please close this thread or post additional comments.


sora
0
 
LVL 1

Expert Comment

by:Bashar
Comment Utility
Hi,
The behavior of Oracle when authenticating the users could be quite different (or unobvious) on Windows and Unix.
You were right that the internal is an alias for sys user.
Maybe I didn't clarify that as it should be.
The only purpose of the internal user is for the backward compatibility with older Oracle releases. It's obsolete from Oracle 9i and upward.
Changing the password of the internal user could be simply by issuing an "alter user sys .." statement, where it should synchronize the password of the sys user in both the database and the password file. This implicitly will change the internal's password.
The other way is discussed by sora in more detail.
The SYSDBA & SYSOPER roles are not stored in the database, so you can't see them in the user_role_privs.

The setup for the operating system authentication involves creating a group called "ORA_DBA" or "ORA_SID_DBA" (where SID is the database SID) in Windows NT/2000. A similar group "dba" should be created in Unix.
Then, you add any os user to that group so he/she will be able to log on as follows "connect / as sysdba".

I have encountered a case (on Solaris) where the password file did not contain any entries besides the internal & sys, still I was able to connect as "system as sysdba" without requiring a password. It might be utilizing the os authentication in some way.

I hope we've answered your questions.

Best Regards,
Bashar.
0
 
LVL 17

Author Comment

by:k_murli_krishna
Comment Utility
One last doubt: If SYSDBA and SYSOPER roles are not stored in database where they are stored.
--- k_murli_krishna
0
 
LVL 5

Expert Comment

by:sora
Comment Utility
They are in-built privileges and outside the database itself


  There are two main administrative privileges in Oracle: SYSOPER and SYSDBA
  These are special privileges as they allow access to a database instance
  even when it is not running and so control of these privileges is totally
  outside of the database itself.

  SYSOPER privilege allows operations such as:
     Instance startup, mount & database open ;
     Instance shutdown, dismount & database close ;
        Alter database BACKUP, ARCHIVE LOG, and RECOVER.
       This privilege allows the user to perform basic operational tasks
     without the ability to look at user data.

  SYSDBA privilege includes all SYSOPER privileges plus full system privileges
     (with the ADMIN option), plus 'CREATE DATABASE' etc..
       This is effectively the same set of privileges available when
     previously connected INTERNAL.




sora
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now