Although the script does the job, what happens when a new table is created
and added to the schema? The script will need to be re-run each time a new
table is created. I can't help but feel that this is a design/security
defect in Oracle.
"Jimbo1" <jamestheboarder_at_googlemail.com> wrote in message
news:1153747837.565475.313930_at_h48g2000cwc.googlegroups.com...
> archon wrote:
>> Hi. I want to grant a user read (select) access to all objects in a
>> schema
>> in Oracle. In sql server, I would just give the user a db_datareader
>> role
>> for that schema. How is that done in Oracle? I don't want to give
>> access
>> on an object-by-object basis one at a time, in case new objects are
>> created
>> afterwards.
>
> You could grant the user the SELECT ANY TABLE privilege. That would
> allow him/her to select from any table in any schema in the database.
>
> This does raise a couple of security issues though; do you want your
> user to have that much power?
>
> If you just want the user concerned to be able to select from the
> tables in one particular schema, you'll have to grant him/her the
> select on each of the tables concerned.
>
> You could write a script to automate this though, and run this script
> in the schema that owns the tables (THAT IS IMPORTANT!), i.e.
>
> DECLARE
> kc_user_to_receive_privs CONSTANT VARCHAR2(30) :=
> 'usertoreceiveprivs';
> BEGIN
> FOR rec IN ( SELECT table_name FROM user_tables )
> LOOP
> EXECUTE IMMEDIATE 'GRANT SELECT ON '||rec.table_name||' TO
> '||kc_user_to_receive_privs;
> END LOOP;
> END;
> /
>
> I've not tested this script, so there might be some syntax errors in
> it. It will essentially do what you need it to do though.
>
> Kind Regards.
>
> James
>
Received on Mon Jul 24 2006 - 09:55:05 CDT
