Re: How to assign multiple users to same schema?

From: Fernando Mendoza <mendozaf_at_attens.com>
Date: 2000/07/05
Message-ID: <39636A97.2B4E38B0@attens.com>#1/1

Below you will find the text for scripts that will do just what you need: (substitute <schema name>, <role> as needed). Create a few roles, grant privileges to the roles and then grant the roles to users...

1. Create roles
2. Create synonyms set termout on set echo off set feedback off set verify off set pause off prompt mk-syn.sql: Create synonyms for <schema name> Objects set termout off set recsep 0 set linesize 150 set pagesize 0 set sqlprompt '' column TPART format A55 column OPART format A50 SELECT 'CREATE PUBLIC SYNONYM ' || LTRIM(RTRIM(object_name)) || ' FOR <schema name>.' || LTRIM(RTRIM(object_name)) || ';' FROM USER_OBJECTS ORDER BY OBJECT_NAME

spool start mk_syn_0.sql
/

3. Grant execute on procedures, functions and packages set termout on
set echo off
set feedback off
set verify off
set pause off
prompt a-exec.sql: Add execute for <schema name> Objects set termout off
set recsep 0
set linesize 110
set pagesize 0
set sqlprompt ''
column TPART format A55
column OPART format A50
SELECT

	'GRANT EXECUTE ON <schema name>.' || LTRIM(RTRIM(object_name)) ||
	' <role 1>, <role 2>, <role 3>, <role 4>' || ';'
FROM
	USER_OBJECTS

WHERE
 OBJECT_TYPE IN ('PROCEDURE','FUNCTION','PACKAGE') ORDER BY OBJECT_NAME spool a_exec_0.sql
/
3. Grant SELECT to roles
set termout on
set echo off
set feedback off
set verify off
set pause off
prompt a-select.sql: Add select for <schema name> Objects set termout off
set recsep 0
set linesize 100
set pagesize 0
set sqlprompt ''
column TPART format A55
column OPART format A50
SELECT

	'GRANT SELECT ON <schema name>.' || LTRIM(RTRIM(object_name)) || 
	' TO <role 1>, <role 2>, <role 3>, <role 4>' || ';'
FROM
	USER_OBJECTS

WHERE
 OBJECT_TYPE IN ('TABLE', 'VIEW', 'SEQUENCE', 'SNAPSHOT') ORDER BY OBJECT_NAME spool a_select_0.sql
/
4. Grant DML to roles:

set termout on
set echo off
set feedback off
set verify off
set pause off
prompt a-edit.sql: Add delete,insert,update for <schema name> Objects set termout off
set recsep 0
set linesize 110
set pagesize 0
set sqlprompt ''
column TPART format A55
column OPART format A50
SELECT
        'GRANT DELETE,INSERT,UPDATE ON <schema name>.' || LTRIM(RTRIM(object_name)) || ' TO <role 1>, <role 2>, <role 3>' || ';' FROM
        USER_OBJECTS
WHERE OBJECT_TYPE IN ('TABLE', 'VIEW')
ORDER BY OBJECT_NAME spool a_edit_0.sql
/
5. Grant roles to users

Good luck,
Fernando M
AT&T ENS
Sybrand Bakker wrote:
>
> grant sufficient privileges to those users or to a role(preferred).
> Sufficient privilges would mean select, insert,update, delete.
> Then either create public synonyms or private synonyms for each separate
> user.
>
> Regards,
>
> Sybrand Bakker, Oracle DBA
>
> "Rayess" <rayess_at_mail.com> wrote in message
> news:8jti0a$ud9$1_at_nnrp1.deja.com...
> > When I create a user, Oracle creates a schema for that user. But what
> > if I want to have many users sharing the SAME schema (with tables,
> > etc...). How would I do that?
> >
> > Thanks.
> >
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
  Received on Wed Jul 05 2000 - 00:00:00 CDT