I'm newbie in this domain, I have a project that aim to create java application (Desktop Application) to manage roles based on users profile in a data warehouse.

The thing that I'm not able to imagine is : How can I give some permissions to a specific table (eg. Fact table), and other permissions to others (eg. Dimension table)

Generally, how can we protect a spesific table in a data warehouse ?

enter image description here

  • What do you mean by "protect": encrypt? hide specific rows? mask some columns? disallow queries? disallow changes? And who is going to be protecting: your application? database server? This doesn't look like a well formulated or researched question.
    – mustaccio
    Jun 29 '16 at 18:29
  • @mustaccio, I mean by "protect" : forbid access, which include explicitly disallow queries, disallow changes... and the application will manage the access (Sorry, i know it's a general question, but I can't explain better than that :/ )
    – Bilal
    Jun 29 '16 at 18:54
  • 1
    Check this out, specifically the Authorization section -- you'll be able to ask a better question.
    – mustaccio
    Jun 29 '16 at 19:26
  • Well, it's an desktop application linked with SQL database, (to not enter in the app details, my goal here is just know how we can protect an specific table in DW)
    – Bilal
    Jun 29 '16 at 19:43

You might be asking too broad of a question but if you wanted to have a user only have access to a table in SQL Server you could simply do a:

--Create Server Level User
USE [master]

--Create DB Level Uesr
use [DatabaseName]
CREATE USER (username) FOR LOGIN (username)

--Grant Select
GRANT SELECT ON DatabaseName.EDWTableName TO username

As your app matures you'll probably move to securing access through stored procedure and granting execute at some time but this answers your immediate question. As far as how to secure SQL Server I'd recommend Microsoft's docs: Securing SQL Server

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.