At 11:49 AM 3/02/2007, you wrote:

>Hi Tom,
>
>I created a sample SP test and was able to SELECT * FROM
>TEST_SP; -- (or EXECUTE PROCEDURE, it doesn't matter)
>Then
>REVOKE EXECUTE ON PROCEDURE TEST_SP FROM SYSDBA;
>
>And tried my SELECT * FROM TEST_SP, I was able to see the data
>
>Am I doing this wrong?

Understanding it wrongly, perhaps?

The attach request passes a username and password via the
client. Because the embedded server doesn't have "server-level
authentication", any username or password will get past the "server
interface" (by design).

However, database-level security (SQL privileges) is no different
from the situation if a full server were attaching to the
database. The user name and the role matter here. Password doesn't
matter (although some programming layers require it, even if it is a no-op).

So there is no way to prevent unauthorised access to a database with
the embedded client by password-protection.

However, at database level, usernames and roles ARE interesting, if
you have protected your databases objects with privileges. The user
names don't even have to exist in any other place but the database,
in ths environment. A database's ACL knows about users and roles and
privileged objects that have been declared via SQL privileges.

If you "log in" as SYSDBA you get SYSDBA's privileges and
restrictions, one of which is that you can't revoke SYSDBA'S
privileges. No other user can revoke SYSDBA'S privileges
either; and no user can revoke a privilege that it didn't grant.

If you log in as the owner then you have the owner's privileges but
you won't be able to grant privileges on objects that the owner
doesn't own or has not been granted WITH GRANT OPTION rights on.

If you don't provide any login details (user, role) then you have
PUBLIC's privileges, which isn't very much, by default only read
access to RDB$ROLES.

You always have to be very scientfic about designing an access scheme
with SQL privileges. They can become a spaghetti dinner with very
little effort from you. Just add sauce and sprinkle Parmesan.

IMO, it is much tidier to define neat packages of privileges into a
role that you're never going to change much; and then just grant and
revoke a role when users come and go. A role name isn't a password
by any stretch of the imagination, but it *can* be made obscure and
case-sensitive to discourage casual trespassing and it can be up to
31 characters. A funky role name is, however, no substitute for a
padlocked concrete bunker for securing a stand-alone computer.

./heLen