Oracle SQL create view privileges

oraclepermissionsview

This is more of a theoritical question but I need help ASAP. Here it is:

With the appropriate use of SQL queries, grant as system
administrators the required access privileges to a database user named
STD01, so he can create a view of a table named CUSTOMER, which
belongs to another database user STD00.

Can anyone help me with this?

I know that I have to grant him with the system privilege of CREATE (ANY) VIEW and also give him all the object privileges (SELECT, INSERT, UPDATE & DELETE) on the CUSTOMER table, but I have no idea how can I do that by using SQL…

Best Answer

To grant privilege to create a view:

GRANT CREATE VIEW TO STD01;

To grant the DML privileges:

GRANT SELECT,UPDATE,INSERT,DELETE ON STD00.CUSTOMER TO STD01;

But that's not all of the object privileges. If you did:

GRANT ALL ON STD00.CUSTOMER TO STD01;

you would also give other privileges such as ALTER, INDEX, FLASHBACK, etc.

Related Solutions

Configure Oracle user in Enterprise Manager to CREATE VIEW on any of his tables

I'm not sure if I'm understanding your question properly. I created a user with the ability to create views for his own schema.

SQL> create user view_user identified by password default tablespace users quota unlimited on users;

User created.

SQL> grant create session, create table, create view to view_user;

Grant succeeded.

SQL> connect view_user
Enter password: 
Connected.
SQL> CREATE TABLE Members (ID number(10), Name varchar2(100), Street_No varchar2(100), ZIP number(5), City varchar2(100));

Table created.

SQL> CREATE VIEW MembersNamesOnly AS SELECT ID, Name FROM Members;

View created.

SQL>

Db2 – Create new Read Only user on DB2 on the whole database

There is no read only privilege on a database in DB2. You will need to grant SELECT privileges on specific tables, preferably via a role to avoid doing this multiple times.

Create a new database role, e.g. create role readonly.
Grant SELECT privileges on the required tables to that role: grant select on myschema.mytable to role readonly.
Create a new user in the operating system, e.g. # mkuser newuser.
Grant the role to the user: grant role readonly to user newuser.

Note that the new user will likely need to change the initial password before he or she can connect to the database.

Best Answer

Related Solutions

Configure Oracle user in Enterprise Manager to CREATE VIEW on any of his tables

Db2 – Create new Read Only user on DB2 on the whole database

Related Question