6 Replies Latest reply: May 1, 2012 9:06 PM by Karan RSS

    Create a role without becoming a member...

    631584
      I looked all over and couldn't find the answer....I'm 99% sure it's possible....Just cannot remember for the life of me.


      What I'm trying to do is create a role (Create Role TestRoleABC) without becoming a member of it or having the admin ability attached to the ID I'm creating the role with.


      So, example being. Using ID "AppID123", I issue "Create Role TestRoleABC". After creation, the ID "AppID123" is now a member of "TestRoleABC" and has the ability to grant it. I only want accounts that have the "Grant Any Role" priv to be able to do so....


      Thanks.
        • 1. Re: Create a role without becoming a member...
          sb92075
          Topher34 wrote:
          I looked all over and couldn't find the answer....I'm 99% sure it's possible....Just cannot remember for the life of me.


          What I'm trying to do is create a role (Create Role TestRoleABC) without becoming a member of it or having the admin ability attached to the ID I'm creating the role with.


          So, example being. Using ID "AppID123", I issue "Create Role TestRoleABC". After creation, the ID "AppID123" is now a member of "TestRoleABC" and has the ability to grant it. I only want accounts that have the "Grant Any Role" priv to be able to do so....


          Thanks.
          when all else fails Read The Fine Manual

          http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_6012.htm#i2066772

          "If you create a role that is NOT IDENTIFIED or is IDENTIFIED EXTERNALLY or BY password, then Oracle Database grants you the role with ADMIN OPTION. However, if you create a role IDENTIFIED GLOBALLY, then the database does not grant you the role. A global role cannot be granted to a user or role directly. Global roles can be granted only through enterprise roles."
          • 2. Re: Create a role without becoming a member...
            631584
            So, what I take out of the documentation, which I seen and skimmed too quickly (thanks for the link!) is that I'm SOL. Only way to create a role that I can assign to users isto create it, then go back and remove the creator from the role. . . .

            Just seems odd. . .
            • 3. Re: Create a role without becoming a member...
              rp0428
              >
              What I'm trying to do is create a role (Create Role TestRoleABC) without becoming a member of it or having the admin ability attached to the ID I'm creating the role with.
              . . .
              Only way to create a role that I can assign to users isto create it, then go back and remove the creator from the role.
              >
              Incorrect - have a SYSDBA user create the role and do the grants. Why do you need a specific user to create the role? That isn't how it is normally done so why are you having to do it this way?
              • 4. Re: Create a role without becoming a member...
                631584
                Because my place of work isn't the typical....place of work .. . .
                • 5. Re: Create a role without becoming a member...
                  rp0428
                  >
                  Because my place of work isn't the typical....place of work .. . .
                  >
                  Then make sure you document for your manager that they are violating 'best practices'.

                  And you might want to ask them that if you do have a user create a role but then
                  >
                  go back and remove the creator from the role.
                  >
                  Now who do they expect to perform ADMIN for that role since the original creator can't do it anymore?

                  It makes no sense to have one ADMIN create a role but then be forbidden from administering it and requiring some other user to do so.
                  • 6. Re: Create a role without becoming a member...
                    Karan
                    create user test_user identified by t;

                    grant create role to test_user;

                    conn test_user/t

                    create role role1 ;

                    conn / as sysdba

                    drop user test_user;

                    grant role1 to any_user;

                    or may be with accounts.. So even this way your work can done

                    Regards
                    Karan