0

If I'm creating the role 'VIPGUEST' and giving them object privileges to these environments:

BedroomDEV
KitchenINT
GarageTEST
LivingroomTRN
DiningroomPROD

Create role VIPGUEST not identified

Grant connect to VIPGUEST

Grant create session TO VIPGUEST

Grant delete, execute to VIPGUEST

Grant insert, load, view to VIPGUEST

Grant refresh, references, select to VIPGUEST

Grant update, resource, index, alter to VIPGUEST

How do I combine these to get the results that I want?

I know my statements are incomplete, but I don’t know how.

Littlefoot
  • 131,892
  • 15
  • 35
  • 57
Edomawadagbon
  • 1
  • "results that I want" Can you describe, what exactly do you want? – Pavel Smirnov Apr 16 '19 at 11:21
  • Yes, thank you Pavel. I want to Create a role for a developer so that he doesn’t have a Dba role. This role will have full object privileges. – Edomawadagbon Apr 16 '19 at 13:28
  • Have a look at this: https://stackoverflow.com/questions/27353642/grant-all-privileges-to-user-on-oracle-schema. You can grant privileges to roles the same way. – Pavel Smirnov Apr 16 '19 at 13:41

1 Answers1

0

There are different categories of privileges, such as system, table or procedure ones. Depending on that, GRANT differs.

You can GRANT CREATE SESSION TO VIPGUEST, but can't GRANT DELETE TO VIPGUEST. Delete what? GRANT DELETE ON EMP TO VIPGUEST would make sense.

The same goes for, for example, EXECUTE: you have to say what you'd want to allow VIPGUEST to execute, e.g. GRANT EXECUTE ON p_insert_employee TO VIPGUEST.

Have a look at the Security Guide (of your database version) for some more info.

P.S. Don't grant CONNECT nor RESOURCE. Those were "popular" roles some time ago but will be deprecated in future Oracle database releases. Correct way is what you're trying to do: create your own role with minimum set of privileges which will enable users (who will be granted that role) to work.

Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • got it! thank you. I'm still having issues understanding the structure of the language. How do I implement these along with the environments have I have (bedrrom, kitchen, garage, etc). How do I combine them? I was thinking Grant update, delete on DiningroomPROD to VIPGUEST; Would something like this work? – Edomawadagbon Apr 16 '19 at 15:29
  • You're welcome. If "DiningroomPROD" is a table, then yes - what you wrote would work. If it is not, what is it, then? You said "environment", but I don't know what it actually means in this context. – Littlefoot Apr 16 '19 at 19:38