Rights for running system procedures?

Question

Can a non-adssys user run the system procedures?

I am developing a BizTalk WCF Adapter for Advantage that can be used and I need to browse and resolve the metadata. This is an add-in for Visual Studio, supporting .Net 2.0 or higher, that generates schemas and a binding file (wsdl). It can also generate classes that can be used in a WCF Service.

Our database has over 1000 tables, 50 views, and 50 procedures. We want to assign objects to a User ID and just return the objects belonging to the user.

Thanks,

Howard

Answer 1

Yes, non-adssys user can run the system procedures and use select to retrieve information from the system tables. Advantage uses a permission, and user/role based system to determine the user's access to the objects in the database. The user's permission will determine whether the procedure can be executed successfully or how much information are returned from the system tables.

This link provides the comprehensive information on the permission system in the Advantage data dictionary.

With regards to tables and views, to see the name of a table or view, the user must have at least the SELECT permission on the table a view. To modify table property such as constraint and index of a table the user must have ALTER permission to the table. For users who do not have ALTER permission to the table, those constrain objects and index objects are hidden from the them.

For stored procedures, the user must have EXECUTE permission on the procedure in order to see the name of the procedure.

The favored method for managing the permissions is to assign users to groups (roles), and grant permissions to the groups. User belonging to a group will inherit the permission from the group.

Answer 2

Conceptually, you might want to look at storing/retrieving the metadata from a descriptive table, then using THAT result to return your objects. You have a single point of access for all users using the ability to filter by SQL clause. Once you have the object data you can use a higher level permissioned "user" to return the objects without actually giving access to individual users. Just an idea.