0

We have requirement to enable auditing on Sybase ASE(Adaptive Server Enterprise/15.7/EBF 27351 SMP SP139 /P/ia64/HP-UX B.11.31/ase157sp138x/4041/64-bit/FBO/Tue Aug 8 07:33:11 2017) and I enabled the same on master database as below:

Result of sp_displayaudit

I was under impression that this would also enable auditing on any change in login/user(sp_addlogin, sp_modifylogin, sp_droplogin, sp_adduser, sp_dropuser) as create/drop/alter was handled at master level, but that was incorrect as this is applicable to only objects and not users/logins. Recently when I dropped one user, it was not logged in the audit event; after checking multiple documents on the Sybase documentation and different portals on the internet and also with the help of vendor, came to know that one more type of auditing(login_admin) needs to be enabled in order to capture these events in audit events.

I executed below command and was expecting that this would solve the underlying issue:

1> sp_audit "login_admin", "all", "all", "on"
2> go
Audit option has been changed and has taken effect immediately.
(return status = 0)
1> sp_displayaudit "login"
2> go
No logins currently have auditing enabled.
(return status = 0)
1>

As evident from the result of sp_displayaudit, logins are still not being audited. I am not really sure if this requires ASE reboot to take effect or I am missing something here.

Any help would be greatly appreciated.

0

After trying hard on this without finding any workable solution, raised a case with Sybase support and they have provided solution.

Below are the commands that can be used to satisfy the mentioned requirement:

--Audit any change at procedure level for login/user/alias

use sybsystemprocs 
go 
sp_audit "exec_procedure", "all", "sybsystemprocs.dbo.sp_addlogin", "on" 
go 
sp_audit "exec_procedure", "all", "sybsystemprocs.dbo.sp_droplogin", "on" 
go 
sp_audit "exec_procedure", "all", "sybsystemprocs.dbo.sp_modifylogin", "on" 
go 
sp_audit "exec_procedure", "all", "sybsystemprocs.dbo.sp_adduser", "on" 
go 
sp_audit "exec_procedure", "all", "sybsystemprocs.dbo.sp_dropuser", "on" 
go

sp_audit "exec_procedure", "all", "sybsystemprocs.dbo.sp_addalias", "on" 
go

sp_audit "exec_procedure", "all", "sybsystemprocs.dbo.sp_dropalias", "on" 
go


--Audit any change at table level for login/user/alias 

sp_audit "delete", "all", "master.dbo.syslogins", "on"
go
sp_audit "insert", "all", "master.dbo.syslogins", "on"
go
sp_audit "update", "all", "master.dbo.syslogins", "on"
go

sp_audit "delete", "all", "master.dbo.sysusers", "on"
go
sp_audit "insert", "all", "master.dbo.sysusers", "on"
go
sp_audit "update", "all", "master.dbo.sysusers", "on"
go

sp_audit "delete", "all", "master.dbo.sysalternates", "on"
go
sp_audit "insert", "all", "master.dbo.sysalternates", "on"
go
sp_audit "update", "all", "master.dbo.sysalternates", "on"
go


sp_audit "delete", "all", "master.dbo.syssrvroles", "on"
go
sp_audit "insert", "all", "master.dbo.syssrvroles", "on"
go
sp_audit "update", "all", "master.dbo.syssrvroles", "on"
go


sp_audit "delete", "all", "master.dbo.sysloginroles", "on"
go
sp_audit "insert", "all", "master.dbo.sysloginroles", "on"
go
sp_audit "update", "all", "master.dbo.sysloginroles", "on"
go

I am sure this would help to anyone who wants to enable auditing at Sybase ASE level and is not sure how to achieve the same.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.