-
Extracting Sequences and GRANTS
Hello,
I am working on this one script to extract the the ddl for the sequences.. This works fine but some sequences have grants and i am having trouble figuring out where to get the grants from:
select 'CREATE SEQUENCE '||SEQUENCE_NAME||chr(10)||
' INCREMENT BY '||INCREMENT_BY||chr(10)||
' START WITH '||LAST_NUMBER||chr(10)||
' MINVALUE '||MIN_VALUE||chr(10)||
' MAXVALUE '||MAX_VALUE||chr(10)||
decode(CYCLE_FLAG,'N',' NOCYCLE','CICLE')||chr(10)||
decode(ORDER_FLAG,'N',' NOORDER','ORDER')||chr(10)||
' CACHE '||CACHE_SIZE
from ALL_SEQUENCES
where SEQUENCE_OWNER = 'ABC' ;
the output is this..but how do i get the grants
CREATE SEQUENCE RTDATE.CLOSURE_ID_SEQ
START WITH 79
INCREMENT BY 1
NOMINVALUE
NOMAXVALUE
NOCYCLE
CACHE 20
NOORDER
/
source database ddl:
CREATE SEQUENCE RTDATE.CLOSURE_ID_SEQ
START WITH 79
INCREMENT BY 1
NOMINVALUE
NOMAXVALUE
NOCYCLE
CACHE 20
NOORDER
/
GRANT SELECT ON RTDATE.CLOSURE_ID_SEQ TO PDS_CCS_MAINT
/
thanks
Jigar
"High Salaries = Happiness = Project Success."
-
I think i figured it out..
SELECT 'GRANT SELECT ON '||uo$.name||'.'||o$.name||' TO '||ue$.name STMT
FROM sys.objauth$ t$, sys.obj$ o$, sys.user$ ur$,
sys.table_privilege_map m$, sys.user$ ue$, sys.user$ uo$
WHERE o$.obj# = t$.obj#
AND t$.privilege# = m$.privilege
AND t$.col# IS NULL
AND t$.grantor# = ur$.user#
AND t$.grantee# = ue$.user#
and o$.owner#=uo$.user#
and ur$.name = 'RTDATE'
and uo$.name = 'RTDATE'
and o$.name like '%SEQ%'
/
"High Salaries = Happiness = Project Success."
-
You certanly don't want to expose a 6 table join (and on top of that even using dictionary tables directly!) just to extract grants on sequences? Grants on sequences are available through USER/ALL/DBA_TAB_PRIVS, just as any other object grants. So something like this:
SELECT 'GRANT SELECT ON '||p.owner||'.'||p.table_name||' TO '||p.grantee stmt
FROM DBA_TAB_PRIVS p, DBA_SEQUENCES s
WHERE p.owner = s.sequence_owner
AND p.table_name = s.sequence_name
AND s.sequence_owner = 'ABC';
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
jmodic,
Little correction with your Query.
SELECT 'GRANT '||privilege||' ON '||p.owner||'.'||p.table_name||' TO '||p.grantee stmt
FROM DBA_TAB_PRIVS p, DBA_SEQUENCES s
WHERE p.owner = s.sequence_owner
AND p.table_name = s.sequence_name
AND s.sequence_owner = 'ABC';
Cheers
Dilip Patel
OCP 8i
Catch me online at Yahoo: ddpatel256
-
Yes, good catch. I forgot that there is more than just SELECT privilege that can be granted on sequences. But it was also the original poster that made the same mistake, I merely coppied that initial part of his/her original query...
Last edited by jmodic; 05-05-2004 at 03:32 AM.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|