Allowing users to access only the data they are authorized to access.
45
votes
5answers
3k views
Do stored procedures prevent SQL injection?
Is it true that stored procedures prevent SQL injection attacks against PostgreSQL databases? I did a little research and found out that SQL Server, Oracle and MySQL are not safe against SQL injection ...
18
votes
4answers
5k views
Why is OS authentication considered poor security for Oracle databases?
Oracle is deprecating OS authentication according to the Oracle Database Security Guide, which says
Be aware that the REMOTE_OS_AUTHENT
parameter was deprecated in Oracle
Database 11g Release ...
14
votes
7answers
2k views
Why is it a bad practice to allow everyone to use the sa login?
Even Microsoft discourages the use of SQL Server authentication mode, but our applications require it.
I've read that it's a best practice to not let users use the sa login directly, instead using ...
14
votes
6answers
245 views
Securing DB passwords
Looking at the structure of most PHP/MySQL-based websites I've seen, it appears that it's not terribly difficult to discern the database password if you dig a bit, as there's invariably a setup or ...
14
votes
2answers
336 views
Is it more secure to go through a 3rd database to connect two databases using the same login?
We have the following setup:
Multiple production database containing private data which is used by desktop software
A web database for a public website which needs some data from the private ...
13
votes
6answers
520 views
Why should an application not use the sa account
My first question ever, please be gentle. I understand that the sa account enables complete control over a SQL Server and all the databases, users, permissions etc.
I have an absolute belief that ...
12
votes
1answer
677 views
MySQL : Why are there “test” entries in mysql.db?
Recently, I posted an answer to a question about mysql.db.
Then, I got to thinking I should ask everyone this question:
I have noticed for years that upon installation of MySQL 5.0+, mysql.db is ...
11
votes
4answers
461 views
How much of a security risk are published conceptual schema?
I was requesting the conceptual schemas from a government agency's information system for my research. My request has been denied on the grounds of it being a security risk.
I don't really have ...
10
votes
2answers
206 views
forensically deleting/updating data
I have a need to forensically remove data from oracle. If I just delete it, my understanding is the data will still actually be in the data file until that space is reused. I'm not concerned about the ...
10
votes
2answers
239 views
Understanding MS SQL Server encryption and backups
So I was asking in chat about how secure SQL Server backup files are.
I'm told that if an attacker has access to the .bak file unencrypted that they can have access to the data.
So lets look at ...
9
votes
3answers
12k views
How do I assign an entire Active Directory group security access in SQL Server 2008?
I would like to use integrated security with my internal application which is all on a domain. Unfortunately, I've never been able to get this to work well. I would like to assign an entire Exchange ...
9
votes
2answers
1k views
Security for Application Developers doing PL/SQL work in Oracle
How do you handle the lack of Schema level privileges in Oracle? Oracle’s security architecture works well for applications that only need object level privileges and it works well for DBAs that need ...
9
votes
3answers
283 views
Any pointers on reducing my privileges in production but not making my job excessively difficult
Running SQL Server 2005 and 2008 on Windows 2008 R2.
We're going to be reducing privileges in production for developers - and I'd like to do the same for myself as a DBA, limiting rights to ...
8
votes
4answers
272 views
Potential risks of users connecting to SQL Server, through Excel, as sysadmin?
I recently discovered that a large swath of the finance department is using Excel to connect to my SQL Server 2000 instance with an account in the sysadmin role. What are my current risks that I ...
8
votes
1answer
236 views
Column level security
I am in need of a solution to hide specific columns in a table. We have people who need to build reports against this database and specifically some of these tables with confidential information but ...
8
votes
4answers
259 views
What would I need to do to make sure my DB policies have to pass a security audit?
I have an audit coming up, and I was wondering what physical, electronic, and logical access controls an auditor would look for when auditing a database for an ERP system. I'm really new to this ...
8
votes
2answers
2k views
How do you reset the sa password?
I lost the sa password on a machine, and when I log in to the machine directly using an account in the admin group, SQL Server Management Studio will not allow me to log in using Windows ...
8
votes
1answer
486 views
What are the security implications of sp_executesql?
By default, in SQL Server, the [public] role has EXECUTE rights on sp_executesql.
However, I've inherited a database server where the previous DBA has revoked the EXECUTE right on sp_executesql.
As ...
8
votes
3answers
1k views
MySQL LDAP authentication
Is LDAP authentication and authorization in MySQL possible? What are the requirements (MySQL version, third-party software etc.)? How it's organized?
Thanks.
7
votes
2answers
1k views
Can Windows Authentication logins be disabled on SQL Server 2008 (or R2)?
So we can do Windows Login or Mixed Mode, but can we configure SQL Server to only use internal logins and to block all Windows Logins?
Is the only solution to add all potential Windows logins and set ...
7
votes
1answer
1k views
Understanding SQL Server permissions
I am rooting around in SQL Server Management Studio against my instance of SQL Server 2008 R2 Express Edition. I am trying to understand how the permissions work.
What I can see is (via the ...
7
votes
1answer
292 views
Choice of authentication approach for financial app on PostgreSQL
First some background.
The LedgerSMB project is an open source financial accounting software project that runs on PostgreSQL. We implement a very large amount of business logic in user defined ...
7
votes
1answer
135 views
Dynamic Dimension Security
I have a cube in SSAS 2012 with about 30 dimensions, one of which is Company.
The Company dimension contains a list of companies, of which there are several hundred in the cube. Our security model ...
6
votes
2answers
670 views
securely exposing sql server 2008 for windows authentication
My company wants to have approximately 100 of their sales people (distributed around the country) to be able to run stored procedures from excel and return the data onto the spreadsheet.
We have sql ...
6
votes
4answers
172 views
SQL Server: what is the reason to favor customized database roles over fixed ones?
This may be a bit vague, but I was pondering how much DBA's use fixed database roles controlling security of databases. So the question is, is there a reason to favor customized database roles over ...
6
votes
2answers
105 views
Limit connection rights on SQL Server
I have an app to deploy in production that uses 'honor system' security. That is, all users connect to the DB using a SQL user/passwd credential and the app manages permissions itself. The latter part ...
6
votes
3answers
300 views
Restrict access for Windows administrator
As the SQL Server best practices says, "Windows Authentication mode is more secure than SQL Authentication". And now I want to know: is there a way to protect SQL Server from user with Windows ...
6
votes
2answers
248 views
How can I secure my database code?
We have a lot of proprietary database code that is simply encrypted using the "WITH ENCRYPTION" option. Unfortunately, this is a very weak method of securing database code.
Is there any better way ...
6
votes
3answers
188 views
How do you protect against malicious PREPROCESSOR attacks in Oracle External Tables?
I'm a new DBA and I recently found out about the option of External Tables in Oracle using the PREPROCESSOR feature ( ...
6
votes
2answers
253 views
Is it safe to run antivirus software on my database servers?
Some implementations of antivirus software are known to Microsoft to cause a variety of problems for SQL Server, and Microsoft has a support article dedicated just to picking the right antivirus ...
6
votes
2answers
998 views
SQL Server: how to avoid sysadmin rights but give adequate rights
I have a collegue who want to get an access to one SQL Server 2008 instance. I'll have to give him rights to this instance. He should have rights to do is e.g.
Add and modify server logins
Add and ...
6
votes
2answers
2k views
Listing the existing SQL Server Logins and Users
I know we can check the logins and the users that are defined, using GUI in SQL Server, but am wondering how we can do this check using script.
I ran the query below but it shows Principal_id which ...
6
votes
1answer
350 views
Login to SQL Server 2k8 R2 SP1 via Active Directory Group
I added an Active Directory group as a Windows Authentication Login to our SQL Server 2008 R2 database.
When a user who is a member of that group tries to connect to the server he is given a login ...
6
votes
1answer
544 views
SQL Server, remove all users' access right to a table object
As a clean up job, I am trying to remove all users' (including non public users) access right to a single table object (apart from superusers) and will subsequently grant new access rights. Is there a ...
5
votes
5answers
864 views
What security threats does the SA account and other known account names pose to security?
Does an known account name like sa, pose a security threat to database? When using windows authentication on SQL Server does it impose the same password policy(if it was set to say account lockout ...
5
votes
2answers
110 views
Most effective way to determine the minimum set of permissions for user
I have a database that an application connects to using db_owner permissions.
How do I effectively determine the minimum set of requirements actually needed by this user (application) to run without ...
5
votes
2answers
1k views
Restricting database access per IP addresses
I have filtered IP addresses to an access DB server like this:
Set the sqlnet.ora
tcp.validnode_checking = YES
tcp.invited_nodes = (localhost, 192.168.100.130, 192.168.100.186)
But in my ...
5
votes
2answers
3k views
The SELECT permission was denied on the (View) object after explicit grant select on view to user
I have a database with a View Named '1098Statement'
We are doing testing for an application with user name 'appuser'.
I have tried the following solutions to granting select on the view and keep ...
5
votes
1answer
614 views
Is there a way to grant a user access to only all non system views of database?
In SQL Server, I have a user in a particular database and I've been asked to grant them access to all of the non-system views of the database only. I believe this can be done by editing securables of ...
5
votes
1answer
85 views
How is SQL Server host identity ensured?
When I connect to some e-commerce website with my browser I use HTTPS that uses a certificate that (more or less) guarantees that example.com is indeed example.com.
Now I connect to a SQL Server ...
5
votes
2answers
353 views
RSExecRole missing
I have multiple servers and 4 of them have reporting services installed with the ReportServer & ReportServerTempDB databases.
A few days ago I was setting up security for a new login and I found ...
5
votes
1answer
119 views
SQL Server Security Hierarchy
I have been working as a developer with SQL Server for years without asking the question, "what is dbo", a shame. Recently I have been studying the security model of SQL Server 2008. I have been ...
5
votes
1answer
312 views
What's the difference between EXECUTE AS USER and EXECUTE AS LOGIN that makes the second one behave unexpectedly?
Recently I've been using EXECUTE AS LOGIN to check if a certain person could or couldn't use a table function in one of our databases. The tests showed that he could but he repeatedly reported ...
5
votes
2answers
123 views
MySQL Permissions Necessary To Reset Password
On my team there is a desire for some users to periodically update their MySQL password.
Our MySQL server is shared between a few projects (and certain users are assigned to certain projects). If I'm ...
5
votes
1answer
160 views
Map Oracle roles to Active Directory groups?
Is there any built-in way to map Oracle database roles to Microsoft Active Directory groups?
5
votes
1answer
510 views
Cross Database Chaining vs. Trustworthy Option
Recently, we had a stored proc request that required access across multiple databases. Of course this is not uncommon to have but in our case, earlier we used service accounts and this service account ...
5
votes
2answers
185 views
SQLAgentReaderRole and SQL AD ID login case affecting ability to edit jobs
I have AD ID users on a SQL 2008 server that can't edit their own jobs. They can create them, but in order to edit them, they have to delete them and then re-add them. The issue is explained here: ...
5
votes
1answer
734 views
Using integrated security over VPN
We've got a client that we're going to allow to use one of our applications. Staff on site regularly connect to our network via VPN. The .Net application that connects to the SQL Server uses ...
5
votes
1answer
327 views
How can I make the audit system create new audit files if they already exist?
I am trying to make my audit files easier to read, I have audit trail configured as xml,extended. The problem is that if my server gets busy and reuses the pids - the logs are being appended to the ...
5
votes
0answers
43 views
SA permissions issues with many nested objects
I have a broker application that's relatively complicated.
Today, after I made some changes, I started getting the error:
The server principal 'sa' is not able to access the database 'XYZ' under ...
