Re: Granting access to SYSTEM_MAKE_BACKUP() function
Duncan Groenewald <[email protected]> Tue, 3 Jul 2007 23:38:52 +1200
| Newsgroups | gmane.comp.db.mckoi |
|---|---|
| Message-ID | <[email protected]> |
--Apple-Mail-10--1002897788
Content-Transfer-Encoding: 7bit
Content-Type: text/plain;
charset=US-ASCII;
delsp=yes;
format=flowed
Looking at the Database.java code could I modify the method below to
check if the user has SELECT permission ?
/**
* Returns true if the user is allowed to execute the given stored
procedure.
*/
public boolean canUserExecuteStoredProcedure(DatabaseQueryContext
context,
User user, String procedure_name) throws
DatabaseException {
// Currently you can only execute a procedure if you are a
member of the
// secure access priv group.
return userHasSecureAccess(context, user);
}
/**
* Returns true if the user is allowed to execute the given stored
procedure.
*/
public boolean canUserExecuteStoredProcedure(DatabaseQueryContext
context,
User user, String procedure_name) throws
DatabaseException {
if (userHasSchemaGrant(context, user,
table.getSchema(),
Privileges.PROCEDURE_EXECUTE_PRIVS)) {
return true;
}
// Currently you can only execute a procedure if you are a
member of the
// secure access priv group.
return userHasSecureAccess(context, user);
}
On 30/06/2007, at 10:25 AM, Tobias Downer wrote:
> I apologize. I just checked the code and the user must belong to
> the "secure access" group to be able to call the backup procedure,
> so you need to add the user to this group if you wish for them to
> use the backup function. The simplest way to do this is by using
> the following command;
>
> insert into SYS_INFO.sUSRUserPriv ( UserName, PrivGroupName )
> values ( '[username]', 'secure access' );
>
> Toby.
>
> Duncan Groenewald wrote:
>> I don't get grantee @PUBLIC. How would I manage to get this ?
>> On 27/06/2007, at 1:48 PM, Tobias Downer wrote:
>>> Hi,
>>>
>>> The following query may help you,
>>>
>>> SELECT * FROM SYS_INFO.sUSRGrant;
>>>
>>> Look for "SYS_INFO.SYSTEM_MAKE_BACKUP" under the param column,
>>> the grantee should be "@PUBLIC". You may need to restart the
>>> database after grants are altered.
>>>
>>> Toby.
>>>
>>> Duncan Groenewald wrote:
>>>
>>>> I just tried this but get the following error:
>>>> Backup Database
>>>> "Exception executing query"
>>>> "User not permitted to call: SYSTEM_MAKE_BACKUP"
>>>> is there any way I can check whether permissions have been
>>>> created ?
>>>> On 23/06/2007, at 4:16 AM, Tobias Downer wrote:
>>>>
>>>>> grant all on SYS_INFO.SYSTEM_MAKE_BACKUP to [username]
>>>>>
>
>
> ---------------------------------------------------------------
> Mckoi SQL Database mailing list http://www.mckoi.com/database/
> To unsubscribe, send a message to [email protected]
>
--Apple-Mail-10--1002897788
Content-Transfer-Encoding: quoted-printable
Content-Type: text/html;
charset=ISO-8859-1
<html><body style=3D"word-wrap: break-word; -webkit-nbsp-mode: space; =
-webkit-line-break: after-white-space; ">
<div><div style=3D"margin-top: 0px; margin-right: 0px; margin-bottom: =
0px; margin-left: 0px; "><font class=3D"Apple-style-span" face=3D"Monaco" =
size=3D"2"><span class=3D"Apple-style-span" style=3D"font-size: =
10px;">Looking at the Database.java code could I modify the method below =
to=A0</span></font></div><div style=3D"margin-top: 0px; margin-right: =
0px; margin-bottom: 0px; margin-left: 0px; "><font =
class=3D"Apple-style-span" face=3D"Monaco" size=3D"2"><span =
class=3D"Apple-style-span" style=3D"font-size: 10px;">check if the user =
has SELECT permission ?</span></font></div><div style=3D"margin-top: =
0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "><font =
class=3D"Apple-style-span" face=3D"Monaco" size=3D"2"><span =
class=3D"Apple-style-span" style=3D"font-size: 10px;"><br =
class=3D"webkit-block-placeholder"></span></font></div><div =
style=3D"margin-top: 0px; margin-right: 0px; margin-bottom: 0px; =
margin-left: 0px; "><font face=3D"Monaco" size=3D"2" style=3D"font: =
10.0px Monaco">=A0=A0</font><font face=3D"Monaco" size=3D"2" =
color=3D"#236e25" style=3D"font: 10.0px Monaco; color: =
#236e25">/**</font></div><div style=3D"margin-top: 0px; margin-right: =
0px; margin-bottom: 0px; margin-left: 0px; "><font face=3D"Monaco" =
size=3D"2" color=3D"#236e25" style=3D"font: 10.0px Monaco; color: =
#236e25">=A0=A0 * Returns true if the user is allowed to execute the =
given stored procedure.</font></div><div style=3D"margin-top: 0px; =
margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "><font =
face=3D"Monaco" size=3D"2" color=3D"#236e25" style=3D"font: 10.0px =
Monaco; color: #236e25">=A0=A0 */</font></div><div style=3D"margin-top: =
0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "><font =
face=3D"Monaco" size=3D"2" style=3D"font: 10.0px Monaco">=A0 =
</font><font face=3D"Monaco" size=3D"2" color=3D"#760f50" style=3D"font: =
10.0px Monaco; color: #760f50">public</font><font face=3D"Monaco" =
size=3D"2" style=3D"font: 10.0px Monaco"> </font><font face=3D"Monaco" =
size=3D"2" color=3D"#760f50" style=3D"font: 10.0px Monaco; color: =
#760f50">boolean</font><font face=3D"Monaco" size=3D"2" style=3D"font: =
10.0px Monaco"> canUserExecuteStoredProcedure(DatabaseQueryContext =
context,</font></div><div style=3D"margin-top: 0px; margin-right: 0px; =
margin-bottom: 0px; margin-left: 0px; "><font face=3D"Monaco" size=3D"2" =
style=3D"font: 10.0px Monaco">=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 User =
user, String procedure_name) </font><font face=3D"Monaco" size=3D"2" =
color=3D"#760f50" style=3D"font: 10.0px Monaco; color: =
#760f50">throws</font><font face=3D"Monaco" size=3D"2" style=3D"font: =
10.0px Monaco"> DatabaseException {</font></div><div style=3D"margin-top: =
0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "><font =
face=3D"Monaco" size=3D"2" style=3D"font: 10.0px Monaco">=A0 =A0 =
</font><font face=3D"Monaco" size=3D"2" color=3D"#236e25" style=3D"font: =
10.0px Monaco; color: #236e25">// Currently you can only execute a =
procedure if you are a member of the</font></div><div style=3D"margin-top:=
0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "><font =
face=3D"Monaco" size=3D"2" style=3D"font: 10.0px Monaco">=A0 =A0 =
</font><font face=3D"Monaco" size=3D"2" color=3D"#236e25" style=3D"font: =
10.0px Monaco; color: #236e25">// secure access priv =
group.</font></div><div style=3D"margin-top: 0px; margin-right: 0px; =
margin-bottom: 0px; margin-left: 0px; "><font face=3D"Monaco" size=3D"2" =
style=3D"font: 10.0px Monaco">=A0 =A0 </font><font face=3D"Monaco" =
size=3D"2" color=3D"#760f50" style=3D"font: 10.0px Monaco; color: =
#760f50">return</font><font face=3D"Monaco" size=3D"2" style=3D"font: =
10.0px Monaco"> userHasSecureAccess(context, user);</font></div><div =
style=3D"margin-top: 0px; margin-right: 0px; margin-bottom: 0px; =
margin-left: 0px; "><font face=3D"Monaco" size=3D"2" style=3D"font: =
10.0px Monaco">=A0 }</font></div> <br =
class=3D"webkit-block-placeholder"></div><div><br =
class=3D"webkit-block-placeholder"></div><div><div style=3D"margin-top: =
0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "><font =
face=3D"Monaco" size=3D"2" style=3D"font: 10.0px Monaco">=A0=A0</font><fon=
t face=3D"Monaco" size=3D"2" color=3D"#236e25" style=3D"font: 10.0px =
Monaco; color: #236e25">/**</font></div><div style=3D"margin-top: 0px; =
margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "><font =
face=3D"Monaco" size=3D"2" color=3D"#236e25" style=3D"font: 10.0px =
Monaco; color: #236e25">=A0=A0=A0* Returns true if the user is allowed =
to execute the given stored procedure.</font></div><div =
style=3D"margin-top: 0px; margin-right: 0px; margin-bottom: 0px; =
margin-left: 0px; "><font face=3D"Monaco" size=3D"2" color=3D"#236e25" =
style=3D"font: 10.0px Monaco; color: #236e25">=A0=A0=A0*/</font></div><div=
style=3D"margin-top: 0px; margin-right: 0px; margin-bottom: 0px; =
margin-left: 0px; "><font face=3D"Monaco" size=3D"2" style=3D"font: =
10.0px Monaco">=A0=A0</font><font face=3D"Monaco" size=3D"2" =
color=3D"#760f50" style=3D"font: 10.0px Monaco; color: =
#760f50">public</font><font face=3D"Monaco" size=3D"2" style=3D"font: =
10.0px Monaco">=A0</font><font face=3D"Monaco" size=3D"2" =
color=3D"#760f50" style=3D"font: 10.0px Monaco; color: =
#760f50">boolean</font><font face=3D"Monaco" size=3D"2" style=3D"font: =
10.0px Monaco">=A0canUserExecuteStoredProcedure(DatabaseQueryContext =
context,</font></div><div style=3D"margin-top: 0px; margin-right: 0px; =
margin-bottom: 0px; margin-left: 0px; "><font face=3D"Monaco" size=3D"2" =
style=3D"font: 10.0px Monaco">=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0=A0User =
user, String procedure_name)=A0</font><font face=3D"Monaco" size=3D"2" =
color=3D"#760f50" style=3D"font: 10.0px Monaco; color: =
#760f50">throws</font><font face=3D"Monaco" size=3D"2" style=3D"font: =
10.0px Monaco">=A0DatabaseException {</font></div><div =
style=3D"margin-top: 0px; margin-right: 0px; margin-bottom: 0px; =
margin-left: 0px; "><font class=3D"Apple-style-span" face=3D"Monaco" =
size=3D"2"><span class=3D"Apple-style-span" style=3D"font-size: =
10px;"><div style=3D"margin-top: 0px; margin-right: 0px; margin-bottom: =
0px; margin-left: 0px; "><font face=3D"Monaco" size=3D"2" style=3D"font: =
10.0px Monaco">=A0=A0 =A0</font><font face=3D"Monaco" size=3D"2" =
color=3D"#760f50" style=3D"font: 10.0px Monaco; color: =
#760f50">if</font><font face=3D"Monaco" size=3D"2" style=3D"font: 10.0px =
Monaco"> (userHasSchemaGrant(context, user,</font></div><div =
style=3D"margin-top: 0px; margin-right: 0px; margin-bottom: 0px; =
margin-left: 0px; "><font face=3D"Monaco" size=3D"2" style=3D"font: =
10.0px Monaco">=A0=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0table.getSchema(),=A0</font></div><div style=3D"margin-top: 0px; =
margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "><font =
face=3D"Monaco" size=3D"2" style=3D"font: 10.0px Monaco">=A0=A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0Privileges.PROCEDURE_EXECUTE_PRIVS)) =
{</font></div><div style=3D"margin-top: 0px; margin-right: 0px; =
margin-bottom: 0px; margin-left: 0px; "><font face=3D"Monaco" size=3D"2" =
style=3D"font: 10.0px Monaco">=A0 =A0 =A0 </font><font face=3D"Monaco" =
size=3D"2" color=3D"#760f50" style=3D"font: 10.0px Monaco; color: =
#760f50">return</font><font face=3D"Monaco" size=3D"2" style=3D"font: =
10.0px Monaco"> </font><font face=3D"Monaco" size=3D"2" color=3D"#760f50" =
style=3D"font: 10.0px Monaco; color: #760f50">true</font><font =
face=3D"Monaco" size=3D"2" style=3D"font: 10.0px =
Monaco">;</font></div><div style=3D"margin-top: 0px; margin-right: 0px; =
margin-bottom: 0px; margin-left: 0px; "><font face=3D"Monaco" size=3D"2" =
style=3D"font: 10.0px Monaco">=A0 =A0 }</font></div> =
</span></font></div><div style=3D"margin-top: 0px; margin-right: 0px; =
margin-bottom: 0px; margin-left: 0px; "><font face=3D"Monaco" size=3D"2" =
style=3D"font: 10.0px Monaco">=A0 =A0=A0</font><font face=3D"Monaco" =
size=3D"2" color=3D"#236e25" style=3D"font: 10.0px Monaco; color: =
#236e25">// Currently you can only execute a procedure if you are a =
member of the</font></div><div style=3D"margin-top: 0px; margin-right: =
0px; margin-bottom: 0px; margin-left: 0px; "><font face=3D"Monaco" =
size=3D"2" style=3D"font: 10.0px Monaco">=A0 =A0=A0</font><font =
face=3D"Monaco" size=3D"2" color=3D"#236e25" style=3D"font: 10.0px =
Monaco; color: #236e25">// secure access priv group.</font></div><div =
style=3D"margin-top: 0px; margin-right: 0px; margin-bottom: 0px; =
margin-left: 0px; "><font face=3D"Monaco" size=3D"2" style=3D"font: =
10.0px Monaco">=A0 =A0=A0</font><font face=3D"Monaco" size=3D"2" =
color=3D"#760f50" style=3D"font: 10.0px Monaco; color: =
#760f50">return</font><font face=3D"Monaco" size=3D"2" style=3D"font: =
10.0px Monaco">=A0userHasSecureAccess(context, user);</font></div><div =
style=3D"margin-top: 0px; margin-right: 0px; margin-bottom: 0px; =
margin-left: 0px; "><font face=3D"Monaco" size=3D"2" style=3D"font: =
10.0px Monaco">=A0=A0}</font></div></div><div><br =
class=3D"webkit-block-placeholder"></div><div><br =
class=3D"webkit-block-placeholder"></div><br><div><div>On 30/06/2007, at =
10:25 AM, Tobias Downer wrote:</div><br =
class=3D"Apple-interchange-newline"><blockquote type=3D"cite"><div =
style=3D"margin-top: 0px; margin-right: 0px; margin-bottom: 0px; =
margin-left: 0px; ">I apologize.<span class=3D"Apple-converted-space">=A0 =
</span>I just checked the code and the user must belong to the "secure =
access" group to be able to call the backup procedure, so you need to =
add the user to this group if you wish for them to use the backup =
function.<span class=3D"Apple-converted-space">=A0 </span>The simplest =
way to do this is by using the following command;</div><div =
style=3D"margin-top: 0px; margin-right: 0px; margin-bottom: 0px; =
margin-left: 0px; min-height: 14px; "><br></div><div style=3D"margin-top: =
0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "><span =
class=3D"Apple-converted-space">=A0 </span>insert into =
SYS_INFO.sUSRUserPriv ( UserName, PrivGroupName ) values ( '[username]', =
'secure access' );</div><div style=3D"margin-top: 0px; margin-right: =
0px; margin-bottom: 0px; margin-left: 0px; min-height: 14px; =
"><br></div><div style=3D"margin-top: 0px; margin-right: 0px; =
margin-bottom: 0px; margin-left: 0px; ">Toby.</div><div =
style=3D"margin-top: 0px; margin-right: 0px; margin-bottom: 0px; =
margin-left: 0px; min-height: 14px; "><br></div><div style=3D"margin-top: =
0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">Duncan =
Groenewald wrote:</div> <blockquote type=3D"cite"><div =
style=3D"margin-top: 0px; margin-right: 0px; margin-bottom: 0px; =
margin-left: 0px; ">I don't get grantee @PUBLIC.<span =
class=3D"Apple-converted-space">=A0 </span>How would I manage to get =
this ?</div><div style=3D"margin-top: 0px; margin-right: 0px; =
margin-bottom: 0px; margin-left: 0px; ">On 27/06/2007, at 1:48 PM, =
Tobias Downer wrote:</div> <blockquote type=3D"cite"><div =
style=3D"margin-top: 0px; margin-right: 0px; margin-bottom: 0px; =
margin-left: 0px; ">Hi,</div><div style=3D"margin-top: 0px; =
margin-right: 0px; margin-bottom: 0px; margin-left: 0px; min-height: =
14px; "><br></div><div style=3D"margin-top: 0px; margin-right: 0px; =
margin-bottom: 0px; margin-left: 0px; ">The following query may help =
you,</div><div style=3D"margin-top: 0px; margin-right: 0px; =
margin-bottom: 0px; margin-left: 0px; min-height: 14px; "><br></div><div =
style=3D"margin-top: 0px; margin-right: 0px; margin-bottom: 0px; =
margin-left: 0px; "><span class=3D"Apple-converted-space">=A0 =
</span>SELECT * FROM SYS_INFO.sUSRGrant;</div><div style=3D"margin-top: =
0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; =
min-height: 14px; "><br></div><div style=3D"margin-top: 0px; =
margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">Look for =
"SYS_INFO.SYSTEM_MAKE_BACKUP" under the param column, the<span =
class=3D"Apple-converted-space">=A0 </span>grantee should be =
"@PUBLIC".<span class=3D"Apple-converted-space">=A0 </span>You may need =
to restart the database<span class=3D"Apple-converted-space">=A0 =
</span>after grants are altered.</div><div style=3D"margin-top: 0px; =
margin-right: 0px; margin-bottom: 0px; margin-left: 0px; min-height: =
14px; "><br></div><div style=3D"margin-top: 0px; margin-right: 0px; =
margin-bottom: 0px; margin-left: 0px; ">Toby.</div><div =
style=3D"margin-top: 0px; margin-right: 0px; margin-bottom: 0px; =
margin-left: 0px; min-height: 14px; "><br></div><div style=3D"margin-top: =
0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">Duncan =
Groenewald wrote:</div><div style=3D"margin-top: 0px; margin-right: 0px; =
margin-bottom: 0px; margin-left: 0px; min-height: 14px; "><br></div> =
<blockquote type=3D"cite"><div style=3D"margin-top: 0px; margin-right: =
0px; margin-bottom: 0px; margin-left: 0px; ">I just tried this but get =
the following error:</div><div style=3D"margin-top: 0px; margin-right: =
0px; margin-bottom: 0px; margin-left: 0px; ">Backup Database</div><div =
style=3D"margin-top: 0px; margin-right: 0px; margin-bottom: 0px; =
margin-left: 0px; ">"Exception executing query"</div><div =
style=3D"margin-top: 0px; margin-right: 0px; margin-bottom: 0px; =
margin-left: 0px; ">"User not permitted to call: =
SYSTEM_MAKE_BACKUP"</div><div style=3D"margin-top: 0px; margin-right: =
0px; margin-bottom: 0px; margin-left: 0px; ">is there any way I can =
check whether permissions have been created ?</div><div =
style=3D"margin-top: 0px; margin-right: 0px; margin-bottom: 0px; =
margin-left: 0px; ">On 23/06/2007, at 4:16 AM, Tobias Downer =
wrote:</div><div style=3D"margin-top: 0px; margin-right: 0px; =
margin-bottom: 0px; margin-left: 0px; min-height: 14px; "><br></div> =
<blockquote type=3D"cite"><div style=3D"margin-top: 0px; margin-right: =
0px; margin-bottom: 0px; margin-left: 0px; "><span =
class=3D"Apple-converted-space">=A0</span>grant all on =
SYS_INFO.SYSTEM_MAKE_BACKUP to [username]</div><div style=3D"margin-top: =
0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; =
min-height: 14px; "><br></div> =
</blockquote></blockquote></blockquote></blockquote><div =
style=3D"margin-top: 0px; margin-right: 0px; margin-bottom: 0px; =
margin-left: 0px; min-height: 14px; "><br></div><div style=3D"margin-top: =
0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; =
min-height: 14px; "><br></div><div style=3D"margin-top: 0px; =
margin-right: 0px; margin-bottom: 0px; margin-left: 0px; =
">---------------------------------------------------------------</div><di=
v style=3D"margin-top: 0px; margin-right: 0px; margin-bottom: 0px; =
margin-left: 0px; ">Mckoi SQL Database mailing list<span =
class=3D"Apple-converted-space">=A0 </span><a =
href=3D"http://www.mckoi.com/database/">http://www.mckoi.com/database/</a>=
</div><div style=3D"margin-top: 0px; margin-right: 0px; margin-bottom: =
0px; margin-left: 0px; ">To unsubscribe, send a message to <a =
href=3D"mailto:[email protected]">[email protected]=
m</a></div><div style=3D"margin-top: 0px; margin-right: 0px; =
margin-bottom: 0px; margin-left: 0px; min-height: 14px; "><br></div> =
</blockquote></div><br></body></html>=
--Apple-Mail-10--1002897788--