Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

public priviledge on create procedure

51 views
Skip to first unread message

Anwei Shen

unread,
Aug 29, 2011, 3:10:05 PM8/29/11
to
db2 udb 9.7.4 on AIX,
I have revoked dbadm/CREATE_NOT_FENCED/IMPLICIT_SCHEMA from pubilc and
user, but still they can create procedures.

db2 => CONNECT

Database Connection Information

Database server = DB2/AIX64 9.7.4
SQL authorization ID = PREAD
Local database alias = PDB

db2 => select * from SYSCAT.dbauth where GRANTEE in ('PREAD' ,
'PUBLIC')

GRANTOR
GRANTORTYPE
GRANTEE
GRANTEETYPE BINDADDAUTH CONNECTAUTH CREATETABAUTH DBADMAUTH
EXTERNALROUTINEAUTH IMPLSCHEMAAUTH LOADAUTH NOFENCEAUTH
QUIESCECONNECTAUTH LIBRARYADMAUTH SECURITYADMAUTH SQLADMAUTH
WLMADMAUTH EXPLAINAUTH DATAACCESSAUTH ACCESSCTRLAUTH
--------------------------------------------------------------------------------------------------------------------------------
-----------
--------------------------------------------------------------------------------------------------------------------------------
----------- ----------- ----------- ------------- ---------
------------------- -------------- -------- -----------
------------------ -------------- --------------- ----------
---------- ----------- -------------- --------------
SYSIBM
S
PUBLIC
G N Y N N
N N N N
N N N N
N N N N
DB2PMTP
U
PMTPREAD
U N Y N N
N N N N
N N N N
N N N N

2 record(s) selected.

db2 => CREATE PROCEDURE TEST (in medianSalary DOUBLE) LANGUAGE
SQL BEGIN END
DB20000I The SQL command completed successfully.
db2 => DROP PROCEDURE TEST
DB20000I The SQL command completed successfully.

MarkB

unread,
Aug 30, 2011, 1:25:22 PM8/30/11
to

Hi Anwei,

you don't have to have CREATE_NOT_FENCED for SQL routines:
http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.sql.ref.doc/doc/r0008329.html
You have to have this authority for EXTERNAL not fenced routines:
http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.sql.ref.doc/doc/r0008328.html

Sincerely,
Mark B.

Anwei Shen

unread,
Aug 30, 2011, 2:51:07 PM8/30/11
to
Yes, I tested today, I need to run this to avoid unwanted procedure.

REVOKE CREATEIN ON SCHEMA

> you don't have to have CREATE_NOT_FENCED for SQL routines:http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db...
> You have to have this authority for EXTERNAL not fenced routines:http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db...
>
> Sincerely,
> Mark B.- Hide quoted text -
>
> - Show quoted text -

0 new messages