This discussion is archived
1 2 Previous Next 17 Replies Latest reply: Dec 5, 2013 10:20 AM by rp0428 RSS

Creating Externally Authenticated users

piontekdd Newbie
Currently Being Moderated

Greetings,

We recently migrated our Security team from Windows XP to Windows 7. With this upgrade, they were forced to stop using the java Oracle 9i Enterprise Manager to manage security and database users. I was able to find the View->DBA tab in Oracle SQL Developer which allows for things like CREATE LIKE, CREATE, etc, but under the CREATE USER, I see nowhere where the tool allows for a user other than a normal database authenticated account. We have a few key databases where we must create externally authenticated users (EXTERNAL) and this just isn't an option. Is this functionality anywhere in the tool?

 

Thanks

 

Bradd

  • 1. Re: Creating Externally Authenticated users
    TSharma-Oracle Guru
    Currently Being Moderated

    I do not think you have an option to create "externally authenticated" user through sqldeveloper.

     

    You can use Oracle Enterprise Manager (OEM) 10g ,11g or 12c. This is web based enterprise manager. You do not need to install on every machine.It is much much better than 9i enterprise mange.

  • 2. Re: Creating Externally Authenticated users
    rp0428 Guru
    Currently Being Moderated
    We recently migrated our Security team from Windows XP to Windows 7. With this upgrade, they were forced to stop using the java Oracle 9i Enterprise Manager to manage security and database users. I was able to find the View->DBA tab in Oracle SQL Developer which allows for things like CREATE LIKE, CREATE, etc, but under the CREATE USER, I see nowhere where the tool allows for a user other than a normal database authenticated account. We have a few key databases where we must create externally authenticated users (EXTERNAL) and this just isn't an option. Is this functionality anywhere in the tool?

     

    I don't understand what you are trying to do.

     

    Post your full sql developer info and explain in detail what you mean; with an example if possible.

     

    You can create users in the DB the way you do with any tool: write the appropriate DDL for CREATE USER. For OS authentication you add the OS_AUTHENT_PREFIX to the user name.

     

    In sql developer create connections for those users using the connections dialog that you use for any other user. On that dialog there is a checkbox for OS authentication.

     

    See this article by Sue Harper and see if the example for local OS authentication she provides answers your question:

    http://www.oracle.com/technetwork/issue-archive/2008/08-may/o38sql-102034.html

     

    To configure local OS authentication for a new user, first find the value of the OS_AUTHENT_PREFIX database initialization parameter in your system's init.ora file. When you create this new user in the database, you must add this parameter value as a prefix to the OS username. The default value is OPS$, for backward compatibility with earlier database releases. (If the value is "", the OS username and the database username are the same, so you don't need to add a prefix to create the Oracle usernames.)

    Establish a basic connection with the HR schema as the SYSTEM user. Execute the following from the SQL worksheet, using your database's OS_AUTHENT_PREFIX prefix and substituting your own OS username for "sue":

     

    CREATE USER ops$sue IDENTIFIED EXTERNALLY;  GRANT Connect, resource to sue;      

    Now create a basic connection for this user from the New / Select Database Connection dialog box. Enter a connection name; select Basic for Connection Type ; fill in the Hostname and Port fields; select OS Authentication ; and provide a SID or Service name . Click Test and Connect as before.

  • 3. Re: Creating Externally Authenticated users
    TSharma-Oracle Guru
    Currently Being Moderated

    rp0428: I guess OP wants to use the GUI interface to create "externally authenticated" users. 

  • 4. Re: Creating Externally Authenticated users
    piontekdd Newbie
    Currently Being Moderated

    I am fully aware that Oracle EM can do this. We aren't giving access to oracle EM , for various reasons, to security.

  • 5. Re: Creating Externally Authenticated users
    piontekdd Newbie
    Currently Being Moderated

    As for writing DDL. This is not something a typical Security End-User knows how to do. It is the work-around we've given them. If Oracle SQL Developer is going to have a DBA view, and manage users, it should have all the same creation functions as other Oracle tools. (AUTHENTICATION TYPE is missing).

  • 6. Re: Creating Externally Authenticated users
    TSharma-Oracle Guru
    Currently Being Moderated

    I do not what security. You can make separate users and give separate grants to them. It will be same as when users were having access on 9i EM. Where is the difference?

     

    But if you say so, you can execute "ddl" statements on sqldeveloper editor to create users. Example is mentioned in the doc provided by rp. I do not think you can create this type of user using GUI. I could be wrong though.

  • 7. Re: Creating Externally Authenticated users
    piontekdd Newbie
    Currently Being Moderated

    Thanks for attempting to answer the question. It'd be nice if things stayed on topic (suggesting another tool isn't on topic, in my opinion). I know how to set up groups and users in Oracle EM. Like I said, we have our reasons for not giving access to that environment to our Security team.

  • 8. Re: Creating Externally Authenticated users
    TSharma-Oracle Guru
    Currently Being Moderated

    Then what you are debating on.I believe you got your answer NO.

  • 9. Re: Creating Externally Authenticated users
    rp0428 Guru
    Currently Being Moderated
    rp0428: I guess OP wants to use the GUI interface to create "externally authenticated" users. 

    You can do that in sql developer.

  • 10. Re: Creating Externally Authenticated users
    rp0428 Guru
    Currently Being Moderated
    As for writing DDL. This is not something a typical Security End-User knows how to do. It is the work-around we've given them. If Oracle SQL Developer is going to have a DBA view, and manage users, it should have all the same creation functions as other Oracle tools. (AUTHENTICATION TYPE is missing).

    You may want to post an enhancement request to the sql developer team to add a checkbox to the 'Create User' window.

     

    On that window you can select the 'sql' tab and add the proper syntax:

    CREATE USER ops$sue IDENTIFIED EXTERNALLY;

    That will create the user using OS authentication (assuming the default OPS$ prefix).

     

    For others the steps are:

    1. open the DBA pane - View -> DBA

    2. select an appropriate connection (or add one from the Connection pane)

    3. open the connection

    4. expand the Security element

    5. right-click on Users

    6. Select 'Create New...'

    7. Enter the user name (and other settings - not password) - e.g. 'abcd

    8. Select the SQL tab - CREATE USER abcd IDENTIFIED BY null ;

    9. Replace the 'BY null' with 'EXTERNALLY'

    10. Apply

     

    If a checkbox were added it would then add the word EXTERNALLY for you.

     

    I suggest leaving the thread open - the sql dev team monitors the forum and may respond.

  • 11. Re: Creating Externally Authenticated users
    TSharma-Oracle Guru
    Currently Being Moderated

    I tried that already but in my version it does not let me modify the SQL tab.

  • 12. Re: Creating Externally Authenticated users
    Jeff Smith SQLDev PM ACE Moderator
    Currently Being Moderated

    I've logged this to be addressed in a future version.

     

    In the meantime, you could:

    • create a snippet to perform this operation
    • use the existing dialog and copy out the SQL to the worksheet and modify/run from there
  • 13. Re: Creating Externally Authenticated users
    rp0428 Guru
    Currently Being Moderated

    TSharma wrote:

     

    I tried that already but in my version it does not let me modify the SQL tab.

    Works for me in 3.2.20.9

  • 14. Re: Creating Externally Authenticated users
    TSharma-Oracle Guru
    Currently Being Moderated

    rp0428 wrote:

     

    TSharma wrote:

     

    I tried that already but in my version it does not let me modify the SQL tab.

    Works for me in 3.2.20.9

     

    I have version Version 3.2.20.10. It does not work in there. Even the Jeff Smith is saying that you have to copy that to worksheet and modify.

1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points