In speaking with customers lately, I am finding more and more attention spent on the lineage of data. It isn’t enough these days to manage these type of complex movements and manipulations of data with spreadsheets. More often than not, I am finding customers asking for the golden nugget feature of being able to reverse engineer the data lineage in a visual manner. This is a highly complicated request. It is not enough these days to just reverse engineer a database and call it documented. More and more, people need to understand and document the movement of data. I find it interesting we have a common way to exchange meta data via CWM, but would’nt be great to have a common meta model for exchange lineage and visualizing that in a simpe form. We, meaning, Embarcadero Technologies have been listening to our customers and are investing in these types of requirements. Our data modeling product ER/Studio today can not only reverse engineer the schema but enables users to document the data lineage visually. Documenting the lineage regardless of transformation syntax provides great optics in the flow of data by the data experts. Until the golden parser is evolved in a manner to reverse engineer visual data lineage, it is a great start to understand impact and garner more interaction as lineage develops. Stay tuned as we continue to innovate and evolve around this visual data lineage life cycle.
I wanted to follow up my previous blog and dig deeper into database server parameters. Today, there are a number of highly sensitive parameters within each DBMS that, depending on their value, can leave the databases or database servers vulnerable to various types of malicious activity. Some key parameters for SQL Server and Oracle are outlined below. Also included are explanations of why these parameters are sensitive, and why an organization might want to standardize on a particular value.
· xp_cmdshell = 0 (Disabled) –this is an extended stored procedure that allows you to execute a command line OS command from within the database (e.g. using T-SQL). This can be a serious security risk, because someone could potentially corrupt your system or escalate their privileges to sysadmin or box administrator. This was enabled by default in SQL Server 2000. Microsoft changed the default to “disabled” in SQL Server 2005.
· OLE Automation Procedures = 0 (Disabled) – These system stored procedures allow the use of COM objects (http://www.microsoft.com/technet/scriptcenter/guide/sas_vbs_wcmr.mspx?mfr=true) within T-SQL (i.e. someone can open a computers File System and open, process, read, and write files). This can also be used to access & manipulate OS objects from inside the database, which could pose a threat or crash the server. This is now disabled by default.
· remote admin connections= 0 –The setting of this parameter determines whether or not the SYSADMIN server role can use the Dedicated Administrator Connection (DAC) to access SQL Server remotely. Organizations concerned that the SYSADMIN server role has been compromised should leave this parameter disabled.
· C2 audit mode= 1 –Enabling this parameter will configure the server to record both failed and successful attempts to access statements and objects. This information can help you profile system activity and track possible security policy violations. Note: this parameter is often disabled for performance reasons.
- remote_os_authent = FALSE– When using OS authentication (remote or local), authentication occurs at the OS level and then the user connects to the database. When connecting to the database the user does not have to supply a password and Oracle maps the database user to the OS user based on user name. Allowing remote OS authentication (setting parameter value to TRUE) exposes a database to great security risk. The risk is that someone can connect to the network with a user name that maps to a DBA, and gain entry with no authentication being done on the database server.
· 07_dictionary_accessibility = FALSE — Setting this parameter to FALSE ensures that users with the ANY privilege are restricted from SYS schema objects (data dictionary objects). These users will still have access to all other schema objects. Setting this parameter to FALSE prevents users with the “drop any table” role from maliciously dropping parts of the data dictionary. For Oracle 8 the default is TRUE and for Oracle 9 and up the default is FALSE
· audit_sys_operations = TRUE – Organizations that need to audit users with SYSDBA or SYSOPER privileges will need to enable this parameter. When set to TRUE, all actions by SYSDBA and SYSOPER users are logged in the operating system file. Note: setting this parameter to TRUE can cause a significant performance hit on the database server. The default value is FALSE.
- remote_login_passwordfile= NONE — Password files allow you to store passwords outside of the database which in turn are used for authenticating administrators (SYSDBA and SYSOPER). The remote_login_passwordfile parameter controls if and how the password file will be used by remote administrators. The parameter value can either be NONE, EXCLUSIVE, or SHARED. NONE restricts the use of the password file; EXCLUSIVE ensures that the password file can be used by only one database; SHARED allows the password file to be used by multiple databases. The security risk of enabling this parameter (SHARED or EXCLUSIVE) is that passwords are vulnerable to theft. Organizations that want to protect against this should set this parameter to NONE.
· audit_trail= NONE – the audit_trail parameter turns auditing on or off. Default is NONE which disables auditing. In most cases you probably want to make sure it stays NONE, as this can incur massive amounts of logging and slow things down. If auditing is desired, there are many possible parameter settings: OS– Oracle writes the audit records to an operating system audit trail; DB – Oracle directs the audit trail to the SYS.AUD$ table; DB,extended – similar to the db setting, but adds two extra columns, SQLBIND and SQLTEXT, to the SYS.AUD$ table; XML – writes audit records to the OS audit trail in XML format; XML,extended – similar to the XML setting, but it includes SQLTEXT and SQLBIND values.
· os_authent_prefix (default is OPS$) — The os_authent_prefix parameter defines a prefix that an Oracle database will append to the front of every operating system username. Oracle will then compare the prefixed OS username with the database username when a user attempts to connect. The provided SQL Server Security Standards Template can be easily used to identify OS authentication prefixes that do not comply vis-à-vis the standard.
The evolution of the DBA in the past 10 years is astounding. That evolution doesn’t follow a linear growth pattern either. I would say it is more of an 89 degree angle. Every time I am speaking to a DBA team, I always ask who is managing less today than they were a year ago. And that question returns nothing but laughs. It is true.
As DBA’s we have more on our plates than ever. We could start with just the ratio of DBA to database support. I typically hear that ratio as each DBA supports between 50 – 80 databases. An added complexity is that those aren’t all on the same platform nor same database version and patch level for that matter. To add just a little more complexity, I add database compliance. There is more red tape around who, what, when, and why than ever. Just managing delegation of duties becomes a matrix larger than any cares to admit. The fast paced environments and ongoing projects continue to ramp at exponential paces. This all seems daunting but it is truly all about the process and tooling. As I said in an earlier post, the tooling is of course the easiest to solve. Tooling to a DBA is about supporting a complex heterogeneous environments with as few interfaces as possible.
That of course is where the cult-like following of DBArtisan (Heterogeneous Database Administration software) began. It is all about having a single interface to be the ultimate dashboard for the enterprise. The tools must be flexible and maintain the db currency to support the entangled database compliance work flows and processes. Once the correct tooling is established the red tape can be broken down and compliance procedures can be established. The book of regulation and compliance is indeed large but there for our protection. And that protection comes with many rules and interpretations of how, what, and why. That is a much larger discussion but I will continue to break this topic down in my upcoming blogs.
My upgrade to Windows 7 is complete! It is always the small things that hold the bigger projects up. And yes, my “small thing” caught me off guard. I use an air card for a lot of my customer presentations and low and behold it does not come packaged for Windows 7. Nor could I find the drivers anywhere on my carriers website. So where does one turn to next? Newsgroups and search engines. After a few searches I found the correct Windows 7 drivers that weren’t from the carrier but from the makers of the air card. I downloaded and installed and finally I am able to hit the information highway in my hotel, and airports, and customer sites. The point of this ramble, is planning. Just when you thought you had planned for everything small items keep arising from this simple upgrade. I will not even bore you with my 2 print driver conflicts. So in restrospect, the matrix of supported applicaitons, drivers, etc, can easily appear limitless. How does an organization start the matrix of support to plan an upgrade such as WIndows 7. I think starting at consolidating as many applications and licensing is a good start. Beyond that, I believe it will be a trial and error, lots of reading and research, and POC to get there. We will all get there and be very happy we did, it is just the “small things” that cause the biggest frustration.
I recently made the leap of faith to Windows 7. The install was painless, but installing all the applications that I use on a daily basis is where the “fun” started. I am a week into my Windows 7 experience and I’m still trying to get my “toolbox” completely installed without conflicts. Some products install smoothly, some in compatibility mode, and items like SQL Server Express not at all. I would classify myself as a very literate PC user and I cannot imagine the planning large organizations must be going through with their compatibility of supported vs. conflict matrix. How can they ensure the rollout of mission critical developer and database tooling is seamless and without error?
This certainly makes me appreciate the efforts of my own company, Embarcadero Technologies. We sell 19 different products, all of which I can use daily without having to install! That’s because we have an innovative approach to deployment called ToolCloud that makes accessing, managing, and sharing tools nearly frictionless. With ToolCloud I can spin up tooling on projects in seconds vs. weeks. And the same technology would offer a huge time saver for those doing Windows 7 testing against their thousands of in-house applications.
In a past life, I helped manage desktop images and profile rollouts, and know what a challenge the task can be. With so many moving parts, it only takes one product update to ruin months of process. With frictionless deployment, solving these installation challenges becomes much more manageable. But I will leave the entire conversation of managing licensing for another day.
Hi, my name is Brent Hansen and I am a software consultant manager at Embarcadero Technologies. I spend most of my days and weeks traveling the U.S. listening to DBAs, developers, architects and IT managers express their frustrations, challenges, and wishes about the software that runs their business. This is what I refer to as “tooling”. In this ongoing blog I am hoping to share my experiences from the trenches and hear your stories about this fast-paced technical industry that I really enjoy and am addicted to.
Implementing a data warehouse project is huge undertaking for a business. Key decisions need to be made upfront. Which hardware, O.S., database platform? How will virtualization effect our decision? How many resources are needed to meet the deadline? Where is the data? How will we analyze the lineage of data? All these heavy questions take a lot of planning and decision making. Then, once these project starts, how do you ensure communication, efficiency, optimization, and commonality?
Just recently, I was speaking with a customer who was trying to keep the wheels on a DW project with each team in separate geographic locations using different design products with no collaboration? They spent more time generating reports and asking if those reports were the most recent than moving the project forward. It is ironic to me in these expensive projects, the investment tools and standardization make all the difference. The planning, resources, and tools are the recipe of success. Many people spend more time in proof concepts on platform than choosing the tools that do all the heavy lifting in these DW efforts. I would go as far as to say that in many projects tools are the last and least expensive part…but possibly the part of the recipe that can most easily be set for success. Ensuring the tools of choice for these highly complex DW projects is absolutely key. Aligning yourself with a toolset that can enhance process and methodology is key.
There is nothing more frustrating than deviating from the plan because of a tool inefficiency or roadblock. Due diligence in tooling makes for peace of mind 6 months, 12 months, 18 months down the road. It is vital that the tool supports the process not the exact opposite. In my experience toolsets that are progressive, agile, and flexible in their support for heterogeneous environments is a must. Tools can either help be a catalyst in finishing projects on time or the hindrance. It is all about the tools.