Tag Info

Hot answers tagged

17

A database may contain malicious code, possibly a procedure that is going to change a password for the "sa" login or drop every database. However the only way that I can see that causing an issue is for an individual to restore the database, and then manually execute any code within that database. It would not execute in any automated manner. There is no ...


10

There are some prevention steps that you could do. Make sure no one but one sysadmin has access to the restored database. Put the db in single user mode after the restore is completed. Check the code inside all stored procedures and functions and triggers inside this database. Perform a dbcc checkdb to make sure there are no integrity issues. Check ...


10

Here's the line I like to use - it preserves everyone's dignity and lets everyone escape without finger-pointing: "Yes, that used to be a best practice, and..." It’s almost easier to explain that line in terms of what it DOESN’T do. It doesn’t dispute the speaker’s claim, because you don’t want to go down the rathole of arguing about whether or not the ...


9

My article will help if you set it up in advance, but not when the event happened in the past and you didn't have any kind of auditing mechanism set up. There is still hope, though. Let's say I did this: CREATE LOGIN flooberella WITH PASSWORD = N'x', CHECK_POLICY = OFF; This information is in the default trace under EventClass 104 (Audit Addlogin Event). ...


8

I'm reaching here, but I can think of at least one dangerous scenario: if you restore a database that has a filetable, those files are now on your network by default (and specifically, on your SQL Server). You could restore a virus. That by itself won't do anything, of course - the virus doesn't suddenly become sentient - but if your users then try to ...


7

Is there a way to trace a SQL session based on the client's TCP port? Yes. You can query sys.dm_exec_connections to identify a session from the client's TCP port (column client_tcp_port). For example: SELECT DEC.session_id FROM sys.dm_exec_connections AS DEC WHERE DEC.client_net_address = '192.168.0.100' AND DEC.client_tcp_port = 63465;


6

The question focuses mostly on a backup containing malware, but it's also possible to get unwanted and potentially malicious behaviour from the restore operation itself. I've accidentally found in the past that it's possible to crash SQL Server by trying to restore a corrupt backup file that causes SQL Server to try to read past the end of the backup file ...


6

RESTORE VERIFYONLY would seem to be a good first step. The ultimate answer is probably 'restore the database in a sandbox VM with no access to the outside world', but let's assume that option is off the table. What else should be done in this situation? Restore verifyonly verifies integrity of database it WILL NOT tell you whether backup includesa ...


6

My goal is to execute a command that requires the sysadmin role (DBCC TRACEON(1224)) You are punching a hole in your security by allowing an unprivileged user run as sysadmin role. If you are trying to set 1224 traceflag, which disables lock escalation based on the number of locks, you can do it on table level using ALTER TABLE e.g. Below enables lock ...


6

This is a prime reason why different applications should be using different principals to authenticate on. If you use Login1 for 5 applications, then it would become a nightmare to manage security if you ever need those 5 different applications to have different security schemes. My recommendation? Have a separate login for both of those applications. ...


6

You dont need to have them use COPY_ONLY. Only An intermediate LOG BACKUPS will break the LSN. What you can do is explicitly DENY BACKUP LOG to [user|group] privilege to developers or developer group. Alternatively, just create a ROLE and deny backup log to that role. So all the users in that role will inherit the permissions. e.g. USE test_kin GO CREATE ...


5

Greenstone Walker is right. If you are granted SA rights, there really isn't much to do there to prevent it. I like that you are worrying about protecting from your ability to make such a mistake. I've seen people burned by an "oops" restore before. Not pretty. There are potentially a few things you can do, though. Your milage may vary but some ideas to get ...


5

First, you can't prevent a sysadmin from doing anything. :-) Any login that is a member of the sysadmin fixed server role operates outside the permissions system - they can do anything. My first thought was to use a DDL trigger to stop the restore. Unfortunately, restoring a database is an operation that does not cause any triggers to fire (see this Connect ...


5

It can be done but it's generally considered fairly dangerous. At a very basic level you set the trustworthy flag on the database and then when use you execute as on the sp it can take advantage of it's server level principals security access. Because of how dangerous this is I don't want to go into any detail here. However I've blogged about it here ...


5

Please see the reference on BOL for Database-Level Roles: db_owner Members of the db_owner fixed database role can perform all configuration and maintenance activities on the database, and can also drop the database. The easiest way to see all of the permissions is to use the sys.fn_my_permissions() function. First verify you are a member of ...


5

Risk mitigation would indicate creating a separate account for each service on each machine. The level of work required to create the accounts necessary is extremely minimal, but the unknown risks that accompany not doing so are quite high, according to Microsoft's own recommendations. Microsoft Best Practices recommend using separate service accounts for ...


5

we're going to allow an external partner (i.e. not an employee of our org) access to this server, like so: Their credentials will own (belong to db_owner) a couple databases. A concern along with the service account being the same on multiple instances is that an outside entity can escalate their permissions quite easily. Given that outside entity is ...


5

Ensure you have a mountain of documentation, both of your current system's flaws, and how to fix them. It's no use just saying "everything sucks bad" unless you have a plan, and can show real benefits to the business of making the change.


4

What risk is there is restoring an unknown database from an unknown source? None. What risk is there in letting an unknown application connect using a sysadmin account to connect to that database and start running code? LOTS! If the application account only has rights within the database and no server level access then there's nothing it can really do ...


4

For normal roles, permissions for all role memberships are cumulative and DENY takes precedence over GRANT. However, sysadmin is a special case. SQL Server permissions are not checked for sysadmin role members so members of that role have full permissions to all databases on the instance.


4

From the 12c docs: The SYS user is automatically granted the SYSDBA privilege upon installation. When you log in as user SYS, you must connect to the database as SYSDBA or SYSOPER. Connecting as a SYSDBA user invokes the SYSDBA privilege; connecting as SYSOPER invokes the SYSOPER privilege. Oracle Enterprise Manager Database Control does not permit you ...


4

It's a totally pointless function that executes arbitrary SQL. It isn't SECURITY DEFINER so the only risk I think it can pose is if you allow users to run arbitrary SQL predicates or call arbitrary functions (in which case you're probably already stuffed) but try to block them from running any command they want. As you guessed, it just executes the SQL ...


4

I would first question why they need direct access to the database. You might ask your manager or legal department if the security policy for the company allows granting this type of access. Is it really needed if they are just going to execute a stored procedure on a regular basis. As you stated this is going to be on a regular basis I would setup an SSIS ...


4

Removing a principal from sysadmin role does not remove the principal from public role. You cannot use deny on members of sysadmin role or the object owners. Using sp_dropsrvrolemember system stored procedure to remove the principal from sysadmin role should be your solution. If somebody does not belong to the party, better kick him out. Remember you need to ...


4

The EXECUTE AS clause of CREATE PROCEDURE can only be used to impersonate a user (not a login) and the scope of impersonation is restricted to the current database. The sysadmin permission is associated with the login, not the user, so you receive a permissions error. The correct way to grant CREATE DATABASE here is to sign the procedure. The process is a ...


4

This example shows that your database users can run queries and collect the execution plans within a database where they have been granted SHOWPLAN (and without being added to the db_owner role), as long as the server-level login has not been explicitly denied the ability to ALTER TRACE. It also shows that unless you explicitly grant any trace-related ...


3

I'm sorry you haven't had a response since yesterday; here's at least a starting point for you. You can try pulling the pieces you need out of. As always, read the discussion threads (I was, regrettably, unable to find a script endorsed by the big names I recognize, so test thoroughly! Schema, object, server, and column level permissions are often ...


3

For reference "Denali" is SQL Server 2012. With regards to "end user confusion", I am not all that concerned with whether a end user is confused or not with regards to SSMS. Microsoft did not develop this tool for the normal end user, but for the database administrator and/or a user that had to manage a database. Therefore there will be a learning curve with ...


3

It does not open up directly to an "attack". It just means that any user from Database 1 (Kdb) can also access database 2 (Ydb). What's usually more critical is, when you have users with DDL-Permissions (create views, procedures) - they will also be able to access objects in database2. Maybe even more, than plain guests can. That depends on the object owners ...


3

Database roles are security principals that are wholly contained within their respective database and are not shared or visible to other databases. So any roles and users that are in database X have no knowledge of database Y. To accomplish your goal, you'll need to recreate the role in database Y and add all the appropriate users to this database and ...



Only top voted, non community-wiki answers of a minimum length are eligible