Home » RDBMS Server » Security » User roles and grants(indirect) (merged)
User roles and grants(indirect) (merged) [message #385868] Wed, 11 February 2009 10:51 Go to next message
aviana
Messages: 106
Registered: July 2007
Senior Member
I have got the "super","ordinary" and "streetmap" roles in my database
"Super" is the topmost role
I had performed the foll:
Grant ordinary to super;
Grant streetmap to ordinary;
Grant succeeded.


Now, please answer my foll.questions:-
1)The role "streetmap" is automatically assigned to "super", isnt't it?
But I have got a user with just the "super" role(not having ordinary role directly)
But when I give the following query I am not getting any rows returned.

SELECT *
FROM user_role_privs
WHERE granted_role IN
(SELECT ROLE FROM role_role_privs WHERE granted_role IN
('STREETMAP'));
Is there a way to see the indirect roles?ie,to see that
"SUPER" is assigned the
"STREETMAP" role indirectly?

2)I need this basically to use in a form query..What I really want is to only allow users with "streetmap" role (This is a newly created role) to change a field..
I already have the users assigned the super and ordinary roles already..Also I have assigned this "streetmap" role to "ordinary" but my problem is,when I have users just have the "Super" they are also allowed to change the field as they are indirectly having this "streetmap" privilege.
User roles and grants(indirect) [message #385870 is a reply to message #385868] Wed, 11 February 2009 10:53 Go to previous messageGo to next message
aviana
Messages: 106
Registered: July 2007
Senior Member
I have got the "super","ordinary" and "streetmap" roles in my database
"Super" is the topmost role
I had performed the foll:
Grant ordinary to super;
Grant streetmap to ordinary;
Grant succeeded.


Now, please answer my foll.questions:-
1)The role "streetmap" is automatically assigned to "super", isnt't it?
But I have got a user with just the "super" role(not having ordinary role directly)
But when I give the following query I am not getting any rows returned.

SELECT *
      FROM   user_role_privs
      WHERE  granted_role IN
	  (SELECT ROLE FROM role_role_privs WHERE granted_role IN
	  ('STREETMAP'));

Is there a way to see the indirect roles?ie,to see that
"SUPER" is assigned the
"STREETMAP" role indirectly?

2)I need this basically to use in a form query..What I really want is to only allow users with "streetmap" role (This is a newly created role) to change a field..
I already have the users assigned the super and ordinary roles already..Also I have assigned this "streetmap" role to "ordinary" but my problem is,when I have users just have the "Super" they are also allowed to change the field as they are indirectly having this "streetmap" privilege.
Re: User roles and grants(indirect) [message #385877 is a reply to message #385870] Wed, 11 February 2009 11:43 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Is there a way to see the indirect roles?

Use "connect by" to get the transitive closure of the roles that is recursive roles.

Quote:
but my problem is,when I have users just have the "Super" they are also allowed to change the field as they are indirectly having this "streetmap" privilege.

How is this a problem? this is what you designed.

Regards
Michel
Previous Topic: problem with auditing
Next Topic: how to provide grant permission to other user for user defined datatype?
Goto Forum:
  


Current Time: Fri Mar 29 10:07:00 CDT 2024