Voice of the DBA

Tag: syndicated

  • The Principle of Least Privilege – Skill #3

    This series of blog posts are related to my presentation, The Top Ten Skills You Need, which is scheduled for a few deliveries in 2011.

    Secure by Default

    SQL Server doesn’t give logins or users any rights by default. That means when you add a login or user to the SQL Server instance, the user cannot access any of the data or objects in the instance until you grant rights.

    That’s not the model that so many people have learned in many applications where once a user has access, they can view anything. This leads to many administrators and developers thinking something is wrong when they create a new login and data cannot be accessed.

    So they start by granting one of two rights initially: sysadmin or db_owner.

    That’s a huge mistake, and leads to security issues down the road if the database contains any type of sensitive information.

    The Principle of Least Privilege

    There’s a security tenet that is known as the principle of least privilege. This essentially means that any user is only allowed to the minimum amount of access needed to accomplish their job. A few examples of what this means in practice:

    • If a user is supposed to only use the HR application to add new employees, they shouldn’t have administrator access.
    • If a web application provides read only views of sales data, the account it uses to access SQL Server should only have read (SELECT) access, and no rights to change data (no INSERT/UPDATE/DELETE)
    • A manager that only maintains an employee’s address information in a self service situation should have read/write access to the address data, but not the salary data, name data, or any other employee data.
    • A developer that is allowed to back up a particular database from the production systems to restore this on the development server should not have system administrator access to production. They should have backup rights only for the database(s) the developer needs.
    • An auditing application that writes to an audit table needs INSERT rights on the table, but not UPDATE, DELETE, or SELECT.

    There are many more examples, but the basic idea is that you grant the rights needed, not every right.

    In Practice

    It feels like a lot of work to deal with roles, or think about the rights needed. It’s really not. Set up a role when someone needs access and grant the rights they need to that object. If they need more rights, grant more rights.

  • SQL Server Backups – When is it current?

    I saw a post recently where someone was asking about the restore sequence for a series of backups. The scenario was this:

    1. Full backup starts at 3:00am, and takes 30 minutes
    2. Log backup 1 starts at 3:05am, and takes 2 minutes
    3. A second log backup starts at 3:35 and takes 2 minutes

    What do you restore?

    The short answer is that it doesn’t matter. If you use NORECOVERY (Always use NORECOVERY) and restore the logs in order, SQL Server will sort things out. If the transactions from backup 2 (the log backup from 3:05) are in the full backup, they won’t be applied twice and the system will let you know.

    The same thing occurs for the second log backup. This is why SQL Server uses the Log Sequence Numbers. They ensure that SQL Server can track which transactions occurred when and in which order.

    When is the full backup consistent?

    If we are wondering when the full backup is complete, or at what point during your system’s life is the full backup going to return you to, it’s easy.

    It is consistent as of the time when the data reading portion of the full backup is complete. We don’t necessarily know when that is, but at that point, the full backup will copy enough log records to get consistent to that point in time. If it’s a lot of log records, it’s possible that this is quite a bit of time before the backup completes and the timestamp goes on the backup file.

  • Computed Columns and UDFs

    I wrote about the basics of computed columns and also using CASE in a computed column recently, but there’s a better way to implement a computation, and reuse the code. You can include a UDF in a computed column.

    UDFs are a great way to encapsulate your code into an object that can be included in stored procedures or even computed columns. The basic definition of a UDF is that it’s a function (as in other languages), but it’s designed to be included in other code, unlike a stored procedure. They’re very similar, but there are a couple types of UDFs:

    * The CLR UDFs can be scalar or table valued.

    For computed columns, you can use a UDF as well. Let me set up a couple tables here and a function. First I’ll set up a table similar to the one on SQLServerCentral that holds user points, add some data, and then create a quick function that calculates the sum of a user’s points.

    CREATE TABLE points
( USERID int
, ItemID int
, points tinyint
)
GO
INSERT INTO points SELECT 1, 1, 2
INSERT INTO points SELECT 1, 2, 1
INSERT INTO points SELECT 1, 3, 1
INSERT INTO points SELECT 2, 1, 1
INSERT INTO points SELECT 2, 2, 1
INSERT INTO points SELECT 2, 3, 2
INSERT INTO points SELECT 2, 4, 1

CREATE FUNCTION UDF_GetUserPoints
( @UserID int
) RETURNS int
AS
BEGIN
 DECLARE @sum INT

 SELECT @sum = SUM( points)
   FROM Points
   WHERE UserID = @UserID
   
RETURN @Sum
END

    If I run the function by itself, I can get the sum of each user’s point total.

    SELECT dbo.UDF_GetUserPoints(1) AS 'points'
UNION 
SELECT dbo.UDF_GetUserPoints(2) AS 'points'
------------------------*/
points
-----------
4
5

    Now let’s go back and set up the user table. I could alter this table if it existed, but in this case I’ll add the points as well as a calculated value for the user’s points. As long as I’m not asking for lots of user’s from this table, this technique is probably OK. Otherwise, I might have a big performance issue. (no SELECT *s from this table)

    CREATE TABLE UserProfile
( UserID INT
, UserName VARCHAR(200)
, points AS dbo.UDF_GetUserPoints(USerID)
)
go
INSERT INTO userprofile SELECT 1, 'Steve'
INSERT INTO dbo.UserProfile SELECT 2, 'Andy'

    Note that I’m not adding a value for the points column. This is a computed column, so I ignore it in inserts.

    SELECT TOP 10  UserID ,
        UserName ,
        points 
 FROM dbo.UserProfile

UserID      UserName      points
----------- ------------- -----------
1           Steve         4
2           Andy          5

    Here the values are calculated from the other table, pulled from my UDF in the computed column.

    Not necessarily a great technique, and I would be careful about using this. Since this function is non-deterministic, we can’t persist the values in the table, so this means that any access of this table for the points column would result in the function execution for the row. Potentially a performance issue.

    If you want to see this idea in action, there’s a similar video on UDFs in Computed Columns at SQL Share as well that covers the topic.

    Disclosure: I am a part owner in SQL Share

    .

  • SQL Saturday #90–Oklahoma City recap as chauffeur and speaker

    I’d never been to Oklahoma City, and when Kristin Ferrier asked me to come to SQL Saturday #90 and do the keynote, I was happy to accept. It’s a short flight from Denver, and not much further from Austin, so I was surprised Friday morning to find Wes Brown sitting down next to me in the Denver airport. Either he was slightly confused as to the best route to Oklahoma City,  or he just wanted to chat with me on the way into town. Maybe he just needed a ride, in any case, it was good to see Wes, a longtime friend.

    IMG_1219

    We flew in early, and met Karla Landrum, the Community Coordinator for PASS in town. We had lunch downtown in Bricktown, and then wandered over to the National Memorial. A somber scene, but one that I’ve wanted to visit for awhile.

    okc_a

    The famous Fence, where people still leave mementos to remember those that passed away.

    okc_b

    From there we headed back to the hotel to relax. Or at least Karla and Wes did. As usual, I headed out to run. I’d been feeling under the weather for a few days, and sometimes a run in the hot weather makes me feel better. I was slightly pressed for time, so I headed down the road from the hotel, not very scenic, but a nice run.

    okc_d

    Friday night was the speaker’s dinner, at another hotel where we met for some appetizers and drinks. It was a small crowd, with only 14 speakers for the event and a few volunteers, and we had a nice time chatting with others. Quite a few people from Dallas had driven up, and it was good to catch up with Tim, Russ, Jen, Sean, Sri, Ryan, and a few more that had made the road trip.

    Saturday started early, as is the case with these events. As designated chauffeur for Karla and Wes, we arrived early at the Moore Norman Technology Center, a large training conference and training center SE of Oklahoma City. It was a great venue, with a large conference room that was split into 3 still-large rooms for the day. We opened with one large room, where I gave the keynote talk. Not sure I did a great job getting a couple things across, as Wes was happy to ride me about throughout the day. However I did get a few nice complements later on from others. For those still looking for the book I recommended, it’s Drive by Dan Pink.

    okc_e

    It felt fairly empty when I started, but there were probably 120-150 people in there. I liked the fact that there were three large screens hanging at the front of the room and two in the back. You can almost see 2/3 of the room above, and seeing my own slides was helpful for me during the talk. The only think I missed was a clock in front of my, which I had at SQL Saturday #64. That is very helpful and I wish more rooms had clocks.

    After my 30 minute talk, the room was divided into thirds, with each track having a room. There were BI, DBA, and Dev talks on the schedule and it allowed people to stick with one area, or easily move to another room. Volunteers in each room did a great job handing out the evals during sessions and it seemed many people filled them out.

    The sponsor tables in the main hallway were a little sparse, with only 3 or 4 sponsors handling tables. A few more sponsors sent prizes or had boxes available, but didn’t send people. I think this is likely to happen more and more because vendors can’t send people to every event. I expect that the prizes and money from vendors will dwindle a bit in 2012, and I hope people can plan more bare bones events in the future.

    okc_fThe conference center catered the food, which was nice. Sandwiches and bag lunches at noon, and a couple of snacks early and late. Coffee seemed to be always available, and I liked the spread for the morning snack.

    I tried to be healthier on this trip, avoiding diet soda and sticking to coffee and water during the event, and enjoying the fruit and veggies you see here.

    At the actual lunch I had a Red Gate tools demo, so I ate early and then set up for a mini-demo of some of the Red Gate tools during lunch. I don’t know if it was because there wasn’t a real lunch area or people were interested, but I had 40-50 people watching me show off Data Compare, SQL Compare, Monitor, and a few more products. I got some good questions as well, which was great to see. Getting some support for the vendor sessions will be important moving forward, and you might find a product that helps you do your job better.

    The one very interesting question was from a guy that was testing backup tools for object recovery. He wanted to recover a large table (80GB or so) along with indexes. I wasn’t sure if our tools would handle that, but dropping an 80GB table is a big no-no. I can’t imagine the need to do that often, or at least, I wouldn’t expect it. Drop that table 2 or 3 times and you might be looking for a new job.

    okc_gI was tired after my session, so I went to the hotel to rest for an hour, and then went out for another run. This time E of the hotel, heading out into the country and pounding down a quite country road like this one.

    It was hotter today, with my calves feeling like they were tanning as I ran. It was like I was running next to an open oven door, and I struggled. Apart from trying to get over a cold, I was just tired and only managed 1.5mi.

    Still, I did feel better after sipping water and showering back at the hotel. I returned to the event feeling refreshed and ready to close down the afternoon.

    Some people had definitely left throughout the day, but still a good crowd for the closing ceremony. There were quite a few prizes given away, and good applause for the vendors providing them. People were anxiously watching the ticket drawing, as you evidenced by this photo of the MidnightDBA crew.

    okc_h

    I was also surprised that 40-50% of the people still there at the end were from Tulsa, a good drive away. Perhaps that’s not surprising as those people would want to make the most of the day if they drove over an hour.

    Afterwards we met at the same hotel as Fri night for the after party. A good selection of appetizers were available and most of the speakers came over. Only 5 or 10 attendees came, which was a little disappointing, but I understand it’s a long day. Still I’d hope more people would think about doing a little networking after the event, especially with local speakers.

    This was the first event in Oklahoma City, I thought it was great. The venue was amazing, the organizers did a great job, and it was fun. I tend to like the idea of fewer tracks and bigger rooms, and enjoyed my trip to OKC. Looking forward to the chance to go back at some point.