Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Posts
    27

    Unanswered: is it possible for different schema to share the same sequence?

    Hi all,

    lets say i have 2 schemas, schema a and schema b.
    i've created 2 sequences and 2 triggers for each schema.

    currently, both schema a and schema b is sharing the same sequence which is from schema b. Is that possible? If yes, how to fix that, i want each schemas to use its own sequences and triggers and not from another schema.

    Regards,
    Jacky
    Warm Regards,
    Jacky

  2. #2
    Join Date
    Sep 2004
    Posts
    60

    Lightbulb

    Sharing same sequence is possible, if grant on sequence is given from owner schema to any other schema.

    You need to create an individual sequence in each schema with schema owner with out giving grants to any other schema to use individually.

    Same is applicable to trigger. (or any other object)

  3. #3
    Join Date
    Jan 2004
    Posts
    27
    Hi abhishekdixit,

    I have checked the grants and found out that the sequence is not given any grants to another schema other than itself. Are there any other possibilities?

    Thanks.
    Warm Regards,
    Jacky

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I'm a little bit confused ... in the first post you said that there are sequences in both schemas, but you use only one sequence - the one which is created in schema B.

    You don't want that - you want that programs written in schema A use sequence from schema A, and programs in schema B to use sequence from schema B.

    In your second post, you said that there are NO grants on those sequences at all. If that's true, how can red coloured text be correct? User A can NOT use sequence from schema B if there's no "GRANT SELECT ON sequence_from_schema_B TO user_A" (or PUBLIC).

    Maybe other people see it clear, but I don't; could you explain it again, please? What you have and what you'd like to have?

    P.S. If you'd like to remove possibility that schema A uses sequence from schema B, simply "REVOKE ALL ON sequence_from_schema_B FROM user_A".

  5. #5
    Join Date
    May 2004
    Location
    BA [ARG]
    Posts
    137
    Perhaps you're using a public synonym for the sequence, just "revoke select on my_seq from userB" and that's it, that would solve the problem.

    Regards,
    Germán.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •