I'm trying to allow an oracle user (userA) privileges to create a table on a cluster in another user's schema (userB). I can't find the relevant privilege to grant, and granting ALL PRIVILEGES on the cluster to userA complains that the table or view doesn't exist. Is what I'm trying to do possible? I also notice there isn't an ALL_CLU_COLUMNS dictionary table; is this related?
A:
userA has to have the CREATE ANY TABLE privilege. Be sure to qualify the userB schema for both the table and cluster object references.
So, using the emp/dept example from Oracle's docs:
userB: CREATE CLUSTER emp_dept (deptno NUMBER(3));
userA: CREATE TABLE userB.dept ( deptno NUMBER(3) ) CLUSTER userB.emp_dept (deptno);
dpbradley
2009-07-16 13:28:49
Is it possible to do:userA: CREATE TABLE userA.dept (deptno NUMBER(3)) CLUSTER userB.emp_dept(deptno) ?
thecoop
2009-07-16 13:50:27
Yes, that works as well.
dpbradley
2009-07-16 13:58:25
In that case, how can I obtain column cluster information from the data dictionary? There isn't a ALL_CLU_COLUMNS dictionary table I can use, and USER_CLU_COLUMNS isn't available...
thecoop
2009-07-16 14:13:11
You have to use the dba_ view:In this case: select * from dba_clu_columns where cluster_name like '%EMP%'
dpbradley
2009-07-16 14:42:39
An additional note: after thinking about it, the concept of an ALL_CLU_COLUMNS view doesn't make sense to me. In the Oracle dictionary the ALL_% views are generally associated with objects on which a schema has privileges. There are no direct object privileges associated with clusters in the Oracle security model - its more of a physical grouping and if anything should have a quota like tablespaces.
dpbradley
2009-07-16 14:48:24