Solved

Stored procedures: Prompt users for input

Posted on 1998-08-11
12
430 Views
Last Modified: 2011-04-14
Is there a way to prompt users for input from a
stored procedure using Transact-SQL?
0
Comment
Question by:vd
  • 8
  • 4
12 Comments
 
LVL 4

Expert Comment

by:mitek
Comment Utility
Why would you need something like that ?
There is no way to do that. On the other hand, i'm just curious, what is the reason why you need this.

TSQL procedures are not supposed to be used in that way. The user input is usually collected somplace else and passed to TSQL SP for processing.

0
 

Author Comment

by:vd
Comment Utility
Mitek,

This is a good point. I wanted to provide an easy way for a  non-sa user to issue certain SA commands without giving him
the SA password! Because he uses ISQL he would like this approach rather than me build a program interface....
0
 
LVL 4

Expert Comment

by:mitek
Comment Utility
I see ...
One way to do that would be create a special account in SQL Server and explicitly grant it the permissions to run only the commands he heeds to run.

then, write a batch script (better yet, a perl script) that will contain the isql call, like

isql -S SERV -U special -P x123

with clear-text password.
the account should be as restricted as possible, giving only the minimum level of necessary permissions to run certain commands on certain set of objects

the perl script, on the other hand, should be also assigned permissions to be readable/executable only by this specific user.

that's the way i would do it ...
0
 
LVL 4

Expert Comment

by:mitek
Comment Utility
Or, if you want to get really fancy, give this account one and only one permission -- to execute a specific SP, that will take the command to be executed as one of its parameters, validate, that it's ok and execute it on the behalf of the user.
But again, the SP will execute these commands with SA authority, so this SP has to be written very carefully.
It should also check the username of the user who runs it and deny anyone who is not on the list of valid users to run it.

Just ideas ...

0
 
LVL 4

Accepted Solution

by:
mitek earned 50 total points
Comment Utility
So, here is the way I would do that:

a) create a special user account. revoke most of its default permissions that it doesn't need and grant it a very specific (narrow) set of permissions.

b) write a perl script that collects the required input, and runs the appropriate query based on that input.

c) make this script non-modifyable by anyone and readable/executable by a specific user who needs to run it. it shouldn't be readable by anyone else (except Administrators, of course)

d) if GRANT statement can't give you the granularity you need, consider writing a store procedure that will verify the user and check (possibly, based on some kind of custom "ACL" table) if the operation requested is allowed. But, in 90% of scenarios, it shouldn't be necessary, and built-in SQL Server security should do just fine.


0
 

Author Comment

by:vd
Comment Utility
mitek, what is a "clear-text" password?
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 4

Expert Comment

by:mitek
Comment Utility
well, i mean, a perl script will contain a call like this one

$result = `isql -S SERV -U special -P passwd -Q "$action"`;

clear-text password means that password is not encrypted,
and potentially anyone can sniff it by looking at the script code

or, better yet, do something like

$result = `isql -S SERV -E -U special -Q "$action"`;

this way, you won't need to put a password in the script file, since option -E employs "trusted connection", meaning it'll use the NT password that was entered by user at the login prompt !

(run isql /? to find out all other options needed for the command to run correctly. there are much more isql options you'll need to put into this call)


0
 

Author Comment

by:vd
Comment Utility
I see...we don't have Perl script. I like your idea of a SP
that executes with SA authority...however, if a non-SA ID attempts to execute a SP which contains, lets say, an sp_addlogin command,an error occurs indicating one has to be a database owner
or an SA to execute this command.....  
0
 
LVL 4

Expert Comment

by:mitek
Comment Utility
well, perl script was just an example. can be dos batch script, or something like that.

the thing is ... i'm almost sure that an SP owned by SA will behave like a unix suid script, meaning that all the commands within it will be executed with SA authority. i noticed that a while ago ... you should definetely test it.

tell me what you found out.
0
 
LVL 4

Expert Comment

by:mitek
Comment Utility
A really sick way of doing what you need to do would be create an SP which will run in SQL Executive, say, every 30 minutes (well, of course, it will be executing with SA authority !)

it will be watching for a special table with flags (and possibly SQL statements, too)

SP does a select against it every 30 min or so. if flags are not set, it does nothing. otherwise, it will execute an SQL statement corresponding to the flag (possibly, but not necessarily, reading it from the same table)

there will be another SP that will modify this table's flag states, an it can be executed by a user.

the idea is, user will not directly run the SA commands. it will be done on his behalf by an SQL Executive SP, at regular time intervals.

but i think it's just paranoic, don't pay attention to this nonsense :))


0
 

Author Comment

by:vd
Comment Utility
I tried creating a SP from SA id; if it contains SA type
commands or SP calls, it will not work for a non SA id!


 
0
 
LVL 4

Expert Comment

by:mitek
Comment Utility
then -- try the sick solution. it's actually not that sick, it's just not obvious and will be harder to implement. but, if implemented correctly, it will surely be one of the safest methods that will do the trick.

notice, that prompting for user input from an SP (if such possibility existed) wouldn't even help, since this SP wouldn't be able to run statements with an SA authority anyway ...

0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
MS SQL export CSV & schedule It 9 40
Restore Procedure question 4 28
SQL Inner Join Vs SubQueries 9 23
SQL Date Retrival 7 21
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

763 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

10 Experts available now in Live!

Get 1:1 Help Now