Re: oracle grants/roles v sql server

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 - 08:30:37 CDT