Can Stealthbits Solutions also provide data security access solution to datawarehouse – data lake with both structured and unstructured data.
Database security is a hot topic these days, especially with all the new and seemingly never-ending security compliance requirements being imposed such as GDPR. This means that organizations and their DBAs must step up their game when it comes to database security. Some DBAs may think these new requirements apply only to production but depending on the situation DBAs may well need to apply stricter security across the board – including development and all test databases (e.g. unit testing, stress testing, and acceptance testing).
This blog series will cover some very simple database security checks which are easy to deploy, and which will start you on the road to better security. Throughout this blog series, I will show examples using different databases, including SQL Server, Oracle, and PostgreSQL. In this first blog in the series, I used Oracle. However, all the security concepts and thus techniques should apply equally well across all other database platforms. You may just need to translate the concepts and example scripts shown here to your specific database platform.
Each week I will post between four and six simple, yet worthwhile database security ideas. Whether you implement all or even just some of these ideas you will effectively harden your database. That’s always a decent first step in improving overall database security. Here is the first batch of ideas.
For Oracle, the SYS schema owns the data dictionary (i.e. the catalog tables and views). Other database platforms often separate the data dictionary into its own distinct database. Basically, you should never connect to Oracle as SYS unless performing special tasks such as patches, version upgrades or running $ORACLE_HOME/rdbms/admin scripts which specifically document that they require SYS access. In fact, Oracle’s Database Administrators Guide very clearly states: “Ensure that most database users are never able to connect to Oracle Database using the SYS account.” Yet many people treat SYS as some sort of general-purpose DBA or root account and routinely connect as SYS as shown in Figure 1 and Figure 2. Connecting to Oracle as SYS should be the exception and occur so rarely as to be a non-issue. In short, do not connect To Oracle as SYS unless you have a legitimate requirement to do so.
The Oracle SYSDBA system privilege permits granted users to perform some very basic DBA commands related to fundamental database instance management – namely:
Yet once again many people seem to think that connecting to Oracle as SYSDBA provides quick and easy general-purpose DBA rights and therefore they routinely connect as SYSDBA as shown in Figure 3 and Figure 4. If I really want to connect as the DEMO user and have general purpose DBA type privileges, then I really need to grant whatever roles and/or privileges I desire the DEMO user to have. And I don’t mean simply granting the DBA role, but that’s another issue covered next. Remember, just connecting to Oracle as SYSDBA does not infer nor confer general purpose DBA type rights – just the subset required for the most basic database instance management commands. Therefore you could connect to Oracle as SYSDBA and find that you cannot do the things you expected – and that’s by design.
There are three pre-defined Oracle roles for version 7.X backward compatibility: CONNECT, RESOURCE, and DBA. Academically speaking these three predefined roles should simply be avoided, or better yet dropped entirely from the database. However, people have become far too accustomed to using them, especially DBA, even though Oracle’s Database Security Guide states “Oracle recommends that you design your own roles for database security rather than relying on this role. This role may not be created automatically by future releases of Oracle Database.” Ideally, you should create your own DBA-type roles such as JUNIOR_DBA, MASTER_DBA, and DEVELOPER_DBA. That way you can better control who gets what privileges. Because do you really want all DBA users and their connections using the predefined DBA role and therefore having all 237 system privileges and 18 system roles as shown in Figure 5. And that’s the number for Oracle 12c, with Oracle 18c those numbers are much higher.
Some database user tools are so useful and instrumental that it’s not uncommon for people to have them running from sun-up to sun-down. But leaving any software running on your desktop when you step away is inherently unsafe. Even with screen savers requiring a password to unlock, it’s still not entirely safe. Furthermore, we routinely have people who leave such useful apps not just running unattended overnight, but across weekends and even vacations – again, this is a highly insecure but common practice. There is no mechanism within most end-user database tools to force idle people to log out, but Oracle provides this capability via a mechanism known as profiles. So security minded DBA’s should strongly consider implementing Oracle profiles. With a profile the DBA can limit their idle time, the number of concurrent connections, password management policies, and basic database resource limits.
Let’s assume that we want to limit SQL*Plus command line users (e.g. DEMO) to no more than eight concurrent database sessions (i.e. simultaneous logins) and to timeout their session when idle for 60 minutes or longer. We simply need to create an Oracle profile like the one shown below which can enforce such security policies as shown in Figure 6 where an idle user has been disconnected.
CREATE PROFILE SQLPLUS_USER LIMIT SESSIONS_PER_USER 8 IDLE_TIME 60; ALTER USER "DEMO" PROFILE SQLPLUS_USER;
NOTE – this requires setting the following Oracle spfile (init.ora) database configuration parameter:
RESOURCE_LIMIT = TRUE (default is FALSE)
This was just the first part of a series which I expect to run for several weeks as there are many more database security best practices to share. So look for the upcoming parts of this series for a complete security checklist. Sign up below to be notified of my upcoming posts!
At the end of this series, I will be wrapping it up in a live webinar.
Bert Scalzo is STEALTHbits Technical Product Manager for databases. He’s an Oracle ACE, blogger, author, speaker, and database technology consultant. He has BS, MS and Ph.D. in computer science, an MBA, and has worked for over 30 years with all major relational databases, including Oracle, SQL Server, DB2 LUW, Sybase, MySQL, and PostgreSQL. Moreover, Bert has also has worked for several of those database vendors. He has been a key contributor for many popular database tools used by millions of people worldwide, including TOAD, Toad Data Modeler, ERwin, ER/Studio, DBArtisan, Aqua Data Studio, and Benchmark Factory. In addition, Bert has presented at numerous database conferences and user groups, including SQL Saturday, SQL PAAS, Oracle Open World, DOUG, ODTUG, IOUG, OAUG, RMOUG, and many others. His areas of interest include data modeling, database benchmarking, database tuning and optimization, “star schema” data warehouses, Linux®, and VMware®. Bert has written for Oracle Technology Network (OTN), Oracle Magazine, Oracle Informant, PC Week (eWeek), Dell Power Solutions Magazine, The LINUX Journal, LINUX.com, Oracle FAQ, and Toad World. Moreover, Bert has written an extensive collection of books on database topics, focusing mainly around TOAD, data warehousing, database benchmarking, and basic introductions to mainstream databases.
Adopting a Data Access Governance strategy will help any organization achieve stronger security and control over their unstructured data. Use this free guide to help choose the best available solution available today!Read more
Start a Free Stealthbits Trial!
No risk. No obligation.