MS SQL Permissions



  • select * from a_view

    Cannot find the object 'a_view', because it does not exist or you do not have permission.

    {look in Object Explorer, view isn't listed}

    Hm, hey co-worker, what's the deal?

    Oh that view doesn't have a grant to the right CORP\Developers group.

    So how am I supposed to write my script to update it?

    Just add the grant and recreate the view.

    There's no way that could poss--

    Command(s) completed successfully.

    The... wha... how... the FUCK!? I don't have permissions to run the view, or even to see the view in Object Explorer. But I can overwrite it!? That doesn't make sense on about 87 different levels.



  • Write only objects?



  • Been a while since I've administered an MS-SQL server but it is totally possible to have write permissions but not read. Really the only scenario I can think of where that makes sense is some kind of auditing system where you want to be able to INSERT new rows but not SELECT, UPDATE, or DELETE.



  • It has to do with your permissions on the actual database - if you view permissions, do you have modify priv?

    There's a separate set of permissions for read/write/exec/modify, and they can be mutually exclusive. The fact you could do that implies your database provisioning is insecure.



  • I wrote a little app that tests for vulnerabilities in linked servers which proved quite fruitful at my work (to the point of full escalation by cross server queries)

    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.SqlClient;
    using System.Linq;
    using System.Text;
    
    namespace SecurityTest
    {
        class Program
        {
            static void Main(string[] args)
            {
                var tryAgain = true;
                while (tryAgain)
                {
                    Console.WriteLine(@"Enter the database name to test.");
                    var data = Console.ReadLine();
                    Console.WriteLine(@"Starting security check");
                    try
                    {
                        using (var conn = new SqlConnection(string.Format(@"Persist Security Info=False;Integrated Security=true;Initial Catalog={0};server=sourceserver", data)))
                        {
                            using (var command = new SqlCommand(@"select top 1 * from targetserver.targetdatabase.targetschema.targettable", conn))
                            {
                                using (var adapter = new SqlDataAdapter(command))
                                {
                                    var result = new DataTable();
                                    adapter.Fill(result);
                                    foreach (var column in result.Columns)
                                    {
                                        Console.Write("{0}{1}", column , ",");
                                    }
                                    Console.WriteLine();
                                }
                            }
                        }
                        Console.WriteLine(@"insecure");
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(@"secure.");
                    }
                    Console.WriteLine(@"Again? (Y/N)");
                    var yn = Console.ReadLine();
                    if (!yn.ToLower().Equals("y"))
                    {
                        tryAgain = false;
                    }
                }
                
                
    
                Console.Write("Press any key to continue . . . ");
                Console.ReadKey(true);
            }
        }
    }
    
    

    The idea is you execute it against a lower priv login, and through cross server queries you escalate your privs



  • Ok yeah yeah, let's ignore stupid implementation details and focus on the forest not the trees: how does that make any sort of fucking sense? Sure it's possible to configure SQL this way, but why is it possible? Isn't this a minefield waiting to happen?

    I could create a "new" View called "happy_dogs" and by accident overwrite an entirely different existing View of the same name. Just bam, stomp it out of existence. No warning.



  • The SQL engine could either impose its own views of what privileges are permissible but probably block off some legitimate edge cases (the Discourse way), or it could allow anything and trust the DBA to set things up in a sensible manner (the TDWTF way).

    TRWTF is whoever is administering that server.



  • @blakeyrat said:

    I could create a "new" View called "happy_dogs" and by accident overwrite an entirely different existing View of the same name. Just bam, stomp it out of existence. No warning.

    This isn't really a permissions problem (aside from the apparent misconfiguration of your DB) is it? How are you logging into the DB? Can you share whatever your DBAs are smoking?



  • It's possible because of the concept of separation of duties. Theoretically the people managing the database shouldn't be the same people querying for data and building reports.

    So you give DBAs create, modify, exec priv but no read - and sql report builders just read access.

    It's easy as hell to misconfigure, for sure. But very secure when done right.

    But of note, a view isn't like a store procedure, you need access to the underlying tables to view the data.



  • I'm pretty sure you couldnt 'bam' stomp it out of existence, that should throw an error.

    Also of note, if you don't specify a schema it's probably making [NW\Blakeyrat].Tablename - assigning you your own schema.



  • @boomzilla said:

    This isn't really a permissions problem (aside from the apparent misconfiguration of your DB) is it?

    Being able to silently overwrite an object you can't even view otherwise? It's definitely a goddamned problem, whether or not it's a permissions problem.

    @Matches said:

    But of note, a view isn't like a store procedure, you need access to the underlying tables to view the data.

    This View didn't have any underlying tables, believe it or not it just returned one row composed of three function calls. And yes, I have permissions to view/execute the functions.



  • @Matches said:

    I'm pretty sure you couldnt 'bam' stomp it out of existence, that should throw an error.

    The standard way of writing a new View or Sproc in SQL is to preface it with a "hey if this already exists, then delete it buddy, 'kay?" If you're writing your new View the standard way, the only warning you'd have is an extra "sure thing, boss" in the output as it got erased before your overwrote it. I don't see many people noticing that. And even if you did notice that, it's not like you know what the old View/Sproc was/did. It's gone by the time you see it.



  • But that's really not the fault of the database in that instance. It's still a configuration error, and you defaulting to saying 'Hey, I have execution, drop, and create privileges, and I want to nuke and recreate this view'



  • I don't care, configurations that make no fucking sense should be impossible. Configurations that make no fucking sense and could also easily lead to accidental data loss should be double-super-secret impossible +1 platinum edition.



  • Depending on just -what- permissions you have on the database, something like this can recover the lost query if

    a) somebody has executed it recently
    and
    b) Not many queries have run since the accident

    SELECT
    deqs.last_execution_time AS [Time],
    dest.TEXT AS [Query]
    FROM
    sys.dm_exec_query_stats AS deqs
    CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
    ORDER BY
    deqs.last_execution_time DESC



  • @blakeyrat said:

    Being able to silently overwrite an object you can't even view otherwise? It's definitely a goddamned problem, whether or not it's a permissions problem.

    Yes, hence why I used the word 'misconfiguration.' It seems like somewhere a person must have set that up, unless MSSQL is TRWTF, which I seriously doubt in this case (though I'm not familiar enough with it to know).



  • Installing SQL server in almost any fashion is already putting you in the realm of 'easily lead to accidental data loss if you let people who don't know wtf they are doing anywhere near it' and changing permissions starts at double super secret impossible + 1.

    The issue is the person who set up your instance set it up with dangerous permissions and allows you to do naughty things. If you want to get really naughty, let me know, and I'll show you all sorts of cool shit.

    But start with something simple, like checking if something actually exists. In this case, you thought it should:

    select 
    	table_name
    	,ordinal_position
    	,column_name
    	,data_type
    	,is_nullable
    	,character_maximum_length 
    from 
    	--Server	 .Database
    	targetserver.targetdatabase.INFORMATION_SCHEMA.COLUMNS
    where 
    	table_name = 'tablename'
    


  • Jesus Christ.

    Even knowing I could use that to check if the object exists, why the fuck would I think to, or be expected to, considering it doesn't show up in Object Explorer?

    Stop defending bugs, you assholes. This is a huge bug. That's ok, software you like can have bugs. I like MS SQL. But I'm not sitting here defending this really fucking stupid behavior which is dumb.



  • Blakey, this isn't a bug, it's a misconfiguration and before you get all holier than thou go look at the broken ass shit you were talking about related to oracle just the other day.



  • @blakeyrat said:

    But I'm not sitting here defending this really fucking stupid behavior which is dumb.

    Me, neither. I'm just saying that I suspect it's your DBAs, not MSSQL.



  • http://what.thedailywtf.com/t/data-redaction-done-oracle-style/2155/13?u=matches

    The way you are currently configured, you are essentially provisioned as a semi-DBA - this is wrong, if this is not what your suspecting, talk to a real DBA.



  • @blakeyrat said:

    I don't care, configurations that make no fucking sense should be impossible. Configurations that make no fucking sense and could also easily lead to accidental data loss should be double-super-secret impossible +1 platinum edition.

    <pooop>


  • @blakeyrat said:

    This is a huge bug

    And showing things to someone that they don't have permissions to see is? I'm not sure what you are saying should be the behavior here. Are you saying that you should be required to have the permissions to see stuff in the view (or rather that it exists) in order to do anything to it?

    EDIT: at the top someone pointed out an example of giving a permission to insert into an auditing table but not seeing what is in it. Which can make sense for the account of an automated service.



  • Maybe there's a bug in Object Explorer?



  • Maybe but remember I also couldn't SELECT from the view.



  • @locallunatic said:

    And showing things to someone that they don't have permissions to see is

    No of course not.

    @locallunatic said:

    I'm not sure what you are saying should be the behavior here.

    If I can't even see that something exists, I shouldn't be able to overwrite it.

    @locallunatic said:

    Are you saying that you should be required to have the permissions to see stuff in the view (or rather that it exists) in order to do anything to it?

    Yes.



  • It's not, there's a specific permission that say 'Prevent viewing this object in the object explorer' - but it can be paired with other provisioning which allows for modifying the objects if you know they exist.

    There is legitimate use cases related to security and keeping people out of which they know nothing about.

    Unfortunately, if your people with modify permissions don't know enough to have those permissions...

    Btw, hiding an object is not default behavior. That was a DBA specific action. The default permission shows the object, but when accessing it gives a permission denied error.



  • @Matches said:

    Btw, hiding an object is not default behavior. That was a DBA specific action.

    And yet as I've said a billion times, that doesn't matter and has nothing to do with the problem here.



  • So DBAs can say that people aren't allowed to know something exists?

    I guess you could do something like hide the audit table from people that way, but if you can modify but not know it exists does seem to be buggy behavior. Even if they have to go out of their way to make the buggy behavior occur, that just makes it more of an edge case rather than not a bug.



  • Correct, typically speaking it's related to financial information/security - it's part of security by obscurity.



  • Generally speaking, you hide it + revoke all priv to touch the table except for groups that specifically need access. The current configuration is wrong.



  • @Matches said:

    Generally speaking, you hide it + revoke all priv to touch the table

    OK, but what @blakeyrat was saying (and I agree with) is that being able to hide it without revoking the other privileges is a bug.



  • It's not a bug, it's a 'feature'

    A stupid feature, but it is marketed as a feature.



  • So it's the third one then:



  • It's more this:



  • Just because it doesn't make sense for your situation doesn't mean that it could never possibly make sense.


  • mod

    @blakeyrat said:

    The standard way of writing a new View or Sproc in SQL is to preface it with a "hey if this already exists, then delete it buddy, 'kay?"

    Standard by whose definition? I never do that precisely to prevent the issue you are describing. That way if there already is an item with that name, I get an error and I know there's something I need to look at. This is an especially odd "standard" when you consider that the drop-and-create methodology is easily supplanted by ALTER statements for Views and Sprocs.

    I don't think this is a permissions issue. I think this is a "@blakeyrat did something stupid" issue.

    Edit: Removed sentence which doesn't really contribute to my point.



  • @abarker said:

    drop-and-create methodology is easily supplanted by ALTERs

    MS SQL Management Studio shows Create Date in the Object Explorer Details, but not last modify. It's really a work around for the combined issue of that and people modifying things directly in the DB without updating scripts with their changes. A common workaround which is why @blakeyrat was calling it the "standard way".


  • mod

    @locallunatic said:

    MS SQL Management Studio shows Create Date in the Object Explorer Details, but not last modify. It's really a work around for the combined issue of that and people modifying things directly in the DB without updating scripts with their changes. A common workaround which is why @blakeyrat was calling it the "standard way".

    But he was saying it's the standard for creating, not for altering. I can see why many people do this to track when something was last changed, but that's not what he was talking about. It still doesn't seem standard for creating something you expect to be new.



  • I've seen it on new things so that the original (script checked into source control) is what is tweaked with each mod during development. Basically so that it is "check out and make mod, check in" every time rather than adding a step to the first change of "put in if exists drop".


  • I survived the hour long Uno hand

    Today I learned that not only do I have access to prod as a non-developer (I didn't know the right server name to connect to, when I was told the correct one I could access it directly), but also I can query the PII tables in prod. It sends an alert to the security person, which she was asking me to help test, but maybe tomorrow I'll suggest hiding the PII tables in prod from the object view just to make it that much harder.

    (Good news, our security person is currently reviewing who has permissions in the prod DB and why)



  • @Yamikuronue said:

    Today I learned that not only do I have access to prod as a non-developer

    Today I learned that not only do I have access to production server as a non-developer, but also that NetMeeting doesn't really time out your remote controlling session if your connection drops - it hangs you up, but won't let you reconnect.

    Today I also learned my IP phone is fucked up, and drops the connection every 20 minutes or so. And I had to push myself a big file through a very small tube. And I'm technically not allowed to bypass the IP phone.

    Oh the joys.



  • Here's a hint:

    For permissions, see here:

    To see objects, here's something for you:

    set nocount on
    set transaction isolation level read uncommitted
    
    declare @sqldata nvarchar(1000), @dbname nvarchar(500)
    
    declare finddb CURSOR FOR 
    select
    	name
    from
    	sys.databases
    
    OPEN finddb
    
    FETCH NEXT FROM finddb 
    INTO @dbname
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
    	set @sqldata = 'select * from ' + @dbname + '.INFORMATION_SCHEMA.COLUMNS'    
    
    	exec(@sqldata)
    
        FETCH NEXT FROM finddb 
    END 
    CLOSE finddb;
    DEALLOCATE finddb
    

  • I survived the hour long Uno hand

    Literally, all I had to know was that instead of Dev[ServerName][Number] or Stg[ServerName][Number] it was [CompanyInitials][ServerName][Number] to connect to the prod MSSQL server (I had tried leaving off the prefix, like our webservers do, and when I didn't get in assumed I didn't have access). After that it's just a matter of opening the [CompanyInitials]_PII database and querying any table.



  • Oops, made a mistake in my query above, fixing it. It's supposed to give every table in every database (all columns) - forgot a cursor.



  • Fixed!

    set nocount on
    set transaction isolation level read uncommitted
    
    declare @sqldata nvarchar(1000), @dbname nvarchar(500)
    
    declare finddb CURSOR FOR 
    select
    	name
    from
    	sys.databases
    OPEN finddb
    
    FETCH NEXT FROM finddb 
    INTO @dbname
    WHILE @@FETCH_STATUS = 0
    BEGIN
    	begin try
    	set @sqldata = 'SELECT  T.NAME AS [TABLE NAME], C.NAME AS [COLUMN NAME]'
    					+' FROM ' + @dbname + '.SYS.OBJECTS AS T'
    					+' JOIN ' + @dbname + '.SYS.COLUMNS AS C'
    					+' ON T.OBJECT_ID=C.OBJECT_ID'
    					+' JOIN ' + @dbname + '.SYS.TYPES AS P'
    					+' ON C.SYSTEM_TYPE_ID=P.SYSTEM_TYPE_ID'
    					+' WHERE T.TYPE_DESC=''USER_TABLE'''
    	exec(@sqldata)
    	
    	set @sqldata = 'SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION,
    			   COLUMN_DEFAULT, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,
    			   NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE,
    			   DATETIME_PRECISION
    		FROM ' + @dbname + '.INFORMATION_SCHEMA.COLUMNS'
    	exec(@sqldata)
    	end try
    	begin catch
    	print 'Unable to access ' + @dbname
    	end catch
        FETCH NEXT FROM finddb INTO @dbname
    END 
    CLOSE finddb;
    DEALLOCATE finddb
    


  • @locallunatic said:

    OK, but what @blakeyrat was saying (and I agree with) is that being able to hide it without revoking the other privileges is a bug.

    Thank God, finally a sane person.

    @abarker said:

    But he was saying it's the standard for creating, not for altering.

    Actually I said "writing", and in this particular example I'm altering a view and not creating a new view. But again, this has nothing to do with my point-- whether or not the "drop and recreate" is standard in general, it's certainly standard here and every other place I've worked in the past.

    It's also the only way to make the script easily runnable on a server which may or may not already have the Object without human intervention. And since we have 8 total DB servers here (2 dev, 3 QA, 1 acceptance, 2 production), and since our DBAs are basically automatons who do only what is spelled-out in great detail to them, that's pretty important for this environment.


    Think of this hypothetical: I have 57,032,321 SQL databases. I want to ensure each database has the exact same a_view on it. Some already have it. Some don't at all. Some have an older version of the same view. How else do you write the view definition to handle this case?

    The "drop and recreate" method is the only thing that works.



  • Also, yes, switching casing is ugly as f, but I couldn't be assed to fix it. I grabbed the cursor stuff from a template I made a long time ago, and smooshed in random other stuff.


  • Discourse touched me in a no-no place

    As much as I hate to say it, I think @blakeyrat is right here. I'm a DBA, and if you told me I had to set things up so developers could alter or replace views without being able to see them or the definitions, I'd have to google it.



  • Isn't that arguing against blakey? To get into the state they've managed to get it, you have somebody who actually IS a DBA not knowing how they managed it off hand.

    It just smells of a DBA who isn't a DBA managing the database (I suspect blakey, due to his vehemous responses!)


Log in to reply
 

Looks like your connection to What the Daily WTF? was lost, please wait while we try to reconnect.