Tag Info

Hot answers tagged

50

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


41

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


39

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


22

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


18

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


17

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


15

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


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


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

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


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

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


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


12

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


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


11

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


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


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


9

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


9

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


9

Back in the days when I worked in an Oracle shop, we had a specific 'dev' (development) server, which had different security restrictions than the 'prod' (production) server. Developers could do whatever they needed, and then we'd hand off the necessary scripts to the DBA to apply to the production server. In the case of our critical systems (SCT Banner, ...


9

Consider the following scenario: There is a Unix user named gaius on the Oracle server with external authentication, so in Oracle there is a corresponding user called ops$gaius. When logged into a shell, I can also log straight into my Oracle schema, and my cron jobs don't need a password embedded in script either. Remote OS authentication is permitted, ...


9

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


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

Not a direct answer about storage of the passwords, but I generally use at least two database connections when building webapps -- one's used 99% of the time for user-related activities, with restricted privileges, and the other's used for 'admin' functionality (delete users, etc.). In a few cases, where I'm installing someone else's package, I'll install ...


8

No. As you suggested, the PCI Data Security Standard actually prefers Windows Authentication over any other means of authenticating to SQL Server. The section of the standard that covers this is Requirement 8: Assign a unique ID to each person with computer access. SQL Server authentication (which Mixed Mode authentication allows) fails, or makes it ...


8

::1 Localhost is the equivalent to 127.0.0.1 Localhost but using IPv6 protocol format to clarify further: :: is an abbreviation for zeros, so this entry really means 0000:0000:0000:0000:0000:0000:0000:0001 localhost


8

OPEN SYMMETRIC KEY MySymetricKey DECRYPTION` BY CERTIFICATE MyCertificate` notice - there is no password here. Yes, there is. You opened the certificate MyCertificate somehow, didn't you? There could be several cases, but any of them ends up with a password: you opened the certificate using a password (obviously, there is a password in this ...



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