Home » RDBMS Server » Security » Role Change (Oracle ,10.2.0.4.0,windows)
Role Change [message #418874] Wed, 19 August 2009 00:12 Go to next message
Mohan10g
Messages: 159
Registered: May 2009
Location: INDIA
Senior Member
Hi all,

I checked my role using dba_role_privs view ,it was granted CONNECT role.

For your information,

GRANTEE GRANTED_ROLE ADM DEF
--------- ------------- --- ---
MOHAN CONNECT NO YES


Can anybody let me know how to change my current role to READONLY_ROLE ??

Regards,
Mohan

Report message to a moderator

Re: Role Change [message #418875 is a reply to message #418874] Wed, 19 August 2009 00:17 Go to previous messageGo to next message
BlackSwan
Messages: 26748
Registered: January 2009
Location: SoCal
Senior Member
>Can anybody let me know how to change my current role to READONLY_ROLE ??

What is READONLY_ROLE?
If this is your creation, you live with the results.

READONLY_ROLE is not an Oracle supplied ROLE.

Report message to a moderator

Re: Role Change [message #418878 is a reply to message #418874] Wed, 19 August 2009 00:21 Go to previous messageGo to next message
Michel Cadot
Messages: 67466
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You must:
1/ CREATE the ROLE
2/ GRANT the role to the user

These are described in Database SQL Reference.

Regards
Michel

Report message to a moderator

Re: Role Change [message #418879 is a reply to message #418875] Wed, 19 August 2009 00:21 Go to previous messageGo to next message
Mohan10g
Messages: 159
Registered: May 2009
Location: INDIA
Senior Member
Thanks for your quick response and for your information.

Report message to a moderator

Re: Role Change [message #418882 is a reply to message #418879] Wed, 19 August 2009 00:34 Go to previous messageGo to next message
Mohan10g
Messages: 159
Registered: May 2009
Location: INDIA
Senior Member
Thanks for your response.

To create role
create role readonly_role
identified by mohan;

grant privileges to role
grant select,insert,update on table to readonly_role;

grant role to users
grant readonly to mohan;

Are above steps are correct for creating and
granting roles to users;

Report message to a moderator

Re: Role Change [message #418883 is a reply to message #418882] Wed, 19 August 2009 00:45 Go to previous messageGo to next message
Michel Cadot
Messages: 67466
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes but you don't need to give a password to the role unless you want to protect it against the user itself and then a SECURE APPLICATION ROLE is a better option.

Regards
Michel

Report message to a moderator

Re: Role Change [message #418884 is a reply to message #418883] Wed, 19 August 2009 00:52 Go to previous messageGo to next message
Mohan10g
Messages: 159
Registered: May 2009
Location: INDIA
Senior Member

Thanks for your input.

What is SECURE APPLICATION ROLE ??

Report message to a moderator

Re: Role Change [message #418885 is a reply to message #418884] Wed, 19 August 2009 01:07 Go to previous messageGo to next message
Michel Cadot
Messages: 67466
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If I put it in upper case this is to allow you to search for it in http://www.oracle.com/pls/db102/db102.federated_search or http://www.oracle.com/pls/db102/homepage

Regards
Michel

[Updated on: Wed, 19 August 2009 01:07]

Report message to a moderator

Re: Role Change [message #419147 is a reply to message #418884] Thu, 20 August 2009 01:21 Go to previous messageGo to next message
Mohan10g
Messages: 159
Registered: May 2009
Location: INDIA
Senior Member
Hi All,

Can anybody let me know how to find out what privileges are given to roles ??


Cheers

Report message to a moderator

Re: Role Change [message #419148 is a reply to message #419147] Thu, 20 August 2009 01:23 Go to previous messageGo to next message
Michel Cadot
Messages: 67466
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
dba_role_privs

Regards
Michel

Report message to a moderator

Re: Role Change [message #419334 is a reply to message #419148] Fri, 21 August 2009 05:54 Go to previous messageGo to next message
Mohan10g
Messages: 159
Registered: May 2009
Location: INDIA
Senior Member

i checked with dba_role_privs view ,it showing me role name.

1* select * from dba_role_privs where grantee='MOHAN'
SQL> /

GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
MOHAN READONLY_ROLE NO YES

I want to know what privileges like select,execute,delete,insert have been give to readonly_role.

Regards

Report message to a moderator

Re: Role Change [message #419338 is a reply to message #419334] Fri, 21 August 2009 06:15 Go to previous messageGo to next message
Michel Cadot
Messages: 67466
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So you didn't put the correct grantee in your query.

Regards
Michel

Report message to a moderator

Re: Role Change [message #419342 is a reply to message #419338] Fri, 21 August 2009 06:36 Go to previous messageGo to next message
Mohan10g
Messages: 159
Registered: May 2009
Location: INDIA
Senior Member

i didnt get you michel.Could you please come again?

Report message to a moderator

Re: Role Change [message #419344 is a reply to message #419342] Fri, 21 August 2009 06:42 Go to previous messageGo to next message
Michel Cadot
Messages: 67466
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
grantee -> the one that has been granted
You want the privileges of READONLY_ROLE role, so grantee is...?

Also check other DBA%PRIVS views.

Regards
Michel

Report message to a moderator

Re: Role Change [message #420273 is a reply to message #419344] Fri, 28 August 2009 00:49 Go to previous messageGo to next message
Mohan10g
Messages: 159
Registered: May 2009
Location: INDIA
Senior Member

I checked dba%privs% but i could not find wat
im looking for.

For example, there is a user called DILIP
and in that user there is one role called MATTHEW.
I want to know wat privileges have been granted
to that MATTHEW role.


Can anybody let me know on this?

Regards

Report message to a moderator

Re: Role Change [message #420275 is a reply to message #420273] Fri, 28 August 2009 01:00 Go to previous message
Michel Cadot
Messages: 67466
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I checked dba%privs% but i could not find wat
im looking for.

Post what you did as you should find it.

Regards
Michel

Report message to a moderator

Previous Topic: SYSTEM schema security
Next Topic: network encryption for oracle
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon Oct 26 11:35:59 CDT 2020
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.