oracle dba account - connect through

The system user has the dba role granted. On most systems this user is regarded as an internal oracle admin user not used by administrators unless you have to. Administrators should have their own named users with all the necessary rights granted.

If you for some reason want to lend someone an admin user without giving them the password you can use this trick. Shown with user system, but it works for any user.

SQL> create user kpe identified by kpe;
User created.
SQL> alter user system grant connect through kpe;
User altered.
SQL> connect kpe[system]/kpe
Connected.
SQL> show user
USER is "SYSTEM"
SQL> alter user system revoke connect through kpe;
SQL> drop user kpe;

GDPR - example 1

The previous post will give you a quick intro to this topic.  http://kp-dba.blogspot.no/2018/02/gdpr-practical-approach.html

Please note that this is not a product. It is my own practical approach. It takes quite a bit to dig out all the plugins you need. What you see here is just examples and meant as inspiration.

There's also lots of commercial software that can help you out.

The most important part of this job is to locate all relevant information from underlying systems. The overview will never be better than the program that presents it. Obviously, you can add plugins crawling across several data sources, to find documents, signatures, etc., but you don't start there. As mentioned earlier, I have chosen to use MySQL. It has the functionality I'm looking for. In addition, it is free and can be installed on many platforms. What am I looking for?

• Good support for character sets

• Easy handling of data in / out of db via the file system

• Generally good functionality and easily recognizable

Character sets are important. When you retrieve data from sub systems, the output encoding can vary. If possible, specify character set by retrieval. Keep in mind that the information will be loaded into a new data model and special characters must be represented correctly.

See below for two small examples of extracting data from subsystems. You might consider installing a cron job / scheduled task to refresh the lists on a regular basis.

List of inactive AD accounts:

File inactive-users.bat:
   powershell.exe -ExecutionPolicy ByPass -file inactive-users.ps1

File inactive-users.ps1:
import-module activedirectory
$domain = "your-domain"
$DaysInactive = 365
$time = (Get-Date).Adddays(-($DaysInactive))
# get AD Users with LastLogonTimestamp less than 365 days
Get-ADUser -Filter {LastLogonTimeStamp -lt $time } -Properties LastLogonTimeStamp,Name,Company,Department,SamAccountName |
# output to csv
select-object Name,Company,Department,SamAccountName,@{Name="Stamp"; Expression={[DateTime]::FromFileTime($_.lastLogonTimestamp).ToString('yyyy-MM-dd_hh:mm:ss')}} | export-csv inactive-users.csv -notypeinformation -encoding utf8

List of users from one of your applications:

ORAENV_ASK=NO
ORACLE_SID=your-db
ORACLE_HOME=your-oracle-home
. $ORACLE_HOME/bin/oraenv
sqlplus /nolog <<eol|grep "tag:"|awk -F "tag:" '{ print $2 }' > app-user-list.csv
  connect user/pw
  select 'tag:'||user-id||','||full_name||','||company||','||days_since_last_login from users;
  exit;
eol

You now have some nice comma separated files that represents a small selection of what you're up to. Now its time to load those files into MySQL. Be sure to set the character set and spot check your data. If you think you have missed out on fields containing useful information, then you must add this field and reproduce your csv file.

• create table ad (Name varchar(60), Company varchar(70), Department varchar(40), SamAccountName varchar(40), Stamp varchar(30));

• load data local infile 'inactive-users.csv' into table ad character set utf8 fields terminated by ',';

• show warnings;

• check the content: select * from ad;

• same procedure for app

MySQL is, by default, case insensitive, but if your system is case sensitive, you can always use the lower and upper functions. The tables now represent the files you have extracted from your sub systems. Dependent on the where clause used in the plugin you now might find some weird rows,  mistakes from the production environment, mistypings, users intended for courses that was never held and so on. But as you have loaded logon data from several sub systems you should be able to connect them. Really, what you want to find is all digital traces for one specific person. You have to get familiar with the data and look for connections. If there are no connections you can make them. Create a table that link together sub systems. To find your entries you can make your queries wider. If you intend to reload the tables representing the sub systems you should be careful altering them. Create new tables or views to add the extra functionality you need. Here in this example I assume that the windows short name (samaccount) is the same as the one used in the app. If this is not the case you have to build a mapping table and join this to sub select with the correct id.

• To list inactive users: select * from ad;

• To list inactive users still registered in app: select * from ad where samaccountname in (select user-id from app);

• You might want to add functionality to days_since_last_login

A simple php sample presenting inactive users still registered in your app could look something along the lines of:

<!DOCTYPE html>
<html>
<head>
<style>
table, th, td {
    border: 1px solid black;
}
</style>
</head>
<body>

<?php
set_include_path('dir ref to your php7 modules');
$servername = "localhost";
$username = "your-acc";
$password = "your-pw";
$dbname = "your-db";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

if (!$conn->set_charset("utf8")) {
    printf("Error loading character set utf8: %s\n", $mysqli->error);
    exit();
}

$sql = 'SELECT * from ad where SamAccountName in (select user-id from users) order by stamp';
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    echo "<table><tr><th>Name</th><th>Company</th><th>Department</th><th>SamAccountName</th><th>LastLogin</th></tr>";
    // output data of each row
    while($row = $result->fetch_assoc()) {
        echo "<tr><td>" . $row["Name"]. "</td><td>" . $row["Company"] . "</td><td>" . $row["Department"] .  "</td><td>" . $row["SamAccountName"] . "</td><td>" . $row["Stamp"] . "</td></tr>";
    }
    echo "</table>";
} else {
    echo "0 results";
}

$conn->close();
?>

</body>
</html>

GDPR - a practical approach

GDPR is an EU regulation that will strengthen the control we as individuals will have over our own digital information. It will also limit the amount of information businesses can store about us. Most businesses need to adjust to be compliant with the regulation. I do realise that GDPR is much bigger than what is discussed here. As an IT person I will focus on the applications and personal information stored in those systems. What does this mean in real life? Well, first of all, lets starts with the obvious, formed as questions:

• Do you have procedures for removing customers or former employees from all internal systems? IE mail, home areas, access to applications, etc.
• Do you have obvious candidates in your systems for removal
• Do you have applications that does not allow deletion of users / customers? If so, are there any procedures for making those users anonymous?
• Can you find all digital traces from any given person registered in your systems?
• Do you perform regular checks against your central repository to capture users who will be candidates for deletion?
• Are your security policies compliant with the regulation?
• Is it natural to send mail directly to the user, with information about what type of information you have stored. And maybe, if the user has been inactive for a long time, offer an unsubscription?
• How good are your procedures? Will they survive an audit?

How many times have you logged in to a web shop and found that they have an impressive amount of information about you. It's nice to not key in all this information every time, but at the same time it's also part of a threat. For how long can they keep this information? Customer lists and passwords are sold and shared, and sometimes also abused.

What about the dentist or physio therapist? Maybe you want to switch to a new one? Are they able to transfer data about you to the new physio therapist? Do they clean up their own systems? Banks and insurance companies are better at this. They can easily move a client from one company to another. Whether they clean up afterwards, well, that's another story.

Either way, you need to get started. Maybe you want to buy stuff. Or, maybe you want to collect information to find a status, a starting point. What this is about is to make your own plugins and let the system tell you what it looks like. To do that you need someone who can extract information about users in all relevant systems. And maybe also look up the things you initially did not think of. This is typically someone in your IT department who can find the underlying systems and extract the user information you need. Once you have found the information you need, you must assemble this and load it into a separate data model. Here you can either just create queries and retrieve information directly. Or  make some simple reports. They can come in handy if you need some extra manpower to clean out all the old fun.

I have created a prototype with some examples of how to proceed. I chose to use Apache, MySQL and PHP on Linux, but that is not important here. Choose anything you think will do the trick. I will later show bits and pieces of what I have done. Then I recon you will get an idea of how you can do something similar. Please join in for a discussion.