Re: Create user question

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Sat, 21 Aug 1999 15:18:59 +0200
Message-ID: <MPG.1228c5b0b271d048989683@news.demon.nl>

[This followup was posted to comp.databases.oracle.misc and a copy was sent to jj.den.boer_at_hccnet.nl, if that address didn't require changing.]

In article <37BFCB46.BFE0ACC5_at_hccnet.nl>, jj.den.boer_at_hccnet.nl says...
> Hi,
>
> I have created a new user in an oracle database. Unfortunately that user
>
> does not have access to the tables in the database which are created
> by another user. I guess it has something to do with de schema.
> How do you give access to a newly created user to access all the
> tables and views in the database.
> Is there a simple way to do that or do you have to grant alle tables
> to that users, that would be a bit clumsy if you have a couple of
> hundred tables and views.
>
> regards Johan den Boer
>
> email : jj.den.boer_at_hccnet.nl
>
>

Hi Johan,

First of all, users only rarely need to see ALL tables. An ordinary user shouldn't have access to the SYS.DBA_% views.
Most common method is to grant all necessary privileges to a role, and grant that role to the end-user. Warning: Roles are not enabled in PL/SQL. This is usually not a problem as the owner of the tables is also the owner of the stored procedures, and all stored procedures automatically run with definer's rights. (In Oracle 8i you can change that to invokers rights)

A short sql script to grant access to a role follows

define rol = '&1'
set feedback off heading off pagesize 0 spool temp.sql

select 'grant '||decode(object_type,'SEQUENCE', 'SELECT'
                                   , 'PROCEDURE','EXECUTE'
                                   ,'FUNCTION','EXECUTE'
                                   ,'PACKAGE','EXECUTE'
                                   ,'SELECT,INSERT,UPDATE,DELETE')
       ||' on '||object_name||' to &rol;'

from user_objects
where object_type in ('TABLE','VIEW','SEQUENCE','PROCEDURE','FUNCTION','PACKAGE') /
SPOOL OFF
START TEMP.SQL This should do it.

Hth,

Sybrand Bakker, Oracle DBA

Received on Sat Aug 21 1999 - 08:18:59 CDT