how to grant select privilege on all sequences within the schema

Posted on 2009-05-20
Last Modified: 2013-12-18
 I have around 30 sequences within a schema say schema_xyz. I would like to give select privilege on all the sequences. What is the best way or command to do it. Any links, resources, sample code, ideas highly appreciated. Thanks in advance.
Question by:gudii9
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 150 total points
ID: 24431720
You can select All Sequences available using the below query

SELECT 'GRANT SELECT ON ' || SCHEMA || '.' || sequencename || 'TO hr;' as grant_stmt

Once you have the list, you can execute the dynamic sql statements in a cursor loop for all Sequences.
LVL 16

Accepted Solution

Milleniumaire earned 200 total points
ID: 24431846
Create the following script and call it grant.sql

set heading off pagesize 0 feedback off
spool grantscript.sql

select 'grant select on '||sequence_owner||'.'||sequence_name||' to xyz;'
from all_sequences
where sequence_owner = 'MYUSER;

spool off


Run the above from sqlplus as follows:

sqlplus username/password @grant

Obviously you will need to change MYUSER and xyz as appropriate.
LVL 35

Assisted Solution

johnsone earned 150 total points
ID: 24433378
This should work as well.

You need to replace schema_abc with the schema you want to grant the privileges to.
  for c1rec in (select 'grant select on ' || sequence_owner || '.' || sequence_name || ' to schema_abc' stmt from dba_sequences where sequence_owner = 'SCHEMA_XYZ') loop
    execute immediate c1rec.stmt;
  end loop;

Open in new window


Author Closing Comment

ID: 31583502
thank you

Featured Post

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Select and Insert Query running slow 4 57
PL SQL Developer 7 67
Oracle Forms 10g: How-To Prevent a Commit of 1 Record in Master-Detail block. 3 83
oracle query 3 25
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question