1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Oracle Security Roles

Discussion in 'Server Administration and Options' started by tyro, Oct 8, 2008.

  1. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    Oracle Security Roles

    In order to provide a security mechanism oracle provides the concept of roles.

    These are predefined permission templates which allow users to access various functions and tables within a database.

    Roles allow levels of access for users. Predefined roles can be created for various user needs such as a manager role, an accounts role or a production role. Each role will allow access to various parts of the database. For instance, user assigned to an accounts role would not be able to see tables and fields assigned to users of the manager or production roles. However, in some cases, depending on the permissions access to various parts of the database may overlap.

    Oracle has a number of predefined roles. The following are pre-defined examples (some are pre 9i):

    CONNECT = Allows a user to connect to the database.

    RESOURCE
    = Allow update, delete on tables.

    DBA = A manager of the database, the most dangerous role!
    Below these roles there are various functions, such as the ability to view and update various fields in tables.
     
  2. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    I will provide few example of oracle sql statements which can be used to create roles for 3 different users in our database:

    First we will create three different roles, to allow access to Project, Employee and Activity tables. Initially these will not have any privileges associated with them, so all users could see the tables. Due to the nature of the database design, it may be necessary to provide privileges to more than one table for each user.

    The Steps Are:

    1. Create the role.
    2. Add privileges to the role.
    3. Grant the roles to various users.

    Let us suppose we have three different users, tyro, Julian and Bets.

    We will rely on the GLOBAL authentication in Oracle, rather than the EXTERNAL operating system permissions.

    contd in next post...

    The steps are created by the DBA as follows:

    1. Create the roles in SQL PLUS, the syntax of which is:

    Code (Text):

    CREATE ROLE rolename
    [NOT IDENTIFIED | IDENTIFIED
    {BY password | EXTERNALLY |GLOBALLY | USING package_name}]
     
    The commands are

    Code (Text):

    CREATE ROLE PROJECT_ROLE IDENTIFIED GLOBALLY;
    CREATE ROLE EMPLOYEE_ROLE IDENTIFIED GLOBALLY;
    CREATE ROLE ACTIVITY_ROLE IDENTIFIED GLOBALLY;
     
    2. Add privileges to the roles (N.B. Privileges can also be assigned to individual users when they are created).

    We will grant SELECT, UPDATE and CREATE SESSION (log on the database) to the project role, SELECT, UPDATE to the employee role, and SELECT, CREATE DATABASE LINK i.e. connect to another database, to the activity role.

    The reason these roles have been chosen is so that users in the project role can view and update aspects of the project information, while users in the employee role will be able to view, update and delete employee information. In this case we have an activity role, which sometimes may have interfaces to other databases, hence this role has provided a privilege that allows a user to create a link to the other database.

    Code (Text):

    GRANT SELECT, UPDATE, CREATE SESSION TO PROJECT_ROLE;
    GRANT SELECT, UPDATE, DELETE TO EMPLOYEE_ROLE;
    GRANT SELECT, CREATE DATABASE LINK TO ACTIVITY_ROLE;
     
    3. Grant the roles to the users.

    Code (Text):

    GRANT PROJECT_ROLE TO ERIC;
    GRANT EMPLOYEE_ROLE TO JANE;
    GRANT ACTIVITY_ROLE TO SANDRA;
     
    The users roles now allows them to perform the various functions on the database.