Understanding Privileges with SQL

Understanding Privileges

Privileges in a database system determine what actions users can perform on database objects. These include:

• System Privileges: Permissions to perform administrative tasks or access system-level functions.
• Object Privileges: Permissions to perform actions on specific database objects like tables, views, or procedures.
• Role Privileges: Permissions granted to roles which can then be assigned to users.

Types of Privileges

• System Privileges: Examples include CREATE TABLE, CREATE VIEW, ALTER SYSTEM, etc.
• Object Privileges: Examples include SELECT, INSERT, UPDATE, DELETE on tables or views.
• Role Privileges: Roles can contain various system or object privileges and can be granted to users.

Checking Privileges in Oracle

• Checking User Privileges

To check what privileges a user has, you can query the USER_SYS_PRIVS and USER_TAB_PRIVS views for system and object privileges, respectively:

• System Privileges:

SELECT privilege
FROM user_sys_privs;

• Object Privileges: 

SELECT table_name, privilege
FROM user_tab_privs;

  Checking Privileges Granted to a Role

Roles can be granted privileges that can be assigned to users. To see what privileges a role has, use:

• System Privileges of a Role:

SELECT privilege
FROM role_sys_privs
WHERE role = 'ROLE_NAME';

• Object Privileges of a Role:

SELECT object_name, privilege
FROM role_tab_privs
WHERE role = 'ROLE_NAME';

Checking Privileges Granted to Other Users

To find out what privileges a specific user has been granted (including those from roles), you can use:

• System Privileges Granted to a User:

SELECT grantee, privilege
FROM dba_sys_privs
WHERE grantee = 'USERNAME';

• Object Privileges Granted to a User:

SELECT owner, table_name, privilege
FROM dba_tab_privs
WHERE grantee = 'USERNAME';

• Role Privileges Assigned to a User:

SELECT granted_role
FROM dba_role_privs
WHERE grantee = 'USERNAME';

Checking Privileges for Specific Database Objects

• Tables

To check which users have specific privileges on a table: 

SELECT grantee, privilege
FROM dba_tab_privs
WHERE table_name = 'TABLE_NAME';

•  Views

To check privileges granted on a view: 

SELECT grantee, privilege
FROM dba_tab_privs
WHERE table_name = 'VIEW_NAME';

• Procedures

To check privileges on stored procedures: 

SELECT grantee, privilege
FROM dba_proc_privs
WHERE object_name = 'PROCEDURE_NAME';

Checking Privileges in Other SQL Databases

While the specific views and commands can vary, the general approach to checking privileges is similar across different SQL databases:

• SQL Server: Use system catalog views like sys.database_permissions, sys.database_role_members, and sys.database_principals.
• MySQL: Use SHOW GRANTS FOR ‘username’@’host’ to display privileges.
• PostgreSQL: Use pg_roles, pg_user, and pg_catalog.pg_tables for information about roles and their privileges.

Example Queries

Example 1: Checking All Privileges for a User 

SELECT *
FROM dba_sys_privs
WHERE grantee = 'HR';
SELECT *
FROM dba_tab_privs
WHERE grantee = 'HR';

Example 2: Checking Privileges on a Specific Table 

SELECT *
FROM dba_tab_privs
WHERE table_name = 'EMPLOYEES';

Conclusion

Checking privileges is vital for ensuring database security and proper access control. By understanding and utilizing system and object privilege views, you can effectively manage and audit user permissions. Always refer to your specific database documentation for the exact syntax and additional details.