46

How can I check a permission granted for particular sequence and assign permission to particular sequence from SQL*Plus?

Michu93
  • 5,058
  • 7
  • 47
  • 80
Jack
  • 785
  • 4
  • 12
  • 19

2 Answers2

86

To grant a permission:

grant select on schema_name.sequence_name to user_or_role_name;

To check which permissions have been granted

select * from all_tab_privs where TABLE_NAME = 'sequence_name'
beny23
  • 34,390
  • 5
  • 82
  • 85
7

Just another bit. in some case i found no result on all_tab_privs! i found it indeed on dba_tab_privs. I think so that this last table is better to check for any grant available on an object (in case of impact analysis). The statement becomes:

    select * from dba_tab_privs where table_name = 'sequence_name';
OPMendeavor
  • 430
  • 6
  • 14
  • 1
    Regular users usually don't have access to any of the `DBA_xxx` views. –  Sep 23 '14 at 12:33
  • 1
    Sure, you are right! But the fact is that i needed to evaluate impacts of a sequence renewal on an Oracle table. with credentials different from DBA ones I didn't find the grant set for other users. with sys credentials i found 3 dependent schemas I will miss if stopped to the first statement (with disservices in prod environment). – OPMendeavor Sep 23 '14 at 14:51
  • I forgot to say thanks for the answer of beny23. essential to look for and find DBA's view ;) – OPMendeavor Sep 23 '14 at 15:33