Author 
Symptom
Protecting sensitive data from prying eyes is a must in today’s IT environment. Sybase ASE 15.7 ESD#2 has introduced a new security feature called Granular Permissions. This feature enables database administrators to fine-tune the separation of duties that has been in place since the introduction of role-based security. Granular permissions provide DBAs with the functionality to avoid security breaches, and have tighter control over which users can access sensitive data.
Grantable system privileges enable you to enforce the following security concepts:
the separation of duties, which requires - for particular sets of operations - that no single individual is allowed to execute all operations within the set
the principle of least privilege, which requires that all users in an information system should be granted as few privileges as are required to do the job
Enabling granular permissions reconstructs system-defined roles (sa_role, sso_role, oper_role, and replication_role) as privilege containers consisting of a set of explicitly granted privileges. You can revoke explicitly granted system privileges from system-defined roles and regrant them to these roles.
Solution
In SAP Business Suite Systems on SAP ASE, granular permissions are enabled when the system is installed on >= SAP ASE 16.0 SP03, when SAP ASE is upgraded to a version >= 16.0 SP03. Setup of granular permissions for SAP ASE logins sapsa, sapsso and roles sap_adm and sap_mon is performed by saphostctrl during SAP ASE upgrade. There is no additonal action required. The changes to SAP ASE user permission performed by saphostctrl during SAP ASE upgrade are documented below.
In case SAP ASE has been upgraded manually (i.e. saphostctrl was not used to perform the SAP ASE upgrade), then these steps can be performed to enable granular permissions manually:
1. Enable granular permissions and unlock the sa SAP ASE login.
Log on as user sapsso and execute the following commands:
use master
go
exec sp_configure 'enable granular permissions',1
go
exec sp_locklogin sa, 'unlock'
go
2.Create users sapsso and sapsa in the master and
Log on as user sa and execute the following commands:
use master
go
if not exists ( select 1 from sysusers where name = 'sapsso' )
begin
exec sp_adduser 'sapsso'
end
go
if not exists ( select 1 from sysusers where name = 'sapsa' )
begin
exec sp_adduser 'sapsa'
end
go
grant manage master key to sapsa
go
grant manage server permissions to sapsso
go
3.Create user sapsso in the SAP database
use
go
if not exists ( select 1 from sysusers where name = 'sapsso' )
begin
exec sp_adduser 'sapsso'
end
go
grant manage database permissions to sapsso
go
grant manage database encryption key to sapsso
go
grant select on sysobjects to sapsso
go
grant manage any object permission to sapsso
go
4.Grant select permission on SVERS resp. BC_DDDBTABLERT to role sap_mon
If this is an ABAP instance log in to SAP ASE as sa and execute these commands
use
go
setuser 'SAPSR3'
go
grant select on SAPSR3.SVERS to sap_mon'
go
setuser
If this is a JAVA instance log in to SAP ASE as sa and execute these commands
use
go
setuser 'SAPSR3DB'
go
grant select on SAPSR3DB.BC_DDDBTABLERT to sap_mon
go
setuser
5.Revoke permissions from dbo to restrict access to user data
Log on as user sapsso and execute the following commands:
use
go
revoke setuser from dbo granted by dbo
go
revoke alter any object owner from dbo granted by dbo
go
revoke manage any user from dbo granted by dbo
go
6.Grants to dbo needed by R3load (DB refresh) and CDS support
Log on as user sapsso and execute the following commands:
use
go
grant drop any object to dbo
go
grant create any function to dbo
go
7.Grants to sap_adm and sap_mon to allow maintenance functionality
Log on as user sapsso and execute the following commands:
use
go
grant reorg any table to sap_adm
go
grant manage any statistics to sap_adm
go
use master
go
grant monitor qp performance to sap_mon
go
8.Lock user sa
Log on as user sapsso and execute the following commands:
use master
go
exec sp_locklogin sa, 'lock'
go
9.Optimize the SAP ASE server configuration for use of granular permissions
Log on as user sapsa and execute the following commands:
use master
go
exec sp_configure 'permission cache entries', 1024
go
More details refer Snote:2106688
