granular permissions

[jayrijnbergen]

Whenever a new version of SAP Sybase ASE is released, I'm always looking forward to try new functionality.

One thing that got my attention is granular permissions

The idea of granular permissions is to only grant the permissions required for the job at hand (least privilege principle)

Sybooks says: grantable system privileges allow you to enforce "separation of duties"

Is it really that simple?

System roles can be restricted by revoking specific permissions, alternatively it's possible to create user defined roles and grant only specific permissions. The downside of using user defined roles is that user defined roles can't be audited, so if you rely on auditing sa_role you might want to restrict sa_role instead of creating a user defined my_sa_role. If you do change system roles, you can always get the original permissions back with procedure sp_restore_system_role, which requires permission manage server permissions.

Granular permissions can be granted on several levels, from server roles to object permissions. Permissions which would otherwise require object owner or dbo can now be granted separately. Which is a very nice feature. E.g. permission identity_insert, which can be very useful in some situations where you don't want to grant dbo access and still want to be able to provide explicit values for identity columns (e.g. with replication).

An example: sso_role. What kind of permissions are granted to sso_role?

Note that several permissions only apply to the current database, the permissions below are granted in the master database

grantor grantee  type  action                        object           column predicate grantable

------- -------- ----- ----------------------------- ---------------- ------ --------- ---------

dbo     sso_role Grant Alter Any Object Owner                         All    NULL      FALSE

dbo     sso_role Grant Change Password                                All    NULL      FALSE

dbo     sso_role Grant Decrypt Any Table                              All    NULL      FALSE

dbo     sso_role Grant Manage Any Encryption Key                      All    NULL      FALSE

dbo     sso_role Grant Manage Any Login                               All    NULL      FALSE

dbo     sso_role Grant Manage Any Login Profile                       All    NULL      FALSE

dbo     sso_role Grant Manage Any Remote Login                        All    NULL      FALSE

dbo     sso_role Grant Manage Any User                                All    NULL      FALSE

dbo     sso_role Grant Manage Auditing                                All    NULL      FALSE

dbo     sso_role Grant Manage Roles                                   All    NULL      FALSE

dbo     sso_role Grant Manage Security Configuration                  All    NULL      FALSE

dbo     sso_role Grant Manage Security Permissions                    All    NULL      FALSE

dbo     sso_role Grant Map External File                              All    NULL      FALSE

dbo     sso_role Grant Select                        builtin authmech        NULL      FALSE

dbo     sso_role Grant Set Tracing Any Process                        All    NULL      FALSE

dbo     sso_role Grant Show Switch                                    All    NULL      FALSE

dbo     sso_role Grant Update Any Security Catalog                    All    NULL      FALSE

dbo     sso_role Grant Use Any Database                               All    NULL      FALSE

How to use this feature to make my server more secure?

e.g. revoke "change password" from sso_role and it won't be possible anymore for sso_role to change the password for any other login.

Or create a user defined role, and only grant permission "Change Password". That sounds pretty good.

But what about the other permissions? Especially "Manage Roles", what can you do with this permission?

With this permission any role can be granted to (or revoked from) any login. There are no restrictions for granting sa_role & sso_role

A login with permission "manage roles" granted, can grant any role, including system roles sa_role and sso_role, to any login!

(Granting some permissions still requires specific permissions, like "manage server permissions", but granting sa_role with this permission already granted is no problem.)

With granular permissions disabled, to grant sa_role the sa_role must be granted to the grantor already.

If the grantor is not granted sa_role, then error 10353 will be raised:

Msg 10353, Level 14, State 6:

Server 'ASE157', Line 1:

You must have any of the following role(s) to execute this command/procedure: 'sa_role' . Please contact a user with the appropriate role for help.

But with granular permissions enabled, there's no restriction and the role is granted without any problem.

Note this is not a bug, this is the way it has been designed... If it's something you really want is a different question.

With default sa_role and sso_role you can also dynamically switch between enabling and disabling granular permissions, so you have full access.

Not all things are very straight forward with granular permissions. For example permission "manage any statistics" and "own any database"

The keyword "Any" means it should apply to all objects (there's an exception: server privileges with this keyword do not apply to sybsecurity)

Manage Any Statistics

This permission only applies to the current database. So it has to be granted in all databases separately.

If you have this permission it's possible to run update statistics on any user table.

For updating statistics on system tables, you need to be dbo as well.

But even when you are dbo, it's not sufficient. For security related tables like: sysroles and sysprotects extra permissions are required.

Even if you can do a select * on these tables, and you have the "Manage Any Statistics" permission, it's not allowed to run the update statistics command for these tables. What is required to run update stats on these tables is permission "Update Any Security Catalog"

IMHO running update statistics is not updating the security catalog itself, so why this permission is required for update stats is a bit of a mystery to me.

Own any database

If you want to restrict the DBA from accessing user data, this permission is probably something to look at. Revoke it from sa_role and the login with this role lost the dbo access to the database and can't access data anymore without explicitly being granted access to it. But what's the impact of doing this? What happens if your database is nearly full? Without dbo access it's not possible to extend the database. You'll have to find other ways to handle that. (e.g. using a procedurewith  option "with execute as owner", but that's a topic for a different blog)

Last but not least, some commands are not working when granular permissions is enabled

E.g. dbcc printolog('Msg')

Msg 10350, Level 18, State 45:

Server 'ASE157', Line 1:

Permission related internal error was encountered. Unable to continue execution.

This command can be easily replaced by dbcc logprint('Msg')

But it does mean you need to test carefully when implementing new functionality like granular permissions.

Note these dbcc commands are unsupported and should be used carefully.

Granular Permissions gives many possibilities, but also put more responsibilities on the DBA and/or security officers to define and manage a secure and practical security configuration. It's not straightforward to define a role with exactly the permissions you need.