Get user permissions in SQL Anywhere

Today I needed to sort out user permissions in a  SQL Anywhere database.  Sybase has several system tables and views that you can find in the help by typing in "SYS" in the help index.  

The one that we need is the SYSTABLEPERM view.  But it's not very user friendly since it just lists the IDs for the users, not the user names.  Same with tables, but that gets a little more complicated.  

To get the permissions granted to each user and user group the following will show you the different settings for each user

SELECT DISTINCT SU.name, 
SP.selectauth,
SP.insertauth,
SP.deleteauth,
SP.updateauth,
SP.updatecols,
SP.alterauth
FROM SYSTABLEPERM SP
JOIN SYSUSERS SU on SU.uid = SP.grantee
ORDER BY SU.Name

This will give you a list of user permissions.  If all the users have the same permission, then the list will be very short.  If users have different access to different tables then the list will be longer as each permission group will be listed separately.  Below is an example of the list from the above query.  

name,         selectauth, insertauth, deleteauth, updateauth, updatecols, alterauth
MainUser          Y,          N,          N,          N,          N,          N
SecondUser        Y,          Y,          Y,          Y,          N,          N
DBA               Y,          N,          N,          N,          N,          N
dbo               Y,          N,          N,          N,          N,          N
diagnostics       Y,          Y,          Y,          Y,          N,          N
MainOffice        Y,          N,          N,          N,          N,          N
MainOffice        Y,          Y,          Y,          Y,          N,          N
MainOffice        Y,          Y,          Y,          Y,          N,          Y
PUBLIC            Y,          N,          N,          N,          N,          N
REPORTS           Y,          N,          N,          N,          N,          N
SYS               N,          Y,          N,          N,          N,          N
SYS               Y,          N,          N,          N,          N,          N

This list gives Yes or No to SELECT, INSERT, DELETE and UPDATE permissions.  updatecols shows if the user has been given UPDATE permission on some columns in a table.  The last column shows if the user has an ALTER permission.  It can be Y,N or G.  G shows if the user has ALTER permission AND can grant ALTER permission to other users.  Y simply indicates that the user has ALTER permission but cannot grant other users ALTER permissions.

When it came to the table names, this exercise got considerably more complicated because they can't really make the query distinct since all columns are unique.  I haven't found a solution for it really, but the following will list the table names as well.

SELECT DISTINCT SU.name, 
SP.selectauth,
SP.insertauth,
SP.deleteauth,
SP.updateauth,
SP.updatecols,
SP.alterauth,
ST.Table_Name
FROM SYSTABLEPERM SP
JOIN SYSUSERS SU ON SU.uid = SP.grantee
JOIN SYSTABLE ST ON ST.table_id = SP.stable_id
WHERE SP.grantee > 100
ORDER BY SU.Name, ST.Table_Name

This will show a list of every user and EVERY table since the table names aren't distinct.  The list is ordered by the user name and then the table name.  Note the "WHERE SP.grantee > 100" filter.  This filters out all system table permissions, which are mostly irrelevant at least for what I needed it for.  If you want to get the grants for a single user or user group, you can filter it on the user name (SU.name)  As the query is, it will show the tables for each user.  If you would rather show the users for each table, swap the ORDER BY clause around:

ORDER BY SU.Table_Name, SU.Name

This will then list each table for each user.  Hopefully this will help someone who wants to do a quick query on the users and user permissions in a database.  What I needed this for was a database at a remote location that only has the old iSQL program for querying, not SQL Central installed.  This makes it impossible to view user permissions without querying the database directly.

For those of you looking for MS-SQL information, look up SYS. tables and views in the help.  I may add a quick article about that in the near future.  Have fun!

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.
Powered by Zendesk