Monday, November 07, 2011

Allowing a Remote User to Administer a Database

If you try to grant SYSOPER or SYSDBA to a user before creating a password file, it will fail:
 
SQL> grant sysoper to joe;
grant sysoper to joe
*
ERROR at line 1:
ORA-01994: GRANT failed: cannot add users to public
password file
 
SQL>
 
To allow you to do this, you need to create a password file. The file= parameter should always be set to $ORACLE_HOME/dbs/orapw$ORACLE_SID. I have tried different values in the past but could not get them to work. Oracle then advised me that I should always use the default value:
 
TEST9 > orapwd file=$ORACLE_HOME/dbs/orapwANDREW \
> password=secret_password entries=2
TEST9 >
 
You then need to set the following initialisation parameter:
 
SQL> select value from v$parameter
  2  where name = 'remote_login_passwordfile'
  3  /
 
VALUE
-------------------------------------------------------
EXCLUSIVE
 
SQL>
  
Then the grant statement will work:
 
SQL> grant sysoper to joe
  2  /
 
Grant succeeded.
 
SQL>
 
And Joe will be able to bounce the database remotely:
 
SQL> conn joe/bloggs@andrew as sysoper
Connected.
SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Database mounted.
Database opened.
SQL>

You can give a user full administration rights on the database as follows:
 
SQL> grant sysdba to administrator
  2  /
 
Grant succeeded.
 
SQL>
 
And that user will be able to carry out other administration tasks remotely as well:
 
SQL> conn administrator/controller@andrew as sysdba
Connected.
SQL> drop user fred
  2  /
 
User dropped.
 
SQL>
 
The V$PWFILE_USERS view shows users listed in the password file:
 
SQL> select * from v$pwfile_users
  2  /
 
USERNAME                       SYSDB SYSOP
------------------------------ ----- -----
SYS                            TRUE  TRUE
JOE                            FALSE TRUE
ADMINISTRATOR                  TRUE  FALSE
 
SQL>
  
Making a user externally identified revokes their SYSDBA or SYSOPER privileges:
 
SQL> conn / as sysdba
Connected.
SQL> alter user administrator identified externally
  2  /
 
User altered.
 
SQL> select * from v$pwfile_users
  2  /
 
USERNAME                       SYSDB SYSOP
------------------------------ ----- -----
SYS                            TRUE  TRUE
JOE                            FALSE TRUE
 
SQL> alter user joe identified externally
  2  /
 
User altered.
 
SQL> select * from v$pwfile_users
  2  /
 
USERNAME                       SYSDB SYSOP
------------------------------ ----- -----
SYS                            TRUE  TRUE
 
SQL>

You cannot reinstate these privileges as long as the users are identified externally:
 
SQL> grant sysoper to joe
  2  /
grant sysoper to joe
*
ERROR at line 1:
ORA-01997: GRANT failed: user 'JOE' is identified externally
 
SQL> grant sysdba to administrator
  2  /
grant sysdba to administrator
*
ERROR at line 1:
ORA-01997: GRANT failed: user 'ADMINISTRATOR' is identified externally
 
SQL>
 
And you cannot make the SYS user externally identified once you are using a password file:
 
SQL> alter user sys identified externally
  2  /
alter user sys identified externally
*
ERROR at line 1:
ORA-01998: REVOKE failed: user SYS always has SYSOPER and SYSDBA
 
SQL> 

The entries= parameter specifies the maximum number of entries allowed in the password file i.e. the number of distinct users allowed to connect as SYSDBA or SYSOPER. The actual number of entries allowed may be higher than this because orapwd carries on adding users until an operating system block is filled. Once that happens, further GRANTs will fail:
 
SQL> grant sysoper to x;
grant sysoper to x
*
ERROR at line 1:
ORA-01996: GRANT failed: password file
'/oracle/app/oracle/product/9.2.7/dbs/orapwANDREW' is full
 
SQL>
 
You will then have to recreate the password file with a higher entries= parameter. If a password file exists, the orapwd command will fail:
 
TEST9 > orapwd file=$ORACLE_HOME/dbs/orapwANDREW \
> password=secret_password entries=10
 
OPW-00005: File with same name exists - please delete or rename
TEST9 >
 
To get round this, you can delete or rename the existing file as suggested. From Oracle 10 onwards, you can also use the force=y parameter:
 
TEST10G > orapwd file=$ORACLE_HOME/dbs/orapwTEST10G \
> password=secret_password entries=1        
TEST10G > orapwd file=$ORACLE_HOME/dbs/orapwTEST10G \
password=secret_password entries=5
 
OPW-00005: File with same name exists - please delete or rename
TEST10G > orapwd file=$ORACLE_HOME/dbs/orapwTEST10G \
> password=secret_password entries=5 force=y
TEST10G >

No comments: