User only with CREATE VIEW right

Padawan

I would like to have a user who can only create views in a certain schema.

So I used "Grant CREATE VIEW TO USER;".

There is still an error message. According to the documentation, I have to use the user as owner or per role as owner. "GRANT OWNERROLE TO USER";

This allows the user to create the views. But the user can do everything else. For example CREATE TABLE.

If I now remove CREATE VIEW from the user, I can still create views. Why are there these CREATE rights? How can I create a User only with CREATE VIEW rights?

3 REPLIES 3

Padawan

The Problem was that the user has more than one GRANT to an ownerrole. One Ownerrole has the CREATE TABLE right. So the User got the right to CREATE TABLES in every Schema. That is not what I would like to have, but it is what I got.

Xpert

It highly depends on the privileges of the owner role of the schema as the user will always inherit these rights.

So you can't use an owner role that has high privileges on a schema but if you want to have a dedicated schema for these user defined views without granting the user create schema privileges it can be done this way

 

 

--sys or dba user
CREATE SCHEMA VIEW_SCHEMA;
CREATE USER JIM IDENTIFIED BY "jim_password";
GRANT CREATE VIEW,CREATE SESSION to JIM;
ALTER SCHEMA view_schema CHANGE OWNER JIM;




--jim
CREATE VIEW view_schema.jims_view AS SELECT * FROM dual;

 

 

if this schema should be usable by multiple users you can create a dedicated role for this

 

--sys
CREATE SCHEMA VIEW_SCHEMA;
CREATE ROLE ROLE_VIEW_SCHEMA_CREATE_VIEW;
GRANT CREATE VIEW TO ROLE_VIEW_SCHEMA_CREATE_VIEW;
ALTER SCHEMA view_schema CHANGE OWNER ROLE_VIEW_SCHEMA_CREATE_VIEW;

CREATE USER JIM IDENTIFIED BY "jim_password";
GRANT CREATE SESSION to JIM;
GRANT ROLE_VIEW_SCHEMA_CREATE_VIEW to JIM;

--jim
CREATE VIEW view_schema.jims_view AS SELECT * FROM dual;

 


Every other user only has to get the create session privilege and the Role role_view_schema_create_view and will be able to create views in that schema.

However the owner of the schema will have to be able to select the objects referenced in the views that you the users want to create.

So even if Jim has the right to select the object DATA.CUSTOMER and has the right to create a view IN VIEW_SCHEMA a view
CREATE VIEW VIEW_SCHEMA.CUSTOMER as select * from DATA.CUSTOMER WHERE CUSTOMER_ID = 1234

may fail if the Schema Owner Role role_view_schema_create_view does not have the right to select DATA.CUSTOMER.

I know this is a bit weird but this caused us a lot of problems in the past

Moderator
Moderator

Hi Carsten,

maybe you should give us a more elaborate test case about what is not working.

If I try to have a user with only CREATE VIEW, it works for me like this

create user jim identified by "jim"; --done as sys
grant create session,create schema,create view to jim;
create schema jim; --done as jim
create view senselessview as select 'Hello' as dummy; --works

Best regards

Uwe