How to Enable Granular Permissions in SAP ASE Database

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 database and grant SAP ASE server permissions
    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 and grant necessary permissions
    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