Search code examples
databaseoracle-databasestandards

Grant Oracle user Read Only to Specific View

I am trying to create a new database user account in Oracle Database 12c Standard Edition that ONLY has access to run a specific view (let's call it "MyApp.MyView").

I created a view as an administrative user under my main application's schema. I created the user (details below) but it seems to have the ability to query any table or view in the app's schema. I ONLY want them to be able to query the specific view I made. Can this be done?

Then, using SQLDeveloper, I right clicked on MyView and granted privileges to MyUser. I can query it as MyUser, but I can also query anything and I don't want that.

-- USER SQL
ALTER USER "MyUser"
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP"
ACCOUNT UNLOCK ;

-- QUOTAS

-- ROLES
ALTER USER "MyUser" DEFAULT ROLE "CONNECT";

-- SYSTEM PRIVILEGES
Solution

  • Below creates a user with the minimum set of privileges needed to query a view/table in another schema. If the user is able to query other tables/views, then they must have been granted elsewhere either through additional grants or roles.

    create user MyUser identified by testpassword account unlock;

grant create session to MyUser;

grant read on MyApp.MyView to MyUser;