Granular permissions not very intuitive

[jayrijnbergen]

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?

[former_member182259]

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.

[former_member229302]

Thanks Jeff for that explanation!!

[jayrijnbergen]

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.

[jayrijnbergen]

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.