Sybase ASE
11.0 11.5 11.9 12.0 12.5 15.x
Last updated: 17 March 2013
Granting SA rights to non-SA users
 
Summary
ASE allows DBAs to selectively grant permissions for executing certain commands to specific users or logins by means of the grant statement. However, permissions for executing some commands (like kill) cannot be granted at all, even though this would sometimes be useful. This document will show how such functionality can be implemented safely, using only regular ASE features.
Stored procedures which allow a non-privileged user to execute kill, sp_sysmon, sp_showplan, dbcc sqltext, and the 302/310/317 optimizer traceflags, can be downloaded here.

Contents

What & Why
By design, permissions on some T-SQL commands cannot be granted in ASE. One example of this is the kill command, which can be used to forcefully terminate a client process in ASE: the kill command requires that the executing login has sa_role, and permission to execute kill cannot be granted in any other way. While this limitation is understandable from a security point of view, it is sometimes undesirable in practice: many DBAs would like to allow their non-SA users to kill their own process. Because granting sa_role to ordinary users is not acceptable for security reasons, it means all requests for such actions still have to go via the DBA, increasing the DBA workload and decreasing productivity. Other examples of useful functions that require special system roles are things like sp_sysmon, sp_showplan and sp_object_stats (these all require sa_role).

Sometimes DBAs have built custom application functionality just to allow their users to perform such privileged commands anyway. For example, I have seen a web-based application on a company's intranet where users could fill in an ASE server name and a spid number for a process they wanted to have killed. A cgi-bin script would then log into the ASE server as "sa" and issue the kill command.
This illustrates the practical need for granting certain commands to ordinary users, even though ASE itself does not currently allow this. Fortunately, this functionality can still be implemented inside ASE, using only regular ASE features. This document will illustrate how to allow ordinary, non-privileged users to execute privileged commands like kill, without giving away sa_role to those users.

The basic idea
To demonstrate how to effectively grant the use of the privileged commands to non-privileged users, we will create a stored procedure called sp_kill which can be used by ordinary users to kill their own processes. The corresponding SQL code can downloaded here.
The key to making this work is a creative way of applying user-defined roles: a role indirect_sa_role is created (with a password), and sa_role is granted to this role. Now indirect_sa_role is granted to an ordinary, non-privileged login as follows:
create role indirect_sa_role 
with passwd mysecretpassword
go
grant role sa_role to indirect_sa_role
go
grant role indirect_sa_role to an_innocent_login
go

When login an_innocent_login executes sp_kill, this stored procedure will use set role to enable indirect_sa_role (and therefore sa_role), the kill command will be executed, and the role will be disabled again. The simplified example below shows how this mechanism works:
--
-- The basic principle...
--
create procedure sp_kill
   @spid int
as
begin
   -- create the 'kill' command string
   select @killcmd = "kill" + str(@spid,5)

   -- enable sa_role 
   set role indirect_sa_role 
   with passwd mysecretpassword on

   -- issue the kill now that we have sa_role
   execute(@killcmd)

   -- disable sa_role 
   set role indirect_sa_role off
end
go
sp_hidetext sp_kill
go

In the above code, sp_hidetext which 'hides' the T-SQL code of sp_kill, is essential in making the whole thing work: by hiding the code (using encryption), the password for enabling the role will not be visible to anyone, and users therefore cannot enable sa_role themselves. This way, the permission to execute kill is effectively "frozen" into sp_kill, and is available for anyone who can execute sp_kill. Because the role password cannot be retrieved from the encrypted T-SQL text, only those commands which have been programmed into the stored procedure are accessible to the user.


The full picture
While the code in the above example illustrates the basic principle, it contains a security weakness making it unsuitable for practical use in this form. The problem lies in the fact that the user executing the above version of sp_kill may be able to interrupt execution of the procedure by hitting CTRL-C. When this happens after indirect_sa_role has been enabled but before it has been disabled again, sa_role would remain enabled for the user's session. Obviously, this would create an unacceptable security problem.

To overcome this security issue, a slightly more elaborate setup is used. Instead of executing the stored procedure directly, it is executed indirectly as an RPC. Again, the user executes sp_kill, but this time the procedure doesn't do very much: it only executes another stored procedure sp_kill_rpc, and it is this last procedure which actually executes the kill statement. Because sp_kill_rpc is executed as an RPC (via CIS), the sequence of statements in sp_kill_rpc effectively becomes atomic for the user executing sp_kill : even if the execution of sp_kill_rpc would be interrupted, this would not give the original user access to sa_role.
The (simplified) stored procedures are as follows:
--
-- This procedure is executed by the user...
-- 
create procedure sp_kill
   @spid int
as
begin
    set cis_rpc_handling on

    -- assumption: SERVERNAME_ALIAS has been set  
    -- up as an alias of the current server name
    execute SERVERNAME_ALIAS...sp_kill_rpc @spid

    set cis_rpc_handling off
end
go


--
-- This procedure is called from sp_kill as a CIS RPC...
--
create procedure sp_kill_rpc
   @spid int
as
begin
   -- create the 'kill' command string
   select @killcmd = "kill" + str(@spid,5)

   -- enable sa_role 
   set role indirect_sa_role 
   with passwd mysecretpassword on

   -- issue the kill now that we have sa_role
   execute(@killcmd)

   -- disable sa_role 
   set role indirect_sa_role off
end
go
sp_hidetext sp_kill_rpc
go
Note that the complete versions of the stored procedures are more complicated, because they contain some additional checks. For example, it is ensured that a user can only kill processes owned by the same login. There are also various CIS-related checks and some code to ensure that sp_kill_rpc can only be invoked from sp_kill. See the code for full details.
When CIS executes the RPC, it will --behind the scenes-- create a new connection to the ASE server itself to execute the RPC. This is a classic "trick" which is based on setting up a remote servername in sysservers which really is an alias of the current server (some older examples of this trick can be found here (simulating dynamic SQL) and here (various tricks with CIS and XP server)).
By using CIS for the RPC mechanism, the login used for the CIS connection will be the same as the login originally executing sp_kill; no external logins need to be set up for this (in fact, it is best not to set up external logins at all as this keeps things like authorisation simple).

Examples
sp_kill is actually very simple to use: only the 'spid' for the process-to-be-killed must be specified. Some additional checks have been built into sp_kill to ensure a user can only kill his own processes.
1> sp_kill 48
2> go

The downloadable code contains not only sp_kill. There is also a procedure sp_sysmon_public, which allows a non-SA user to execute sp_sysmon. You can specify the normal sp_sysmon parameters:
1> sp_sysmon_public "00:10:00", diskio
2> go

Another procedure is sp_showplan_public, which allows a normal user to display query plans with sp_showplan, but only for his own processes. You can specify the normal sp_showplan parameters (though just a 'spid' will do as well):
1> -- display query plans for process 35
2> sp_showplan_public 35 
3> go

The procedure sp_shutdown allows a normal user to shutdown ASE, optionally "with nowait":
1> -- shutdown ASE
2> sp_shutdown 
3> go

1> -- shutdown ASE 'with nowait' (not recommended !)
2> sp_shutdown "with nowait"
3> go

The procedure sp_sqltext allows a normal user to run dbcc sqltext on his own sessions:
1> -- display currently executing SQL text for process 35
2> sp_sqltext 35 
3> go

Lastly, sp_optimizer_trace lets normal users invoke the optimizer-related traceflags 302, 310 and 317:
1> sp_optimizer_trace 'select count(*) from sysobjects', 302
2> go

1> declare @cmd varchar(100)
2> select @cmd = 'select count(*) ' +
3>             + 'from sysobjects o, sysindexes i ' 
4>             + 'where i.id = o.id'
5> exec sp_optimizer_trace @cmd, 310, 317
6> go
Security: be careful with the role password
The most obvious security issue is of course the password for indirect_sa_role: the DBA must make sure nobody will know what this password is. For example, do not leave the SQL script which you've used to create the role and stored procedures lying around on your system, because this will containing the actual role password. Anyone who knows this password will effectively have sa_role, so the DBA should be careful not to give this away. In fact, it may well be a good idea to simply forget the role password, as there's really no need for anyone to know it anyway: when the stored procedures would need to be recreated, you'd simply set a new role password as follows:

1> alter role indirect_sa_role drop passwd
2> go
1> alter role indirect_sa_role 
2> add passwd myNEWsecretpassword
3> go

Note that it is not possible to derive the role password from sp_kill's hidden (= encrypted by sp_hidetext) SQL text: even Sybase Technical Support will not be able to decrypt this SQL text for you.


Security: things (not) to do
In order to avoid security problems (i.e. normal users getting hold of sa_role), please observe the following guidelines:
  • CIS is used to make the whole thing secure; for this reason,it is best not to grant permission to run the connect command to normal users (actually, if this would be granted, it wouldn't immediately lead to an insecure situation, but it's safer if it isn't).


  • A separate stored procedure sp_enable_indirect_sa_role is used to enable the role. This procedure should not be executable for normal users, and no execute permissions are therefore granted on it in the installation script. DO NOT change this!
    For the same reason, it is assumed that the owner of the sybsystemprocs database is sa, and that no dbo alias users are created in this database.


  • The stored procedure sp_enable_indirect_sa_role contains the password to enable the role. It is essential that the T-SQL source code of this procedure is hidden through sp_hidetext. DO NOT change this!


  • The sp_*_rpc procedures (such as sp_kill_rpc) are designed to be invoked as an RPC by the proper stored procedures to guarantee security. Some checks have been implemented for this, and one of these relies on the fact that non-SA users cannot read certain columns in the master database. Therefore, make sure any dumps of the master database cannot be copied or 'borrowed' by individuals who could then load these onto their own ASE servers where they are able access that information (but then, this is a basic security issue that applies to the dumps of all of your databases -- you wouldn't want someone to walk away with a dump of your customer database, would you ?).


  • Features like auditing can be used to intercept a security-related parameter passed to the sp_*_rpc procedures. The same applies to certain traceflags. This parameter is part of some security checks, and is expected not to be visible to non-priviliged users. When intercepted, this will not immediately allow a user to break to the security measures, but it is better not to keep this parameter invisible. In fact, both auditing and traceflags require sa-role and/or sso_role, so there shouldn't really be a security problem here.
    Please note that folks with packet sniffers are harder to stop (but if someone is running a packet sniffer on your system, he/she will mostlikely be looking for more interesting information, such the 'root' password...).
Lastly, note that most of the above conditions are satisfied by default in ASE, so in the majority of cases, security will be guaranteed. These issues are listed here to ensure DBAs are aware of these points.

Notes
  • Every time a role with a password is enabled, some log records are written to the transaction log in the master database. therefore, ensure the log of master is truncated regularly (as always, do a database dump of master before truncating its log).


  • Note that the stored procedures discussed above can only be executed by logins to who indirect_sa_role has been granted. This also applies to the sa login; however, this login typically has sa_role anyway so it shouldn't be necessary to execute these procedures.


  • While the mechanism described in this document is quite powerful, some things cannot be implemented this way. For example, most dbcc commands require sybase_ts_role, which cannot be granted to other roles (although this could be achieved by manually inserting a row into master..sysattributes). Still, the main problem is that any output from dbcc commands will only be visible after traceflag 3604 has been enabled; because this traceflag is session-specific, it cannot be enabled for the executing user's session using the mechanism followed by the stored procedures in this document, because the CIS RPC executes in a different process than the originating user.


  • While most of the functionality described in this document will work in ASE 11.5 or later, sp_kill requires ASE 12.0 because it uses the "execute immediate" feature (this was needed because the kill command doesn't accept a variable for the 'spid' parameter).


  • The code has been tested on ASE 12.0, 12.5, 15.x and 15.7


  • All T-SQL commands used in the code are regular ASE features, which are fully documented and supported.
    The only exception (though still harmless) is the way sybase_ts_role is granted to indirect_sa_role in the installation script (details are here).


Installation
To install the stored procedures and user-defined role as described above, follow these steps:
  • Download the code from here; this will get you a file named GRANT_SA.SQL. In March 2013, the code was updated to also work on ASE 15.7


  • First edit the file and change the role password (set to "mysecretpassword" in the downloaded file) to something different; this password occurs various times in the file, so make sure you change all occurrences !


  • Also, remove or edit the line where the login 'an_innocent_login' is granted indirect_sa_role -- this is just an example of how to grant this role to a login; it will generate an error when executed unchanged.


  • Execute this script using "isql", using a login having both sa_role and sso_role.


  • When you're running 12.5.0.3 ESD#1 or earlier, you'll get a message that sp_sqltext and sp_optimizer_trace cannot be installed (followed by some messages about the connection being broken). That's all fine -- read about the details here.


  • Bear in mind that the script you've just executed contains the role password! Therefore, make sure the script is properly protected (or just delete it and keep the original, downloaded version instead).


  • For the installed stored procedures to work correctly, some CIS-related configuration settings need to be set. When these are not set correctly, the user will be notified automatically.
Conclusion
There is a practical need to allow non-SA users to run privileged commands or procedures without giving these users sa_role. While some useful functionality can be created using the stored procedures described in this document, it would be even more useful when the permission mechanism in ASE would be enhanced to allow this type of functionality as a built-in ASE feature. In the end, this would improve the usability of ASE and allow DBAs to be more efficient and more productive.



Procedures that work only in 12.5.0.3 ESD#2 (or later)

In January 2004, two stored procedures were added to GRANT_SA.SQL:
  • sp_sqltext: allows non-SA users to run dbcc sqltext on their own sessions.
  • sp_optimizer_trace: allows non-SA users to execute T-SQL statements while viewing the output of optimizer-related traceflags 302, 310 and 317 (this procedure was written by Steve Bradley).
What's special about these procedures?
These stored procedures are special in that they effectively enable and disable traceflag 3604 inside a single stored procedure. Until 12.5.0.3 ESD#2, this was not possible: enabling 3604 took effect only in the next batch. However, apparently as a side-effect of a bugfix in 12.5.0.3 ESD#2, 3604 can now take effect inside the same batch.
The following code illustrates this:
dbcc traceon(3604)
dbcc sqltext(@@spid)
dbcc traceoff(3604)
go
On 12.5.0.3 ESD#2 or later, these lines of code will display themselves (since that's what your session is currently executing). In contrast, in 12.5.0.3 ESD#1 and before, you'll see nothing except the DBCC execution completed[...] messages -- you'd have to put a 'go' after the first line to get the same effect.

Here's another example. The following lines will execute the T-SQL statement; in 12.5.0.3 ESD#2, the optimizer's 302 output is also displayed, while this won't be visible in earlier versions:
dbcc traceon(3604,302)
exec('select count(*) from sysobjects')
dbcc traceoff(3604,302)
go
Thanks to Steve Bradley for first spotting this changed behaviour!

Installation issues
Since these stored procedures are not useful in pre-12.5.0.3 ESD#2, they are installed only when your ASE server is at least on this patch level. All other procedures described on these pages are installed first, and independent of this particular patch level.
Note that another trick was needed to make sp_sqltext work. dbcc sqltext requires sybase_ts_role, but unfortunately, this role cannot be granted to another role (you'll get an error). I presume this is for security reasons, so as not to inadvertently grant the right to execute dbcc commands through another role.
Fortunately, a workaround/hack does the job anyway: by explicitly inserting a row into master..sysattributes, sybase_ts_role is indeed granted to indirect_sa_role; the installation script handles this. Because the password of indirect_sa_role is unknown (which is precisely the reason why the procedures described on this page actually work safely), this does not constitute any weakening of security, in my opinion. Hence, I believe this is safe.



Another variation on this theme...

Bret Halford of Sybase submitted the following variation on the theme described above. His method allows non-SA users to run optdiag from within ASE by executing optdiag via xp_cmdshell. This is done by a stored procedure in which the username and password for logging into ASE have been hard-coded, and hidden by sp_hidetext.
Instead of using optdiag (which requires sa_role), one could also use Kevin Sherlock's free procedure sp__optdiag, as this produces almost exactly the same output. The difference is that sp__optdiag does not include cluster ratio information (the algorithm for calculating these ratios is proprietary), and by using optdiag via the following method, this information would also be available.
Security considerations: please note that the username and password used for optdiag may be visible via the Unix ps command.

Bret's code goes as follows:
-- !! Please use a different password !!
sp_addlogin "secret_sa_login", "top_secret_password"
go

sp_role "grant", "sa_role", "secret_sa_login"
go

use sybsystemprocs
go

create procedure sp_optdiag @parameters varchar(255) as  
declare @cmdstring varchar(255)
select @cmdstring = 
         "optdiag " + 
         @parameters + 
         " -Usecret_sa_login -Ptop_secret_password -S" + 
         @@servername
exec xp_cmdshell @cmdstring
go

-- Important: this is what keeps the password secret!
sp_hidetext sp_optdiag
go

grant execute on sp_optdiag to public
go

--
-- now execute this as follows:
--
exec sp_optdiag "statistics my_db..my_table.my_col"
go

 
 This document is located at www.sypron.nl/grant_sa.html