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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Ready to get started with anonymous questions?

It's easy! Check out this step-by-step guide for asking an anonymous question on Experts Exchange.

Question has a verified solution.

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

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Suggested Courses

621 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