Solved

How to limit access to a table

Posted on 2008-10-07
20
1,021 Views
Last Modified: 2012-05-05
Hi Experts, I have a database table I would like to limit access on.   The table will hold credit card numbers and will be located in it's own schema.

Database A -  All Other Tables and the application logs into this database
Database B -  Table: CreditCards  .. nothing else

Database A - database function (getCreditCard) and stored procedure (saveCreditCard) will be used to fetch and save the credit card information.

How can I define the security on table "CreditCards" so that only the stored procedure/function of database A can access the table?   I don't want to be able to select from the table from Database A at all, except through the procedure/function.

Thanks!!!

0
Comment
Question by:gdemaria
  • 6
  • 6
  • 4
  • +1
20 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22662920
simply put a DENY SELECT for all the users in that database (except the table and procedure owner, eventually).
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 22662944
I can't remember...is there an easier way to do it than going through each individual user or group and denying permissions?  I thought there was, but I can't remember it if there is.
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 22662953
Since it is a different schema, I think you can get away with not giving users explcit select permissions on the tables, but eventhen the db_owner and sa can read the data.
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 39

Author Comment

by:gdemaria
ID: 22662986
If it helps, the database "A" is connected to our web site through a single user "sa"

Also, could you guys be a bit more specific on how to do this.  I was searching in sql manager and couldn't find anything close ...  Pseudo syntax for the command line would be fine ..
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 22663010
your website is connecting to the database through the sa account?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22663046
>If it helps, the database "A" is connected to our web site through a single user "sa"
you cannot deny anything to sa.

you will have to create some other login for this application (which is recommended, anyhow, to avoid using sa as application login !!!!)

once that is done, in the interface, in the table context menu, find manage permissions, add the user you created in that database for the new login, and in the select column click until the symbol is a red cross (which means : DENY).
0
 
LVL 39

Author Comment

by:gdemaria
ID: 22663107
Ok, I see I shouldn't be using "sa" for the application.  oops!  thanks for that!

> once that is done, in the interface, in the table context menu, find manage permissions, add the user you created in that database for the new login, and in the select column click until the symbol is a red cross (which means : DENY).

Ok, but that sounds like I am denying table access to the application user.  If I do that , will my procedure and function still be able to access the table (insert,update and delete)?




0
 
LVL 60

Expert Comment

by:chapmandew
ID: 22663151
If you deny access to the table to the user that you're going to connect with, I believe it will still be denied if you give that same user exec permissions on the stored procedure.  Deny overrides a grant.  You could use EXECUTE AS or SETUSER (if on 2000) to override this behavior if necessary though.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22663210
if the table and the procedure is owned by dbo (which is just fine), the application user is granted EXEC on the procedure and DENY SELECT on the table, the procedure will run just fine.
0
 
LVL 60

Assisted Solution

by:chapmandew
chapmandew earned 200 total points
ID: 22663257
In that case, you can DENY select on the entire schema to the user and then GRANT exec on the schema to the user.

for 2005 only:

DENY SELECT ON SCHEMA::yourschemaname to UserName

GRANT EXEC ON SCHEMA::yourschemaname to Username  --use this if you want the user to be able to execute any sproc/function in the db.
0
 
LVL 6

Expert Comment

by:bcsql
ID: 22663305
one more thing if you are holding CC info in a DB encrypt the CC info and secure the encryption key (or pass phrase).
0
 
LVL 39

Author Comment

by:gdemaria
ID: 22663823
I can't seem to separate the privs between user and procedure, here's what I have..

database A - all tables and the select and update procedure and function
database B - credit card table

new user called "app"
App has access to both database A and B

I am playing with the roles of the credit card table and assigning different combinations to "app" but when the procedures where so does a direct select of the table, when 'select * from databaseB.creditcards' is denied from user "app"  so it seems is the use of the procedure/function

I don't see a way to grant EXEC on the procedures, but it seems unnecessary because they are owned by the dbo in database A, and I have not be denied running them.  The error I get is "denied select from table creditcards"


bcsql - the data will be encrypted as well, thanks
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 300 total points
ID: 22671948
the problem here is that the table and the procedure are NOT in the same database.
you will need to have those somehow in the same db, for example a "copy" of the procedure in db b, and call that from db a.
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 22671983
Or if you're on 2005 you can setup a trust between the two databases

alter database dbname
set trust_worthy on
0
 
LVL 39

Author Comment

by:gdemaria
ID: 22672201
Thanks, I will try these suggestions!
0
 
LVL 39

Author Comment

by:gdemaria
ID: 24463940

I would like to close this question myself but the link to Accept Multiple Solutions is not visible, why?

The only link available is "Accept as Solution"  -  I would like to split points..  
0
 
LVL 39

Author Comment

by:gdemaria
ID: 24463946
After I objected to the auto-close, the Accept Multiple solutions link appeared.

Moderator, I think that should be logged as a bug.
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
T-SQL: Do I need CLUSTERED here? 13 38
Return 0 on SQL count 24 28
Show Results for Latest DateTime in a View 27 24
SSRS Enable Remote Errors 4 23
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now