RE: grant permission to create a view but cannot grant permissions on view to others

RE: grant permission to create a view but cannot grant permissions on view to others

 

  


What problem are you running into?

If the user has SELECT authority on all tables defined in the view,
along with either CREATIN or IMPLICIT_SCHEMA then the user can create
the view.

If I understand the manual correctly, the user that creates a view
cannot grant use to another without first having grant authority on the
base tables.

Rick

To create a view
You must have either:
* SYSADM or DBADM authority
* Both
* One of the following privileges for each table or view:
* CONTROL privilege on the table or view
* SELECT privilege on the table or view
* One of the following privileges for each table or view:
* IMPLICIT_SCHEMA authority on the database, if
the implicit or explicit schema name of the view does not exist
* CREATEIN privilege on the schema, if the schema
name of the view exists
PUBLIC and group privileges are not checked for any table or view
specified in the CREATE VIEW statement. If you create a view and you
have only SYSADM authority, you are explicitly granted DBADM authority.
To grant and revoke privileges on a view
You need the proper authorizations:
* To grant and revoke privileges on a catalog view, you need
either SYSADM or DBADM authority.
* To grant and revoke privileges on a user-defined view, you need
to meet the following requirements:
* To grant and revoke the CONTROL privilege, you need
SYSADM or DBADM authority.
* To grant privileges other than CONTROL, you need one of
the following authorizations. To revoke privileges other than CONTROL,
you need one of the first three of these authorizations.
* SYSADM authority
* DBADM authority
* The CONTROL privilege on the view
* The privilege that you want to grant, along with
the Grant option (the right to grant the privilege to other users and
groups)
Example
You can grant the ALTER privilege on a user-defined view if you hold one
of these authorizations:
* SYSADM authority
* DBADM authority on the database in which the
view resides
* The CONTROL privilege on the view
* The ALTER privilege, along with the right to
grant the ALTER privilege on this view to other users and groups




-----Original Message-----
From: Kermit Lowry
[mailto:db2udbdba-ezmlmshield-x14160910.[Email address protected]
Sent: Tuesday, November 30, 2004 12:17 PM
To: LazyDBA Discussion
Subject: grant permission to create a view but cannot grant permissions
on view to others

I have a gentleman in my IT shop who is trying to grant permissions for
a user to create a view, but not allow that user to grant permission on
that view to someone else. He says you can do this in Oracle with the
"without admin" clause, but we cannot find this functionality in the
documentation we have perused.

If this can be done in udb 8.1.5, could someone please assist?

TIA,
Kermit


---------------------------------------------------------------------
PLEASE CLICK REPLY-ALL TO SEND A REPLY TO EVERYONE
website: http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html


DB2 & UDB email list listserv db2-l LazyDBA home page