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 |
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 #669798 is a reply to message #669796] |
Mon, 14 May 2018 07:52 |
|
EdStevens
Messages: 1376 Registered: September 2013
|
Senior Member |
|
|
ramya29p wrote on Mon, 14 May 2018 05:53Hi,
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 |
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 #669855 is a reply to message #669852] |
Thu, 17 May 2018 06:34 |
|
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 |
|
Barbara Boehmer
Messages: 9097 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;
|
|
|
Goto Forum:
Current Time: Fri May 31 23:08:39 CDT 2024
|