How to handle the database objects security in database through stored procedures
Clumsy Solution –> Grant explicit permissions to user X on table1 and table2, similarly grant execute permissions and table access to users Y and Z. In a production environment think of many users available in a database and thousands of objects, it is not possible.
Good Solution –> Create all objects with dbo user and provide execute permission ONLY on stored procedures to user X, thus when user would execute the stored procedure, SP would be able to modify the table1 and trigger on table1 would be able to modify tabl2, though explicit access on tables have not been granted to user X. This is how stored procedure maintain the security features. User X shall not be able to access table1 and table2 explicity but stored procedure run by user X can modify those tables.
This is how objects gets created in production environment.Check the Sybase Wiki @ sybasewiki.com