This discussion is archived
6 Replies Latest reply: Mar 26, 2013 10:40 AM by jgarry RSS

Create read only user

11g.DBA Newbie
Currently Being Moderated
Hi,

I want to create a new user for schema 'test'
Want the user just to read any table,insert into table for schema test.
USER:hms1
PASSWORD:hms1
SCHEMA:test
TABLESPACE:users
  • 1. Re: Create read only user
    TSharma-Oracle Guru
    Currently Being Moderated
    Replace the name with your username. This is just read only.

    http://stackoverflow.com/questions/7502438/oracle-how-to-create-a-readonly-user

    If you want to give insert , just replace 'select on' with 'insert on'
  • 2. Re: Create read only user
    sb92075 Guru
    Currently Being Moderated
    11g.DBA wrote:
    Hi,

    I want to create a new user for schema 'test'
    Want the user just to read any table,insert into table for schema test.
    Title is below; but INSERT is above. READ ONLY means no INSERT. Please make decide single criteria!
    Create read only user
  • 3. Re: Create read only user
    rp0428 Guru
    Currently Being Moderated
    >
    I want to create a new user for schema 'test'
    >
    In Oracle a user IS a schema and will have whatever privileges you grant to it.

    So grant the privileges on any objects owned by 'test' that you want. But you will need to grant them one by one; there is no way to grant privileges to multiple objects at once.

    You can grant the privileges directly to the user or, better:

    1. create a role named TEST_READ_ONLY
    2. grant the privileges to the role
    3. grant the role to your new user
  • 4. Re: Create read only user
    Kh$n Journeyer
    Currently Being Moderated
    schema and user are same things. schema is database object and we know them as user.

    step 1: create user
    create user test identified by test account unlock;

    step2: grant required role;

    grant connect,resource to test;

    Step3: grant permission to test user to view data of any table in any schema.

    grant select any table to test;

    Note: as per oracle documentation
    System privileges that provide access to objects in other schemas do not give other users access to objects in the SYS schema. For example, the SELECT ANY TABLE privilege allows users to access views and tables in other schemas, but does not enable them to select dictionary objects (base tables of dynamic performance views, regular views, packages, and synonyms). You can, however, grant these users explicit object privileges to access objects in the SYS schema.


    Test user is created which can create table and insert data into its own table schema select any schema table data,

    Edited by: Asad99 on Mar 25, 2013 10:31 PM
  • 5. Re: Create read only user
    rp0428 Guru
    Currently Being Moderated
    >
    schema and user are same things. schema is database object and we know them as user.
    >
    That's what I just said isn't it?

    Why are you directing your comments at me? OP is the one with the problem. You need to reply to OP.
  • 6. Re: Create read only user
    jgarry Guru
    Currently Being Moderated
    Asad99 wrote:
    schema and user are same things. schema is database object and we know them as user.

    step 1: create user
    create user test identified by test account unlock;

    step2: grant required role;

    grant connect,resource to test;
    Please read the resource role description at http://psoug.org/reference/roles.html especially the part in red.
    >

Legend

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