system, sys, internal, dba, sysdba and sysoper
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
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\PWD
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\PWD
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
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\PWD
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\PWD
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
ASKER
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
--- k_murli_krishna
If you are done, please close this thread or post additional comments.
sora
sora
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.
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.
ASKER
One last doubt: If SYSDBA and SYSOPER roles are not stored in database where they are stored.
--- k_murli_krishna
--- k_murli_krishna
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
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
ASKER
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