Re: oracle grants/roles v sql server

From: DA Morgan <damorgan_at_psoug.org>
Date: Mon, 24 Jul 2006 20:50:14 -0700
Message-ID: <1153756241.381997@bubbleator.drizzle.com>

Jimbo1 wrote:
> 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

Before taking James' advice be aware that the word "database" means something very different in Oracle than it does in SQL Server. In Oracle granting this privilege is a monstrous security breach.

You will need to grant SELECT on each table individually. And if you have security issues ... at the column level.

This is documented in Morgan's Library at www.psoug.org if you click on the Object Privileges page.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Mon Jul 24 2006 - 22:50:14 CDT