execute as owner restricted

[jayrijnbergen]

Since ASE 15.7 it's possible to create stored procedures with option "execute as owner"

Which is quite a nice feature, e.g. to allow a user to unlock a login without having sso_role, you simply create a procedure like this

create procedure sp__locklogin  @login  varchar(30) = null, @action varchar(10) = null

with execute as owner

as  exec sp_locklogin @login, @action

Normally you would use the sa login to create this procedure in sybsystemprocs and grant execute permissions to a user defined role.

Grant the user defined role to a login, and that login can lock or unlock any other login wihout having sso_role

However, this feature presented a big security loop hole....

Just having a dbo alias in database sybsystemprocs allows you to create any procedure with execute as owner and executing it as the sa login, e.g.

create procedure sp_myproc  @CMD varchar(500)

with execute as owner

as  exec (@cmd)

With the latest patch level SP136 for ASE 15.7 (and latest patch level for 16.0) this security hole has been partly fixed. (See KB 2202914)

When impersonating the database owner, via a dbo alias or via setuser, it's not possible anymore to refer to objects outside of the database when using option "with execute as owner". Instead you'll have to create the object with the database owner itself.

(Creating the proc sp_myproc, executing any SQL statement as login sa, is still allowed)

Example of what will fail

use a login having sa_role (not sa), which gets dbo alias in sybsystemprocs

create proc sp__listen

with execute as owner

as select * from master..syslisteners

go

Msg 16367, Level 16, State 1:

Server 'ASE157', Procedure 'sp__listen', Line 1:

EXECUTE AS OWNER procedures cannot be created by an alias or with the SETUSER command when the procedure has SQL statements hat reference objects across databases. Create the procedure from a non-impersonated session.

The only way to get this procedure created is using the sa login itself.

(Of course the same result can be achieved by executing sp_listener from sp__listen, but that's not the point)

Error 16367 is also produced at other times, e.g. when running installmaster.

This must be executed using the sa login, because several procedures for sp_sysmon rely on this feature (KB 2183652) and can now only be installed by sa

In many environments the sa login is locked out for security reasons

Do you want to unlock the sa login just to deploy a stored procedure in sybsystemprocs?