1 2 Previous Next 15 Replies Latest reply: Apr 18, 2013 8:08 PM by sb92075 RSS

    Roles and Security

    999406
      I have setup a 11g Oracle database.

      Can I please have some help to create some user accounts (3 levels, eg. Administrator, Power User, and Guest style users) as well as setting up appropriate levels of security implemented via ROLES and PRIVILEGES for Roles.

      Thanks in advance
        • 1. Re: Roles and Security
          marksmithusa
          Well nothing else has yet failed (or been attempted), Read the Fine Manual:

          http://docs.oracle.com/cd/E11882_01/server.112/e10575/tdpsg_intro.htm
          • 2. Re: Roles and Security
            sb92075
            996403 wrote:
            I have setup a 11g Oracle database.
            We are impressed.

            >
            Can I please have some help to create some user accounts (3 levels, eg. Administrator, Power User, and Guest style users) as well as setting up appropriate levels of security implemented via ROLES and PRIVILEGES for Roles.
            Is application 3-tier like below?

            EndUser<=>browser<=>WebServer<=>ApplicationServer<=>DatabaseServer


            How do I ask a question on the forums?
            SQL and PL/SQL FAQ
            • 3. Re: Roles and Security
              Osama_Mustafa
              Can I please have some help to create some user accounts (3 levels, eg. Administrator, Power User, and Guest style users) as well as setting up appropriate levels of security implemented via ROLES and PRIVILEGES for Roles.

              Thanks in advance
              your question depend on what you need from this user to do , What you want them to do ? what you want to see ? what you want to check , select , insert ?
              http://docs.oracle.com/cd/B19306_01/network.102/b14266/admusers.htm
              • 4. Re: Roles and Security
                999406
                I see that each user has many available roles. What roles should I add to a user if I wish for the user to be the following types of user:

                Administrator, Power User, or Guest style user?

                Are there default roles to add to a user to be each of the following types?
                • 5. Re: Roles and Security
                  Justin_Mungal
                  996403 wrote:
                  I see that each user has many available roles. What roles should I add to a user if I wish for the user to be the following types of user:

                  Administrator, Power User, or Guest style user?

                  Are there default roles to add to a user to be each of the following types?
                  It sounds like you're relating Windows OS security groups to Oracle. This is not a good idea, and you're going to be confused until you take the time to read the documentation. As you know, there are many predefined roles. You need to study all of them, and determine which users need to be in which role. It's more complex than just Administrator/Power User/Guest. For your specific request, study +4 Configuring Privilege and Role Authorization+ of Oracle® Database Security Guide.
                  • 6. Re: Roles and Security
                    999406
                    Thanks for the resource link.

                    I have read the document and I understand how Roles work.

                    My question is this:
                    Are there a default list of roles that I can use for the following types of users:
                    Administrator
                    Power User
                    Guest

                    I am still not sue which ones to add for the above types of users.

                    Edited by: 996403 on Apr 18, 2013 3:16 AM
                    • 7. Re: Roles and Security
                      John Stegeman
                      Until you can tell us what you want the "Administrator" "Power User" and "Guest" roles to be able to do, the answer is "NO"

                      This is an Oracle Database, not Windows.
                      • 8. Re: Roles and Security
                        999406
                        I am wanting the Administrator to have control over everything, the Power User to be a User who also has the ability to create tables, triiggers etc, and the Guest to just be able to view data in the database without changing anything.

                        Can you correct me if I am wrong with the following suitable roles for the users:

                        Administrator
                        - All roles
                        Power User
                        - Connect
                        - Resource
                        Guest
                        - Connect
                        • 9. Re: Roles and Security
                          John Stegeman
                          No, that's not correct.

                          Administrator may be similar to DBA, but have a look at what a DBA can do before you go granting that.

                          Connect, Resource would allow one to connect and create objects in their own schema plus would have unlimited quota on their default tablespace.

                          connect would allow one to connect. They wouldn't be able to see any objects except those which were granted directly to them or to PUBLIC. If you want them to be able to see specific data, you have to grant SELECT on the objects they should have access to.
                          • 10. Re: Roles and Security
                            Justin_Mungal
                            996403 wrote:
                            I am wanting the Administrator to have control over everything, the Power User to be a User who also has the ability to create tables, triiggers etc, and the Guest to just be able to view data in the database without changing anything.

                            Can you correct me if I am wrong with the following suitable roles for the users:

                            Administrator
                            - All roles
                            Power User
                            - Connect
                            - Resource
                            Guest
                            - Connect
                            You have to get out of this Administrator/Power User/Guest Windows security group paradigm. Windows security groups cannot be directly correlated to Oracle security groups, and that is why you are having so much trouble doing so. I recommend that you:

                            -stop comparing Oracle to Windows
                            -learn what security rights your database users need
                            -fully understand the predefined roles, and then assign users to those roles only if they require every right that those roles grant
                            -create your own application roles for any users that have requirements that do not align exactly with the predefined groups

                            We are only encouraging you to do things in a manner that follows best practices, and doing so will keep your headaches to a minimum later on down the road.
                            • 11. Re: Roles and Security
                              999406
                              OK, thanks.

                              I have been asked to demonstrate 3 levels of users (Roles) – for eg. Administrator, Power User, and Guest style users. This is not for a commercial situation, I just need to demonstrate how to do this action.
                              I understand how to do it, I am just not sure on the specific roles to add as examples.
                              • 12. Re: Roles and Security
                                Osama_Mustafa
                                Start with this
                                http://osamamustafa.blogspot.com/2013/04/oracle-security-where-to-start-what-to.html
                                • 13. Re: Roles and Security
                                  EdStevens
                                  996403 wrote:
                                  OK, thanks.

                                  I have been asked to demonstrate 3 levels of users (Roles) – for eg. Administrator, Power User, and Guest style users. This is not for a commercial situation, I just need to demonstrate how to do this action.
                                  I understand how to do it, I am just not sure on the specific roles to add as examples.
                                  Well, if it is really just a demo, I'd create the three roles asked for "ADMIN", "POWERUSER", "GUEST".

                                  Are these roles in relation to the database, or an application?

                                  Grant some roles and or privilges to each, according to what you want those with the given role to be able to do. No one but you knows what that might be. The fundamental principal of role/privilege security is that you grant the least privileges necessary for the person to do their job.
                                  • 14. Re: Roles and Security
                                    999406
                                    These roles are in relation to the database
                                    1 2 Previous Next