Tag: syndicated

  • T-SQL Tuesday #183 Invite: Tracking Permissions

    It’s time for T-SQL Tuesday again and this month I’m hosting. I realized that I didn’t host in 2024 and since I run the thing, I ought to be a part of it one of these days.

    So, this is that month. The format is the invite comes out today and you have a week to write a post. Please post it on your blog and link back to this post or leave a comment on this post with the link to yours. That way I can summarize in a few weeks.

    With that, the invite…

    Managing Database Permissions

    One of the things I see lots of people struggle with is the database permissions for their logins/users. DBAs or Ops people are always getting requests to add people, rarely requests to remove people, and not often enough, requests to audit who has permissions.

    Lots of systems that live for years have people with too many permissions but no one knows this.

    It could be SQL Server, Oracle, CosmosDB, RDS, PlanetScale, etc. Well not that last one, they haven’t been around for years, have they? They have, since 2018, so maybe people do have this issue in MySQL.

    Ultimately, we have to audit or understand permissions at some point. Or we need to update permissions. This could be a developer task (as they add/change objects) or it could be strictly Operations. I’ve seen both.

    This month, I’m wondering how you track permissions, which I assume involves some code. Show us a good way to do any of these:

    • check permissions
    • update permissions
    • add new logins/users across systems
    • remove people
    • report on permissions at various levels (user, object, database, etc.)

    You should publish your post on Feb 11, 2024 UTC. Link back to this post with a trackback/pingback or a comment.

    Looking forward to what you come up with.

  • Declaring a Complex PK in a CREATE TABLE: #SQLNewBlogger

    Recently I was talking with someone who had not named any of the primary keys (PKs) in their database. They used system generated names and when they ran comparisons, they got all sorts of drops and creates they didn’t expect. This post shows how easy it is to declare PKs with names, even with complexity.

    Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

    The Scenario

    I’m going to use a schema that I have for baseball data. It illustrates the point well, I think.

    For my friend, imagine a table like this one:

    CREATE TABLE [dbo].[salaries](
         [yearID] [int] NOT NULL,
         [teamID] [varchar](3) NOT NULL,
         [lgID] [varchar](2) NOT NULL,
         [playerID] [varchar](9) NOT NULL,
         [salary] [int] NULL,
    PRIMARY KEY CLUSTERED 
    (
         [yearID] ASC,
         [teamID] ASC,
         [lgID] ASC,
         [playerID] ASC
    )
    )

    If I create this table, the system will decide what the PK is. In fact, after I run this, I can see in sys.objects that the name is some random code.

    2025-01_0258

    What’s worse is that if this were an FK and I were dropping these in some script, I’d have issues in other systems. This name is different on each system.

    The better solution is to make a simple change. Before the Primary Key keyword, I can add Constraint and a name, like this:

    CREATE TABLE [dbo].[salaries](
         [yearID] [int] NOT NULL,
         [teamID] [varchar](3) NOT NULL,
         [lgID] [varchar](2) NOT NULL,
         [playerID] [varchar](9) NOT NULL,
         [salary] [int] NULL,
    CONSTRAINT salariesPK PRIMARY KEY CLUSTERED 
    (
         [yearID] ASC,
         [teamID] ASC,
         [lgID] ASC,
         [playerID] ASC
    )
    )

    If I run this, then I have a better named PK.

    2025-01_0259

    Be explicit in your work. It makes for better code and easier, repeatable, reliable deployments.

    SQL New Blogger

    This is a simple thing, a code smell, but one that wastes DBA and developer time. This post shows a simple thing you can do to have better code. This took me about 10 minutes and you could do something similar.

    Write this and maybe someone asks you how to do this in an interview.

  • AIs Struggle with Family

    This post looks at a logic problem asked to an AI, and which is struggles at finding an answer. This is based on an article noting this question stumps most AIs. The article is at: https://futurism.com/logic-question-stumps-ai

    This is part of a series of experiments with AI systems.

    The Setup

    I loaded a Deepseek model to compare with my Mistral model. I did this with the following commands:

    docker run ollama
    docker run -d -v ollama:/root/.ollama -p 11434:11434 --name ollama ollama/ollama
    docker exec -it ollama ollama pull deepseek-r1:1.5b
    docker exec -d ollama ollama run deepseek-r1:1.5b
    docker run -d -p 3000:8080 --add-host=host.docker.internal:host-gateway -v ollama-webui:/app/backend/data --name ollama-webui --restart always ghcr.io/ollama-webui/ollama-webui:main

    From there, I had a website.

    The Logic Problem

    In the article, we ask a question.  I asked: >Alice has [5] brothers and she also has [3] sisters. How many sisters does Alice’s brother have?

    The response was interesting, using the Deepseek-r1:1.5b model. Here it is, with the entire response in Italics.

    First, determine the total number of children in the family by adding Alice’s brothers (5) to her sisters (3). This gives a total of 8 children.

    Next, since Alice is one of those 8 children and she has 5 brothers, subtracting Alice from the total gives the number of brothers as 4. However, this calculation leads to a discrepancy because it doesn’t align with the original information provided.

    Recognizing that there might be an error in the initial assumption, consider that Alice has 3 sisters, meaning there are at least 4 girls (including herself). Adding her 5 brothers results in 9 children, which still does not match the given numbers.

    Finally, re-evaluating the problem, it’s clear that without additional context or information, determining the exact number of sisters a brother has is not possible with the provided details.

    Solution:

    Let’s break down the problem step by step.

    1. Total Number of Children in the Family:

      Alice has:

      • 5 brothers

      • 3 sisters

      Since Alice herself is one child, the total number of children is:

      [
      \text{Total children} = \text{Number of brothers} + \text{Number of sisters} + 1 = 5 + 3 + 1 = 9
      ]

    2. Number of Brothers:

      Alice has 5 brothers in her family.

    3. Number of Sisters Alice’s Brother Has:

      Since the total number of children is 9 and there are 5 brothers, we can determine the number of sisters each brother has by subtracting the number of brothers from the total number of children:

      [
      \text{Sisters per brother} = \frac{\text{Total children}}{\text{Number of brothers}} = \frac{9}{5}
      ]

      However, this division does not result in a whole number, which suggests that the problem might be interpreted differently.

    4. Alternative Interpretation:

      Considering that Alice has 3 sisters, it implies there are at least:

      • 1 (Alice) + 3 = 4 girls

      Including her own siblings, if each brother has an equal number of sisters, we can find the exact number by dividing the total number of children among all siblings:

      [
      \text{Sisters per brother} = \frac{\text{Total children}}{\text{Number of brothers} + 1} = \frac{9}{5 + 1} = \frac{9}{6} = 1.5
      ]

      This result still doesn’t make sense in the context of having whole sisters.

    5. Conclusion:

      Given the information provided, it’s not possible to determine an exact number of sisters that each brother has because:

      • The total number of children is fixed at 9.

      • Each brother has a different number of sisters based on their position in the family tree.

      Therefore, with the current details, there isn’t enough information to definitively state how many sisters Alice’s brother has.

    \boxed{\text{Cannot determine}}

    That’s interesting, but is this a Deepseek problem? Let’s try another.

    Mistral

    Mistral is the first model I played with locally. Let’s ask it the question. This response was shorter, so I’ll add it as an image:

    2025-01_0090

    It seems the AIs don’t quite understand family, and they’re making simple mistakes in putting things together. I could see humans making this mistake, but they’d catch it quickly if someone pointed something out.

    Let’s try one more thing.

    What’s a Family?

    I then ran this prompt: In a family, if I say Alice has 2 brothers and 1 sister, how many children are in the family?

    Easy, isn’t it? If I have 3 siblings, whether brothers or sisters, then there are 4 kids. How does the AI do?

    2025-01_0094

    I suppose Alice could be male (Alice Cooper), but even in that case, the AI seems to struggle to separate out Alice from the counts. For some reason, even listing the siblings out it counts 5 rather than 4. I guess 2+2=5 (for extremely confused AIs).

    Conclusion

    This isn’t to imply the AIs aren’t useful or helpful, but rather they aren’t intelligent in a human sense, or even logical in a human sense. They are very well trained search engines that can put things together in a way that we can’t easily program. They can predict the way to approach problems with mimicry of how humans think.

    However, they don’t have common sense. They are just working with patterns that are very complex, but aren’t intelligent.

    They are useful and can help you solve problems, however, they do make mistakes. Knowing something about the problem you’re asking them to solve is important. These are tools to help us, not tools that replace us.

  • Advice I Like: Why am I doing this?

    You are never too young to wonder “Why am I still doing this?” You need to have an excellent answer – from Excellent Advice for Living

    I’d say that you’re also never too old to ask these questions either. When you realize that you’re doing something out of habit or familiarity and not for other reasons, I think it’s worth re-evaluating the situation.

    It’s very easy to get into a rut and keep doing those things, even things you don’t like, from habit, obligation, or just because.

    Don’t do that. Instead, make sure you actively choose to do things. Things you want, things that you are obligated to do, committed to, or even things that matter to you. Don’t just keep doing something for no reason.

    I’ve been posting New Words on Fridays from a book I was reading, however, a friend thought they were a little depressing. They should be as they are obscure sorrows. I like them because they make me think.

    To counter-balance those, I’m adding in thoughts on advice, mostly from Kevin Kelley’s book. You can read all these posts under the advice tag.