Home » SQL & PL/SQL » SQL & PL/SQL » Implement Profile, PW Policy & Roles (Oracle SQL Developer (XE) - )
Implement Profile, PW Policy & Roles [message #689713] |
Tue, 02 April 2024 12:52 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/5c8a213b3af48b1b65012d4c23892fe7?s=64&d=mm&r=g) |
noname369
Messages: 1 Registered: April 2024
|
Junior Member |
|
|
I am student and New to Oracle SQL. If I have asked the wrong question in the wrong place, please just tell me where the question should be asked. If the question does not make sense, that is mostly likely because I am New - so patience, please.
My assignment is related to the title, and I have the following:
CONNECT sys AS sysdba;
alter session set "_ORACLE_SCRIPT"=true;
--Create Users
CREATE USER DBSEC_ADMIN IDENTIFIED BY password DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP;
CREATE USER DBSEC_CLERK IDENTIFIED BY password DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP;
CREATE USER DBSEC_DEV IDENTIFIED BY password DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP;
--Create Profiles
CREATE PROFILE DBSEC_ADMIN_PROF LIMIT SESSIONS_PER_USER 5 CONNECT_TIME 480 IDLE_TIME 60 PASSWORD_LIFE_TIME 30 PASSWORD_GRACE_TIME 7;
CREATE PROFILE DBSEC_DEV_PROF LIMIT CONNECT_TIME 120 IDLE_TIME 120 CPU_PER_CALL 60 PASSWORD_LIFE_TIME 30 PASSWORD_GRACE_TIME 14;
CREATE PROFILE DBSEC_CLERK_PROF LIMIT SESSIONS_PER_USER 1 CPU_PER_CALL 5 CONNECT_TIME 480 IDLE_TIME 30 LOGICAL_READS_PER_CALL 100 PASSWORD_LIFE_TIME 30 PASSWORD_LOCK_TIME 3 PASSWORD_GRACE_TIME 14;
--Create Roles
CREATE ROLE DBSEC_ADMIN_ROLE;
CREATE ROLE DBSEC_CLERK_ROLE;
CREATE ROLE DBSEC_SUPERVISOR_ROLE;
CREATE ROLE DBSEC_QUERY_ROLE;
I am having trouble with this section - attempting to assign privileges to roles. Am I required to have a table called dbsec? If yes, is there a default table that can be set up or are specific parameters required?
--Assign Privileges to Roles
--GRANT SELECT, ALTER ON dbsec TO DBSEC_ADMIN_ROLE;
--GRANT SELECT, INSERT, UPDATE ON dbsec TO dbsec_clerk_role;
--GRANT SELECT, INSERT, UPDATE, DELETE ON dbsec TO dbsec_supervisor_role;
--GRANT SELECT ON dbsec.customer TO dbsec_query_role;
End of question / problem
I do not want to cheat or have the answer given to me - I am just looking for guidance and trying to figure out what I am doing wrong or what I do not know.
GRANT SELECT, ALTER ANY TABLE TO DBSEC_ADMIN_ROLE;
GRANT SELECT, INSERT, UPDATE ANY TABLE TO DBSEC_CLERK_ROLE;
GRANT SELECT, INSERT, UPDATE, DELETE ANY TABLE TO DBSEC_SUPERVISOR_ROLE;
GRANT SELECT ON DBSEC.CUSTOMER TO DBSEC_QUERY_ROLE;
--Assign Roles and Profiles to Users
ALTER USER dbsec_admin PROFILE dbsec_admin_prof;
ALTER USER dbsec_clerk PROFILE dbsec_clerk_prof;
ALTER USER dbsec_dev PROFILE dbsec_dev_prof;
GRANT dbsec_admin_role TO dbsec_admin;
GRANT dbsec_clerk_role TO dbsec_clerk;
GRANT dbsec_admin_role, dbsec_supervisor_role TO dbsec_dev;
--Connect as dbsec_clerk w/ password and perform a Query
CONNECT dbsec_clerk/inft6157clerk;
--Trying a different approach
SELECT * FROM DBSEC.SUPPLIER;
|
|
|
Re: Implement Profile, PW Policy & Roles [message #689715 is a reply to message #689713] |
Tue, 02 April 2024 14:55 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68653 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.
Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.
Many things are wrong in your script.
- Quote:
CONNECT sys AS sysdba;
Never do that for such a script.
Read SYS/SYSDBA is special.
- Quote:
alter session set "_ORACLE_SCRIPT"=true;
Never set underscore parameter without Oracle approval.
As you are new, NEVER do that.
- Quote:
Am I required to have a table called dbsec?
Yes.
- Quote:
If yes, is there a default table that can be set up or are specific parameters required?
No such table but you can create one in one of the schema your created or another specific schema to contain the objects you will create but NOT in SYS, SYSTEM or any Oracle schema maybe in DBSEC schema as your last query seems to suggest.
In this later caae the statement should be something like:
GRANT SELECT, INSERT, UPDATE ON dbsec.supplier TO ...
- Quote:
GRANT SELECT, ALTER ANY TABLE TO DBSEC_ADMIN_ROLE;...
Never grant %ANY% privilege (but for DBA in specific cases).
|
|
|
Re: Implement Profile, PW Policy & Roles [message #689718 is a reply to message #689713] |
Wed, 03 April 2024 01:35 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8932 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
I think there is some confusion here,--GRANT SELECT, ALTER ON dbsec TO DBSEC_ADMIN_ROLE;
--GRANT SELECT, INSERT, UPDATE ON dbsec TO dbsec_clerk_role;
--GRANT SELECT, INSERT, UPDATE, DELETE ON dbsec TO dbsec_supervisor_role;
--GRANT SELECT ON dbsec.customer TO dbsec_query_role; The first three grants assume that there is a table DBSEC in your current schema, which is SYS. THat is highly unlikely. The fourth grant is better: it refers to a table named CUSTOMER in the DBSEC schema. My guess is that you are expected to have a user DBSEC who owns a table CUSTOMER, and the first three grants should be on DBSEC.CUSTOMER
HTH, J.
|
|
|
|
|
|
|
Re: Implement Profile, PW Policy & Roles [message #689727 is a reply to message #689722] |
Wed, 03 April 2024 11:02 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68653 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Barbara Boehmer wrote on Wed, 03 April 2024 15:58...
For example, in 23c, you can now grant privileges on an entire user/schema, instead of just individual objects.
There is a nice article about that here:
https://oracle-base.com/articles/23c/schema-privileges-23c
...
Thanks for these information and link.
@noname369, pay attention at the "Considerations" section of this article.
[Updated on: Wed, 03 April 2024 11:03] Report message to a moderator
|
|
|
Goto Forum:
Current Time: Sun May 19 19:09:40 CDT 2024
|