Nested Procedures && Using Signature Based Security
May 17, 2006
I am currently developing a project that requires a server level permission for one stored procedure (ALTER ANY LOGIN)
To this effect, I plan to create a certificate, sign the stored procedure with it, import the certificate into the master DB and assign privileges.
I also understand that modification to the code invalidates the signature (after all thats the point of signing something).
But what about user defined functions and stored procedures referenced by the signed procedure? Does SQL server follow the dependancy chain and include referenced procedures in the signature? Or does the privilege assigned to the certificate not apply when the nested procedure is executed?
If this is not the case couldn't a restricted database user just alter a nested stored procedure they have been granted ALTER access to and make themselves SA or something?
So to sum up, do you have to duplicate the functionality of otherwise nested procedures into a certificate signed procedure to protect server security?
View 3 Replies
ADVERTISEMENT
Nov 25, 2001
I cannot seem to get nesting of stored procedures to work. I must have the syntax wrong. Should the following work:
SELECT *
FROM spWod_rptWoStatusSummary
Where spWod_rptWoStatusSummary is a Stored Procedure.
The above does not work. I get an error telling me that spWod_rptWoStatusSummary is an object that does not exit.
Does anyone know what the correct syntax is?
View 1 Replies
View Related
Nov 25, 2001
I cannot seem to get nesting of stored procedures to work. I must have the syntax wrong. Should the following work:
SELECT *
FROM spWod_rptWoStatusSummary
(Where spWod_rptWoStatusSummary is a Stored Procedure).
The above does not work. I get an error telling me that spWod_rptWoStatusSummary is an object that does not exit.
Does anyone know what the correct syntax is?
View 1 Replies
View Related
Dec 5, 2005
Hi Out There
The attempt to create the following stored procedure, which is supposed to call the previously created stored procedures inside it,
causes the following errors.
Server: Msg 137, Level 15, State 2, Line 1
Must declare the variable '@OfferIDTwo'.
Server: Msg 137, Level 15, State 2, Line 1
Must declare the variable '@OfferIDThree'.
Server: Msg 137, Level 15, State 2, Line 1
Must declare the variable '@OfferID'.
Server: Msg 137, Level 15, State 2, Line 1
Must declare the variable '@OfferID'.
Server: Msg 137, Level 15, State 2, Line 1
Must declare the variable '@OfferID'.
Server: Msg 137, Level 15, State 2, Line 1
Must declare the variable '@OfferID'.
Create Procedure spPrepareOfferSimulation
@OfferIDOne int
As
exec spPopulateOfferTables @OfferID
go
exec spPopulateOfferProduct @OfferID
go
exec spPopulateOfferDictionary @OfferID
go
exec spPopulateOfferCondition @OfferID
go
exec spPopulateOfferError @OfferID
go
exec spPopulateOfferLimit @OfferID
go
exec spPopulateOfferQA @OfferID
go
Can someone provide me with some productive input to fix this mess?
View 10 Replies
View Related
Apr 14, 2008
I have a caller stored procedure (sp_A) which calls several stored procedures (sp_1,sp_2,sp_3,sp_4, sp_5)
The statements in the called stored procedures sp_1 to sp_5 are all contained within BEGIN TRY -- BEGIN TRAN and BEGIN CATCH blocks which ensure that any errors in the SQL statements are rolled back when an error occurs.
However, I have noticed that when the called stored procedure fails, the caller procedure does not fail Rather it continues processing the remaining sps. I want to add code in my calling sp to stop this fom happening. Any ideas?
I have used this statement within my calling sp but no joy yet.
set @ErrCode = 0
exec @ErrCode = sp_executesql @Sql
if @ErrCode <> 0
begin
return 1
end
Any one with ideas? Thanks
View 12 Replies
View Related
Sep 28, 2007
I need the output of a stored procedure in another SP. That's simple (using a temporary table). But there's a small (big error ) problem.
Since I have to manually define the temporary table with it's fields and datatype to recieve the output from the nested SP , this approach would fail if, in the future more parameters are required to be returned . Is it possible to immunise my SP to such a consequence at creation time, rather them having to amend the temporary table later ?
Any idea how to overcome the above problem ? Is there a way that the temporary table can be automatically created like we have in a SQL statement with the INTO keyword. Any ideas ?
Thanks,
Alec
View 9 Replies
View Related
Apr 30, 2008
I have a stored procedure that returns a scalar value and I want to use that value (among other places) within another stored procedure.
-- The general purpose stored procedure is far too long to include here but I've included the last several lines of code at the bottom to show general gist of it and how it exits.--
The only way I can find to use that returned value "criteria" in a stored procedure is to define a temporary table, INSERT it into the table and then extract it from the temprary table into the variable where I actually wanted it.... i.e.
DECLARE @CriteriaTable TABLE ( Criteria VARCHAR(8000) )INSERT INTO @CriteriaTable (Criteria) EXEC psp_WRP_DisplayCriteria @UserID, 'Dealers, Prefix, Serial Range, Models, Makes, Sold Date', @UserGroup SELECT @Criteria=Criteria FROM @CriteriaTable
This seems like a ridiculously long winded and confusing way of doing things, especially since Im doing it in a dozen different procedures, half a dozen reports and 1 (so fasr) asp.net/VB web site - and I can't help thinking there must be a better way using just one or even zero extra lines of code to do this.
e.g. SELECT@Criteria = (EXEC dbo.psp etc...)
... or some variation thereof with the correct syntax.
or even better going to where that variable is used and changing ...
EXEC dbo.[psp_InsertWarrantyReportTracking]
@UserID = @UserID ,
@ReportName = 'rptChassisTrackExtdWarranty', -- <------
@ReportCriteria = @Criteria
to ...
EXEC dbo.[psp_InsertWarrantyReportTracking] @UserID = @UserID , @ReportName = 'rptChassisTrackExtdWarranty', -- <------ @ReportCriteria = (EXEC dbo.psp_ etc...)... or some variation thereof with the correct syntax.
But no matter how I try and how I search I can't find any way other than in what is otherwise a completely supefluous temporary table.
Am I missing something or is that REALLY the only way to get a hold of that returned value?
------ Last portion of the general purpose routine I'm trying to call ---------------------If Patindex('%RO Audit%',@Parmlist) > 0
Begin
set @Criteria = @Criteria + '- RO Audit date'
select @All=ROAuditAll, @From = ROAuditFrom, @To = ROAuditTo
from dbo.tblWRParameters where @UserID = UserName
If @All = 1
set @Criteria = @Criteria + '- ALL' + @NL
else
set @Criteria = @Criteria + 'is between ' + rtrim(@From) + ' and ' + rtrim(@to) + @NL
END
--Return the combined parameter field
select @Criteria as Criteria
END
View 12 Replies
View Related
Mar 22, 2001
Has anyone faced problems in calling one stored procedure from within another stored procedure ? I am considering using nested SPs, and would appreciate any inputs on it.
Thks,
SC
View 2 Replies
View Related
Mar 11, 2014
I have 2 Procedures. P1 and P2. Even if there is error in P1 , I want changes in the P2 to be committed. How do I do it?
Following is the code sample I have used for testing. With this code , update in both the procedures are rolled back.
create procedure P2
as
begin try
BEGIN tran
update table1
set Description = '*P2*' + Description
where Code = 'b'
[Code] ....
View 2 Replies
View Related
Apr 29, 2008
I have a procedure that calls other procedures which in turn call other procedures. I have a transaction in the first procedure since I want to rollback everything done if something goes wrong except for rows inserted in a loggtable in my database. The reason for this is that all error shall be saved in this table, otherwise there is no way to find out what error occured.
How do you solve this?
View 4 Replies
View Related
Mar 21, 2008
I have nested a Stored Procedure within a stored procedure. The nested stored procedure ends in a select statement. What I'd like to do is either capture the results of the select statement (it will be 1 row with 3 columns, I only need the contents of first column), or suppress the select statement from displaying in the final results of the Stored Procedure it is nested in.
Is there any way to do either of those?
View 1 Replies
View Related
Jan 7, 2008
For every trigger and stored procedure I have a try-catch that writes to an error_log table.
The problem is the inner error is not preserved, always get:
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
As seen below - though commented out:
I tried commiting any transactions - though I didn't create one.
I played with the
XACT_STATE though that was 0
My test case was last procedure has 1/0
Thanks,
Russ
-----------------------------------------------------------
Below is what I have
Step 1)
ALTER Trigger [trg_ActivityLogEntryReportsError] ON [dbo].[ActivityLog]
FOR INSERT AS
DECLARE @ActivityLogID int
,@AlertMessageTypeID int
,@comment nvarchar(max)
,@Error_Source nvarchar(max)
--- etc.
SELECT
@ActivityLogID = ActivityLogID
,@AlertMessageTypeID = AlertMessageTypeID
,@Comment = Comment
FROM INSERTED
BEGIN TRY
if @AlertMessageTypeID = 2 -- activity reported an error
begin
exec proc_CreateAlertLogEntry_forError
@ActivityLogID
,@Comment
update ActivityLog
set flgActivityChecked = 1
where @activityLogId = activityLogID
end
END TRY
BEGIN CATCH
select
@Error_Source = 'trg_ActivityLogEntryReportsError '
,@Error_Procedure = ERROR_Procedure()
--- etc.
INSERT INTO ERROR_LOG
( Error_Source
,Error_Procedure
,Error_Message
--- etc.
)
VALUES
(
@Error_Source
,@Error_Procedure
,@Error_Message
---etc.
,@Error_Comment )
-- if @@TRANCOUNT > 0
--begin
--commit
--end
END CATCH
Step 2)
/*
This will be called by a Trigger
*/
ALTER Procedure [dbo].[proc_CreateAlertLogEntry_forError]
(@ActivityLogID int
,@Comment nvarchar(max))
AS
Declare
@ProcessScheduleID int
,@ProcessID int
--,@comment nvarchar(max)
,@Error_Source nvarchar(max)
---etc
BEGIN TRY
insert into AlertLog
(
AlertMessageTypeID
,comment
,ActivityLogID
)
values
(
2 -- error
,@comment
,@ActivityLogID
)
end
END TRY
BEGIN CATCH
PRINT 'ERROR OCCURED'
PRINT ERROR_Procedure() + ' ' + ERROR_MESSAGE()
select
@Error_Source = 'proc_CreateAlertLogEntry_forError '
---etc.
INSERT INTO ERROR_LOG
( Error_Source
,Error_Procedure
,Error_Message
---etc.
)
VALUES
(
@Error_Source
,@Error_Procedure
,@Error_Message
--- etc.)
-- if @@TRANCOUNT > 0
--begin
--commit
--end
END CATCH
update ActivityLog
set
flgActivityChecked = 1
,UpdatedDate = getdate()
,UpdatedBy = suser_sname()
where
ActivityLogID = @ActivityLogID
STEP 3
ALTER Trigger [trg_AlertLogEntry_SendsOnInsert] ON [dbo].[AlertLog]
FOR INSERT AS
declare
@AlertLogID Int
,@AlertMessageTypeID int
,@Comment nvarchar(max)
,@Error_Source nvarchar(max)
,@Error_Procedure nvarchar(max)
,@Error_Message nvarchar(max)
--- etc.
SELECT
@AlertLogID = AlertLogID
,@AlertMessageTypeID = AlertMessageTypeID
,@Comment = isnull(Comment,'')
,@ActivityLogID = isnull(ActivityLogID,-1)
FROM INSERTED
BEGIN TRY
PRINT 'trg_AlertLogEntry_SendsOnInsert'
PRINT @COMMENT
exec proc_SendEmail
@AlertLogID
,@AlertMessageTypeID
,@comment
,@ActivityLogID
END TRY
BEGIN CATCH
select
@Error_Source = 'trg_AlertLogEntry_SendsOnInsert '
,@Error_Procedure = ERROR_Procedure()
,@Error_Message = ERROR_MESSAGE()
--- etc.
INSERT INTO ERROR_LOG
( Error_Source
,Error_Procedure
-- etc.)
VALUES
(
@Error_Source
,@Error_Procedure
,@Error_Message
---etc.)
-- if @@TRANCOUNT > 0
--begin
--commit
--end
END CATCH
STEP 4
ALTER Procedure [dbo].[proc_SendEmail]
(
@AlertLogID Int
,@AlertMessageTypeID int
,@Comment nvarchar(max) = ''
,@ActivityLogID int = -1
)
AS
declare @AlertSubject nvarchar(512)
,@AlertBody nvarchar(max)
,@myQuery nvarchar(512)
,@profile_name1 nvarchar(128)
,@return_value int
,@mymailitem int
,@Error_Source nvarchar(max)
---etc.
,@Error_Comment nvarchar(max)
,@Test int
/*
@return_value int -- not using at this point but 0 is OK 1 is failure
@mymailitem int -- not using now could store mailitem_id which is on msdb.dbo.sysmail_mailitems
sysmail_mailitems.sent_status could be either 0 new, not sent, 1 sent, 2 failure or 3 retry.
*/
select top 1 @profile_name1 = [name] from msdb.dbo.sysmail_profile
order by profile_id
set @profile_name1 = rtrim(ltrim(@profile_name1))
print '@profile_name1: ' + @profile_name1
print '@comment: ' + @comment
Declare @CrsrRecipient Cursor
BEGIN TRY
PRINT 'proc_SendEmail'
--set @test = 1/0 'test crashing
select
@AlertSubject = 'AlertSubject'
,@AlertBody = 'AlertBody'
,@recipients = 'russ@test.com'
print 'sending email ' + CAST(getdate() as nvarchar(100))
EXEC @return_value = msdb.dbo.sp_send_dbmail
@profile_name = @profile_name1
,@recipients = @EMAILID
,@body = @AlertBody
,@subject = @AlertSubject
,@mailitem_id = @mymailitem OUTPUT
print 'Done ' + CAST(getdate() as nvarchar(100))
print cast(@return_value as nvarchar(100))
update alertlog
set AlertSendStatusID = 1 --sent
where
@AlertLogID = AlertLogID
END TRY
BEGIN CATCH
PRINT 'ERROR OCCURED'
PRINT ERROR_Procedure() + ' ' + ERROR_MESSAGE()
select
@Error_Source = ' proc_SendEmail '
,@Error_Procedure = ERROR_Procedure()
--- etc.
INSERT INTO ERROR_LOG
( Error_Source
,Error_Procedure
---etc.)
VALUES
(
@Error_Source
,@Error_Procedure
,@Error_Message
--- etc.)
update alertlog
set AlertSendStatusID = 2 --error
where
@AlertLogID = AlertLogID
--if @@TRANCOUNT > 0
--begin
--commit
--end
END CATCH
View 5 Replies
View Related
Jun 8, 2001
We are migrating a SQL 6.5 application with 1900 stored procedures that use 100's of temp tables to SQL 2000.
A problem we have encountered was that we started out getting an "invalid column" errors on certain procedures. Investigation determined that the error was being generated in a nested procedure. The table that caused the error ended up being a temp table that was created using "select into". The following select statement from that temp table gave the invalid column error.
First thinking it was the "Select Into" we then discovered that the outer most procedure had created a temp table of the same name prior to executing the lower level procedure. After the select into, the next statement was a SELECT that went against what it thought was the inner temp table. However, it grabbed the outermost temp table and then couldn't find the appropriate columns and generated the error.
The solution, of course, was to rename the inner most temp table. We also remove the "select into" in the procedure by explicitly creating the temp table.
We tried creating some test procedures to attempt to reproduce this scenario without complete success.
Our test created 3 procedures (sp1 calling sp2 calling sp3) to mimic the current scenario. Sp1 created a temp table and executed sp2, which executed sp3. Sp3 created another temp table using the same name as the one created in sp1.
If we create all three procedures at the same time, it doesn't matter if we change the order in which they are created or whether we create the inner temp table explicitly or with a "select into", SQL Query Analyzer won't let us create the procedure because it finds that the temp table has been declared twice. If we create the procedures separately however, they compile and allow sp3 to create a temp table by the same name as sp1. After creating the procedures independently, they runs properly in all cases with everything in proper scope and no problems.
Admittedly, this is bad coding to start with, but what is happening with the scope of the temp tables within the stored procedures?
Thanks,
Glen Smith
View 1 Replies
View Related
Jul 23, 2005
Hi,I'm adapting access queries to sql server and I have difficulties withthe following pattern :query1 : SELECT * FROM Query2 WHERE A=@param1query 2: SELECT * FROM Table2 WHERE B=@param2The queries are nested, and they both use parameters.In MS Acccess the management of nested queries with parameters is soeasy (implicit declaration of parameters, transmission of parametersfrom main query to nested query)that I don't know what the syntax should be for stored procedures.The corresponding stored procedure would be something likeCREATE TABLE #TempTable (...table definition...)INSERT INTO #TempTable ExecProc spQuery2 @Param2SELECT * FROM #TempTable WHERE A=@Param1And spQuery2 would be : SELECT * FROM Table2 WHERE B=@ParamI was wondering if this syntax would work and if I can skip theexplicit declaration of #TempTable definition.Thanks for your suggestions.
View 5 Replies
View Related
Feb 11, 2006
Hi there,I have a data manipulation process written in a Nested Stored procedurethat have four levels deeper. When I run these individual proceduresindividually they all seems to be fine. Where as when I run them alltogether as Nested proces (calling one in another as sub-procedures) Logfile is growing pretty bad like 25 to 30GB.. and finally getting kickedafter running disk space. This process is running around 3hrs on a SQLserever Standard Box having dual processer and 2gb ram.This procedures have bunch of bulk updates and at least one cursor ineacch procedure that gets looped through.I was wondering if anybody experienced this situation or have any clueas to why is this happening and how to resolve this?I am in a pretty bad shape to deliver this product and in need of urgenthelp.Any ideas would be greatly appreciated..Thanks in advance*** Sent via Developersdex http://www.developersdex.com ***
View 1 Replies
View Related
Sep 16, 2015
We have a required to run multiple procedures in Single Go . And Error Occurred in any Procedure the it will rollback all the changes( Either all Proc run or None)
DECLARE @StartTime DATETIME=getdate(), @EndTime DATETIME=getdate()-1 , @Message VARCHAR(400)
BEGIN TRY
SET XACT_ABORT ON
BEGIN TRANSACTION
EXEC PROC1 @StartTime,@EndTime,@Message OUTPUT --[ Error Handling done here]
EXEC PROC2 @StartTime,@EndTime,@Message OUTPUT --[ Error Handling done here]
[Code] ....
Problem Statement is its not capturing the Error Message from Either Proc1 or Proc 2., its Capturing the Flat Message (The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction). How do i capture the Error Occurred in Proc 1 or Proc 2 into Log Tables.
View 7 Replies
View Related
Apr 16, 2007
Hi all,
I had developed all my reports using CRXI and stored procedures of MSSQL 2000. Now I am migrating from CRXI to SSRS. But i have a problem because of my report stored procedures. These stored procedures are complex and nested. When i try to use procedures in a report, it gives a error. When i comment out my EXEC line in my procedures, it goes OK.
Please help me to do this ?
View 1 Replies
View Related
Sep 15, 2014
Consider a situation where a stored procedure taking a varbinary(max) (BLOB) input parameter then calls a nested stored procedure and passes along that varbinary(max) as an input parameter to the nested stored procedure.
Is a copy of the BLOB provided to the nested stored procedure (passed by value) OR is the BLOB passed by reference.
My interest is in understanding the potential memory hit when handling large BLOBs in this environment.
For example, if the BLOB is 200MB, will SQL server need to allocate memory for a new copy each time it's passed to another stored procedure?
Looks like table type parameters are passed by reference, but I haven't been able to find any info on BLOBS in this context.
View 8 Replies
View Related
Jul 9, 2006
Hi,
I would like to use a stroed procedure within another stored procedure ( nested sp )
in a SQL project in VS.NET 2005. Since I have to use "context connection = true" as
connection string, I wont be able to use this connection for another sqlconnection
object. Because its already open. and If i try to use a regular connection string
("server=localhost;...") it will raise a security permission error. Having this
problem, Im not able to use nested stored procedures. Would anyone please give me a
hint how to resolve this issue?
View 4 Replies
View Related
Feb 15, 2008
Hi,
We are currently developing a crm that has the following main tables: client, sales, actions, contacts and employees with up to 4 million records. The db contains several other tables with extra information but these aren't relevant for this problem.
Clients have sales, contacts and actions. sales and actions can have seperate contacts as well, and contacts can be related to employees in a way.
Offcourse a security model was implemented but this only provides the option to hide or show all the records of these main tables. Now people noticed that it is a must to let people see for example only companies they created or do sales with. In other words access must be given at a record level.
The first thing that pops in my head is to create a client_security, sales_security, ... table that contains the employee_id and client_id or sales_id, or ... And add a join with one of these security tables for each select that is executed.
I wonder (because of the huge amount of data) if there isn't a better way to get this done?
Another possible issue that came to mind was, that when some data doesn't need protection or some people have the rigth to see everything, we would have to create the records in the security table anyways, resulting in probably hundreds of thousands of records.
Feedback on our solution (if we can call it that allready), or even keywords to find more about this issue via google (record based security really isn't the best description I guess) are most appreciated.
thx in advance.
View 5 Replies
View Related
Jul 20, 2005
First of all, I have never done any web-based stuff, so if thefollowing sounds ignorant, it's because I am!So far all our SQL Servers are accessed only over our network and weuse Windows authentication. Now the guy I'm working with on thedesign of our next stuff wants the two new databases (a transactionalone and my data warehouse) to be additionally accessed by web-basedapplications via our company intranet (NOT THE INTERNET). How do weauthenticate under these conditions? The webserver machine will betalking to the SQL Server ones, i.e. the databases will each be on thetheir own separate boxes. Can the webserver be a "user"? If so,and we want the actual users to have different privileges, then theweb-based apps have to manage that? Or is there a way for theweb-based apps to grab the Windows user and pass it to SQL Server?
View 7 Replies
View Related
Sep 30, 1999
How would you set up a group of developers-application programers in SQL 6.5 to let them have authority so that they all can store, update, delete, & execute each others stored procedures, within a particular database.
They are not permitted to modify the table structures within a data base, but I can not seem to let them have authority so that
they can work on and execute any of their sp's unless the DBO actually does the sp modifications? They do not want to modify any code by putting the sp owners name in front of the sp name (I don't blame them), otherwise Error 2812 results.
TIA for all you responses
View 1 Replies
View Related
Feb 26, 2004
Hello, everyone:
How to security the stored procedures? I want to prevent the stored procedures to be changed accidently. Thanks.
ZYT
View 5 Replies
View Related
Jul 23, 2005
First off I am a rookie at Sql Server. Ok let's give this a try. Mycompany has bought a new software package called Viewpoint. It's OnSql Server and written in VP. We do not have access to the code.There is a option in the software package called "ApplicationSecurity". When this is clicked you are unable to access the ViewpointDatabase from an outside application. Since I can't get to the code Ihave no idea how this feature works.I would like to keep the "Application Securty" ON so no one can accessthe Viewpoint Database from an outside application but I would like towrite my own outside application where I can access the Viewpointapplication tables while the Application Security in ON. Does anyoneknow what I am talking about? I know it is not very clear and I amworking with limited information, but any help would be appreciated.Thanks
View 1 Replies
View Related
Jul 3, 2007
Hi,
I wonder if SQL Server 2005 supports row based security?
I need to set some users to see data filtered by a specific field and value...
Example: User XPTO only sees data about vendor code = '123'
Is this possible in the box?
Best Regards,
View 1 Replies
View Related
Jan 16, 2007
I am attempting to set up a new user that has only the ability to run reports in the report manager.
I have created a new ActiveDirectory entry for DOMAINReportUser. I have a created a new folder with the reports, and have set this user as a Browser role on this directory, and all reports in the directory.
I have made it throught the security maze to gain access to these reports as this user, but I cannot dynamically run the reports. As the BUILTINAdministrator (content manager), I get a grayed background on the parameters and can run the reports. As this DOMAINReportUser, I get what looks like HTML parameter items, and cannot run a dynamic report. Even if I change the role of this user to Content Manager, I still cannot run a dynamic report.
How do you properly set up a user to be able to dynamically run, and only run, a report in Report Manager, and have this user only see reports in a single folder?
I have been fighting the security issues of creating a RS site and properly setting up access, and have yet to find a single site or person explaining the entire process in any coherant method.
Mark
View 3 Replies
View Related
Sep 27, 2000
To try to secure an outside web application we set up a user that
only has permission to execute a series of stored procedures that are
related to the appliation. Unfortunately a couple of those stored
procedures have to access system resources outside SQL Server so we
are using a call to xp_cmdshell from inside the stored procedure
SQL Server apparently won't let us do that unless we give our
restricted user (who is calling the initial stored procedure) execute
permission on xp_cmdshell. This, of course, negates most of the benefit
of setting up a restricted user. Is there some simple way I am missing
of running xp_cmdshell from inside s stored procedure without the user
calling the stored procedure having execute permission on xp_cmdshell?
View 1 Replies
View Related
Jul 20, 2005
Hi all,I know that it is possible to encrypt Stored Procedures using 'withencyption'.But my problem is that when there are so many decriptingmethods available how far will the encyption be secure.Is there any other method to encrypt the stored procedures that areresiding on the customer sites.We do not want the customers to meddle with the SPs.If anyone knows can u please let me know.ThanksDilini
View 1 Replies
View Related
Aug 1, 2006
Okay, I have sort of a peculiar permissions question I am wondering if someone can help me with. I'm suspect there's a simple answer, but I'm unaware of it. Basically, here's the scenario...
I have a CLR stored procedure which does some dynamic SQL building based on values sent in via XML. It's a CLR stored procedure using XML because I want to build a parameterized statement (to guard against SQL Injection) based on a flexible number of parameters which are basically passed in the XML.
The dynamic SQL ends up reading from a table I'll call TableX and I actually discovered an (understandable) quirk with security.
Basically, the connection context is impersonating a low-privilaged Windows account ("UserX") coming from a .NET application. UserX has no permission to the table referenced in the dynamic SQL and because of the dyanmic nature of the query, the stored procedure apparently adopts the security context of UserX. Naturally, this throws a security exception saying UserX has no SELECT permission on TableX.
Now, I can give UserX read permission to the table in question to get things running, but one of the points of using stored procedures is to defer security to the procedure level vs. configuration for tables or columns.
So in striving toward my ideal of security at the procedure level, my question is what is the best way to allow minimum privilege in this case?
I thought about having the internals of the CLR stored procedure run under a different (low-privalaged) security context, but I am wondering if there's an alternate configuration that may use the same connection, and be as secure, but simpler.
View 8 Replies
View Related
Oct 30, 2006
Hi I have created a Client/Server application. The Client connects remotely to the SQL 2005 server using thier unique user name and password.
The client application allows the users to update a form.
I need to add to the database a digital signature for that user when they update that form. This is intended to be a replacement for a physical signature that would appear on a paper form.
View 1 Replies
View Related
Jul 18, 2007
I'm putting together a demo for signing a stored procedure and I'm coming up blank as to why it is failing. Below is the code:
USE master
GO
--Create a pair of logins
CREATE LOGIN TestLogin WITH PASSWORD = 'P@55w0rd'
GO
CREATE LOGIN DummyLogin WITH PASSWORD = 'P@55w0rd'
GO
--Create a demo database
CREATE DATABASE SignatureDemo
GO
USE SignatureDemo
GO
--Create a low privileged user in the database
CREATE USER TestLogin FOR LOGIN TestLogin
GO
CREATE USER DummyLogin FOR LOGIN DummyLogin
GO
CREATE SCHEMA Test AUTHORIZATION DummyLogin
GO
--Create a dummy table and add data to it
CREATE TABLE Test.MyTable
(ID INT IDENTITY(1,1),
TestVal VARCHAR(10) NOT NULL)
GO
INSERT INTO Test.MyTable
(TestVal)
VALUES ('Test1')
GO
INSERT INTO Test.MyTable
(TestVal)
VALUES ('Test2')
GO
--Create a procedures to access test table
CREATE PROCEDURE Test.asp_Proc1
AS
SELECT ID, TestVal FROM Test.MyTable
GO
CREATE PROCEDURE dbo.asp_SignatureTest
AS
EXEC Test.asp_Proc1
GO
GRANT EXECUTE ON dbo.asp_SignatureTest TO TestLogin
GO
--Verify that TestLogin can not select from the table
EXECUTE AS LOGIN = 'TestLogin'
EXEC asp_SignatureTest
REVERT
GO
--Create a database master key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@55w0rd'
GO
CREATE CERTIFICATE TestCert WITH SUBJECT = 'Test Certificate'
GO
--Sign the procedure, remove the private key, and backup to a file
ADD SIGNATURE TO Test.asp_Proc1 BY CERTIFICATE TestCert
GO
ALTER CERTIFICATE TestCert REMOVE PRIVATE KEY
GO
BACKUP CERTIFICATE TestCert TO FILE = 'TestCert.cer'
GO
--Create a user in the database mapped to the certificate
CREATE USER CertLogin FROM CERTIFICATE TestCert
GO
--Grant permissions to the user
GRANT EXECUTE ON Test.asp_Proc1 TO CertLogin
GO
--Verify that TestLogin can now select from the table (I'm still receiving an execute permissions error on Test.asp_Proc1 for some reason.)
EXECUTE AS LOGIN = 'TestLogin'
EXEC asp_SignatureTest
REVERT
GO
USE master
GO
DROP DATABASE SignatureDemo
DROP LOGIN TestLogin
DROP LOGIN DummyLogin
DROP LOGIN CertLogin
DROP CERTIFICATE TestCert
GO
View 3 Replies
View Related
Sep 20, 2015
There is a STIG Check that does not allow grant "Connect SQL" directly to any logins except SQL System and the SA account. My way of resolving this is to do the following:
Step One:
We create a Server Role called SQL_APPLICATIONS – for the application accounts
We create a Server Role called SQL_DBA – for the DBA accounts
and give them direct “Connect SQL’ server permissions. MAKE THE ROLE OWNER = sysadmin (group)
Note: I think that creating a Server Role is only available starting with SQL Server 2012, but not sure. I am using SQL 2012
Step two:
I add the members (Logins - SQL & Windows) – in this case any application accounts and DBA accounts to the new roles respectively
Step three:
I remove the “Connect SQL” Permission from each Login
The first problem i noticed is that the maintenance plans failed with "The owner domainusername of job db_backup does not have server access.I am currently using a test system and wondered If you think I will have trouble with the application connecting when I try and implement on the production systems.
View 3 Replies
View Related
Jun 29, 2007
Hi All,
I have a report running and I am attempting to assign role based security. I added a group to the site level security. The group I added contains child groups. It doesn't seem that report server is looking into the child groups to see if the logged in user is a member of the child group. Is there anyway to get this to work instead of adding all the groups directly? I suspect that report server is using cominterop and cominterop is not traversing the directory tree?
Thanks,
Darren
View 1 Replies
View Related
