Roles Granted to Users
To determine roles granted to specific users, query the `DBA_ROLE_PRIVS` view:
SELECT
GRANTEE,
GRANTED_ROLE
FROM
DBA_ROLE_PRIVS
WHERE
GRANTEE IN ('USER1', 'USER2');
Object Privileges of the Roles
Once you have the roles for the users, you can find out the object privileges for those roles using the `DBA_TAB_PRIVS` view:
SELECT
GRANTEE AS ROLE_NAME,
OWNER,
TABLE_NAME,
PRIVILEGE
FROM
DBA_TAB_PRIVS
WHERE
GRANTEE IN (
SELECT GRANTED_ROLE
FROM DBA_ROLE_PRIVS
WHERE GRANTEE IN ('USER1', 'USER2')
);
This will give you the object-level privileges (like `SELECT`, `UPDATE`, etc.) that each of the roles has on database tables, views, etc.
System Privileges of the Roles
For system-level privileges that each role has, query the `DBA_SYS_PRIVS` view:
SELECT
GRANTEE AS ROLE_NAME,
PRIVILEGE
FROM
DBA_SYS_PRIVS
WHERE
GRANTEE IN (
SELECT GRANTED_ROLE
FROM DBA_ROLE_PRIVS
WHERE GRANTEE IN ('USER1', 'USER2')
);
This gives you system privileges like `CREATE SESSION`, `ALTER SYSTEM`, etc., that each role possesses.
Remember, if you want a full picture of the roles' permissions, you'll also need to consider roles that are granted to other roles. This requires recursive querying of the `DBA_ROLE_PRIVS` view.
Lastly, if you don't have access to the `DBA_` prefixed views, you might have access to their `ALL_` or `USER_` counterparts. These provide similar information but are limited in scope based on your permissions.
Always ensure you have the necessary permissions to access these DBA views and exercise caution when working with database permissions.
REMOVE ACCESS
REVOKE privilege_name ON object_name FROM role_name;
REVOKE SELECT ON EMPLOYEES FROM HR_ROLE;
--Rerunnable
DECLARE
v_count NUMBER;
BEGIN
SELECT COUNT(*)
INTO v_count
FROM DBA_TAB_PRIVS
WHERE TABLE_NAME = 'YOUR_TABLE_NAME'
AND PRIVILEGE = 'YOUR_PRIVILEGE'
AND GRANTEE = 'YOUR_USER';
IF v_count > 0 THEN
EXECUTE IMMEDIATE 'REVOKE YOUR_PRIVILEGE ON YOUR_TABLE_NAME FROM YOUR_USER';
END IF;
END;
/
In Oracle, permissions can be granted directly to a user or indirectly via roles. To view all permissions granted to a user, you'll need to query several data dictionary views. Here are some steps to see the permissions granted to a specific user:
1. **Direct Object Privileges**:
These are privileges granted directly on database objects (like tables, views, sequences, etc.) to a user.
SELECT OWNER, TABLE_NAME,SELECT OWNER, TABLE_NAME, PRIVILEGE
FROM DBA_TAB_PRIVS
WHERE GRANTEE = 'YOUR_USERNAME';
2. **System Privileges**:
These are privileges that allow users to perform specific database operations.
SELECT PRIVILEGE
FROM DBA_SYS_PRIVS
WHERE GRANTEE = 'YOUR_USERNAME';
3. **Role Privileges**:
Roles are named groups of related privileges that you grant as a group to users or other roles.
SELECT GRANTED_ROLE
FROM DBA_ROLE_PRIVS
WHERE GRANTEE = 'YOUR_USERNAME';
4. **Privileges via Roles**:
To see the privileges granted to a user through roles, you'll first need to list all roles assigned to the user (as in step 3) and then look up privileges associated with each of those roles.
SELECT OWNER, TABLE_NAME, PRIVILEGE
FROM DBA_TAB_PRIVS
WHERE GRANTEE IN (SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'YOUR_USERNAME');
5. **Roles Granted to Roles**:
In Oracle, roles can also be granted to other roles. If a user has been granted a role, and that role has been granted additional roles, you'll need to recursively check privileges. This can get complex and might require a recursive PL/SQL block or hierarchical query.
6. **Default Roles**:
Check which of the roles granted to the user are set as DEFAULT. When a user logs in, the privileges of the default roles are immediately available to them, while non-default roles must be explicitly enabled during the session.
SELECT *
FROM DBA_DEFAULT_ROLE
WHERE GRANTEE = 'YOUR_USERNAME';
7. **Public Privileges**:
Remember that certain privileges might be granted to the special `PUBLIC` role. All users, including the one you're investigating, inherit privileges granted to `PUBLIC`.
8. **Column-level Privileges**:
Oracle also allows for column-level privileges. To see if a user has been granted specific privileges on certain columns of a table or view, you can query:
SELECT TABLE_NAME, COLUMN_NAME, PRIVILEGE
FROM DBA_COL_PRIVS
WHERE GRANTEE = 'YOUR_USERNAME';
Replace `'YOUR_USERNAME'` with the name of the user you're investigating. Remember that usernames and role names in Oracle are typically in uppercase, so ensure you're using the correct case when querying.
After gathering this information, you'll have a comprehensive view of the permissions granted to a user, both directly and through roles.