Hi,
Is it possible to grant users using sqlexp on Linux? If so how or point me to the documentation I'm unable to find.
I've added sysprogress as user and sqlexp can show me data but I'm unable to grant rights. And I was also unable to find how to grant read-only rights. Another question I have does the sql user need to be in the _user table?
Richard
The command line should be something like this:
DB=sports
PORT=sqlsports # as defined in /etc/services
URL="jdbc:datadirect:openedge://localhost:-1;databaseName=$DB"
URL="${URL};servicename=$PORT;User=Paul;Password=TheWiz!"
URL="${URL};defaultSchema=pub"
sqlexp -driverUrl "$URL" -infile $SCRIPTDIR/update_grants.$DB.sql -sqlverbose
update_grants_sports.sql should look like this:
GRANT SELECT ON PUB."customer" TO bob;
GRANT SELECT ON PUB."customer" TO jane;
GRANT SELECT ON PUB."customer" TO mike;
COMMIT;
Hallo
In the pdf documentation you can have a look at OpenEdge Data Management: SQL Development. for V11.3 on page 65.
This explains the GRANT statement.
By default sysprogress will exist and also the user that created the database.
There is VST tables you can check to see what has been assigned. _sysdbauth _systabauth _sysfldauth.
Thank you
A Venter
What I'm missing in the documentation is how sqlexp on Linux should work or look like. For instance after 'grant dba to "user1";' enter, 'grant resource to "user1";' enter, then 'commit;' enter and close with 'exit' and enter. I can't find (knwoledgebase or docs) if this is correct. After trying it like this and start sqlexp as user1 I get 'no access'.
I'll give it another try tonight, maybe I did something wrong. Thanks for the replies!
The command line should be something like this:
DB=sports
PORT=sqlsports # as defined in /etc/services
URL="jdbc:datadirect:openedge://localhost:-1;databaseName=$DB"
URL="${URL};servicename=$PORT;User=Paul;Password=TheWiz!"
URL="${URL};defaultSchema=pub"
sqlexp -driverUrl "$URL" -infile $SCRIPTDIR/update_grants.$DB.sql -sqlverbose
update_grants_sports.sql should look like this:
GRANT SELECT ON PUB."customer" TO bob;
GRANT SELECT ON PUB."customer" TO jane;
GRANT SELECT ON PUB."customer" TO mike;
COMMIT;
Thanks a lot Paul!
this is what i have used in some of my scripts for UNIX and Linux (requires that a server be started first. SQL cannot be used in single-user mode):
sqlexp ${DB_NAME} -S ${DB_PORT} <<-HERE_EOF
create user '${DB_USERID}', '${DB_PASSWORD}';
grant dba,resource to '${DB_USERID}';
commit;
quit;
HERE_EOF
to give somebody just read access, you grant them the SELECT privilege on a table.Thanks Paul and Gus!