Status: Unverified
GOAL:
How to create a 4GL procedure to create SQL92 syntax to modify privileges.
GOAL:
How to create a 4GL procedure to create SQL92 syntax to modify permissions.
FACT(s) (Environment):
Progress 9.x
FIX:
This script does not manage all SQL privileges.
It is a tool to manage the most common privileges.
After this script is run in the Procedure Editor, the privs.sql will need to be run in a SQL92 Client which is connected to the database the script was originally run against.
DEF VAR cOutfile AS CHAR NO-UNDO INIT "c:\privs.sql" FORMAT "x(30)".
DEF VAR cUser AS CHAR NO-UNDO FORMAT "x(30)".
DEF VAR cRights AS CHAR NO-UNDO FORMAT "x(40)" INIT "".
DEF VAR lSelect AS LOGICAL INIT NO.
DEF VAR lInsert AS LOGICAL INIT NO.
DEF VAR lUpdate AS LOGICAL INIT NO.
DEF VAR lDelete AS LOGICAL INIT NO.
DEF VAR lTables AS LOGICAL INIT NO.
DEF VAR cTables AS CHAR VIEW-AS EDITOR INNER-LINES 5 INNER-CHARS 50.
DEF VAR i AS INT NO-UNDO INIT 1.
DEF VAR iEntries AS INT NO-UNDO.
DEF BUTTON btnGo AUTO-GO.
DEF BUTTON btnEnd AUTO-ENDKEY.
DEF FRAME frGetChoices
"Output File " cOutfile SKIP(.5)
cUser SKIP(.5)
lSelect SKIP(.5)
lInsert SKIP(.5)
lUpdate SKIP(.5)
lDelete SKIP(.5)
"Enter yes if you want this performed for all tables." SKIP(.5)
lTables SKIP(.5)
"If you selected no for all tables then enter tables below." SKIP(.5)
"Tables must be in the format <ownership>.<tablename>"
"Tables Should be separated by comma."
cTables SKIP(.5)
space(5) btnGo SPACE(30) btnEnd
.
UPDATE
cOutfile
cUser
lSelect
lInsert
lUpdate
lDelete
lTables
cTables
btnGo
btnEnd
WITH SIDE-LABELS CENTERED FRAME frGetChoices.
IF cUser = "" THEN
DO:
MESSAGE "User can not be left blank" VIEW-AS ALERT-BOX.
UNDO,RETRY.
END.
IF (lSelect AND lUpdate AND lDelete AND lInsert) THEN
DO:
ASSIGN cRights = "all".
END.
ELSE
DO:
ASSIGN cRights="".
IF lSelect
THEN ASSIGN cRights = cRights + "Select".
IF lInsert AND cRights = ""
THEN ASSIGN cRights = cRights + "Insert".
ELSE IF lInsert AND cRights <> "" THEN
cRights = cRights + ",Insert".
IF lUpdate AND cRights = ""
THEN ASSIGN cRights = cRights + "Update".
ELSE IF lUpdate AND cRights <> "" THEN
cRights = cRights + ",update".
IF lDelete AND cRights = ""
THEN ASSIGN cRights = cRights + "Delete".
ELSE IF lDelete AND cRights <> "" THEN
cRights = cRights + ",delete".
END.
IF lTables THEN
DO:
OUTPUT TO value(cOutfile).
FOR EACH _file WHERE NOT (_file-name BEGINS "_")
AND NOT (_file-name BEGINS "SYS").
DO:
PUT UNFORMATTED "grant " cRights " on pub.~"" _file-name "~" to " cUser ";" SKIP(1).
PUT UNFORMATTED "commit;" SKIP(1).
END.
END.
OUTPUT CLOSE.
END.
ELSE
DO:
OUTPUT TO value(cOutfile).
iEntries = NUM-ENTRIES(cTables).
DO i = 1 TO iEntries.
DO:
PUT UNFORMATTED "grant " cRights " on " ENTRY(i,cTables) "~" to " cUser ";" SKIP(1).
PUT UNFORMATTED "commit;" SKIP(1).
END.
END.
OUTPUT CLOSE.
END.