Restricting a user to access only his own tables in Oracle SQL Developer

Question

I am confused in the permission, privileges in the SQL Oracle Developer. The user created can access all the schema/user 's tables. I want to restrict specific user in the database to access (ALTER, DROP, UPDATE etc) to his own tables only. Can any one specifies me how to perform this task.

I mean which privileges to be choose from system privileges for the user to access only his own tables. Thank you

Solution

As long as you do not explicitly grant a permission, the user won't have it. Just let's use a little excursus:

CREATE USER dummy IDENTIFIED BY password;

Now that user exist -- but cannot even logon to the database. He's got no permissions yet. Only after

GRANT CREATE SESSION TO dummy;

that user can logon -- but no more. To be able to do things within his own schema, one usually grants things like

GRANT CREATE TABLE, CREATE VIEW, ALTER TABLE, DROP TABLE TO dummy;

Now he an do things -- but only in his own schema. He can also insert, update, delete -- but still is restricted to his own objects. To tamper (or even just see) other users data, the other user must explicitly grant this:

GRANT SELECT ON some_table TO dummy;

or the DBA grants him global permissions:

GRANT SELECT ANY TABLE TO dummy;

So you will have to check what permissions the user has. As there are many possible permissions, my answer might get too long -- but especially watch out for permissions including the ANY keyword (as in my last example). Also check possible roles granted to that user, which might include such permissions. Being logged in as sysdba, you can check them e.g. with the following query:

SELECT granted_role
  FROM dba_role_privs
 WHERE grantee='DUMMY';

(note the UPPERCASE user name here - that's how Oracle stores them internally, and it is case sensitive). For the first mentioned (direct) privileges, this would be:

SELECT privilege
  FROM dba_sys_privs
 WHERE grantee='DUMMY';

Once you figured out the privileges/roles granted to that special user he should not have, you can take them away from him, e.g.:

REVOKE SELECT ANY TABLE FROM dummy;

Comments

[Izzy]

I tried a basic answer (see below), but would strongly suggest two things: 1) get yourself some good tutorials (there are some "basic dba" things around), and 2) check with the DBA branch for concerning questions, as there are the people most familiar with this stuff ;)