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!

How create only read permissions user Oracle 8.05

Discussion in 'Server Administration and Options' started by rcarabaj, Aug 4, 2010.

  1. rcarabaj

    rcarabaj Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Hello:

    Please, I apologize because I am new to database administration tasks and my question could be seen as very simple .
    I have an Oracle 8.05 working database, and have need to create a general-query-user to let all programmers run their report generators. There is an scheme in the database and programmers should read all data tables, and code from triggers, procedures, functions and packages, but it is very important that they can only read such objects. There are already roles that can be used in this database, but I have fear that my lack of expertise can open a security hole. Perhaps it´s better to define a complete new role for this type of access. Please, I would appreciate you can tell me the exactly steps (scripts ) I should acomplish to create an all-objects-only-read user within a given schema.
    Thanks very much for your time. My best regards.
    Roberto
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    All objects in a specific schema or all objects, period? I'm hoping it's the former and not the latter as you would open security holes by allowing even read-only access to many data dictionary and fixed views and tables.

    Basically the task is to generate a list of grants for the tables/views/procedures/functions in the given schema. To make this easier on you to grant you should create a new role for this access. Let's say you have a schema named BORTZ and all developers need to be able to read from those tables. I would create a role, BORTZ_READ_ONLY, then grant select on every table and view in the BORTZ schema to BORTZ_READ_ONLY; I would also grant execute on the procedures and functions to that same role. I would then grant that new role to any user needing such access. As an example:

    Code (SQL):
    SQL> CREATE ROLE bortz_read_only;
     
    ROLE created.
     
    SQL> GRANT SELECT ON bortz.rubango TO bortz_read_only;
     
    GRANT succeeded.
     
    [Continue the grants FOR ALL other TABLES IN the schema.]
     
    SQL> GRANT CREATE SESSION TO bortz_read_only;
     
    GRANT succeeded.
     
    SQL> GRANT bortz_read_only TO yazz IDENTIFIED BY werpo;
     
    GRANT succeeded.
     
    SQL> CONNECT yazz/werpo
     
    Connected.
     
    SQL> SELECT * FROM bortz.rubango;
     
    [DATA returned ]
     
     
    You may want to set up private or public synonyms for the schema tables to allow users to select froim the tables by name and not have to use the owner.name syntax as shown in the example; that decision is yours to make.
     
    rcarabaj likes this.
  3. rcarabaj

    rcarabaj Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    David:

    Thanks, thanks very much for your fast answer. It is extremely useful for me !!!.