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
<source lang="sql">
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></source>
Assign object privileges to roles
<source lang="sql">
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></source>
Assign Role to User
<source lang="sql">
SQL> SQL> GRANT TestRole TO Alice; SQL></source>
Checking Object Privileges Granted to a Role
You can check which object privileges have been granted to a role by querying role_tab_privs.
<source lang="sql">
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;</source>
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.
<source lang="sql">
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;</source>
Checking System Privileges Granted to a Role
You can check which system privileges have been granted to a role by querying role_sys_privs.
<source lang="sql">
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;</source>
Creating Roles
<source lang="sql">
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;</source>
Default Roles
<source lang="sql">
ALTER USER steve DEFAULT ROLE ALL EXCEPT overall_manager; SET ROLE NONE; SET ROLE ALL EXCEPT overall_manager;</source>
Dropping a Role
<source lang="sql">
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;</source>
Drop Role
<source lang="sql">
SQL> SQL> DROP ROLE TestRole; Role dropped.</source>
Granting Privileges to Roles
You can grant both system and object privileges to a role, as well as grant another role to a role.
<source lang="sql">
GRANT SELECT, INSERT, UPDATE, DELETE ON tableNames TO product_manager; GRANT CREATE USER TO hr_manager; GRANT product_manager, hr_manager TO overall_manager;</source>
Granting Roles to a User
You grant a role to a user using GRANT.
<source lang="sql">
GRANT overall_manager TO userName;</source>
Output session roles from procedure
<source lang="sql">
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></source>
Query user granted roles
<source lang="sql">
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</source>
Regina and Kristen are manager level users
<source lang="sql">
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.</source>
Revoking a Role
<source lang="sql">
REVOKE overall_manager FROM steve;</source>
Revoking Privileges from a Role
<source lang="sql">
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;</source>
Rich and Brad are maintenance level users
<source lang="sql">
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.</source>
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
<source lang="sql">
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></source>
Unassign Role from User
<source lang="sql">
SQL> SQL> REVOKE TestRole FROM Alice;</source>
