It is really easy to log in as another user, just use the proxy user feature that is available since 8i. Unfortunately it does not work with SYS, but works with any other user. Basically you can grant the privilege to users to connect as another user.
SQL> alter user system identified by Oracle123;
User altered.
SQL> alter user hr identified by password account unlock;
User altered.
So the password of SYSTEM is Oracle123, and the password of HR is password. Now allow SYSTEM to connect as HR:
SQL> alter user hr grant connect through system;
User altered.
Finally, connect with SYSTEM and its password, providing HR in the brackets:
SQL> connect system[hr]/Oracle123
Connected.
SQL> show user
USER is "HR"
You can use any user for this, except SYS:
SQL> alter user hr grant connect through sys;
alter user hr grant connect through sys
*
ERROR at line 1:
ORA-28154: Proxy user may not act as client 'SYS'
It depends on the GUI but they all should have a way of selecting the SYSDBA privilege. Toad has it appear as a drop-down box called 'Connect as:' as does Oracle Enterprise Manager. For SQL Developer you use the 'Role' box when creating a new connection.
As far as / as sysdba is concerned it just means that operating system authentication is being used so no password is needed. You can only use it if you are logged in as the oracle user or a member of the dba group (or for Windows, I believe as an administrator).
When you grant a user the SYSDBA privilege it only means they have the option to log in with the SYSDBA privilege and not that they will have dba privileges when they log in normally. The same is true for the SYS user: you can log in as SYS without the SYSDBA privilege and so lack the ability to shutdown the database, for example.
Will users administer the database by first logging in to the database server using eg ssh? Then you can use OS authentication.
Best practice for OS authentication: No-one should be logging in as the oracle OS user. Each DBA should have their own OS account and login as that. Their OS account should be a member of the OS DBA group (remember that only members of the OS DBA group can login using sqlplus / as sysdba).
If you want to allow remote administration, then you'll either need a secure connection to the database (just like SSH provides that to the server when administering locally) or you'll need to use a password file.
Best Answer
It is really easy to log in as another user, just use the proxy user feature that is available since 8i. Unfortunately it does not work with SYS, but works with any other user. Basically you can grant the privilege to users to connect as another user.
So the password of
SYSTEM
isOracle123
, and the password ofHR
ispassword
. Now allowSYSTEM
to connect asHR
:Finally, connect with
SYSTEM
and its password, providingHR
in the brackets:You can use any user for this, except
SYS
: