Icon Frequently Asked Questions

How do I retrieve the effective table privileges for a particular user ?

This information isn't available as an Information schema table at this time, but you can use the following procedure to do so:

CREATE PROCEDURE "EffectiveTablePrivileges" (IN "UserName" VARCHAR COLLATE ANSI, IN "TableName" VARCHAR COLLATE ANSI)
BEGIN
   DECLARE TempCursor CURSOR WITH RETURN FOR TempStmt;

   PREPARE TempStmt FROM 'SELECT TablePrivileges.*
                            FROM Information.TablePrivileges INNER JOIN
                            (SELECT Name FROM Configuration.Users WHERE Name=?
                             UNION ALL
                             SELECT Name FROM Configuration.UserRoles WHERE GrantedTo=?) Authorizations
                            ON TablePrivileges.GrantedTo=Authorizations.Name
                            WHERE TablePrivileges.Name=?';
   EXECUTE TempStmt USING UserName, UserName, TableName;
END

If you want to view the effective privileges for a view instead of a table, you would change TablePrivileges to ViewPrivileges, and so on for functions, procedures, and other privileged objects. For more information on user security in general, please see the User Security topic in the ElevateDB SQL manual.
Image