At 02:15 AM 17/12/2007, you wrote:

>Follow scenario:
>
>Create a role named ROLE_TEST used for accessing a project.
>--> create role ROLE_TEST
>
>create 2 users named USER_A en USER_B with gsec.
>
>I grant (with SYSDBA account USER_A and USER_B) both access to the
>role with admin option.
>--> grant ROLE_TEST to USER_A with admin option
>--> grant ROLE_TEST to USER_B with admin option
>Those two ppl are my admin ppl for a project and they don't have
>access to SYSDBA, they only are responsible to give access to existing
>users to the project, they given it using a administration program
>that is part of the large project.
>
>Now USER_A grant the role ROLE_TEST to some user USER_C, so this user
>can now have access to the project.
>
>USER_A is ill or isn't reachable for a long period of time and USER_C
>should be denied of the project. Now i thought because USER_B has with
>admin option he could revoke the role from USER_C. But it can't, it
>seems that only SYSDBA and the grantor (in this case USER_A) can
>revoke it.
>
>I expecting following rules:
>1. The grantor can give the role to some grantee
>2. The grantor can revoke the privileges:
>2.1 He is SYSDBA
>2.2 He was the grantor
>2.3 The grantee have gotten the rights through another grantor and he
>doesn't have admin option as well on this role.
>
>Questions:
>Is this normal behaviour?

Yes.

>and if it is, can you explain why?

Because the SQL standards were designed by a committee. ;-) Privileges are fine if you design your privileges schema well, keep it simple and document it. "Simple" includes appointing WITH ADMIN OPTION to one and only one administrative user and keeping that user's login credentials safe and available to a very small number of authorised humans.

./hb