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.’
‘Create’ a Connection
Click on the green ‘+’ button in the DBA panel. It will ask you to choose a previously defined SQL Developer connection.
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.
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:
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.
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!
14 Comments
I’m getting the following error message when attempting to create a new user:
ORA-65906: invalid common user or role name.
I appreciate any suggestions.
you’re in a container database most likely, and you really probably don’t want to do what you’re trying to do…but if you are, it would be C##USERNAME not USERNAME, otherwise connect to your pluggable database
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!!
SELECT
*
FROM
USER_SYS_PRIVS;
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.
That looks like an OS Authenticated user account…is it?
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”
you’re either connecting to the wrong database, or you’re trying to connect as sys w/o the AS SYSDBA flag set
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?? 🙂
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.
What about assigning a non Default profile?
That’s not baked into the UI, you’ll have to do it via SQL in the worksheet.
I am using sql developer verion 4.0.2.1.5. On selecting view and dba, nothing shows up
Try resetting the desktop. Window – reset all windows…or something close to that.