Authorization problem with embsql 
Author Message
 Authorization problem with embsql

Hi, I'm writing a program in C++ with embedded-sql (Windows 2000). The login
user must have EXECUTE PRIVILEGE, but i can't revoke the SELECT PRIVILEGE
from this user.
Example:
Consider two users. SALARY and TOTAL. The user SALARY must select each row
of table salary. The user TOTAL must select each row of another table, but
he can't select any row of table salary. My program is slc.exe. Now, I start
the program with slc [user] [pass] and the two users must have the EXECUTE
PRIVILEGE. The command-line in the C++ program is:

EXEC SQL DECLARE c1 CURSOR FOR SELECT... FROM TABLE SALARY WHERE ...

Now, if the program connects the user total (I revoked the privilege select
from table salary, but i granted the privilege execute, necessary to work
with emb-sql), the cursor works and the output is all the rows of table
salary (but the user doesn't have privilege to select !!!!).

The question is: how can i write only one executable-file, that stop the
output if the user don't have select privilege on the specified table? Thank
You. Bye.



Mon, 13 Sep 2004 22:16:38 GMT
 Authorization problem with embsql

Quote:

> Hi, I'm writing a program in C++ with embedded-sql (Windows 2000). The login
> user must have EXECUTE PRIVILEGE, but i can't revoke the SELECT PRIVILEGE
> from this user.
> Example:
> Consider two users. SALARY and TOTAL. The user SALARY must select each row
> of table salary. The user TOTAL must select each row of another table, but
> he can't select any row of table salary. My program is slc.exe. Now, I start
> the program with slc [user] [pass] and the two users must have the EXECUTE
> PRIVILEGE. The command-line in the C++ program is:

> EXEC SQL DECLARE c1 CURSOR FOR SELECT... FROM TABLE SALARY WHERE ...

> Now, if the program connects the user total (I revoked the privilege select
> from table salary, but i granted the privilege execute, necessary to work
> with emb-sql), the cursor works and the output is all the rows of table
> salary (but the user doesn't have privilege to select !!!!).

> The question is: how can i write only one executable-file, that stop the
> output if the user don't have select privilege on the specified table? Thank
> You. Bye.

What EXECUTE privileges are you talking about?  DB2 (Unix/Windows) does not
yet have that kind of priveleges.

Which bind option did you use for the package?  If you used BIND, then the
SQL statements are executed with the authority of the user who did the bind
operation.  If it is RUN, then the user who connected to the database inside
the executable is used.

--
Knut Stolze
DB2 Spatial Extender
IBM Silicon Valley Lab



Tue, 14 Sep 2004 02:06:44 GMT
 Authorization problem with embsql

Quote:

> What EXECUTE privileges are you talking about?  DB2 (Unix/Windows) does not
> yet have that kind of priveleges.

My answer wasn't complete... EXECUTE privileges don't exist for tables.

--
Knut Stolze
DB2 Spatial Extender
IBM Silicon Valley Lab



Tue, 14 Sep 2004 02:10:27 GMT
 Authorization problem with embsql
Ale,

You are correct - in an ESQL program, granting execute privilege on the
package gives the user the same privileges the binder has for static SQL
statements (within the restrictions of the static SQL).

If the ESQL program has dynamic SQL statements, the user must have
execute privilege on the package AND the appropriate authority to
execute the dynamic SQL statement(s) (i.e. SELECT privilege to access a
table or view).

So - the only way you can do what you want is with dynamic ESQL.

This is described in the Administration Guide under "Controlling Access
to Database Objects".

--
====================================
To reply, delete the 'x' from my email

Jerry Stuckle
JDS Computer Training Corp.

====================================



Tue, 14 Sep 2004 04:49:44 GMT
 Authorization problem with embsql

Quote:

> Ale,

> You are correct - in an ESQL program, granting execute privilege on the
> package gives the user the same privileges the binder has for static SQL
> statements (within the restrictions of the static SQL).

> If the ESQL program has dynamic SQL statements, the user must have
> execute privilege on the package AND the appropriate authority to
> execute the dynamic SQL statement(s) (i.e. SELECT privilege to access a
> table or view).

> So - the only way you can do what you want is with dynamic ESQL.

> This is described in the Administration Guide under "Controlling Access
> to Database Objects".

See remarks above about bind/run; a user does not need to have select
privilege to access a table in dynamic esql if the dynamicrules bind
option is used and the the authorization ID of the package owner has
been granted access.

DYNAMICRULES
Defines which rules apply to dynamic SQL at run time for the initial
setting of the values used for authorization ID and for the implicit
qualification of unqualified object references.

RUN
Specifies that the authorization ID of the user executing the package
is to be used. This is the default value.

BIND
Specifies that all of the rules that apply to static SQL for
authorization and qualification are to be used at run time. That is,
the authorization ID of the package owner is to be used for
authorization checking of dynamic SQL statements, and the default
package qualifier is to be used for implicit qualification of
unqualified object references within dynamic SQL statements.
When binding a package with this option, the binder of the package
should not have any authorities that the user of the package should
not receive, because dynamic SQL statements will be using the
authorization ID of the package owner. The following dynamically
prepared SQL statements cannot be used within a package that has been
bound with this option: GRANT, REVOKE, ALTER, CREATE, DROP, COMMENT
ON, RENAME, SET CONSTRAINTS, and SET EVENT MONITOR STATE.



Sat, 18 Sep 2004 18:16:03 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. Problems starting CGI containing Oracle embSQL from Apache

2. Authorization problem

3. authorization problem with MySQL

4. Authorization problem in Oracle Webserver 2.0 (and 1.0)

5. Login problem - Authorizations locked?

6. Authorization problem, can't see tables.

7. Serge Rielau, My Apologies (DB2 for Win98 Authorization Problem)

8. Authorization Problems

9. MMDB_RC_NO_AUTH -- authorization problem

10. WaveLink Studio Authorization problems

11. Apache 1.3.2 - mysql authorization problems

12. Use Of the HTTP authorization to logon to sql server


 
Powered by phpBB® Forum Software