Home » SQL & PL/SQL » SQL & PL/SQL » Grant select on all tables of schema [Merged]
Grant select on all tables of schema [Merged] [message #427882] Mon, 26 October 2009 05:21 Go to next message
dietbeck
Messages: 6
Registered: October 2009
Junior Member
Trying this (I want to grant select to one schema on all tables of another schema)

create or replace PROCEDURE GRANT_SELECT (schemaName in varchar2) AS

CURSOR ut_cur IS
  SELECT table_name FROM user_tables;

RetVal  NUMBER;
sCursor INT;
sqlstr  VARCHAR2(250);

BEGIN
  FOR ut_rec IN ut_cur
  LOOP
    sqlstr := 'GRANT SELECT ON '|| ut_rec.table_name
    || ' TO ' || schemaName;
    sCursor := dbms_sql.open_cursor;

    dbms_sql.parse(sCursor,sqlstr, dbms_sql.native);

    RetVal := dbms_sql.execute(sCursor); 
    dbms_sql.close_cursor(sCursor);
  END LOOP; 
END grant_select; 


getting the following error:

ORA-25191: cannot reference overflow table of an index-organized table
ORA-06512: at "SYS.DBMS_SYS_SQL", line 909
ORA-06512: at "SYS.DBMS_SQL", line 39
ORA-06512: at "ACD.GRANT_SELECT", line 17
ORA-06512: at line 6


Thanks for help
Re: Select privilege for all tables of a schema [message #427884 is a reply to message #427882] Mon, 26 October 2009 05:25 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Change the query so it doesn't select overflow tables.
Re: Select privilege for all tables of a schema [message #427885 is a reply to message #427882] Mon, 26 October 2009 05:31 Go to previous messageGo to next message
balakrishnay
Messages: 54
Registered: September 2009
Location: Pune
Member

Hi,

Why dont you try this .

select ' grant all on '||owner||'.'||object_name||' to user_name;' from all_objects
where owner='username';


Regards

Bala
Re: Select privilege for all tables of a schema [message #427886 is a reply to message #427885] Mon, 26 October 2009 05:32 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
balakrishnay wrote on Mon, 26 October 2009 10:31

Hi,

Why dont you try this .

select ' grant all on '||owner||'.'||object_name||' to user_name;' from all_objects
where owner='username';


Regards

Bala


Because it's massive overkill?
OP only asked for SELECT.
Re: Select privilege for all tables of a schema [message #427889 is a reply to message #427882] Mon, 26 October 2009 05:38 Go to previous messageGo to next message
balakrishnay
Messages: 54
Registered: September 2009
Location: Pune
Member
cookiemonster,

OH.. Yes he can change that all on to what ever he want ..

Regards

Bala

[Updated on: Mon, 26 October 2009 05:40]

Report message to a moderator

Re: Select privilege for all tables of a schema [message #427891 is a reply to message #427882] Mon, 26 October 2009 05:42 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
There's also the fact that it'll almost certainly give the same error the OP has encountered anyway, so it doesn't actually solve anything.
Re: Grant select on all tables of schema [Merged] [message #427909 is a reply to message #427882] Mon, 26 October 2009 06:28 Go to previous message
Michel Cadot
Messages: 63808
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select distinct iot_type from dba_tables;
IOT_TYPE
------------

IOT_OVERFLOW
IOT

Regards
Michel
Previous Topic: Need help with a SQL Query
Next Topic: where clauses parse order
Goto Forum:
  


Current Time: Wed Sep 28 16:05:00 CDT 2016

Total time taken to generate the page: 0.06657 seconds