4 Replies Latest reply: Feb 20, 2013 3:31 AM by Gary Graham-Oracle RSS

    SQL developer does not support connection as SYSASM

    grooveshine
      SQL Developer (3.2.20.09) only allows connection as sysdba, not sysasm.
      To access and administer ASM instances (create diskagrouip, add disk, ...) SYSASM role is required (SYSDBA will not work).

      Are there plan to support SYSADM in future release ?
      Are there workaround to connect as SYSASM in current 3.2 release?
      Thx.

      Alain
        • 1. Re: SQL developer does not support connection as SYSASM
          Gary Graham-Oracle
          Hi Alain,

          Not that I have heard of. An ASM instance is not a database instance. Although the SQL Developer tool has added more and more database administration features over time, Automatic Storage Management is a specialized sub-category with its own tools, created by a distinct development group within Oracle. For example, see http://docs.oracle.com/cd/E16338_01/server.112/e10500/asmca.htm

          Typically we try to minimize duplicating functionality between products unless that functionality is being transitioned to us. Maybe someday...

          Regards,
          Gary
          SQL Developer Team
          • 2. Re: SQL developer does not support connection as SYSASM
            grooveshine
            Hi Garry,

            Thank you for this reply.
            I understand the situation of having different instance type, but I was expecting Oracle to promote his own tools and thereby not limiting the access to certain type only.
            Of course there are ohers tools and we are using Enterprise Manager / Cloud control as well, but it is sometime easier, more convenien, for DBAs to manage SQL Worksheets and SQL scripts.

            Appart of this SYSASM role, another one SYSOPER , much older, is also not possible.
            Anyway, anotyher day maybee as you wrote...

            Regards,
            Alain
            • 3. Re: SQL developer does not support connection as SYSASM
              rp0428
              >
              SQL Developer (3.2.20.09) only allows connection as sysdba, not sysasm.
              To access and administer ASM instances (create diskagrouip, add disk, ...) SYSASM role is required (SYSDBA will not work).

              Are there plan to support SYSADM in future release ?
              Are there workaround to connect as SYSASM in current 3.2 release?
              >
              When you say 'only allows connection as sysdba, not sysasm' it sounds like you are just referring to the selections in the drop-down list.

              Have you tried adding 'AS SYSASM' to the user name for the connection?

              I don't have an ASM instance availble right now but I can connect as sys and do this
              grant sysdba to scott;
              Then if I create a connection as user 'scott' and try this grant it does not work:
              grant select any table to hr;
              
              Error starting at line 1 in command:
              grant select any table to hr
              Error report:
              SQL Error: ORA-01031: insufficient privileges
              01031. 00000 -  "insufficient privileges"
              *Cause:    An attempt was made to change the current username or password
                         without the appropriate privilege. This error also occurs if
                         attempting to install a database without the necessary operating
                         system privileges.
              . . .
              But if I create the connection as user 'scott as sysdba' the grant works just fine.
              grant succeeded.
              Clearly the two connections are different so the 'as sysdba' is definitely being used.

              Don't know if that is intended behavior for sql developer or a bug but you might want to try using 'scott as sysasm' (using your user) and see if that works.

              Also don't know if that will work for SYSASM or SYSOPER.

              Please post the result.

              The dev team will have to test and comment on whether this is supported behavior or a bug.
              • 4. Re: SQL developer does not support connection as SYSASM
                Gary Graham-Oracle
                Interesting. It seems the Oracle JDBC driver handles both of...

                1. A username suffixed with "as <role>" but no separate role property
                2. A username plus a role

                in a similar fashion. Neither SQL Developer nor the JDeveloper framework (on which we rely) block the format in (1) from passing through to the driver when creating a database connection. So if your workaround also works for the SYSADM role, it is not due to SQL Developer either helping or hindering.

                Thanks,
                Gary