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

Answer 1

A user will only have permissions on tables outside of their schema if

• Object-level privileges on each table have been granted to the user (either directly or via a role), or
• A system-level privilege has been granted to the user (either directly or via a role) that allows them to access all the objects of a particular type.

If the user has been granted a system-level privilege (i.e. SELECT ANY TABLE), you can revoke the system-level privilege. If the user has been granted explicit privileges on each table, you would need to revoke privileges on each table.

My guess is that the user has been granted a role (potentially the DBA role) that contains one or more of the ANY system privileges. If that's the case, you'd need to either revoke the role from the user or to revoke the privilege from the role.

What roles are granted to the user?

SELECT granted_role
  FROM dba_role_privs
 WHERE grantee = <<the user name>>

What system privileges are granted to the user?

SELECT privilege
  FROM dba_sys_privs
 WHERE grantee = <<the user name>>

What system privileges are granted to the roles that are granted to the user?

SELECT *
  FROM dba_sys_privs
 WHERE grantee IN (SELECT granted_role
                     FROM dba_role_privs
                    WHERE grantee = <<the user name>>)