Home » SQL & PL/SQL » SQL & PL/SQL » Implement Profile, PW Policy & Roles (Oracle SQL Developer (XE) - )
icon8.gif  Implement Profile, PW Policy & Roles [message #689713] Tue, 02 April 2024 12:52 Go to next message
noname369
Messages: 1
Registered: April 2024
Junior Member
Confused 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;

Report message to a moderator

Re: Implement Profile, PW Policy & Roles [message #689715 is a reply to message #689713] Tue, 02 April 2024 14:55 Go to previous messageGo to next message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
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).

Report message to a moderator

Re: Implement Profile, PW Policy & Roles [message #689718 is a reply to message #689713] Wed, 03 April 2024 01:35 Go to previous messageGo to next message
John Watson
Messages: 8962
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.

Report message to a moderator

Re: Implement Profile, PW Policy & Roles [message #689719 is a reply to message #689718] Wed, 03 April 2024 02:09 Go to previous messageGo to next message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Quote:
My guess is that you are expected to have a user DBSEC who owns a table CUSTOMER,

Or SUPPLIER as the last statement seems to suggest. Smile

Report message to a moderator

Re: Implement Profile, PW Policy & Roles [message #689722 is a reply to message #689719] Wed, 03 April 2024 08:58 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA
Senior Member
noname369,

Welcome to the OraFAQ forums.  

You have not said what Oracle version you are using.  You can find that by running:

SELECT * FROM v$version;

Please run that and post the result here.

I imagine that a school may have the newest version 23c that has newer features than most of us here have on our computers.
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

As to the DBSEC user/schema that may or may not be something new as well.  
You can query dba_users or all_users to determine if you already have such a thing:

select username from dba_users where username = 'DBSEC';

My best guess is that DBSEC is an abbreviation for database security.
You may find that you have it, but the account needs to be unlocked.

Report message to a moderator

Re: Implement Profile, PW Policy & Roles [message #689723 is a reply to message #689722] Wed, 03 April 2024 09:39 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA
Senior Member
A little internet research found some similar assignments and one said,

"DBSEC tables refer to the tables you created under previous project. DBSEC is the owner of those data."

So, I suspect that there is some DBSEC owner/schema that should have been provided or you should have created.

Report message to a moderator

Re: Implement Profile, PW Policy & Roles [message #689724 is a reply to message #689723] Wed, 03 April 2024 09:48 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA
Senior Member
Please see the following that indicates that you need to create a DBSEC user and customer table and provides the structure of that table that you need to create.


https://www.coursehero.com/file/213576576/Database-Security-Final-Project-Editeddocx/

Report message to a moderator

Re: Implement Profile, PW Policy & Roles [message #689727 is a reply to message #689722] Wed, 03 April 2024 11:02 Go to previous message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
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

Previous Topic: RETUNR FUNCTION USING "TYPE"
Next Topic: correct treatment of date across different timezones
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sun Dec 22 00:13:53 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.