Oracle 10g: Anlegen eines neuen Schemas mit Leserechten auf ein altes Schema

[Rumak18]

Hallo,

ich habe ein neues Schema (USER) angelegt. Dieses Schema soll nun Leserechte auf alle Tabellen eines vorhandenen Schemas erhalten. Wie stelle ich das an?
GRANT SELECT ON "ALTES_SCHEMA" TO "NEUES_SCHEMA" ;
funktioniert nicht!

[defo]

ich würde es eher so machen:
grant select on altesschema.tabellexy to neuesschema;
für alle notwendigen Tabellen

Das ganze natürlich als User, der auch die Rechte hat, also AltesSchema, dba oder so.

[Rumak18]

Hallo,
danke für die Info, aber welche Tabelle soll ich denn angeben?
Ich habe nämlich die Info, dass der neue User(Schema) auf alle Tabellen des alten Schemas(User) zugreifen soll.

[dimitri]

Hi,

wir haben dafür dieses Skript:

SET LINESIZE 1000
SET PAGESIZE 0
SET SERVEROUTPUT ON
SET VERIFY OFF
SET SCAN ON
SET SERVEROUTPUT ON SIZE 1000000
WHENEVER OSERROR EXIT FAILURE
SPOOL create_grants.log
set echo on
DECLARE 
 CURSOR c_schematables IS
   SELECT DISTINCT
     object_name, data_object_id,
     object_type
       || DECODE (object_type,
                  'TABLE', DECODE (TEMPORARY,
                                   'N', NVL2 (data_object_id, NULL, '_EXT'),
                                   ''
                                  ),
                  ''
                 ) AS object_type
   FROM 
     user_objects
   WHERE 
     object_name NOT LIKE 'BIN$%' 
     AND object_type IN
          ('TABLE',
           'VIEW',
           'SEQUENCE',
           'PACKAGE',
           'FUNCTION',
           'PROCEDURE',
           'TYPE',
           'TABLE PARTITION'
          );
 l_grantee VARCHAR2(32):=UPPER('&1');
 l_count NUMBER:=0;
BEGIN
 
 FOR l_tables IN c_schematables   
 LOOP   
   CASE l_tables.object_type
     WHEN 'TABLE_EXT' THEN
       DBMS_OUTPUT.PUT_LINE('GRANT SELECT ON '||l_tables.object_name||' TO '||l_grantee);
       EXECUTE IMMEDIATE 'GRANT SELECT ON '||l_tables.object_name||' TO '||l_grantee; 
     WHEN 'TABLE' THEN
       DBMS_OUTPUT.PUT_LINE('GRANT SELECT,INSERT,UPDATE,DELETE ON '||l_tables.object_name||' TO '||l_grantee);
       EXECUTE IMMEDIATE 'GRANT SELECT,INSERT,UPDATE,DELETE ON '||l_tables.object_name||' TO '||l_grantee; 
     WHEN 'TABLE PARTITION' THEN
       DBMS_OUTPUT.PUT_LINE('GRANT SELECT,INSERT,UPDATE,DELETE ON '||l_tables.object_name||' TO '||l_grantee);
       EXECUTE IMMEDIATE 'GRANT SELECT,INSERT,UPDATE,DELETE ON '||l_tables.object_name||' TO '||l_grantee; 
     WHEN 'VIEW' THEN
       DBMS_OUTPUT.PUT_LINE('GRANT SELECT,INSERT,UPDATE,DELETE ON '||l_tables.object_name||' TO '||l_grantee);
       EXECUTE IMMEDIATE 'GRANT SELECT,INSERT,UPDATE,DELETE ON '||l_tables.object_name||' TO '||l_grantee; 
     WHEN 'SEQUENCE' THEN
       DBMS_OUTPUT.PUT_LINE('GRANT SELECT ON '||l_tables.object_name||' TO '||l_grantee);
       EXECUTE IMMEDIATE 'GRANT SELECT ON '||l_tables.object_name||' TO '||l_grantee;
     WHEN 'PACKAGE' THEN
       DBMS_OUTPUT.PUT_LINE('GRANT EXECUTE ON '||l_tables.object_name||' TO '||l_grantee);
       EXECUTE IMMEDIATE 'GRANT EXECUTE ON '||l_tables.object_name||' TO '||l_grantee; 
     WHEN 'FUNCTION' THEN
       DBMS_OUTPUT.PUT_LINE('GRANT EXECUTE ON '||l_tables.object_name||' TO '||l_grantee);
       EXECUTE IMMEDIATE 'GRANT EXECUTE ON '||l_tables.object_name||' TO '||l_grantee; 
     WHEN 'PROCEDURE' THEN
       DBMS_OUTPUT.PUT_LINE('GRANT EXECUTE ON '||l_tables.object_name||' TO '||l_grantee);
       EXECUTE IMMEDIATE 'GRANT EXECUTE ON '||l_tables.object_name||' TO '||l_grantee; 
     WHEN 'TYPE' THEN
       DBMS_OUTPUT.PUT_LINE('GRANT EXECUTE ON '||l_tables.object_name||' TO '||l_grantee);
       EXECUTE IMMEDIATE 'GRANT EXECUTE ON '||l_tables.object_name||' TO '||l_grantee; 
     ELSE
       DBMS_OUTPUT.PUT_LINE('Für '||l_tables.object_name||' kann kein Grant vergeben werden..');
   END CASE;
   l_count:=l_count+1;
 END LOOP;
 DBMS_OUTPUT.PUT_LINE(l_count||' Grants angelegt.');
 EXCEPTION
  WHEN OTHERS THEN
       DBMS_OUTPUT.PUT_LINE('Fehler in create_grants.sql: '||SQLERRM);
       DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_CALL_STACK);
       RAISE;
END;
/
SPOOL OFF
EXIT

Einfach im Schema das die Rechte erteilen soll anmelden und den Zieluser als Parameter angeben:
sqlplus user/password@tnsname @skriptname.sql schema_das_rechte_bekommt.

Danach noch im Zielschema die Synonyme anlegen:

SET LINESIZE 1000
SET PAGESIZE 0
SET SERVEROUTPUT ON
SET VERIFY OFF
SET SCAN ON
SET SERVEROUTPUT ON SIZE 1000000
WHENEVER OSERROR EXIT FAILURE
WHENEVER SQLERROR EXIT FAILURE
SPOOL create_synonyms.log
DECLARE
 l_owner VARCHAR2(32):=UPPER('&1');
 CURSOR cTables IS 
        SELECT 
          object_name 
        FROM 
          all_objects 
        WHERE 
          owner=l_owner
          AND object_name NOT LIKE 'BIN$%' 
          AND object_type IN('TABLE','SEQUENCE','VIEW', 'PACKAGE', 'FUNCTION', 'PROCEDURE', 'TYPE');
 l_count NUMBER:=0;
BEGIN
 FOR tabs IN cTables LOOP
   DBMS_OUTPUT.PUT_LINE('CREATE OR REPLACE SYNONYM '||tabs.object_name||' FOR '||l_owner||'.'||tabs.object_name);
   EXECUTE IMMEDIATE 'CREATE OR REPLACE SYNONYM '||tabs.object_name||' FOR '||l_owner||'.'||tabs.object_name;
   l_count:=l_count+1; 
 END LOOP;
 DBMS_OUTPUT.PUT_LINE('Es wurden '||l_count||' Synonyme angelegt.');
 EXCEPTION
  WHEN OTHERS THEN
       DBMS_OUTPUT.PUT_LINE('Fehler in create_synonyms.sql: '||SQLERRM);
       DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_CALL_STACK);
       RAISE;
END;
/
EXIT

Gleiche Funktionsweise wie oben, nur das man sich jetzt am Zielschema anmeldet und als Parameter das Schema angiebt, welches die Tabellen beinhaltet.

Dim

PS: Wenn nur um Leserechte vergeben werden sollen, musst Du das Skript entsprechend anpassen und eben kein UPDATE,INSERT und DELETE vergeben.

[defo]

connect altesSchema@db

select * from user_tables;

oder direkt die Grant Befehle listen lassen:
[highlight=sql]
select 'grant select on altesSchema.' || ut.TABLE_NAME ||
' to NeuesSchema;' as myCommanList
from user_tables ut
where [ Ausschlusskriterien ]
[/highlight]

Das gleiche ggF. für die Views, sofern vorhanden, gewünscht, angewiesen...

P.S.: Dimitris Antwort habe ich übersehen.
Aber nun hast Du ja die Macht. Achte darauf, was das Script ausspuckt und sorge dafür, dass nicht mehr Berechtigungen vergeben werden, als gewollt und vertretbar.
P.S. 2: Mit den Synonymen wär ich vorsichtig, kurz gesagt, ich find sie schei..
Lieber explizit den Zugriff über "altesSchema.Tablename", da kann nichts schiefgehen und jeder weiß, wo er dran ist.