In the SQL-92 database, as well as in other SQL databases, regular users do not have access to database objects until the proper permissions are granted to them.
In order to grant privileges, connect to the database using a DBA account. In this case, use the default DBA that is the user who created the database. There is also a default DBA account in all OpenEdge database, entitled, SYSPROGRESS. The SYSPROGRESS would be created in the data, from the user that created the database.
Very important, ensure that there is a user, defined in the database, with DBA rights. New users will not be able to be created and rights will not be able to be granted/changed, unless a user with DBA rights exists.
Perform the following SQL statements to grant select access on the pub.customer to user1, grant full access on pub.customer to user2 and select access to the pub.state table to all users:
grant select on pub.customer to user1;
grant all on pub.customer to user2;
grant select on pub.state to public;
commit;
Add a commit statement to make the database changes permanent. In sqlexp, the AutoCommit option is false by default and if or when the user disconnects, the changes done on the database are rolled back.
The revoke SQL statement can be used to revert permissions given by the grant statement.
Notice that permissions can be granted to users even though the users have not been explicitly created.
NOTE: The SQL-92 Guide and Reference book provides additional information on the GRANT and REVOKE statements.
Try the following SQL statements and connect as user1 first and then user2:
select * from sysprogress.sysdbauth
Users (user1) and (user2) should be able to access this table because it is public.
Using the following command, both user1 and user2 should not be able to access this table because it is not public:
select "_userid", "_password", "_user-name" from pub."_user"
Using the following command: both users should be able to access records in pub.customer:
select * from pub.customer
Using the following command, user1 and user2 should be able to access this table because it is public:
select * from pub.state
Using the following command, user2 only should be able to update the pub.customer table:
Enabling authentication or creating users in the database: In order to enable authentication, users should be created so the engine will know the passwords for these users. To create users use the SQL statement CREATE USER.
Users can also be created from the 4GL Data Administration tool, however, in this case these users are regular users without special permissions from SQL-92.
When users are created in the database, the default DBA (the user who created the database) becomes disabled. It is important to grant DBA access to at least one user so you will have a valid DBA account. For example, the default DBA can be created as a user to have at least a valid DBA account.
create user 'dba1','password';
grant dba to 'dba1';
create user 'user1','x';
create user 'user2','x';
commit;
As stated above, ensure that a commit command is executed following such updates to ensure that the updates are not lost, when the user logs out.
An ODBC interface does not need a commit statement because in ODBC, AutoCommit is enabled by default. Even though, it is standard to create users to restrict the access to unauthorized users, for some applications, it may not be required to enable authorization.
If you wish to add a SQL DBA with resource rights to the database, it cannot be a user created on the 4GL side when security was turned on. There is a uniqueness required for the SQL user.
Permissions granted or revoked from the SQL side only apply to users that access the database using SQL.
