Stored Procedures: Grant Command

I'd like to trigger a stored procedure whereby when the field gets updated, a stored procedure is run that grants or revokes access to the user whose record is updated. Basically, I'm trying to control access levels via an employee's record. However, if I write an SPL that says "grant all on tablename to theusername" where tablename and theusername are variables, the variables are handled as literals and tries to grant a user named "theusername" access to a table named "tablename". I know how to get around this with 4GL, etc., but I'm hoping to find a way to put this code into SPL so Java and other programs don't have to handle the process. In fact, I wrote an 4GL program and tried invoking it with an SPL, but it seems most system commands can not be invoked SPL. Any clues or guidance offered would be greatly appreciated. LO

Lane O'Connor <laneoc@davesworld.net> wrote in message news:3939C875.6B967900@davesworld.net... > I'd like to trigger a stored procedure whereby when the field gets > updated, a stored procedure is run that grants or revokes access to the > user whose record is updated. Basically, I'm trying to control access > levels via an employee's record. > > However, if I write an SPL that says > > "grant all on tablename to theusername" One small point, do you have to GRANT ALL - doesn't that leave them a lit bit too much power over the table? GRANT SELECT, GRANT INSERT and so on, and then at a later date you can expand it to allow different clases of user. My apologies if you thought of that already, which is likely I suppose since you had the sense to automate the permission granting. > where tablename and theusername are variables, the variables are handled > as literals and tries to grant a user named "theusername" access to a > table named "tablename". Yeah, SPL just doesn't do dynamic SQL. In fact I had trouble when I tried to write a 4GL program to do this - I never got round to finishing it because we have a script somewhere. There's quite a lot about SPL in the deja archives (if they still work) for this newsgroup. It's quite easy to hack together a script round something like OUTPUT TO PIPE 'dbaccess some_db WITHOUT HEADINGS' SELECT 'GRANT INSERT ON ' || tabname ' TO ... and so on. > I know how to get around this with 4GL, etc., but I'm hoping to find a > way to put this code into SPL so Java and other programs don't have to > handle the process. > > In fact, I wrote an 4GL program and tried invoking it with an SPL, but > it seems most system commands can not be invoked SPL. The program really should work, and a system call from SPL is the way to go for what you want to do. It's very probably either a matter of permissions or of environment. This is usually what prevents things from working from SPL systems calls, just like with cron or nohup. You need to write a script to call the program, make sure that the script sets absolutely all the environment variables that it needs, and that it and the program have execute permissions and that they're not generating temp files (including error logs) in some directory where write permissions might be denied to the person running the SP. Procedures run as the log-in ID of the person running them. (Not quite sure how it all goes under NT though). Try running your script with nohup, and if it doesn't work then add env vars till it does. If you don't have a script, then that's a good first step. > Any clues or guidance offered would be greatly appreciated. Slight appreciation is probably more appropriate for this post! > > LO > -- Andrew Pearson: "exactly what the web needs less of".

Lane O'Connor wrote: > > I'd like to trigger a stored procedure whereby when the field gets > updated, a stored procedure is run that grants or revokes access to the > user whose record is updated. Basically, I'm trying to control access > levels via an employee's record. > > However, if I write an SPL that says > > "grant all on tablename to theusername" > > where tablename and theusername are variables, the variables are handled > as literals and tries to grant a user named "theusername" access to a > table named "tablename". If you are running the later engines check out the Dynamic SPL bladelet from Paul Brown. > > I know how to get around this with 4GL, etc., but I'm hoping to find a > way to put this code into SPL so Java and other programs don't have to > handle the process. > > In fact, I wrote an 4GL program and tried invoking it with an SPL, but > it seems most system commands can not be invoked SPL. You can run any command you have permission to run from within SPL, but what you might be encountering is a locking problem 'cos the system command runs in another session to the calling SPL > > Any clues or guidance offered would be greatly appreciated. > > LO -- Paul Watson # WF Software Ltd # You are only young once Tel: +44 1436 674729 # but you can be immature Fax: +44 1436 678693 # for ever www.wfsoftware.com #

Lane O'Connor wrote: > I'd like to trigger a stored procedure whereby when the field gets > updated, a stored procedure is run that grants or revokes access to the > user whose record is updated. Basically, I'm trying to control access > levels via an employee's record. > > However, if I write an SPL that says > > "grant all on tablename to theusername" > > where tablename and theusername are variables, the variables are handled > as literals and tries to grant a user named "theusername" access to a > table named "tablename". SPL doesn't handle Dynamic SQL unless you get the Dynamic SQL, umm, Datablade which Paul Brown described a month or two ago. And GRANT statements never accept place holders (forn either the permissions nor the user being given permission nor the user giving the permission), so you definitely have to do this with dynamic SQL (or include all possible users and all possible permissions in the SP. > [...] -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v1.00.PC1 -- see http://www.perl.com/CPAN #include <disclaimer.h>

>Subject: Stored Procedures: Grant Command >From: Lane O'Connor laneoc@davesworld.net >Date: 04.06.00 05:09 W. Europe Daylight Time >Message-id: <3939C875.6B967900@davesworld.net> > >I'd like to trigger a stored procedure whereby when the field gets >updated, a stored procedure is run that grants or revokes access to the >user whose record is updated. Basically, I'm trying to control access >levels via an employee's record. > >However, if I write an SPL that says > >"grant all on tablename to theusername" > >where tablename and theusername are variables, the variables are handled >as literals and tries to grant a user named "theusername" access to a >table named "tablename". > >I know how to get around this with 4GL, etc., but I'm hoping to find a >way to put this code into SPL so Java and other programs don't have to >handle the process. > >In fact, I wrote an 4GL program and tried invoking it with an SPL, but >it seems most system commands can not be invoked SPL. > >Any clues or guidance offered would be greatly appreciated. > >LO > > > > > > > for java you can write a java stored procedure, provided you have 9.2 Nona