Oracle PL/SQL Tutorial/User Privilege/Roles
Содержание
- 1 Assign CONNECT and RESOURCE Roles
- 2 Assign object privileges to roles
- 3 Assign Role to User
- 4 Checking Object Privileges Granted to a Role
- 5 Checking Roles Granted to a User
- 6 Checking System Privileges Granted to a Role
- 7 Creating Roles
- 8 Default Roles
- 9 Dropping a Role
- 10 Drop Role
- 11 Granting Privileges to Roles
- 12 Granting Roles to a User
- 13 Output session roles from procedure
- 14 Query user granted roles
- 15 Regina and Kristen are manager level users
- 16 Revoking a Role
- 17 Revoking Privileges from a Role
- 18 Rich and Brad are maintenance level users
- 19 Roles
- 20 Unassign Role
- 21 Unassign Role from User
Assign CONNECT and RESOURCE Roles
SQL>
SQL> CREATE USER Alice IDENTIFIED BY simplepassword;
User created.
SQL>
SQL> GRANT CONNECT, RESOURCE to Alice;
Grant succeeded.
SQL>
SQL>
SQL> drop user alice;
User dropped.
SQL>
SQL>
Assign object privileges to roles
SQL> GRANT SELECT, INSERT ON employee TO entry;
SQL> GRANT SELECT, INSERT ON customer TO entry;
SQL> GRANT SELECT, UPDATE ON employee TO maintenance;
SQL> GRANT SELECT, UPDATE ON customer TO maintenance;
SQL> GRANT EXECUTE ON validate_salary TO maintenance;
SQL> GRANT SELECT, INSERT, UPDATE, DELETE ON employee TO manager;
SQL> GRANT SELECT, INSERT, UPDATE, DELETE ON customer TO manager;
SQL> GRANT EXECUTE ON validate_salary TO manager;
SQL> GRANT EXECUTE ON adjust_salary TO manager;
SQL>
SQL>
SQL>
Assign Role to User
SQL>
SQL> GRANT TestRole TO Alice;
SQL>
Checking Object Privileges Granted to a Role
You can check which object privileges have been granted to a role by querying role_tab_privs.
SQL> desc role_tab_privs;
Name Null? Type
ROLE NOT NULL VARCHAR2(30) --User to whom the privilege was granted.
OWNER NOT NULL VARCHAR2(30) --User who owns the object.
TABLE_NAME NOT NULL VARCHAR2(30) --Name of the object on which privilege was granted.
COLUMN_NAME VARCHAR2(30) --Name of the column (if applicable).
PRIVILEGE NOT NULL VARCHAR2(40) --Privilege on the object.
GRANTABLE VARCHAR2(3) --Whether the privilege was granted with the GRANT option. Equal to YES or NO.
SELECT *
FROM role_tab_privs;
Checking Roles Granted to a User
You can check which roles have been granted to a user by querying user_role_privs. A user who creates a role is also granted that role by default.
SQL> desc user_role_privs;
Name Type Description
USERNAME VARCHAR2(30) --Name of the user to whom the role has been granted.
GRANTED_ROLE VARCHAR2(30) --Name of the role granted to the user.
ADMIN_OPTION VARCHAR2(3) --Whether the user is able to grant the role to another user or role. Equal to YES or NO.
DEFAULT_ROLE VARCHAR2(3) --Whether the role is enabled by default when the user connects to the database. Equal to YES or NO.
OS_GRANTED VARCHAR2(3) --Whether the role was granted by the operating system.
SELECT * FROM user_role_privs;
Checking System Privileges Granted to a Role
You can check which system privileges have been granted to a role by querying role_sys_privs.
SQL> desc role_sys_privs;
Name Null? Type
ROLE NOT NULL VARCHAR2(30) --Name of the role.
PRIVILEGE NOT NULL VARCHAR2(40) --System privilege granted to the role.
ADMIN_OPTION VARCHAR2(3) --Whether the privilege was granted with the ADMIN option. Equal to YES or NO.
SELECT *
FROM role_sys_privs;
Creating Roles
GRANT CREATE ROLE TO store;
GRANT CREATE USER TO store WITH ADMIN OPTION;
CREATE ROLE product_manager;
CREATE ROLE hr_manager;
CREATE ROLE overall_manager IDENTIFIED by manager_password;
Default Roles
ALTER USER steve DEFAULT ROLE ALL EXCEPT overall_manager;
SET ROLE NONE;
SET ROLE ALL EXCEPT overall_manager;
Dropping a Role
CREATE ROLE product_manager;
GRANT SELECT, INSERT, UPDATE, DELETE ON employee TO product_manager;
REVOKE ALL ON products FROM product_manager;
DROP ROLE product_manager;
Drop Role
SQL>
SQL> DROP ROLE TestRole;
Role dropped.
Granting Privileges to Roles
You can grant both system and object privileges to a role, as well as grant another role to a role.
GRANT SELECT, INSERT, UPDATE, DELETE ON tableNames TO product_manager;
GRANT CREATE USER TO hr_manager;
GRANT product_manager, hr_manager TO overall_manager;
Granting Roles to a User
You grant a role to a user using GRANT.
GRANT overall_manager TO userName;
Output session roles from procedure
SQL>
SQL> create or replace procedure myProcedure
2 authid current_user
3 as
4 begin
5 for rec in (select * from session_roles)
6 loop
7 dbms_output.put_line(rec.role);
8 end loop;
9 end;
10 /
Procedure created.
SQL> execute myProcedure
DBA
SELECT_CATALOG_ROLE
HS_ADMIN_ROLE
EXECUTE_CATALOG_ROLE
DELETE_CATALOG_ROLE
EXP_FULL_DATABASE
IMP_FULL_DATABASE
GATHER_SYSTEM_STATISTICS
SCHEDULER_ADMIN
XDBADMIN
XDBWEBSERVICES
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> drop procedure myProcedure;
Procedure dropped.
SQL>
Query user granted roles
SQL>
SQL>
SQL> COL grantee FORMAT A8
SQL> COL granted_role FORMAT A30
SQL> COL grantor FORMAT A8
SQL> COL privilege FORMAT A12
SQL> COL owner FORMAT A4
SQL> COL table_name FORMAT A30
SQL>
SQL>
SQL> SELECT grantee
2 , granted_role
3 FROM dba_role_privs
4 WHERE grantee = "PLSQL";
GRANTEE GRANTED_ROLE
-------- ------------------------------
PLSQL CTXAPP
PLSQL CONNECT
PLSQL RESOURCE
3 rows selected.
SQL>
SQL> -- Query resources.
SQL> SELECT grantor
2 , owner
3 , table_name
4 , grantee
5 , privilege
6 FROM dba_tab_privs
7 WHERE grantee = "PLSQL";
no rows selected
Regina and Kristen are manager level users
SQL> CREATE ROLE manager;
Role created.
SQL>
SQL> CREATE USER regina identified by regina;
User created.
SQL> CREATE USER kristen identified by kristen;
User created.
SQL> GRANT CONNECT TO regina, kristen;
Grant succeeded.
SQL> GRANT manager TO regina, kristen;
Grant succeeded.
SQL>
SQL> drop user regina;
User dropped.
SQL> drop user kristen;
User dropped.
SQL>
SQL> drop role manager;
Role dropped.
Revoking a Role
REVOKE overall_manager FROM steve;
Revoking Privileges from a Role
CREATE ROLE product_manager;
GRANT SELECT, INSERT, UPDATE, DELETE ON employee TO product_manager;
REVOKE ALL ON products FROM product_manager;
DROP ROLE product_manager;
Rich and Brad are maintenance level users
SQL> CREATE ROLE maintenance;
Role created.
SQL>
SQL> CREATE USER rich identified by rich;
User created.
SQL> CREATE USER brad identified by brad;
User created.
SQL> GRANT CONNECT TO rich, brad;
Grant succeeded.
SQL> GRANT maintenance TO rich, brad;
Grant succeeded.
SQL>
SQL> drop user rich;
User dropped.
SQL> drop user brad;
User dropped.
SQL> drop role maintenance;
Role dropped.
Roles
- A role is a group of privileges that you can assign to a user or another role.
- You can grant a role to multiple users and roles.
- When adding or deleting a privilege from a role, all users and roles assigned that role automatically receive or lose that privilege.
- You can assign multiple roles to a user or role.
- You can assign a password to a role.
36. 7. Roles 36. 7. 1. Roles 36. 7. 2. <A href="/Tutorial/Oracle/0700__User-Privilege/CreatingRoles.htm">Creating Roles</a> 36. 7. 3. <A href="/Tutorial/Oracle/0700__User-Privilege/GrantingPrivilegestoRoles.htm">Granting Privileges to Roles</a> 36. 7. 4. <A href="/Tutorial/Oracle/0700__User-Privilege/GrantingRolestoaUser.htm">Granting Roles to a User</a> 36. 7. 5. <A href="/Tutorial/Oracle/0700__User-Privilege/AssignRoletoUser.htm">Assign Role to User</a> 36. 7. 6. <A href="/Tutorial/Oracle/0700__User-Privilege/CheckingRolesGrantedtoaUser.htm">Checking Roles Granted to a User</a> 36. 7. 7. <A href="/Tutorial/Oracle/0700__User-Privilege/CheckingSystemPrivilegesGrantedtoaRole.htm">Checking System Privileges Granted to a Role</a> 36. 7. 8. <A href="/Tutorial/Oracle/0700__User-Privilege/CheckingObjectPrivilegesGrantedtoaRole.htm">Checking Object Privileges Granted to a Role</a> 36. 7. 9. <A href="/Tutorial/Oracle/0700__User-Privilege/AssignCONNECTandRESOURCERoles.htm">Assign CONNECT and RESOURCE Roles</a> 36. 7. 10. <A href="/Tutorial/Oracle/0700__User-Privilege/DefaultRoles.htm">Default Roles</a> 36. 7. 11. <A href="/Tutorial/Oracle/0700__User-Privilege/RevokingaRole.htm">Revoking a Role</a> 36. 7. 12. <A href="/Tutorial/Oracle/0700__User-Privilege/RevokingPrivilegesfromaRole.htm">Revoking Privileges from a Role</a> 36. 7. 13. <A href="/Tutorial/Oracle/0700__User-Privilege/DroppingaRole.htm">Dropping a Role</a> 36. 7. 14. <A href="/Tutorial/Oracle/0700__User-Privilege/ReginaandKristenaremanagerlevelusers.htm">Regina and Kristen are manager level users</a> 36. 7. 15. <A href="/Tutorial/Oracle/0700__User-Privilege/RichandBradaremaintenancelevelusers.htm">Rich and Brad are maintenance level users</a> 36. 7. 16. <A href="/Tutorial/Oracle/0700__User-Privilege/UnassignRole.htm">Unassign Role</a> 36. 7. 17. <A href="/Tutorial/Oracle/0700__User-Privilege/DropRole.htm">Drop Role</a> 36. 7. 18. <A href="/Tutorial/Oracle/0700__User-Privilege/Assignobjectprivilegestoroles.htm">Assign object privileges to roles</a> 36. 7. 19. <A href="/Tutorial/Oracle/0700__User-Privilege/UnassignRolefromUser.htm">Unassign Role from User</a> 36. 7. 20. <A href="/Tutorial/Oracle/0700__User-Privilege/Outputsessionrolesfromprocedure.htm">Output session roles from procedure</a> 36. 7. 21. <A href="/Tutorial/Oracle/0700__User-Privilege/Queryusergrantedroles.htm">Query user granted roles</a>
Unassign Role
SQL>
SQL> CREATE USER Alice IDENTIFIED BY simplepassword;
User created.
SQL>
SQL> GRANT CONNECT, RESOURCE to Alice;
Grant succeeded.
SQL>
SQL> REVOKE RESOURCE FROM Alice;
Revoke succeeded.
SQL>
SQL> drop user alice;
User dropped.
SQL>
SQL>
SQL>
Unassign Role from User
SQL>
SQL> REVOKE TestRole FROM Alice;