This discussion is archived
1 2 Previous Next 17 Replies Latest reply: Feb 15, 2013 7:04 AM by Justin Cave RSS

Grant Read only privilege

Meenakshy singh Newbie
Currently Being Moderated
Hello,

My requirement is to create user that has read only rights for all tables,views,synonyms,functions,procedures,packages,triggers,indexes,constraints,db links and users to slected schema.

Thanks
Meena
  • 1. Re: Grant Read only privilege
    asahide Expert
    Currently Being Moderated
    Hi,

    What do you mean "read only"?
    "grant select on table" -> OK, but "grant select on procedure" -> NG..

    Do you want to access dictionary data(like dba_tables)? or real table data(like employees)?

    Regards,
  • 2. Re: Grant Read only privilege
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    Pl post details of OS and database versions, along with what business/technical issue you are trying to solve.

    A possible solution is to grant these two privileges (use with care !)

    GRANT SELECT ANY TABLE TO <...>;
    GRANT SELECT ANY DICTIONARY TO <...>;

    http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_9013.htm#SQLRF01603

    HTH
    Srini
  • 3. Re: Grant Read only privilege
    vlethakula Expert
    Currently Being Moderated
    For functions.procedures,packages :

    grant execute on <objectname> to <username>
  • 4. Re: Grant Read only privilege
    moreajays Pro
    Currently Being Moderated
    Hi Meena,

    You have option to either grant privilege on each object or use "ANY" privilege
    Granting privilege on each object will be tedious task.
    Using "ANY" privilege has one drawback that any user having these grants will have access on all schema's of DB & not to particular schema.
    Also for code's like function/procedure/package there is no such read only method its directly execute privilege
    select 'grant '|| privilege || ' to <schema> ;' from dba_sys_privs where privilege like '%SELECT%';
    select 'grant '|| privilege || ' to <schema> ;' from dba_sys_privs where privilege like '%EXECUTE%';
    Thanks,
    Ajay More
    http://www.moreajays.com
  • 5. Re: Grant Read only privilege
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    Check the document :
    http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9013.htm

    you can use grant select, grant execute ......
  • 6. Re: Grant Read only privilege
    Meenakshy singh Newbie
    Currently Being Moderated
    Hi,

    I have to create same user-id for 2 different databases.My database version are:-

    1.On Windows 2000 server - database version - Oracle9i Enterprise Edition Release 9.2.0.8.0
    2.On AIX machine - database version - Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit.

    The new user-id will be used by application team for troubleshooting purpose.

    Read only means that user id shouldn't be allowed to execute update,delete and insert SQL statements.
    The user id should be able to query(All tables,views,synonyms,function,procedure,packages,triggers,indexes,constraints,DB links and users) to selected schemas.

    I want to access real table data(like employees)

    Thanks,
    Meena
  • 7. Re: Grant Read only privilege
    Girish Sharma Guru
    Currently Being Moderated
    You need not to do anything, because in Oracle any new user can not access any other user's object(s) until and unless that new user do not have granted either select or update privilege on other user's schema objects. Suppose I said :

    create user newuser identified by pw;

    it means till now this newuser is dont have any privilege on other user schema. Ok, if I said :

    grant select on scott.emp to newuser;
    grant select on hr.table1 to newuser;

    then only newuser will be able to select (even select, not any DML) scott.emp and hr.table1 only. If I said :

    grant update on user2.table3 to newuser;

    it means now newuser will be able to select (only select no DML) on scott.emp, hr.table1 and select and update on user2.table3 only.

    Regards
    Girish Sharma
  • 8. Re: Grant Read only privilege
    Meenakshy singh Newbie
    Currently Being Moderated
    Hi,

    I have 8 schemas and each schemas is having 50-60 tables ,views ,synonyms, functions, procedures, packages, triggers, indexes, constraint, DB links & users.
    so for each tables ,I need to grant this command

    select 'GRANT SELECT ON '||table_name||' TO <read_only_user>;' from user_tables;

    Likewise for-
    USER_TABLES
    USER_VIEWS
    USER_SYNONYMS

    Any other way to grant read only access to directly schemas inspite of giving to individually tables.
    And how to grant for FUNCTION, PACKAGE and PROCEDURE,DB links?


    Thanks,
    Meena
  • 9. Re: Grant Read only privilege
    vlethakula Expert
    Currently Being Moderated
    No you cannot give access directly to schema. We need to give select,insert,update,delet/execute privileges owned by the corresponding user.

    Probably you can loop all those commands in PL SQL procedure and run, something like below

    DECLARE
    --One off script to execute grants to apps_query
    v_error VARCHAR2(2000);
    v_error1 VARCHAR2(2000);
    BEGIN
    FOR p_rec IN (SELECT *
    FROM dba_objects
    WHERE owner = 'WEB'
    AND object_type IN ('SYNONYM', 'VIEW','TABLE')
    AND object_name NOT LIKE '%BIN%')
    LOOP
    BEGIN
    EXECUTE IMMEDIATE 'grant select on ' || p_rec.owner||'.'||p_rec.object_name ||
    ' to demo';
    EXCEPTION
    WHEN OTHERS THEN
    v_error := substr(SQLERRM, 1, 2000);
    dbms_output.put_line(v_error);
    END;
    END LOOP;
    FOR q_rec IN (SELECT *
    FROM dba_objects
    WHERE owner = 'WEB'
    AND object_type IN ('PACKAGE','PACKAGE BODY','PROCEDURE','FUNCTION')
    AND object_name NOT LIKE '%BIN%')
    LOOP
    BEGIN
    EXECUTE IMMEDIATE 'grant execute on ' || q_rec.owner||'.'||q_rec.object_name ||
    ' to demo';
    EXCEPTION
    WHEN OTHERS THEN
    v_error1 := substr(SQLERRM, 1, 2000);
    dbms_output.put_line(v_error1);
    END;
    END LOOP;
    END;
    /
  • 10. Re: Grant Read only privilege
    Richard Harrison . Expert
    Currently Being Moderated
    Hi Meena,
    I think Srini already gave you the answer:

    'select any table' gives you permission to view any data in any other schema
    'select any dictionary' gives you access to be able to view source code for any stored plsql as well as any other object.

    Be aware though that even a select right is dangerous (apart from giving free access to all the companies data) it also enables the use of

    select x,y,z from table for update;

    even though you can never update the rows (you wont have rights) - you can still lock them......

    Regards,
    Harry
  • 11. Re: Grant Read only privilege
    rp0428 Guru
    Currently Being Moderated
    >
    I have 8 schemas and each schemas is having 50-60 tables ,views ,synonyms, functions, procedures, packages, triggers, indexes, constraint, DB links & users.
    so for each tables ,I need to grant this command

    select 'GRANT SELECT ON '||table_name||' TO <read_only_user>;' from user_tables;

    Likewise for-
    USER_TABLES
    USER_VIEWS
    USER_SYNONYMS

    Any other way to grant read only access to directly schemas inspite of giving to individually tables.
    And how to grant for FUNCTION, PACKAGE and PROCEDURE,DB links?
    >
    NO - you DO NOT need to do that and you SHOULD NOT do that.

    A 'read only' user does NOT need to access functions, packages, procedures or sequences. At most they only need access to the DDL and source code for those objects.

    Even for 'read only' users SECURITY is the first priority. Since these users will NOT need to execute any code you should:

    1. set up a role hierarchy

    2. grant the necessary privileges to the appropriate role

    3. grant the appropriate roles to the appropriate users.

    That gives you the maximum flexibility while still maintaining control over 'who can do what'.

    The following is just an example and uses explicit names for the users and roles to make it clear what object (user or role) is mentioned. Assume you have two schemas, scott and hr, that you want to provide read only access to.
    -- create a role for read only access to EVERYTHING we define later
    create role read_only_everything_role;
    
    -- create a role for read only access to scott
    create role read_only_scott_role;
    
    -- create a role for read only access to hr
    create role read_only_hr_role;
    
    -- provide the grants that a read only user might need from scott
    grant select on scott.emp to read_only_scott_role
    grant select any dictionary to read_only_scott_role
    
    grant select on scott.dept to read_only_scott_role
    grant select any dictionary to read_only_scott_role
    
    -- provide the grants that a read only user might need from hr
    grant select on hr.employees to read_only_hr_role;
    grant select any dictionary to read_only_test_role
    
    grant select on hr.departments to read_only_hr_role
    grant select any dictionary to read_only_hr_role
    
    -- provide the grants to the EVERYTHING role
    grant read_only_scott_role to read_only_everything_role;
    grant read_only_hr_role to read_only_everything_role;
    
    -- create the read only users
    --  create a read only user for scott only
    create user read_only_scott_user identified by read_only_scott_user
    
    --  create a read only user for hr only
    create user read_only_hr_user identified by read_only_hr_user
    
    --  create a read only user for EVERYTHING
    create user read_only_everything_user identified by read_only_everything_user
    
    -- grant connect and resource to the users so they can connect
    grant connect, resource to read_only_scott_user;
    
    grant connect, resource to read_only_hr_user;
    
    grant connect, resource to read_only_everything_user;
    
    -- grant the read only roles to the appropriate user
    grant read_only_scott_role to read_only_scott_user;
    
    grant read_only_hr_role to read_only_hr_user;
    
    grant read_only_everything_role to read_only_everything_user;
    A ROLE hierarchy like the above makes it MUCH easier to control 'who can do what'. You can easily grant/revoke privileges to EXACTLY the users that you want by issuing a grant/revoke to the appropriate role. This helps ensure that you don't accidentally grant/revoke a privlege to the wrong user.

    That approach is also much more flexible since you can then grant one, or more, of those read only roles to your existing users. And if one of those users needs privileges to another schema in the future you just add the correct role to that read_only user.

    NOTE: I created new 'read_only_xx_user' roles above for example only. You should NEVER create generic users that require two users to use the same password. That would be a security violation.
  • 12. Re: Grant Read only privilege
    Meenakshy singh Newbie
    Currently Being Moderated
    Hi Richard,

    Thanks for reply.

    But my problem is bit diiferent.

    In database I have total of + more than 20  schemas out of which  only 8 schemas+ read access is required by the user
    How do give only 8 schemas? I don't wish to give individually to each table in each schema neither i want to give full database read access to user.

    'select any table' gives you permission to view any data in any other schema +(which i don't want to do .i need to specifically giveonly to defined 8 schemas)+
    'select any dictionary' gives you access to be able to view source code for any stored plsql as well as any other object.


    Thanks
    Meena.

    Edited by: Meenakshy singh on Jan 25, 2013 11:19 AM

    Edited by: Meenakshy singh on Jan 25, 2013 11:20 AM
  • 13. Re: Grant Read only privilege
    Meenakshy singh Newbie
    Currently Being Moderated
    Hi Rp,

    Got your point and totally agree with you. But give access to each table is not a feasible option for me as i have hundreds of table ,which will take me long time to do .
    I have around 8 schema and each having 50 + tables.

    Need some other way or possible write a script to achieve this.

    Thanks
    Meena
  • 14. Re: Grant Read only privilege
    Justin Cave Oracle ACE
    Currently Being Moderated
    It's easy enough to write a bit of PL/SQL to automate the process
    BEGIN
      FOR t IN (SELECT * FROM dba_tables WHERE owner = 'A')
      LOOP
        EXECUTE IMMEDIATE 'GRANT SELECT ON a.' || t.table_name || ' TO read_only_a';
      END LOOP;
    END;
    will grant SELECT access on every table in A's schema to the READ_ONLY_A role.

    Justin
1 2 Previous Next

Legend

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