Prompted by James Koopmann's post about default Oracle accounts.
"The good thing here is that many of these accounts, after a normal installation are expired and locked"
Being blunt, "EXPIRED & LOCKED" does not mean safe, unbreakable, unusable or ignorable.
EXPIRED means that you cannot use the account password to access the account. My trick from last week would still allow access through a proxy account.
LOCKED is a bit better, as is lacking a 'CREATE SESSION' privilege. But even if FRED is locked, nothing stops user BARNEY using any available grant to run a procedure owned by FRED, and if that procedure is the default DEFINER RIGHTS, then it runs with an privilege granted to FRED. This is why SQL injection vulnerabilities in code owned by these LOCKED schemas are still dangerous.
But there's more...
Lets have some fun with APEX.
Brand new user, nothing up my sleeves.
create user exp_test identified by exp_test default tablespace users quota unlimited on users;
grant create table to exp_test;
grant create trigger to exp_test;
grant create procedure to exp_test;
grant create sequence to exp_test;
grant create view to exp_test;
Five basic privileges and not even as many as recommended in the documentation.
Those privileges are sufficient to create an Apex workspace for the schema though. A Workspace has an ADMIN user and you can log into that workspace as the ADMIN user. You can have other users (developers or plain, apex-authenticated users) on the workspace too.
You can then take your apex-authenticated admin or developer and go into APEX's SQL Worksheet and execute SQL as the schema user. Try it. No CREATE SESSION required. Now try
alter user exp_test account lock;
alter user exp_test password expire ;
Still accessible through Apex. No worries about the account being locked or passwords being expired.
I've done that using XE with Apex 4.0 and the embedded PL/SQL Gateway. Obviously the account you use to connect to the database needs to be unlocked, with CREATE SESSION and an unexpired password.
But beyond that Apex uses magic (or rather, I believe, DBMS_SYS_SQL). You can log out of one apex application running in a workspace on schema FRED, log into another running against BARNEY and it's all using the same set of database sessions. The apex builder/administration application is all running in Apex as well, on the same connections, with all its majestic power.
So an expired and locked account still has enough oomph to bite your legs off.
6 comments:
Hi Gary,
I imagine it's done via ALTER SESSION (a privilege which apex 4.0 has ) rather than DBMS_SYS_SQL which APEX 4.0 doesn't have execute privileges on.
Good catch. Having a few problems with this whole rigmarole and SQL Worksheet at the moment...
This is the generic problem of a product subverting the security of the underlying database engine. It happens with a lot of others, not just Apex.
What could have possessed the Apex designers to come up with their own logins, rather than simply going through the Oracle security and creating/dropping users as needed?
What, the product is gonna be "portable" now?...
Niall,
While Apex doesn't have permissions on DBMS_SYS_SQL, it does have XMLDB as a pre-requisite.
http://download.oracle.com/docs/cd/E17556_01/doc/install.40/e15513/pre_require.htm#BABICIHB
And XMLDB does have execute permissions on DBMS_SYS_SQL.
While the ALTER SESSION command can change the current schema, there's nothing in the ALTER SESSION privilege that would give a database session connected as FRED (or ANONYMOUS or APEX_PUBLIC_USER) to perform DML or DDL as a different user.
Noons,
The classic Oracle security model of passwords tied to a user tied to a schema tied to a bunch of SQLs in the shared pool is, I think, outdated. It is hard to scale to hundreds of concurrent users, let alone more.
Hard parsing the same query for FRED as for BARNEY when they are both have the same roles and privs just doesn't cut it any more.
That said, I'd LOVE to see the equivalent of the Network ACL security model applied to DBMS_SYS_SQL. That way, you could grant DBMS_SYS_SQL just for specific target schemas the same way as you can grant UTL_HTTP for specific domains.
Probably have to expand this in another post.
Along a similar vein, I generally do *not* lock accounts, but set their passwords to something that can never be entered by the user. eg
alter user BLAH identified by values 'not-possible';
The justification for this is that someone snooping around for access to your db, will get:
ORA-28000: the account is locked
for a locked account. Straight away - they now know that account exists, possibly opening up an avenue to exploit that account in some way.
With the impossible password, they will get:
ORA-01017: invalid username/password
which yields no such clues
Cheers
Connor
Actually, I've been probing further. Looks like the missing link is "WWV_DBMS_SQL", which is a SYS owned package with a dependency on DBMS_SYS_SQL which has EXECUTE granted to APEX_040000.
Oh, and contains PARSE_AS_USER as a procedure
Post a Comment