Managing User & Role Security with Oracle SQL Developer

thatjeffsmith SQL Developer 12 Comments

Tell Others About This Story:

With the advent of SQL Developer v3.0, users have had access to some powerful database administration features. Version 3.1 introduced more powerful features such as an interface to Data Pump and RMAN. Today I want to talk about some very simple but frequently ran tasks that SQL Developer can assist with, like:

  • identifying privs granted to users
  • managing role privs
  • assigning new roles and privs to users & roles

Before getting started, you’ll need a connection to the database with the proper privileges. The common ROLE used to accomplish this is the ‘DBA‘ role. Curious as to what the DBA role is actually comprised of? Let’s find out!

Open the DBA Console

First make sure you’re connected to the database you want to manage security on with a privileged administrator account. Then open the View menu and select ‘DBA.’

Accessing the DBA panel

‘Create’ a Connection

Click on the green ‘+’ button in the DBA panel. It will ask you to choose a previously defined SQL Developer connection.

Defining a DBA connection in Oracle SQL Developer

Once connected you will see a tree list of DBA features you can start interacting with.

Expand the ‘Security’ Tree Node

As you click on an object in the DBA panel, the ‘viewer’ will open on the right-hand-side, just like you are accustomed to seeing when clicking on a table or stored procedure.

Accessing the DBA role

If I’m a newly hired Oracle DBA, the first thing I might want to do is become very familiar with the DBA role. People will be asking you to grant them this role or a subset of its privileges. Once you see what the role can do, you will become VERY protective of it. My favorite 3-letter 4-letter word is ‘ANY’ and the DBA role is littered with privileges like this:

ANY TABLE privs granted to DBA role

So if this doesn’t freak you out, then maybe you should re-consider your career path. Or in other words, don’t be granting this role to ANYONE you don’t completely trust to take care of your database.

If I’m just assigned a new database to manage, the first thing I might want to look at is just WHO has been assigned the DBA role. SQL Developer makes this easy to ascertain, just click on the ‘User Grantees’ panel.

Who has the keys to your car?

Making Changes to Roles and Users

If you mouse-right-click on a user in the Tree, you can do individual tasks like grant a sys priv or expire an account. But, you can also use the ‘Edit User’ dialog to do a lot of work in one pass.

As you click through options in these dialogs, it will build the ‘ALTER USER’ script in the SQL panel, which can then be executed or copied to the worksheet or to your .SQL file to be ran at your discretion.

A Few Clicks vs a Lot of Typing

These dialogs won’t make you a DBA, but if you’re pressed for time and you’re already in SQL Developer, they can sure help you make up for lost time in just a few clicks!

Tell Others About This Story:

Comments 12

  1. I would really like to know if we can somehow check our own, current, connected user to check it’s roles, grants, etc.

    Just like we open the “Other users” tab and check all other users attributes, why there’s no “currect” user?? Or if it’s hidden somewhere, why it’s so hidden? 🙂

    Why not just rename “other users” to “Users” and include your own?

    Thanks!!

  2. Hi
    I am abhishek and I am new to oracle db,here is the problem I am facing:

    I create user “c##project”,and I give all the required privileges to it ,now when I try to connect developer tool through username->”c##project”
    it does not get connect it ,it gives me the below error :

    ORA-01017: invalid username/password; logon denied

    though the credential I have entered are correct and I have given all the privileges to it,can you pls help me out how can I connect to sqldeveloper tool by the user we have created.I get successfully connected to it sqldeveloper tool through username:sys but when I try to connect sqldeveloper tool through other user we have created in security node I am not able to connect to it.

    1. thatjeffsmith Post
      Author
      1. Hi I created user and with the that user I want to login using tool sqldeveloper but when i enter the user and pwd in sqldev tool it gives me the below messages

        ORA-01017: invalid username/password; logon denied

        why this error message is thrown when the username and pwd i entered is absolutely right.

        I can connect to sqlplus but when i want to use same username and pwd and want to connect with sqldeveloper tool i am not able to login and it gives the messages “ORA-01017: invalid username/password; logon denied”

        1. thatjeffsmith Post
          Author
          1. I can connect sqldev by username : sys and connection as type:sysdba,
            but when i create other user for example c##project and when i try to login by username:c##project an connection type as :sysdba sqldev tool denies the login and gives error message invalid username and password

            how can I connect to sqldev tool by using other users other then sys hope you are getting my problem?? 🙂

          2. thatjeffsmith Post
            Author

            c## looks like an OS authenticated account prefix. Is this an OS authenticated account?

            The only account that requires the as SYSDBA is ‘SYS’ and you shouldn’t use it, pretty much, ever.

    1. thatjeffsmith Post
      Author
    1. thatjeffsmith Post
      Author

Leave a Reply

Your email address will not be published. Required fields are marked *