Home » SQL & PL/SQL » SQL & PL/SQL » Oracle Role creation on Schema
Oracle Role creation on Schema [message #669796] Mon, 14 May 2018 05:53 Go to next message
ramya29p
Messages: 111
Registered: November 2007
Location: Chennai
Senior Member
Hi,
Could any one please tell me can we create Role at schema level as well.

I am bit confused about role creation.

For Example, I have a Database HXQ1 and I need to create 4 new schemas Q1,Q2,Q3 and Q4.

to provide a privileges I need to create a roles.

Whether roles can be created on schema levels [Q1,Q2,Q3,Q4 ] or Database HXQ1 level.

Please clarify.
Re: Oracle Role creation on Schema [message #669797 is a reply to message #669796] Mon, 14 May 2018 05:57 Go to previous messageGo to next message
Michel Cadot
Messages: 65480
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

No feedback, no thank, no help above all when rules are ignored.

[Updated on: Mon, 14 May 2018 05:57]

Report message to a moderator

Re: Oracle Role creation on Schema [message #669798 is a reply to message #669796] Mon, 14 May 2018 07:52 Go to previous messageGo to next message
EdStevens
Messages: 932
Registered: September 2013
Senior Member
ramya29p wrote on Mon, 14 May 2018 05:53
Hi,
Could any one please tell me can we create Role at schema level as well.

I am bit confused about role creation.

For Example, I have a Database HXQ1 and I need to create 4 new schemas Q1,Q2,Q3 and Q4.

to provide a privileges I need to create a roles.

Whether roles can be created on schema levels [Q1,Q2,Q3,Q4 ] or Database HXQ1 level.

Please clarify.
Please point out at what point in the CREATE ROLE syntax (here) is there provision for creating a role on a schema.

Do you understand that a role is nothing more than a named collection of privileges and/or other roles?
Do you understand that a role is GRANTed to a user, in exactly the same manner and for exactly the same purpose as GRANTing a privilege?

BTW, I do hope that those schema names (Q1, Q2, Q3, Q4) are not indicative of "quarter-1", etc ... that you are not creating schemas to hold data based on time periods.
Re: Oracle Role creation on Schema [message #669852 is a reply to message #669798] Thu, 17 May 2018 01:37 Go to previous messageGo to next message
ramya29p
Messages: 111
Registered: November 2007
Location: Chennai
Senior Member
(Q1,Q2,Q3,Q4) are not quarters. Just for example I have mentioned it.

Please clarify me, suppose if I want to create 4 roles role_q1,role_q2,role_q3,role_q4, will it be created on a database HXQ1.

and can these roles assigned to the schemas.

[Updated on: Thu, 17 May 2018 01:37]

Report message to a moderator

Re: Oracle Role creation on Schema [message #669853 is a reply to message #669852] Thu, 17 May 2018 02:11 Go to previous messageGo to next message
Michel Cadot
Messages: 65480
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Mon, 14 May 2018 12:57

No feedback, no thank, no help above all when rules are ignored.

Re: Oracle Role creation on Schema [message #669855 is a reply to message #669852] Thu, 17 May 2018 06:34 Go to previous message
EdStevens
Messages: 932
Registered: September 2013
Senior Member
ramya29p wrote on Thu, 17 May 2018 01:37
(Q1,Q2,Q3,Q4) are not quarters. Just for example I have mentioned it.

Please clarify me, suppose if I want to create 4 roles role_q1,role_q2,role_q3,role_q4, will it be created on a database HXQ1.
It should be self-evident that the role will be created in whatever database you are connected to when you issue the CREATE ROLE command.

Quote:
and can these roles assigned to the schemas.
I'm not sure what you mean by "assigned". Like I said, roles are granted to a user with the GRANT command. Do you understand what it means to GRANT a role to a user? Do you understand the difference (or lack of) between a 'schema' and a 'user'?

Why are you wanting to create a role? And why do you want to "assign it to a schema"? What business problem are you trying to solve?
Previous Topic: Materialised Views - FAST Refresh
Next Topic: Adding up graphs
Goto Forum:
  


Current Time: Sun May 20 14:56:14 CDT 2018