SQL Anywhere contains a little-known feature called SETUSER which "[a]llows a database administrator to assume the identity of another authorized user on the same connection".
Here's an example... First, the DBA creates an important table containing an inserted_by column for audit trail purposes, plus two other user ids:
Second, the EvilDoer administrator runs a SETUSER statement to masquerade as InnocentVictim, then inserts a row in the important table:"%SQLANY12%\bin32\dbisql.com"^ -c "ENG=ddd12;DBN=ddd12;UID=dba;PWD=sql;CON=ddd12-DBA" CREATE TABLE DBA.important ( pkey INTEGER NOT NULL DEFAULT AUTOINCREMENT PRIMARY KEY, data VARCHAR ( 30 ) NOT NULL, inserted_by VARCHAR ( 128 ) NOT NULL DEFAULT CURRENT USER, inserted_at TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP ); GRANT CONNECT TO EvilDoer IDENTIFIED BY '6kfiwn3gk'; GRANT DBA TO EvilDoer; GRANT CONNECT TO InnocentVictim IDENTIFIED BY 'nlr9bk6j'; GRANT DELETE, INSERT, SELECT, UPDATE ON DBA.important TO InnocentVictim;
The four SELECT statements show how SETUSER changes the effective CURRENT USER value to and from 'InnocentVictim', and how the row in the important table looks like it was inserted by InnocentVictim:"%SQLANY12%\bin32\dbisql.com"^ -c "ENG=ddd12;DBN=ddd12;UID=EvilDoer;PWD=6kfiwn3gk;CON=ddd12-EvilDoer" SELECT CURRENT USER AS "Before"; SETUSER InnocentVictim; -- start masquerading SELECT CURRENT USER AS "During"; INSERT DBA.important ( data ) VALUES ( 'im in ur base killin ur d00dz' ); COMMIT; SETUSER; -- remove mask SELECT CURRENT USER AS "After"; SELECT * FROM DBA.important;
Before -------------- EvilDoer During -------------- InnocentVictim After -------------- EvilDoer pkey data inserted_by inserted_at ---- ------------------------------ -------------- ----------------------- 1 im in ur base killin ur d00dz InnocentVictim 2013-04-28 09:21:40.591
No bias here! <g>
The names "EvilDoer" and "InnocentVictim" were chosen to emphasize a security loophole created by SETUSER: the ability of one single user to impersonate another user without knowing that user's password, and without requiring the collusion of anyone else.Permissions aren't the issue here; both EvilDoer and InnocentVictim can INSERT anything they want in the important table.
The problem is that EvilDoer can insert a row and pretend it was InnocentVictim that did it... the audit trail is now worthless because the CURRENT USER special value is no longer guaranteed to return the current user.
Why does SETUSER exist at all?
Historically speaking, the stated purposes for SETUSER include the following:- Creating objects You can use SETUSER to create a database object that is to be owned by another user.
- Permissions checking By acting as another user, with their permissions and group memberships, a database administrator can test the permissions and name resolution of queries, procedures, views, and so on.
- Providing a safer environment for administrators The database administrator has permission to perform any action in the database. If you want to ensure that you do not accidentally perform an unintended action, you can use SETUSER to switch to a different user ID with fewer permissions.
- CREATE TABLE can specify the owner without having to use SETUSER,
- one person can have multiple user ids with different privileges, without having to impersonate a different person, and
- the CONNECT USING statement is almost as easy to use as SETUSER when switching to a different user id, and safer too because it requires a password.
We report, you decide
In SQL Anywhere Version 12, the Help doesn't promote SETUSER other than providing the basic syntax topic, and it doesn't use the word "impersonation" at all, at least not in the context of SETUSER.All that has changed in Version 16; "Impersonation" is now a huge topic, with a whole new reason for SETUSER to exist:
"Suppose a data entry clerk, JSmithClerk, is having difficulties performing an operation in the database. He is talking with PJonesIT in the IT department, and PJonesIT decides to impersonate JSmithClerk to observe and troubleshoot the problems that JSmithClerk is experiencing. In order for PJonesIT to impersonate JSmithClerk:" and so on ...plus a whole new set of rules and regulations governing SETUSER.
In other words, SETUSER has been released from its cage (documentation obscurity in Version 12 and earlier) and its days as a "little-known feature" may be numbered.
One argument in favor of SETUSER goes like this: "DBAs can do anything, even directly UPDATE the inserted_by column, so SETUSER is no big thing."
That's true in Version 12 and earlier, SETUSER can only be used by DBAs. In version 16, however, the new role-based access control (RBAC) security model separates the SET USER privilege from other administrator privileges. By using the new GRANT SET USER statement, plus a view to limit direct access to the inserted_by column, the EvilDoer no longer has to be a DBA. The following example shows how EvilDoer can be set up as an ordinary user who can still impersonate another user:
CREATE VIEW DBA.Vimportant AS
SELECT pkey,
data
FROM DBA.important;
GRANT CONNECT TO EvilDoer IDENTIFIED BY '6kfiwn3gk';
GRANT SET USER TO EvilDoer;
GRANT DELETE, INSERT, UPDATE ON DBA.Vimportant TO EvilDoer;
GRANT SELECT ON DBA.important TO EvilDoer;
GRANT CONNECT TO InnocentVictim IDENTIFIED BY 'nlr9bk6j';
GRANT DELETE, INSERT, UPDATE ON DBA.Vimportant TO InnocentVictim;
GRANT SELECT ON DBA.important TO InnocentVictim;
That's true, but it raises the bar (or lowers the bar, if you prefer limbo dancing to pole vaulting) on preserving evidence... first you have to save all the transaction log files, then you have to guarantee you're not missing any, and then you have to plow through them all.
Here's a little story about searching transaction logs: In 2006 I was engaged as a "Special Master" by the Southern District Court of Florida, Miami Division, in the copyright infringement suit "Dunn and Dunn Data Systems v. EncomPos Software LLC". Part of my job was to answer the questions "what did they do and when did they do it", and that involved searching through endless backup copies of SQL Anywhere transaction logs from multiple databases... let's just say I had to repeatedly remind myself of the Consultant's Golden Rule: If They Want To Pay Me THIS Much Money To Pick Cigarette Butts Out Of The Urinals, I'll Do It With A Smile. The point is, searching for evidence in transaction logs is a lot harder than querying a application-level audit trail.
"How do I get rid of SETUSER?"
You can't, not in SQL Anywhere Version 12 and earlier. Somebody has to be DBA, and that person can use SETUSER, so that person can impersonate anyone.In Version 16, however, there might be a way to create a database where even the administrators can't use SETUSER.
Or maybe not... REVOKE SET USER doesn't seem to have any effect after GRANT DBA:
In other words, DBA2 can still execute SETUSER... so much for brute force.GRANT CONNECT TO DBA2 IDENTIFIED BY 'h5lfhg86jd'; GRANT DBA TO DBA2; REVOKE SET USER FROM DBA2;
Three possible explanations come to mind:
- I'm missing something, or
- GRANT DBA is old-school, and there's something in the miles and miles of new rules and regulations explaining why REVOKE SET USER only works after you have
drunk the Kool-Aidfully committed to the new security model, or
- it's a bug.
