Bikle.com

Ajax Demo:

Plain HTML
(no Ajax here):

of URLs:

sysdbaTips.html 12-06-2001 Bikle ----------------- Tips for Using The Oracle SYSDBA Role This page shows a few simple tips related to the Oracle SYSDBA role. Due to Oracle's announcement that it will desupport CONNECT INTERNAL I decided attempt to connect as SYSDBA. I knew that SYSDBA was a role so I knew enough to grant it to the account which I wanted to use: 16:56:16 SQL> grant sysdba to danb; grant sysdba to danb * ERROR at line 1: ORA-01994: GRANT failed: cannot add users to public password file Drat. I bumped into a problem. I typed "oracle ORA-01994" at google.com and found a tip that suggested my use of the password file was halting progress. I CONNECTed INTERNAL and looked at my init.ora parameters: 16:56:39 SQL> show parameters remote NAME TYPE VALUE ------------------------------------ ------- ------------------------------ remote_dependencies_mode string TIMESTAMP remote_login_passwordfile string NONE remote_os_authent boolean FALSE remote_os_roles boolean FALSE 17:05:29 SQL> Next, I looked for another database which had a different value for remote_login_passwordfile: % sqlplus internal SQL*Plus: Release 8.1.7.0.0 - Production on Fri Dec 7 01:29:38 2001 (c) Copyright 2000 Oracle Corporation. All rights reserved. Connected to: Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production With the Partitioning option JServer Release 8.1.7.0.0 - Production 01:29:39 SQL> show parameters remote NAME TYPE VALUE ------------------------------------ ------- ------------------------------ remote_dependencies_mode string TIMESTAMP remote_login_passwordfile string EXCLUSIVE remote_os_authent boolean FALSE remote_os_roles boolean FALSE A search of google.com and the oracle documentation on the keywords "remote_login_passwordfile exclusive" reveals a wealth of information and reading material on the remote_login_passwordfile init.ora paramter. I'll not duplicate that information here. I can say, however, that the init.ora parameter value pair: remote_login_passwordfile = EXCLUSIVE solved my problem. 01:29:49 SQL> grant connect to dbikle identified by d88t88; Grant succeeded. 01:30:08 SQL> alter user dbikle default tablespace gendata; User altered. 01:30:21 SQL> alter user dbikle temporary tablespace temp; User altered. 01:30:37 SQL> grant sysdba to dbikle; Grant succeeded. 01:30:45 SQL> connect dbikle as sysdba; Enter password: d88t88 Connected. 01:31:12 SQL> That seemed fairly simple. This demo taught me I need to remember two things: 1. I need to setup my databases so remote_login_passwordfile=EXCLUSIVE 2. The password stored in the password file is put there by the SQL GRANT command and it is the same password which is used to authenticate the user. One experiment I need to run is this: -Build a new 9i database with a new password file -Shutdown the 9i database -Forget the password in the password file -Toss the password file in the trash -Build a new password file with a new password (demo syntax: orapwd file=/oracle/app/oracle/product/BIKLE9I/dbs/orapwBIKLE9I password=ad87 entries=11) -See if I can start the 9i database If I can start the 9i database, then I'd consider myself free from the requirement that I memorize at least one SYSDBA password. I did run the above experiment on an 8.1.7 database. Result: I was able to connect as SYS and start the database. Screen dump: First I shutdown the database: SQL> shutdown Database closed. Database dismounted. ORACLE instance shut down. SQL> exit Disconnected from Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production With the Partitioning option JServer Release 8.1.7.0.0 - Production Then I logged into the UNIX oracle account account and put the password file in the trash: oracle% cd /oracle/app/oracle/product/BIKLE/dbs/ oracle% mv orapwBIKLE orapwBIKLEtrash Next, I built a new password file: oracle% orapwd file=/oracle/app/oracle/product/BIKLE/dbs/orapwBIKLE password=bicykle2 entries=11 Then, I attempted to start the database from the UNIX dbikle account using the old password: BIKLE:/home/dbikle% sqlplus SQL*Plus: Release 8.1.7.0.0 - Production on Sun Jan 20 23:17:32 2002 (c) Copyright 2000 Oracle Corporation. All rights reserved. Enter user-name: SYS/bicykle AS SYSDBA ERROR: ORA-01031: insufficient privileges Then, I attempted to start the database from the UNIX dbikle account using the new password: Enter user-name: SYS/bicykle2 AS SYSDBA Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 154140832 bytes Fixed Size 73888 bytes Variable Size 128425984 bytes Database Buffers 24576000 bytes Redo Buffers 1064960 bytes Database mounted. Database opened. SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> exit Disconnected from Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production With the Partitioning option JServer Release 8.1.7.0.0 - Production BIKLE:/home/dbikle% id uid=3008(dbikle) gid=3001(users) BIKLE:/home/dbikle% All I need is the UNIX root password or oracle password. These passwords will be enough for me to create a new password file. With a new password file, I'll be able to CONNECT AS SYSDBA and start the database. Another piece of information, for 9i, I picked up is important. If I know the password to the UNIX oracle account, I'll be able to CONNECT AS SYSDBA. For several weeks I walked around with the mistaken impression that the above statement was untrue. I picked up this impression via the command line below: sqlplus / AS SYSDBA which I ran from the UNIX oracle account command line. The above command does not get me connected as SYSDBA; it gives me an error. So, I tinkered around awhile and got the string '/ AS SYSDBA' to work from a username prompt and also from a sqlplus command line. I got it wo work after I had created an OPS$ORACLE account. Due to my dimness, I assumed a connection between the OPS$ORACLE account and the '/ AS SYSDBA' string starting to work. Now I know better. I do not need an OPS$ORACLE account if I want the string '/ AS SYSDBA' to get me connected. Here is a screen dump of me trying out various things with SYSDBA: X9% id uid=1001(oracle) gid=101(dba) X9% sqlplus SQL*Plus: Release 9.0.1.0.0 - Production on Fri Mar 29 19:59:49 2002 (c) Copyright 2001 Oracle Corporation. All rights reserved. Enter user-name: / AS SYSDBA Connected to: Oracle9i Enterprise Edition Release 9.0.1.0.0 - Production With the Partitioning option JServer Release 9.0.1.0.0 - Production 20:00:00 SQL> Hey, it worked! Now I'll run another experiment. I need to find out if I may CONNECT AS SYSDBA from an OPS$APPLMGR account. First I login to the UNIX oracle account. Then, I fire up sqlplus to create an ops$applmgr with a password rather than IDENTIFIED EXTERNALLY. X9% id uid=1001(oracle) gid=101(dba) X9% sqlplus SQL*Plus: Release 9.0.1.0.0 - Production on Fri Mar 29 21:53:16 2002 (c) Copyright 2001 Oracle Corporation. All rights reserved. Enter user-name: / AS SYSDBA Connected to: Oracle9i Enterprise Edition Release 9.0.1.0.0 - Production With the Partitioning option JServer Release 9.0.1.0.0 - Production 21:53:21 SQL> CREATE USER ops$applmgr IDENTIFIED BY YOHAA; User created. 21:54:04 SQL> GRANT SYSDBA TO OPS$applmgr; Grant succeeded. 21:54:18 SQL> GRANT CONNECT TO OPS$applmgr; Grant succeeded. 21:54:34 SQL> exit Disconnected from Oracle9i Enterprise Edition Release 9.0.1.0.0 - Production Now I login to the UNIX applmgr account and attempt to CONNECT as "/": X9% id uid=1002(applmgr) gid=102(netops) gemprod2-applmgr-X9% sqlplus SQL*Plus: Release 9.0.1.0.0 - Production on Fri Mar 29 22:13:33 2002 (c) Copyright 2001 Oracle Corporation. All rights reserved. Enter user-name: / Connected to: Oracle9i Enterprise Edition Release 9.0.1.0.0 - Production With the Partitioning option JServer Release 9.0.1.0.0 - Production 22:13:35 SQL> What I just demonstrated is behavior going back all the way to Oracle Version 5. I was curious about if this authentication mechanism got changed when Oracle 9i came out. Now is where it gets interesting. I login to the UNIX applmgr account and attempt to CONNECT AS SYSDBA: X9% id uid=1002(applmgr) gid=102(netops) X9% sqlplus SQL*Plus: Release 9.0.1.0.0 - Production on Fri Mar 29 21:56:00 2002 (c) Copyright 2001 Oracle Corporation. All rights reserved. Enter user-name: / AS SYSDBA ERROR: ORA-01031: insufficient privileges This is interesting behavior. Also it matches what I found on 8.1.7. When I first bumped into it I thought it was a bug. Now it looks like a feature. It maintains the practice of placing the UNIX oracle account at a higher privilege level than the other UNIX accounts. If I'm willing to supply a password I get connected with no problem: Enter user-name: OPS$APPLMGR/YOHAA AS SYSDBA Connected to: Oracle9i Enterprise Edition Release 9.0.1.0.0 - Production With the Partitioning option JServer Release 9.0.1.0.0 - Production 22:06:57 SQL> exit Disconnected from Oracle9i Enterprise Edition Release 9.0.1.0.0 - Production With the Partitioning option JServer Release 9.0.1.0.0 - Production X9% I'm uncomfortable with allowing UNIX accounts other than oracle access to the SYSDBA role. So I issue this command to scramble the password: 22:13:35 SQL> ALTER USER ops$applmgr IDENTIFIED BY VALUES 'x'; User altered. 22:32:19 SQL> Here is another experiment. Can a user with DBA privilege grant SYSDBA to another user, change that user's password to a known value, and then connect to SYSDBA as that user. Let's find out. X9% sqlplus SQL*Plus: Release 9.0.1.0.0 - Production on Fri Mar 29 22:38:43 2002 (c) Copyright 2001 Oracle Corporation. All rights reserved. Enter user-name: / as sysdba Connected to: Oracle9i Enterprise Edition Release 9.0.1.0.0 - Production With the Partitioning option JServer Release 9.0.1.0.0 - Production 22:38:48 SQL> CREATE USER BIKLE IDENTIFIED BY Y2K; User created. 22:39:01 SQL> GRANT DBA TO BIKLE; Grant succeeded. 22:39:15 SQL> exit Disconnected from Oracle9i Enterprise Edition Release 9.0.1.0.0 - Production With the Partitioning option JServer Release 9.0.1.0.0 - Production X9% sqlplus BIKLE/Y2K SQL*Plus: Release 9.0.1.0.0 - Production on Fri Mar 29 22:39:31 2002 (c) Copyright 2001 Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.0.1.0.0 - Production With the Partitioning option JServer Release 9.0.1.0.0 - Production 22:39:32 SQL> CREATE USER MIKE IDENTIFIED BY Y2K; User created. 22:40:23 SQL> GRANT SYSDBA TO MIKE; GRANT SYSDBA TO MIKE * ERROR at line 1: ORA-01031: insufficient privileges 22:40:39 SQL> So, the answer is no; a DBA cannot create a SYSDBA. Question: Can a DBA change the password of a SYSDBA? Let's find out: gemprod2-oracle-X9% id uid=1001(oracle) gid=101(dba) gemprod2-oracle-X9% sqlplus SQL*Plus: Release 9.0.1.0.0 - Production on Sat Mar 30 01:05:45 2002 (c) Copyright 2001 Oracle Corporation. All rights reserved. Enter user-name: / as sysdba Connected to: Oracle9i Enterprise Edition Release 9.0.1.0.0 - Production With the Partitioning option JServer Release 9.0.1.0.0 - Production 01:05:51 SQL> -- ALTER SESSION SET NLS_DATE_FORMAT='DD-Mon-YYYY HH24:MI:SS'; 01:05:51 SQL> CREATE USER BIKLE_SYSDBA IDENTIFIED BY B1; User created. 01:06:35 SQL> GRANT SYSDBA TO BIKLE_SYSDBA; Grant succeeded. 01:06:48 SQL> CREATE USER BIKLE_DBA IDENTIFIED BY B2; User created. 01:07:19 SQL> GRANT DBA TO BIKLE_DBA; Grant succeeded. 01:07:34 SQL> CONNECT BIKLE_DBA/B2 Connected. 01:08:01 SQL> ALTER USER BIKLE_SYSDBA IDENTIFIED BY B3; User altered. 01:08:20 SQL> 01:11:43 SQL> CONNECT BIKLE_SYSDBA/B3 AS SYSDBA Connected. 01:12:52 SQL> SHUTDOWN IMMEDIATE Database closed. Database dismounted. ORACLE instance shut down. 01:13:18 SQL> So, the answer is yes, a DBA can change the password of a SYSDBA and use the changed password to gain access to a higher level of privilege.