Hi,

Thanks for the lengthy reply. Makes sense.

Also, is there a "WITH ENCRYPTION"(available in sql server) option for SPs in firebird. In SQL Server when you try to look at SP that was compiled "WITH ENCRYPTION", you can't see the source.

I just feel like I should hide something :)

Vlad

----- Original Message ----
From: Helen Borrie <helebor@...>
To: firebird-support@yahoogroups.com
Sent: Friday, February 2, 2007 9:58:02 PM
Subject: Re: [firebird-support] How to change SYSDBA password for embedded













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












<!--

#ygrp-mlmsg {font-size:13px;font-family:arial,helvetica,clean,sans-serif;}
#ygrp-mlmsg table {font-size:inherit;font:100%;}
#ygrp-mlmsg select, input, textarea {font:99% arial,helvetica,clean,sans-serif;}
#ygrp-mlmsg pre, code {font:115% monospace;}
#ygrp-mlmsg * {line-height:1.22em;}
#ygrp-text{
font-family:Georgia;
}
#ygrp-text p{
margin:0 0 1em 0;
}
#ygrp-tpmsgs{
font-family:Arial;
clear:both;
}
#ygrp-vitnav{
padding-top:10px;
font-family:Verdana;
font-size:77%;
margin:0;
}
#ygrp-vitnav a{
padding:0 1px;
}
#ygrp-actbar{
clear:both;
margin:25px 0;
white-space:nowrap;
color:#666;
text-align:right;
}
#ygrp-actbar .left{
float:left;
white-space:nowrap;
}
.bld{font-weight:bold;}
#ygrp-grft{
font-family:Verdana;
font-size:77%;
padding:15px 0;
}
#ygrp-ft{
font-family:verdana;
font-size:77%;
border-top:1px solid #666;
padding:5px 0;
}
#ygrp-mlmsg #logo{
padding-bottom:10px;
}

#ygrp-vital{
background-color:#e0ecee;
margin-bottom:20px;
padding:2px 0 8px 8px;
}
#ygrp-vital #vithd{
font-size:77%;
font-family:Verdana;
font-weight:bold;
color:#333;
text-transform:uppercase;
}
#ygrp-vital ul{
padding:0;
margin:2px 0;
}
#ygrp-vital ul li{
list-style-type:none;
clear:both;
border:1px solid #e0ecee;
}
#ygrp-vital ul li .ct{
font-weight:bold;
color:#ff7900;
float:right;
width:2em;
text-align:right;
padding-right:.5em;
}
#ygrp-vital ul li .cat{
font-weight:bold;
}
#ygrp-vital a {
text-decoration:none;
}

#ygrp-vital a:hover{
text-decoration:underline;
}

#ygrp-sponsor #hd{
color:#999;
font-size:77%;
}
#ygrp-sponsor #ov{
padding:6px 13px;
background-color:#e0ecee;
margin-bottom:20px;
}
#ygrp-sponsor #ov ul{
padding:0 0 0 8px;
margin:0;
}
#ygrp-sponsor #ov li{
list-style-type:square;
padding:6px 0;
font-size:77%;
}
#ygrp-sponsor #ov li a{
text-decoration:none;
font-size:130%;
}
#ygrp-sponsor #nc {
background-color:#eee;
margin-bottom:20px;
padding:0 8px;
}
#ygrp-sponsor .ad{
padding:8px 0;
}
#ygrp-sponsor .ad #hd1{
font-family:Arial;
font-weight:bold;
color:#628c2a;
font-size:100%;
line-height:122%;
}
#ygrp-sponsor .ad a{
text-decoration:none;
}
#ygrp-sponsor .ad a:hover{
text-decoration:underline;
}
#ygrp-sponsor .ad p{
margin:0;
}
o {font-size:0;}
.MsoNormal {
margin:0 0 0 0;
}
#ygrp-text tt{
font-size:120%;
}
blockquote{margin:0 0 0 4px;}
.replbq {margin:4;}
-->









____________________________________________________________________________________
The fish are biting.
Get more visitors on your site using Yahoo! Search Marketing.
http://searchmarketing.yahoo.com/arp/sponsoredsearch_v2.php

[Non-text portions of this message have been removed]