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: 146
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: 68625
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: 1376
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: 146
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: 68625
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 messageGo to next message
EdStevens
Messages: 1376
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?
Re: Oracle Role creation on Schema [message #669890 is a reply to message #669852] Wed, 23 May 2018 01:46 Go to previous message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
ramya29p wrote on Wed, 16 May 2018 23: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.

and can these roles assigned to the schemas.

The following just demonsrates what you seem to be asking for, but without knowing your whole situation, it is not necessarily what you should do.

create role role_q1;
grant role role_q1 to q1;

create role role_q2;
grant role role_q2 to q2;

create role role_q3;
grant role role_q3 to q3;

create role role_q4;
grant role role_q4 to q4;


Previous Topic: Help required for adding patition by range in table
Next Topic: How to implement regex (merged 2)
Goto Forum:
  


Current Time: Thu Mar 28 12:06:38 CDT 2024