This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Apr 18, 2013 6:08 PM by sb92075 RSS

Roles and Security

999406 Newbie
Currently Being Moderated
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 Journeyer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    Start with this
    http://osamamustafa.blogspot.com/2013/04/oracle-security-where-to-start-what-to.html
  • 13. Re: Roles and Security
    EdStevens Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    These roles are in relation to the database
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points