Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2003
    Location
    Palmer, Alaska USA
    Posts
    13

    Question Unanswered: How does one retrieve user table privileges via sql?

    I want to be able to retrieve user table privileges
    via sql for particular database tables (for web page
    display) and I assume that somewhere within the
    sysmaster schema this data exists. I am, however,
    having great difficulties in figuring out how to get
    at this data. Does anyone know what table(s) to look
    in and what flag(s), if any, are needed?

    TIA!
    Marty
    Don't take my word for it ... trial and error is my methodology (mostly error)...and then I forget and go thru it all again!

  2. #2
    Join Date
    Aug 2003
    Location
    India
    Posts
    4

    Re: How does one retrieve user table privileges via sql?

    Originally posted by DoomDog
    I want to be able to retrieve user table privileges
    via sql for particular database tables (for web page
    display) and I assume that somewhere within the
    sysmaster schema this data exists. I am, however,
    having great difficulties in figuring out how to get
    at this data. Does anyone know what table(s) to look
    in and what flag(s), if any, are needed?

    TIA!
    Marty
    ===========================
    Hi,

    Try this query.

    select st.tabname,st.owner, sa.tabauth,sa.grantor,sa.grantee
    from systabauth sa, systables st
    where sa.tabid=st.tabid and st.tabname='{tablename}';

    replace {tablename} with the table name you want to see the privileges.

    sa.tabauth will give an output like su-idx-- or s-------

    each represents
    s - Select
    u - Update
    * - Column level authority
    i - Insert
    d - Delete
    x - Index
    a - Alter
    r - References

    If the tabauth privilege code is uppercase (for example, S for select), a user who has this privilege can grant it to others. If the tabauth privilege code is lowercase (for example, s for select), the user who has this privilege cannot grant it to others.

    Regards,

    Niks

  3. #3
    Join Date
    Mar 2003
    Location
    Palmer, Alaska USA
    Posts
    13

    Smile

    Niks, thank you for the reply! Your code works great, too!

    Marty

Posting Permissions

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