Hey,
I have 3 columns in a table Ex:
Select Column1,Column2,Column from TableName
No. 1. Person A should have permission to read values only (Column1, Column3 of the table) -
2. Person B, should have permission to read only (Column 2).
Here my question is , I have to write one single stored procedure to statisfy both conditions. Which means, if person A execute this stored procedure , he shoud get only column 1 & 3 values . similarly other person b should get column 2 value. Ex:
Column 1 - Empid
Column 2 - SSN (Only for Top user display)
Column 3 - Join Date
Person A & B as a SQL or Windows login
I am trying to find the best way to implement a complex column based security. Basically i need to secure data among lot of tables with millions of rows, so that some users may see some columns and others see diferent ones... Whats the best way to implement this kind of procedure securely and flexible?
I want to enforce a unique constraint on a column which must be encrypted in MSSQL 2005 using Cell Level Encyption (CLE).
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'itsaSECRET!!!3£3£3£!!!' CREATE CERTIFICATE ERCERT WITH SUBJECT = 'A cert for use by procs' CREATE SYMMETRIC KEY ERKEY WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE ERCERT
[Code] ....
The output makes it obvious why the constraint has 'not' been enforced.
I want to perform column level and database level encryption/decryption.... Does any body have that code written in C# or VB.NET for AES-128, AES-192, AES-256 algorithms... I have got code for single string... but i want to encrypt/decrypt columns and sometimes the whole database... Can anybody help me out... If you have Store procedure in SQL for the same then also it ll do... Thanks in advance
I have the following Problem: ( not simular to http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=101916 )
In one Table ( Objects ) exists an Id to my internal Security Tables, where the combination of many Features together results in, which Data the user could see. Today, i use only one SQL Account and the Security ist solved in my Application, In an SP is a where Clause generated, every SQL Statement is extended whitch this where clause. This work fine, but everyone with SQL User and PW could see everything this the Query Analyser or Management Studio.
The perfect solition could be: Several Usergroups should have Access to my DB. Only a few Views / SP where execuable for these Usergroups.
The Application calls alway the same View / SP an depending on the Login the Data ist filtered in the right way.
Is ist possible to filter a view with dynamic SQL ?
2.nd Question:
Is it possible to restrict Users / roles depending on the Network IP Address / Network Mask ?
The Security Problem only exists, when Users with VPN are connecting, internal Users always have full access.
I am attempting to create a view only user in Report Manager which can only view and run reports from a single directory. I have the following configured:
Active directory Group: DomainReport Users - Group Scope: Global; Group Type: Security; Member of: <none>
Active directory User: DomainReportUser - Member of: DomainReport Users group
Default web site Reports virtual directory: Directory security: Integrated Windows Authentication only
Default web site ReportServer virtual directory: Directory security: Enable Anonymous Access (user: domainadministrator) & Integrated Windows Authentication
Report Manager
Site Settings->Item-level roles: New role: Report Viewer; view folders and view reports only items selected
ReportFolder(Report Manager folder with reports): Properties->Security: Added DomainReportUser with Report Viewer role
When I go to my Report Manager site (e.g. http://url/reports) I get the Windows security form, in which I enter the DomainReportUser credentials. However, after I log in I have full rights to all folders and functions of Report Manager, as if I logged in as BuiltinAdministrator.
At what level of security is this breaking down? As far as role-based, I believe DomainReportUser should only have access to limited resources of Report Manager when logging on. What is allowing him to have Content Manager control of Report Manager? Is there a better way to set up a "view reports only" user access to Report Manager?
I have a BI Reporting scenario, wherein i have to fetch Reports from analysis Services. when the user tries to access a report, he should be validated uisng the Windows Authentication ID, and only data specific to that user should be display. I am not sure if this user authentication is to be done on analysis Services/reporting services.
Any suggestions/pointers would be highly appreciated.
I need to secure a datawarehouse table at the row level based on 1 tomany keys on that table. A user should only see the rows they haveaccess to. I need to be able to figure out which rows they have accessto using a single sql statement and it cannot be dynamic SQL or astored procedure (this is a limitation based on the reporting tool weuse).The conditions can be any combination of "and" and "or" operators. Ihave seen posts (and actually have three of his books) by Joe celkodescribing disjunctive canonical form. If not familiar please clickthe link below.http://groups.google.com/group/comp...db6abcfa6209d2fA rules table in disjunctive canonical form looks like it mightpotentially be a solution, but I can't figure out how to use it acrossmultiple keys.Here ia an example of what I am trying to do including current tablestructures and sample data. I cannot really change the generic_facttable schema, but the security cross reference table can be modified oraditional tables added.CREATE TABLE generic_fact (generic_fact_key int NOT NULL ,salesrep_key int NOT NULL ,product_key int NOT NULL ,customer_key int NOT NULL ,sales_amount decimal(18, 0) NOT NULL ,CONSTRAINT PK_generic_fact PRIMARY KEY(generic_fact_key))GOinsert into generic_fact values(1,1,10,20,45)insert into generic_fact values(2,1,10,21,90)insert into generic_fact values(3,1,11,22,17)insert into generic_fact values(4,2,10,20,32)insert into generic_fact values(5,2,13,25,6)insert into generic_fact values(6,2,12,24,56)insert into generic_fact values(7,3,11,34,75)CREATE TABLE security_cross_reference (cross_ref_key int NOT NULL ,user_key int NOT NULL ,security_type varchar (50) NOT NULL ,security_value int NOT NULL ,security_operator varchar (20) NOT NULL ,CONSTRAINT PK_security_cross_reference PRIMARY KEY(cross_ref_key))GOinsert into security_cross_reference values(1,1,'product',10,'or')insert into security_cross_reference values(2,1,'customer',21,'or')insert into security_cross_reference values(3,2,'customer',20,'and')insert into security_cross_reference values(4,3,'salesrep',3,'and')insert into security_cross_reference values(5,4,'salesrep',1,'and')insert into security_cross_reference values(6,4,'customer',22,'and')insert into security_cross_reference values(7,4,'product',11,'and')So based on the data in the security cross reference tableUser 1 can see sales for customer 21 or product 10(generic_fact table rows that have generic_fact_key values of 1,2,and4)User 2 can see sales for customer 20(generic_fact table rows that have generic_fact_key values of 1 and 4)User 3 can see sales for salesrep 3(generic_fact table rows that have a generic_fact_key value of 7)User 4 can see sales for salesrep 1 and product 11 and customer 22(generic_fact table rows that have a generic_fact_key value of 3)Does anyone have any ideas on the best way to do this?
I am trying to implement row level security in reporting services. Could any help me on this by providing step by step process on this and would be great if you can also tell me what requirements(tables..) should we have to implement security? It would be great if you can proivde sampele code on this.
Can somebody explain the process for implementing Item-Level security for reports? My requirement is as follows:
I have some reports pertaining to a specific department, where in I deployed them to a specific folder on Reporting Services. Now I need to give exclusive read access to those reports for the users in that department. They should not be able to create any folders / new reports etc.
The steps I have followed:
1. Deployed the reports from VS 2005 to a specific folder called "TheirReports" and in this I placed the Data source also as a seperate folder.
2. I clicked on Site Settings in Report Manager and then clicked on Configure Item-Level role definitions.
3. Clicked on New Role and gave a name as ReportsBrowser and checked the options --> "View Data sources / View folders / View reports / View resources"
4. Now I went back to the specific folder and clicked on Properties -> Security -> New Role Assignment.
5. Added the necessary users binding them to ReportsBroser role.
When I'm checking from the user machine, they are able to create new folders etc.
Could someone please advise if the report folder structure I am proposing will be secure enough so unauthorised users can't access the reports?
I have created three folders; Departments (that contains subfolders being one for each department), Sections (that contains subfolders being one for each section), and Open Access. A manager will either see the Department or Section folder and then the relevant subfolder for the area he/she manages.
I have deployed the reports from the Development Studio to the Home page, made them invisible there, but created linked reports that then show in the subfolders. Each subfolder contains a separate linked report and each has parameters set within the Properties tab of SSRS that only enables the manager who will access that subfolder to see the report details specific to their area.
A manager will then be given the browser role for only the subfolder he/she has access to.
I would like to be sure that a manager will not be able to see information in other subfolders. Will my proposed structure be secure enough?
Hi, I am trying to replicate the row level security rules in reporting service and realize the "security filter" property is a pure client side filter expression.
In our old system, we are using a set of rules to decide the row level security, something like:
if user is owner of this file or ( if user's department matches this file and the user is the department manager ) or if this user has unfinished task associated with this file or ...
So obviously it need more information than the just GetUserId(). What we have done is creating a CLR UDF as GetUserCaseList( int uid ) and returns a two column table ( case_id, case_right ).
We want to somehow inject these logics into the reporting while the user is using reportBuilder.exe, but we cannot find a way to put it into the data source view and/or the semantic data model.
I am pretty sure I am not the only guy who's having this problem, but I cannot find any hint....
I've implemented and tested cell level security on the cube. It's testing certain level conditions, and returns #N/A (as normal) when the user is not supposed to see the cell value. Since I always use .FormattedValue in my reports, works fine in Report Services (and Excel and ProClarity, etc.)
Here's the problem:
When RS parameters encounter this situation, the parameter dataset "breaks" (The following system error occurred: Type mismatch.) This is happening, because the parameter fields (ParameterValue, ParameterCaption, ParameterLevel) are being replaced by #N/A, due to the cell level security. This is happening, because these are actually defined as members, and hence passing through cell level security.
What I need to do is find a way to have these specific members bypass the cell level security, so that the parameter datasets still work. (Failing that, a new way of specifying parameters in MSRS.)
I've tried the following a a cell level security rule, but it doesn't seem to work:
[Measures].CurrentMember is [Measures].[ParameterValue] or [Measures].CurrentMember is [Measures].[ParameterCaption] or [Measures].CurrentMember is [Measures].[ParameterLevel] or [Measures].[Is Visible]
I work on test SSRS setup and trying to give one user enough rights so she can download RDLs from server, but no matter what I do on Folder leverl, on report level her security are still only as a <Browser>. Structure of our Server is:
Home/NewReports/Misc/Report01.
I'm checking those in <Folder Settings>/<Security> where this user is OK (Browser, Content Manager, Publisher, Report Builder).
So she looks OK in all folders Home/NewReports/Misc, but on report level she still only a browser.
Our db team tried everything on SSRS server working with Site settings and Folder option, how to make that report inherit security ?
HiI need to set security for row level but not based on Database user'slogin. It should be based on the user table login. For the particularuser I need to allow only the particular records to access insert,update delete and select.Let me explain clearlyFor example think we are using asp/asp.net websiteEg:www.test.comSo take this is our website and if you try this URL then you will get awindow for Login name and password.For example the Login name is windows user name (Here windows usermeans server windows user and not client) and windows password. So ifyou have login user id you can able to login in our site and we haveanother check. We have our own usertable this table consist all theuser login names and user rights. We will check the windows user whologin in our site has rights in the usertable I mean he is present inthe usertable if he is not present then we will display a message youhave no rights to access this site.If he has login id in our usertable then he allowed viewing ourpages. Still if he has the login id we will check the user who loginhas how much right to access to each page and the records of each tableits all depend on the user rights.So, here I need the row level security. For each and every table weneed to check the corresponding user and executing the record producelot of business logic problem for us.So after the user login we need automatically to set row levelsecurity for all the tables. Based on the user who login.So from there if we try select * from <tablename> then we can only ableto get the allowed records to select, insert, update, delete.Please can some one help how to solve this?Note:For some help you can refer the below URL (See in that they only givenabout the row level and column level security for each database usersnot for our required concept)http://www.microsoft.com/technet/pr...5/multisec.mspxThanks in advanceRams
My developers would like a 'sandbox' database with full ddl and dml permissions, however, they do not want others to read/change/drop their objects. With SQL 2005, can DDL permissions be granted to a user at the schema level? I'd rather not set up a database for each developer.
I'm implementing row-level security in a SQL Server database that uses Microsoft Access for the front end. I'm using a UDF (a view behaves the same way) to restrict access to specific rows of a base table based on membership in a role. According to the reading I've done, if the base table has DENY ALL permissions for the role, and the UDF has GRANT ALL, members of the role should be able to update records in the base table via the UDF, without having direct access to the base table. However, I find that unless I grant appropriate permissions on the base table, the user is unable to update the table via the UDF.
Is this expected behavior? Nothing I've read suggests I should have to grant permissions on the columns of the base table.
All of a sudden our reports stopped working today giving this error:
Server Error in '/Reports' Application.
Configuration Error Description: An error occurred during the processing of a configuration file required to service this request. Please review the specific error details below and modify your configuration file appropriately.
Parser Error Message: Security level already defined for 'RosettaMgr'.
Version Information: Microsoft .NET Framework Version:1.1.4322.2407; ASP.NET Version:1.1.4322.2407
If I comment out the lines 22 - 25 in the web.config file the reports start to work again, but I am reluctant to do this as I'm not sure what the implications would be if left..
Hi i followed Microsofts "Implementing Row-and-Cell-Level Security in Classified Databases Using SQL Server 2005"
this works fine when i insert delete data on a normal script (mangement studio)
my project runs in a SSIS package, different users. i cannot do a bulk insert using OLEDB data Destination i get the following error
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Conflicting locking hints are specified for table "dbo.tblUniqueLabelMarking". This may be caused by a conflicting hint specified for a view.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Conflicting locking hints are specified for table "dbo.tblUniqueLabelMarking". This may be caused by a conflicting hint specified for a view.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Conflicting locking hints are specified for table "dbo.tblUniqueLabel". This may be caused by a conflicting hint specified for a view.". Error: 0xC0209029 at Data Flow Task, OLE DB Destination 1 [1741]: The "input "OLE DB Destination Input" (1754)" failed because error code 0xC020907B occurred, and the error row disposition on "input "OLE DB Destination Input" (1754)" specifies failure on error. An error occurred on the specified object of the specified component.
I have setup a Database Audit Specification as follows:
Audit Action Type: SELECT | Object Class: DATABASE | Object Name: SHOPDB | Principal Name: public
Now, when I perform a SELECT query with a bound parameter such as:
SELECT * FROM myTable WHERE name='queryname'
What I see through the Audit Logs is something like:
SELECT * FROM myTable WHERE name='@1'
I understand that it is by design that we cannot see these parameters throught Database Level Auditing. I would like to know whether it is possible to see these parameters by any other means using
(1) SQL Server Enterprise Edition, (2) SQL Server Standard Edition, or (3) by an external tool.
I've been through a number of tutorials on how to enable row-level security based on a userID, but my problem is more complicated and I do not have sufficient understanding of report models to guess.
My security information is defined in a table within my database. It contains a username and an account mask. An account mask maps to 1 or more account codes contained in the other data tables in my report model. A user may have more than one account mask defined for his account.
I understand the concept of directly mapping the logged-in user to a field containing a matching username. Is it possible to do a two-step mapping, so that based on the user ID I can get the account mask(s) and then evaluate which account codes match the mask(s)?
Or is there a different/better way to set this up? Defining SQL roles/groups is not an option, because of some compatibility issues with external systems.
So far, because my security table has no defined relationship with the data tables, I have not even been able to get it into my report model (Would love any suggestions on that one, too.)
I'd appreciate any ideas or suggestions - even if only something to investigate. Thanks, Sarah
First of all, I've been a reader of swynk.com for quite sometime now, and I'd like to say 'thank you' to everyone who contributes.
Today, I'm the town moron.. haha I'm having issues with column level constraints. I have a varchar(50) where I want to keep *,=,#,/, .. etc, OUT OF the value input. I don't want to strip them. I simply want for sql to throw an error if the insert contains those (and other characters). The only characters that I want in the column are A-Z and 0-9. However, it's not a set number of characters per insert. It always varies... There has to be an easier way to do this than creating a constraint for every possibilty... Any help would be greatly appreciated.
Is it possible to have a corruption in one of the columns in a table at a specific row? If yes, what could be the possible causes for such corruption? Why I am asking? Because it happened that I wasn't able to read the value of a specific column but I was able to update it with other value! it sounds weird if you are able to write but not to read!! **I wasn't able to read but after I updated it with another value...it was readable... Please advise...??
(I AM REPOSTING THIS IN SQL FROM VB LANGUAGE BECAUSE I RECEIVED NO RESPONSES...ANY HELP WOULD BE APPRECIATED)
This is for a telephony application using Last In Wins concurrency...
Basically, we want to update only the columns that have changed in a row, not the entire row with the few changed columns. We are using TableAdapters exclusively.
We need concurrency down to the column level...that is to say, if a concurrency exception is thrown, we don't just want the new row to be UPDATED and overwrite the old row in the datasource...we only want the new columns to be UPDATED to the datasource...consider the following sequence...
- Fill table A and table B identically
- change column1 value in table A
- Update table A
- change column2 value in table B
- Update table B - this will generate a concurrency error.
Most of the help centers around using the merge command, to update table B, but in the situation above, we would lose the change to column1 (which we don't want to loose). Merge preserves the changes in table B (column2), but it would have the old value for column1, thus we would loose the value in column 1 when we updated the new merged table.
So, we need a way to build a datatable made up of the values in the datasouce plus the changes in the table B, which we will then update.
I have played endlessly with for/next type of constructs to build the new table of new column values using row version data, but I cannot get it to happen.
Upon exception for UPDATING table B, how do we build a temporary table from the datasource (so we get the change to column 1) and the new changes in table B, so we can update the temporary table and not loose any data.
With Item as( Select 1 as ItemId,'ItemName1' as ItemName,100 as position union all Select 2 as ItemId,'ItemName2' as ItemName,200 as position union all Select 3 as ItemId,'ItemName3' as ItemName,300 as position union all Select 4 as ItemId,'ItemName4' as ItemName,400 as position union all Select 5 as ItemId,'ItemName5' as ItemName,500 as position union all Select 6 as ItemId,'ItemName6' as ItemName,600 as position union all Select 7 as ItemId,'ItemName7' as ItemName,700 as position),
Mapping as ( Select 1 as Parent, 2 as child union all Select 1 as Parent, 3 as child union all Select 1 as Parent, 4 as child union all Select 5 as Parent, 6 as child union all Select 5 as Parent, 7 as child )Expected Result:
