Extracting Sequences and GRANTS
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Extracting Sequences and GRANTS

  1. #1
    Join Date
    Aug 2000
    Location
    Chicago IL
    Posts
    586

    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."

  2. #2
    Join Date
    Aug 2000
    Location
    Chicago IL
    Posts
    586
    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."

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  4. #4
    Join Date
    Feb 2003
    Location
    INDIA
    Posts
    96
    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

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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 04: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
  •  


Click Here to Expand Forum to Full Width