DOITLIKEITSYOURJOB - Users, Permissions, Roles

DOITLIKEITSYOURJOB

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.

Page updated

Report abuse