Questions tagged [role]
A set of permissions.
282
questions
0
votes
1
answer
28
views
Postgresql - Create role to connect to database and revoke all other permissions
I have an application which is configured with the multitenancy strategy of "single database multi schema".
Now I want to create a role which can have only specific permission that is to :
...
- 101
0
votes
0
answers
29
views
Set role for database connection in java (spring boot with multitenancy)
For every http request there is tenant identifier based on which the schema is switched (datasource switching).
connection.setSchema(tenantIdentifier);
Now to secure the data access I am introducing ...
- 101
1
vote
0
answers
24
views
Can I actively renounce roles given to me?
I am a database developer. Over the many years working in the same company I have been granted many database roles. Many more than I should actually have.
I know I could make a list of the roles I don'...
- 193
1
vote
1
answer
266
views
How do I see which roles have `admin option` on them?
I've seen plenty of questions about someone trying to grant a role_specification to some other_role, but getting the GRANT error
ERROR: must have admin option on rolerole_specification
and the ...
- 345
0
votes
0
answers
180
views
Error in Login securables - SQL Server
I am using SQL Server 2017 and the latest SSMS version. When I click securables of login in SSMS, I see this error:
Object reference not set to an instance of an object. (SqlMgmt)
Program Location:
...
0
votes
1
answer
206
views
Postgresql: Even when I grant the owner role, I can't alter tables
I have a Postgres database with a schema named foo owned by a user also named foo, which also owns all of the tables in it.
While logged in as foo, I created a user bar and granted it everything foo:
...
- 113
2
votes
1
answer
110
views
Can a user with db_ddladmin elevate their privileges to db_owner
I have a question about DB-level roles in SQL Server, can a user with db_ddladmin elevate their privileges to db_owner or any other role?
Thank you.
- 21
2
votes
1
answer
485
views
In Postgres, is an unprivileged role able to execute a function with elevated privileges?
For context, I am using:
postgREST: a thin REST client on top of Postgres (which does all the heavy lifting)
Azure AD: which handles identity and access of the organization
Since users, roles, ...
- 143
1
vote
0
answers
66
views
PostgreSQL roles with cyclic membership
I have a PostgreSQL database that I would like to manage "like a MS SQL Server" database but only for authorized people. There are a few "business tables" to which they should have ...
0
votes
0
answers
141
views
"postgres" user/role does not exit upon installation of Postgres via Homebrew
I am new to Postgres and I have been following instructions from this site (https://www.codementor.io/@engineerapart/getting-started-with-postgresql-on-mac-osx-are8jcopb) in order to create a local ...
0
votes
0
answers
32
views
Application Role - Reset Password using hash or Encryption form
I need to automate reset password of database application role. Password must be encrypted or hash form due to security reason.
I tried to convert password in hash form using 'Hashbytes' as mentioned ...
0
votes
1
answer
56
views
Confirm server role of logged in user
I am adding a DB Admins LDAP group to multiple servers with sysadmin access. In this group is my domain account. I use this domain account to log in to a server via SSMS. My understanding is that a ...
- 37
0
votes
1
answer
102
views
MariaDB event privileges/grants
I'm having multiple databases in MariaDB and I'm trying to set up an automation task, that would take data from one database/scheme and insert them with some transformation to another one.
For this ...
- 111
0
votes
2
answers
91
views
Cannot alter created role
I am fairly new to SQL. I use microsoft sql server management studio. I used Master database. Created a server role. But when I try to add members to the role or grant it permissions it says it doesn'...
0
votes
0
answers
6k
views
"no schema has been selected to create in" even after opening up postgres permissions
diesel migration list
Failed with: no schema has been selected to create in
This is the error I'm trying to solve. I recently made some changes to roles and permissions in Postgres, and now my diesel ...
- 365
0
votes
1
answer
155
views
Restrict access to certain tables for users who have DBA roles?
I have an Oracle Database which includes many users and some of them have DBA roles. The thing is, DBA's shouldn't delete any records from SYS.AUD$ table. Is there a way that i can achieve this?
- 123
0
votes
1
answer
92
views
Make all users within group role inherit permissions
I have a group role that consists of around 1,000 users. Those users do not inherit group role permissions, and I would like to make them all inherit them instead.
What would be the way to do it ...
- 101
0
votes
1
answer
407
views
Azure SQL Vulnerability assessment error
In our SQL Managed Instance database, we created a user-defined database role and added 20 users to it. Now, during scanning by Microsoft Defender for Cloud, we are getting the following error/warning:...
- 289
1
vote
1
answer
90
views
Flattened view for multi-role permission system
I'm currently working on a multi-role permission system for one of my projects, my database (postgres) tables are setup like this:
user
id
username
email
…
user_role
id
user_id
role_id
role
id
...
- 13
0
votes
0
answers
229
views
RBAC Data Model Design
I'm trying to implement roles and permissions for an application I'm working on, from my understanding its an RBAC system.
There are Users, Sites, and for each site a user can have different ...
0
votes
2
answers
311
views
Grouping user into Role to access FDW table
Recently I setup Forward Data Wrapper. Here is what I do:
CREATE SERVER foreign_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '192.168.125.1', port '5432', dbname 'template1 ');
IMPORT ...
- 167
1
vote
2
answers
641
views
setting search_path to role doesn't seem to work in postgres
I've created a backend api that connects to postgres and performs various queries.
I've created a specific user (role + can login privilege) for that backend api:
backend_api_user
I've also dropped ...
- 121
0
votes
0
answers
1k
views
pg_dump permission denied for relation error for owner user
I am attempting to dump out a PostgreSQL database with pg_dump:
/usr/bin/pg_dump -f /home/crmpicco/rfc_754622.sql -F c -x -E UTF8 -b --disable-triggers -U root -h tillman.ff1lvb.us-west-2.rds....
- 145
0
votes
1
answer
995
views
How to give Read_write users access to foreign tables imported through foreign data wrapper?
I had a question regarding the extension postgres_fdw. I am trying to use postgres_fdw to import foreign tables from DB A to DB B. These databases are on the same host.
In Database B, I have a set of ...
0
votes
1
answer
373
views
Do Postgres Roles carry over from Databases if Databases are in the same server?
If I am creating a new Database on the same hostname/server, Will the roles/users carry over?
Or will I have to create them again with new names and passwords?
I see roles/users listed as server ...
0
votes
1
answer
2k
views
How to grant read-write access to a user within the whole database cluster?
I got a database cluster with 1300 databases. I created a role and a user and granted access using the command GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO myuser, so myuser ...
- 1
2
votes
1
answer
560
views
Error trying to add a login to Azure SQL Special Role 'dbmanager'
Upon running these commands on an Azure SQL instance:
CREATE LOGIN test WITH PASSWORD=N'Passw0rd'
GO
ALTER ROLE dbmanager ADD MEMBER test
GO
... I'm getting an error:
Cannot add the server principal ...
- 549
1
vote
1
answer
425
views
Is rdstopmgr an AWS RDS user?
I am running postgres on AWS RDS. RDS creates some users on its own, such as rdsadmin and rdsrepladmin. I am familiar with these
However, I also see rdstopmgr, when I query postgres for roles. Google ...
2
votes
0
answers
76
views
Default roles ticked when creating new login through SSMS GUI
When creating a new login via the GUI:
In the User Mapping section, certain databases will have some roles pre-ticked in the Database Role Membership section.
What controls this behavior? It's ...
- 21
1
vote
2
answers
2k
views
unable to drop user in PostgreSQL 12.4
I am unable to drop user as I am getting default privileges error.
postgres=# drop user xyz;
ERROR: role "xyz" cannot be dropped because some objects depend on it
DETAIL: owner of default ...
- 133
4
votes
1
answer
829
views
How to revoke SA permissions that seem to be "stuck" to a login?
Apologies in advance if I don't use some terminology correctly here. I'm not a DBA but I've been temporarily forced into the role.
I've inherited an application that runs on SQL Server 2016 ...
- 43
0
votes
1
answer
995
views
Proper roles for a backup-only user on PostgreSQL
I am working on new processes and policies for my PostgreSQL databases when they are first created, and I am trying to define what role attributes are necessary for a role specifically used for ...
- 37
0
votes
1
answer
3k
views
Create readonly user in postgresql
Using pg 13.4, created a readonly user reader for database abc, via:
CREATE ROLE reader WITH LOGIN PASSWORD 'psssswwwd'
NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION VALID UNTIL 'infinity'...
- 253
-1
votes
1
answer
241
views
What is the benefit of application roles instead of SQL Server authentication in SQL Server?
I have a database that is used by an application. This application implements all the business logic and control what a user can do according at its privileges.
I want to avoid users can access to ...
- 107
0
votes
1
answer
710
views
ALTER permission on database allows creation of security principals
It appears the ALTER permission granted at the database level allows the principal to which it is granted, to create security principals in the database.
Consider the below example:
/* setup database *...
- 1,928
0
votes
1
answer
911
views
SQL Server User permissions-Hide all tables and give/show access to some views
I am using SQL Server 2019. I have gone through some questions here and not found an answer. I am trying to not to show some tables (with a certain Schema e.g. Structure) for a SPECIFIC user in the ...
- 476
0
votes
0
answers
52
views
Creating sql server custom database role that only allows reading some columns and nothing else
I want to create an sql server Custom Database Role which only allows reading some columns of some tables, and nothing else. this is my attempt.
CREATE ROLE CustomDatabaseRole01
GO
GRANT ...
- 1
0
votes
1
answer
433
views
How do I give my non-Azure app access to Azure SQL Server without whitelisting an ip address?
I have an app that is deployed to Vercel. It is registered in Azure AD and uses AAD for authentication. I added my personal ip to the whitelist, and when I run it locally, everything works as it ...
- 639
0
votes
1
answer
114
views
Dropping everything in a database owned by a certain role -- not working
I want to delete everything in a certain database owned by a certain role. I've tried to do and nothing has been deleted.
sudo -u postgres psql
postgres=# \l
List ...
- 1
1
vote
1
answer
513
views
Give a user access to restore a database that they are owner on without the server role dbcreator
A outside vendor has a SQL user with db_owner rights on two databases, live and training. From time to time they would like to be able to backup live and restore on top of training. The easiest ...
- 13
1
vote
0
answers
36
views
"Friendly" role name since Azure PostgreSQL connection via AD security group role requires the AD group name as the username@pgservername?
With PostgreSQL's use of the rolename@pgservername and AD auth requiring the security group name as the role name, any user connecting via AD must know their AD security group name. I'd like to user ...
- 83
0
votes
1
answer
5k
views
How to add a login to the dbcreator role?
Using SSMS, I'm having trouble adding a new login to the server dbcreator role on my Azure SQL instance.
Here's the syntax I'm using:
CREATE LOGIN test WITH PASSWORD = 'SuperSecret!'
ALTER SERVER ROLE ...
- 549
1
vote
1
answer
1k
views
row level security using roles in SQL Server
I would like to implement row level security on all of my tables. Each table has a column named data_classification that has a list of values. Ideally I would like to create database roles which are ...
- 63
1
vote
0
answers
2k
views
Create multi-user database schema with roles, permissions, and services
I am in the process of designing a user administration database and I am stuck in the creation of the tables and their relationships, in order to explain myself below I expose the requirements of the ...
- 11
1
vote
0
answers
46
views
Azure SQL custom backup role
We are currently running an Elastic Pool within azure to host all our databases.
We need to back up these to on-premise as well in case of an emergency, and for it we are trying to use sqlpackage.
Now ...
- 11
2
votes
0
answers
98
views
Create a specific role in a replicated AZURE SQL database, which is in read mode
Can you give me some help with my Azure Sql, I am having a difficulty with assigning a new role to a database with "geo-redundancy".
My current structure is:
BD1 in zone_A replicated to ...
- 21
0
votes
2
answers
719
views
ALTER ROLE on replica
I have a primary server, with a secondary replicating it.
I want to change a role from LOGIN to NOLOGIN.
I can do this easily on the primary, but the change doesn't replicate to the secondary, and I ...
- 203
0
votes
1
answer
1k
views
Drop role postgresql
I just inherited an AWS RDS PostgreSQL 12.5 instance (so, no superuser access) and I'm required to do some cleanup and delete a bunch of groups/roles/users. I'm executing the following commands with ...
0
votes
1
answer
2k
views
Can't create schema for a db with PostgreSQL
Use psql -U postgres to login.
postgres=> CREATE DATABASE mydb;
postgres=> \c mydb;
You are now connected to database "mydb" as user "postgres".
When create schema, got
...
- 167
1
vote
2
answers
175
views
Create a view in a user database that can access selected system database tables
Using the principle of least privilege, what access should I give to a Role "Checklist_role" that has read permission to a view "MaintenanceJobs_view" in a user database "...
