I have a consultant coming in to do some work on our Oracle instances. I want to create a user for him so that he can only modify the Oracle instances.
First, to have a secure setup make sure that at least the following objects are secured. (Any less will potentially make it possible for the user to change his/her own access rights)
Then right click on “Users” and select “Add user”.
Enter a username and default password in the dialogue (he/she will be prompted to change it when they log in). Click OK.
You might already have a suitable role to give him/her, but for examples sake lets create one.
Right click on “Roles” and select “Add role”.
Then enter a role name and click OK.
Select the Consultant Role and click “Add Grant” in the lower right corner.
A line with “…” will appear in the list. Click the first “…” and select read in the dropdown list.
Repeat the process of clicking “Add Grant” and then select write and execute in the dropdown list.
We have now created a Consultant Role with the rights read, write and execute. But on what objects? We have to first define an Object Set that contains all the Oracle instances in order to associate the role and the oracle instances.
Right click on “Object Sets” and select “Add new object set”.
Enter a descriptive name for the object set and click OK.
Then we define the object set by entering a Dbwql Query that return all the Oracle instances. In our case the query “instance[databasetype=‘oracle’]” will return all registered instances of the type oracle.
Now we have a user “consultant”, a role “Consultant Role” that implies the rights read, write and execute, and a Object set “All Oracle instances” that contain all our Oracle instances.
There are two ways of tying these together. We could add the object set in the “On” column of the role directly, giving the Consultant Role to the consultant user, which would then result in a correct setup. A more flexible way is to associate the object set when assigning the role to the user, then the role can be used for different objects sets and for different users.
Select the consultant user and click “Add grant” in the lower right corner.
Click on the “…” in the “Rights and Roles” column. The role “Consultant Role” should appear in the list, select it.
Click on the “…” in the “On” column. The “All Oracle instances” object set should appear in the dropdown list, select it.