on 04-14-2015 8:14 AM
Using granular permissions on ASE 15.7 shows some non-intuitive behavior
Sybooks says permission "Update Any Security Catalog" is required for:
Updating, inserting, and deleting these security-related system catalogs, which are restricted from direct update
master.dbo.syslogins | |
master.dbo.syssrvroles | |
master.dbo.sysloginroles | |
db.dbo.sysroles | |
db.dbo.sysprotects |
That makes sense, any direct update on this should require special permissions
Then there's the permission "Manage any statistics": Update or delete statistics on any table owned by anyone
However, permission "manage any statistics" is not sufficient for some tables
update index statistics sysroles
go
Msg 10331, Level 14, State 2:
Server 'ASE157', Line 1:
Permission denied, database testdb, owner dbo. You need the following permission(s) to run this command: UPDATE ANY SECURITY CATALOG.
update index statistics sysprotects
go
Msg 10330, Level 14, State 1:
Server 'ASE157', Line 1:
UPDATE STATISTICS permission denied on object sysprotects, database testdb, owner dbo
Very non-intuitive behavior.
I've raised a case with support, but they just say updating sysroles requires update any security catalog
IMHO update stats is not updating the table, manage any statistics should be sufficient
What do you think about this?
Hit any similar issues with granular permissions?
Some things that are not so intuitive sometimes need some thinking. Essentially, first of all - the update statistics being applicable to everyone I think is a bit misleading as well - it likely should read - "anyone with permission to read the table/columns".....for example, if you add a user to any database but do NOT grant them any permission on a table (one that also doesn't have public permissions), and you try to run update statistics as that user, you will get:
Could not execute statement.
UPDATE STATISTICS permission denied on object rs_databases, database
rep_analysis_157, owner dbo
Sybase error code=10330, SQLState="42000"
Severity Level=14, State=1, Transaction State=1
Line 1
update statistics rs_databases
....so FIRST you must have "select" permission on the table in order to run update statistics.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Yeah, that's what I thought...
But that's not true either
to run update statistics on most objects, excluding the objects requiring update any security catalog, only the update statistics permission is required.
1> select * from sysobjects
2> go
Msg 10332, Level 14, State 1:
Server 'ASE157', Line 1:
SELECT permission denied on column audflags of object sysobjects, database testdbX, owner dbo
1> update statistics sysobjects (audflags) with print_progress=1
2> go
Update Statistics STARTED.
Update Statistics table scan started on table 'sysobjects'.
...Sorting started for column 'audflags' (column id = 18).
Update Statistics FINISHED.
Example on a user table
1> select * from tabx
2> go
Msg 10330, Level 14, State 1:
Server 'ASE157', Line 1:
SELECT permission denied on object tabx, database testdbX, owner dbo
1> update statistics tabx with print_progress=1
2> go
Update Statistics STARTED.
Update Statistics table scan started on table 'tabx' for summary statistics.
Update Statistics FINISHED.
CR783406 was created.
RFE: "Manage any statistics" should allow updates to system tables
under GP
1> update statistics sysroles
2> go
Msg 10331, Level 14, State 2:
Server 'XXX', Line 1:
Permission denied, database master, owner dbo. You need the following
permission(s) to run this command: UPDATE ANY SECURITY CATALOG.
What happens is that if the table involved is a system table, it doesn't even check to see if any other granular permission are set only if bit (UPDATE ANY SECURITY CATALOG) is on.
Feature request:
When granular permissions is enabled, then permission "manage any statistics" should be sufficient to delete and update statistics on any table
That should include all system tables, even security related system tables since we are NOT modifying the contents of these tables.
User | Count |
---|---|
87 | |
10 | |
10 | |
9 | |
7 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.