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?
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.
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
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