Re: oracle grants/roles v sql server

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@xxxxxxxxxxxxxxxx
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
.

Relevant Pages

  • Re: same application on multiple schemas
    ... As for the "comparison" with Sql Server, it was not intended to ask ... why Oracle does "not" support sql server behaviour:) I know oracle (I ... For every customer that will use our application, ... will use schema "SchemaA" ...
    (comp.databases.oracle.server)
  • Re: Synchronization Wizard weirdness
    ... Using the Oracle Enterprise Manager and Oracle Change Manager packages, ... :> I have a Test Schema in the same database as the Main schema and need to ... Wizard between the Main schema and Test schema -- obviously returned the full ... :> All seems to go well, until the script actually runs. ...
    (comp.databases.oracle.tools)
  • Re: same application on multiple schemas
    ... As for the "comparison" with Sql Server, it was not intended to ask ... why Oracle does "not" support sql server behaviour:) I know oracle (I ... For every customer that will use our application, ... will use schema "SchemaA" ...
    (comp.databases.oracle.server)
  • Re: same application on multiple schemas
    ... As for the "comparison" with Sql Server, it was not intended to ask ... why Oracle does "not" support sql server behaviour:) I know oracle (I ... For every customer that will use our application, ... will use schema "SchemaA" ...
    (comp.databases.oracle.server)
  • Re: Synchronization Wizard weirdness
    ... I'm an old-time Informix DBA who has now switched over to Oracle. ... I have a Test Schema in the same database as the Main schema and need to ... only has warnings, but the Script fails. ... Learn about materialized views, refresh on commit ...
    (comp.databases.oracle.tools)
Loading