1. What is the difference between system privileges and object privileges?
- System Privileges: These allow users to perform administrative tasks or actions on the database level. For example, creating a user, creating a table, or modifying database configurations.
- Object Privileges: These apply to specific database objects (e.g., tables, views, procedures) and define what actions a user can perform on those objects, such as SELECT, INSERT, UPDATE, DELETE, and EXECUTE.
2. What is the purpose of the WITH GRANT OPTION in a GRANT statement?
- The WITH GRANT OPTION clause allows the grantee to pass the privileges they have received onto other users. This can be useful for delegating authority but also increases the risk of privilege propagation if not managed carefully.
Example:
GRANT SELECT ON employees TO john_doe WITH GRANT OPTION;
3. What is the difference between roles and privileges?
- Roles: A role is a named collection of privileges, and it simplifies the process of assigning multiple privileges to users. Roles can be granted to one or more users and can contain both system and object privileges.
- Privileges: Privileges are individual permissions that define what a user can do with a database object (like a table, view, etc.).
Example:
CREATE ROLE analyst_role;
GRANT SELECT, INSERT ON employees TO analyst_role;
GRANT analyst_role TO jane_doe;
4. Can I grant privileges to multiple users at once?
- Yes, you can grant privileges to multiple users at once. For example:
ยท GRANT SELECT ON employees TO user1, user2, user3;
5. How do I grant privileges on a specific schema or database object?
- When granting privileges on a specific object (such as a table or view), you refer to the object by its full name, including the schema name if necessary.
GRANT SELECT ON hr.employees TO john_doe;
6. What happens if I revoke a privilege that has been passed on using the WITH GRANT OPTION?
- When a privilege is revoked from a user who has granted that privilege to other users, the users who were granted the privilege indirectly lose their access as well (unless they were granted the privilege directly).
Example:
REVOKE SELECT ON employees FROM john_doe;
7. Can I grant a privilege on a table and restrict access to only certain columns?
- Yes, Oracle allows granting column-level access privileges. For instance, you can grant SELECT access on specific columns of a table:
GRANT SELECT (first_name, last_name) ON employees TO john_doe;
8. Can roles be nested (i.e., one role can contain another role)?
- Yes, Oracle supports role nesting. A role can contain other roles, allowing you to group roles logically and assign them in a hierarchical manner.
Example:
CREATE ROLE read_only;
GRANT SELECT ON employees TO read_only;
CREATE ROLE db_user;
GRANT read_only TO db_user;
GRANT db_user TO jane_doe;
9. How do I check which privileges are granted to a user?
- You can query the USER_TAB_PRIVS and USER_ROLE_PRIVS views to check the object and system privileges granted to a user:
SELECT * FROM USER_TAB_PRIVS WHERE GRANTEE = 'USER_NAME';
SELECT * FROM USER_ROLE_PRIVS WHERE GRANTEE = 'USER_NAME';
10. What is the PUBLIC role in Oracle?
- The PUBLIC role is a special role in Oracle that includes privileges granted to all database users. For example, you can grant a privilege to PUBLIC to ensure that all users in the database can access an object.
Example:
GRANT SELECT ON employees TO PUBLIC;
Note: Be cautious when granting privileges to PUBLIC as it provides access to all users.
11. Can I grant system privileges like CREATE SESSION to a role?
- Yes, system privileges (like CREATE SESSION) can be granted to roles, and then roles can be granted to users. This is a common practice in role-based access control.
Example:
GRANT CREATE SESSION TO app_role;
GRANT app_role TO john_doe;
12. What is the effect of the REVOKE statement?
- The REVOKE statement removes a previously granted privilege. If the privilege was granted with the WITH GRANT OPTION, it also removes the ability of the grantee to grant the privilege to others.
Example:
REVOKE SELECT ON employees FROM john_doe;
13. Can I grant the ability to create and drop tables to a user?
- Yes, you can grant system privileges such as CREATE TABLE, DROP TABLE, and ALTER to users so they can create and modify tables.
Example:
GRANT CREATE TABLE, DROP TABLE TO john_doe;
14. How do I grant access to stored procedures or functions?
- You can grant the EXECUTE privilege on stored procedures or functions to users or roles to allow them to execute the procedures or functions.
Example:
GRANT EXECUTE ON proc_name TO john_doe;
15. Can I grant the same privilege multiple times to the same user?
- Yes, you can grant the same privilege multiple times, but Oracle will not duplicate the privilege. It is a no-op (no operation) if the same privilege is granted again to the same user.
No comments:
Post a Comment