List all schema´s object

Examples of schema objects include tables, views, sequences, synonyms, indexes, clusters, database links, procedures, and packages.

Privileges are of two types :

System level privileges :

Which allow the creation, modification, deletion, execution of groups of objects

CREATE TABLE, CREATE VIEW, CREATE SEQUENCE privileges for example allow the user who received them to create tables, views and sequences

Object level privileges :

Which allow manipulations on specific objects

the SELECT, INSERT, UPDATE, DELETE privileges on the SCOTT.EMP table for example allow the user who received them to select, add, modify and delete rows in the EMP table belonging to the Martin user

The best practice is to start by creating a role :

CREATE ROLE yourRole ;

Then apply it to a specific schema object uv_GetAllEmployees view for example :

GRANT SELECT, INSERT, UPDATE, DELETE ON uv_GetAllEmployees TO yourRole ;

Once the role is created, it can be assigned to a user or to another role :

GRANT yourRole TO yourUser ;

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Amira Bedhiafi5y ago
Examples of schema objects include tables, views, sequences, synonyms, indexes, clusters, database links, procedures, and packages.
Privileges are of two types :

System level privileges :

Which allow the creation, modification, deletion, execution of groups of objects

CREATE TABLE, CREATE VIEW, CREATE SEQUENCE privileges for example allow the user who received them to create tables, views and sequences

Object level privileges :

Which allow manipulations on specific objects

the SELECT, INSERT, UPDATE, DELETE privileges on the SCOTT.EMP table for example allow the user who received them to select, add, modify and delete rows in the EMP table belonging to the Martin user

The best practice is to start by creating a role :
CREATE ROLE yourRole ;

Then apply it to a specific schema object uv_GetAllEmployees view for example :
GRANT SELECT, INSERT, UPDATE, DELETE ON uv_GetAllEmployees TO yourRole ;

Once the role is created, it can be assigned to a user or to another role :
GRANT yourRole TO yourUser ;
Nitin Sontakke5y ago
Hello,

Which database system are we talking about here?

In any case, it is advisable to create roles and grant access to role than the user.

You can then add / remove users from role, which effectively grant / revoke access to objects.

Hope it helps!