Tag Info

Hot answers tagged

57

No, stored procedures do not prevent SQL injection. Here's an actual example (from an in-house app someone created where I work) of a stored procedure that unfortunately permits SQL injection: CREATE PROCEDURE [dbo].[sp_colunmName2] @columnName as nvarchar(30), @type as nvarchar(30), @searchText as nvarchar(30) AS BEGIN ...


46

You have several different questions in here, so I'll knock 'em out individually: "I've read that it's a best practice to not let users use the sa login directly, instead using Windows Authentication" You're mixing two things here: the concept of SA, and the concept of SQL authentication and Windows authentication. SQL authentication is a list of ...


41

SQL-Injection attacks are those where untrusted input is directly appended queries, allowing the user to effectively execute arbitrary code, as illustrated in this canonical XKCD comic. Thus, we get the situation: userInput = getFromHTML # "Robert ') Drop table students; --" Query = "Select * from students where studentName = " + userInput Stored ...


25

Yes, to some extend. Stored Procedures alone will not prevent SQL Injection. Let me first quote about SQL Injection from OWASP A SQL injection attack consists of insertion or "injection" of a SQL query via the input data from the client to the application. A successful SQL injection exploit can read sensitive data from the database, modify database data ...


20

Please note what MySQL 5.0 Certification Study Guide say in its bulletpoints on Page 498 Paragraph 6: On Unix, MySQL comes with a mysql_secure_installation script that can perform several helpful security-related operations on your installation. The script has the following capabilities: Set a password for the root accounts Remove any ...


20

Set the AD group as a login. And "login" means server level login not the AD concept of user/login. In SQL Server speak, this is a server level principal Create a mapped user in. You shouldn't really permission a user directly on tables. And "user" means database user not the AD concept of user: in SQL Server speak, this is a "database level principal" Add ...


19

It depends on your business, but the main thing in most cases is to make sure it is not seen as an IT issue. It is a security issue and while the two overlap massively business peopel are more likely to listen if you say "security" than if you are just "moaning about general IT stuff". Do you work with any clients that have security requirements? That is a ...


19

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 ...


18

No application needs to have SA access - ever. (Unless its sole purpose is database administration of some kind.) It is a general rule to never grant more rights to any login (application- or personal-) than the business requires that login to have. No application is completely secure. Most have some kind of SQL Injection or XSS vulnerability. If an ...


16

No. While the documentation currently has the following arguably ambiguous statement about what this flag means: Password policy is checked. What it really means, and should say, is that the flag serves two purposes: The password policy might have been checked, but only if (a) the password policy was enabled at the time the password was last ...


14

Actually if you read this whitepaper: SQL Server Separation of Duties Whitepaper It will tell you that NO ONE should be using sysadmin privileges on their account. Your daily access account should be given minimum access, not explicit sysadmin rights. Given them CONTROL SERVER is actually close to what sysadmin is and then allows you to still DENY/REVOKE ...


14

This is an interesting question: When does Oracle really delete data physically ? The unit of data in Oracle is a block. Let's see what happens when we delete a row. Here's an example with a simple table on 11gR2 (see "How to dump Oracle Data Block?"): CREATE TABLE test_delete_data(id NUMBER,data VARCHAR2(100)); INSERT INTO test_delete_data VALUES (1, ...


14

No. If a user is a Windows Administrator of a box, assume that they own everything on the box (including SQL Server). With Windows Administrator rights it is trivial to bypass any targeted protection you apply (such as a logon trigger that identifies their user name), by impersonating someone else (including NT AUTHORITY\SYSTEM, which gets de facto admin ...


14

Your problem boils down to access control. The first defense I'd propose is to simply deny access to the untrusted users. If they can't get into the database, they can't query the database and get at the sensitive data. If they must be allowed to access the database server, you can look at either explicitly granting them read permission to the tables they ...


13

The words "securely", "exposing", "outside" and "database" do not belong together. Under no circumstance should you ever expose a SQL server to the internet. It's a very bad idea. So your question really should be: How do we give access to our SQL server to remote locations? The answer: VPN. The remote users should establish a VPN connection to your ...


13

SELECT CAST(CRYPT_GEN_RANDOM(16) AS UNIQUEIDENTIFIER) Should do the trick I would have thought. CRYPT_GEN_RANDOM Returns a cryptographic random number generated by the Crypto API (CAPI).


13

You can do this with a normal view, as long as the users involved haven't already got access to the base table. EG: SQL> create user reportuser identified by reportuser; User created. SQL> grant create session to reportuser; Grant succeeded. SQL> grant create synonym to reportuser; Grant succeeded. SQL> select user from dual; USER ...


13

I think you're conflating authentication and authorization. I completely agree that keeping the security model in the DB is wise, especially as LedgerSMB is designed with access from multiple clients in mind. Unless you plan on going 3-tier with a middleware layer it makes perfect sense to have users as database roles, especially for something like an ...


12

Agreed with gbn (so +1), but I think there are two other possibilities at play: It is quite possible that their conceptual schema has a lot of overlap with their physical schema. Knowing table names gives you a decent head start in planning your SQL injection attacks. It is very likely they don't have their conceptual schema documented. Organizations ...


11

The password has been marked as 'EXPIRED' or marked with an 'EXPIRY_DATE' in dba_users. You will have to change it. You can set it back to the same password. The easy way would be setting the password "by values". This usually bypasses history checking. 12:28:33 SQL> select * from dba_users where username = 'MYUSER1'; USERNAME ...


11

Fixed database roles provide permissions to the whole database. Custom roles come into play when you don't want to give users permissions to all of the database, just a portion of it. For example, db_datareader provides SELECT permission to every table, view, etc in that database. For compliance reasons there may be some tables or views that HR staff should ...


10

There isn't much you'll be able to do to prevent people from getting access to your code. If it's encrypted WITH ENCRYPTION it can be decrypted pretty quickly. If you use CLR procedures the T/SQL statements that are executed against the SQL Server can be captured via SQL Profiler very easily. Not to mention many shops don't like SQL CLR and wouldn't bring ...


10

I see two lines of attacking this. Compliance. Is there any mandated compliance criteria in effect in your shop? Search carefully through its wording and see if you find anything that would be incompatible with the application 'requirement'. If you find anything that prevent the sa use by an application you have a bullet proof water tight case, as the ...


10

Does an known account name like sa, pose a security threat to database? A "god" user account with a known name is generally considered a worse idea than a god user with a less well known name. It makes brute force attacks that bit easier as the attacker only has to guess the password and not the username and the password. Also having a god user anyway ...


10

Pretty much everything. I'd start with their potential ability to use xp_cmdshell (and sp_configure if they can't, so then they can ... and whatever the account returned by xp_cmdshell 'whoami.exe' can do....), then move onto their ability to do drop database. Further risks include not just finance users being able to do these things, but any program on a ...


10

This is explained in Extending Database Impersonation by Using EXECUTE AS. The EXECUTE AS context is trusted only in the current database and allowing it to spill over to other databases is a escalation of privilege attack vector. There are two solutions, both described in the article linked above: the easy one is to mark the database TRUSTWORTHY: ALTER ...


10

This means that the login [R2Server\AAOUser] is already mapped to a user in that database. Or, in other words, another database user is using this login. You can see what database user is using your login with the following query: use YourDB go SELECT su.name as DatabaseUser FROM sys.sysusers su join sys.syslogins sl on sl.sid = su.sid where sl.name = ...


10

As Thomas mentioned that it can be done using LOGON Trigger. Below is the script that will help you out /* http://www.sqlservercentral.com/scripts/Security/69558/ Credit: Gregory A. Ferdinandsen --greg@ferdinandsen.com --Revision 1.0, 8 Feb 10 --Requires SQL 2005 SP2 or higher */ if not exists (select 1 from master..sysdatabases where name = 'SQL_Audit') ...


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

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 ...



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